Using a utility dimension to implement time-based calculations

The MDX language implemented in SQL Server Analysis Services offers various time-related functions. Chapter 3, Working with Time showed how they can be utilized to construct useful sets and calculations. The main focus there was to show how to make OLAP cubes time-aware; how to detect the member that represents today's date and then create related calculations.

In this chapter we meet time functions again. This time our focus is to generate time-based calculations in the most effective way. One way to do it is to use the built-in Time Intelligence Wizard, but that path is known to be problematic. Best practice says we need a separate utility dimension instead, a utility dimension with members representing common time-based calculations like the year-to-date, year-over-year, previous period, and so on.

The idea behind the utility dimension is to minimize the number of calculations in a way that measures are combined with members...