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 the way that measures are combined...