The Right Place for Calculated Members

Suppose you have an OLAP application with 25 or 30 measures and you want to add some calculations, such as Year-To-Date Total, Year-To-Date Average, Growth Rate Current Year vs. Previous Year, and so on. People usually add these calculations to the Measures dimension. So, if you have 25 measures and you want to define 4 or 5 calculations, you have to define more than 100 calculated measures. This simple example almost reaches SQL Server 7.0 OLAP Services' limit on the number of measures allowed per cube, which is 128. SQL Server 2000 Analysis Services lifts this limit to 1024 measures per cube. Still, this approach requires a lot of work to define and manage all the calculated measures and is confusing to end users, who will see a long list of measures with similar names.

An alternative is to add a dimension to your cube to hold these calculations. Let's look at an example of an additional dimension called Periodicity, which has a single input member called Periodical that you assign all input data to. You define all the calculations in this dimension, for example:

([Year-To-Date Total] as 'Sum({Ytd(),[Periodicity].[Periodical])'

The multidimensional nature of the OLAP model creates a Year-To-Date Total for each existing measure and for any measure that you might add in the future. More than that, the end user can easily slice the data by measure and by the new Periodicity dimension.