Professional SQL Server™ Analysis Services 2005 with MDX

9.7. Adding Intelligence to the Cube

Similar to adding intelligence to Dimensions that you learned in Chapter 8 you can add intelligence to the Cube. Figure 9-48 shows the various enhancements that can be done to a Cube using the Business Intelligence Wizard. You have learned most of these enhancements in earlier chapters. In this chapter you will learn the enhancements to define and understand semi-additive behavior as well as defining currency conversion whenever fact data needs to be converted to appropriate local currency.

Figure 9.48. Figure 9-48

9.7.1. Semi-Additive Measures

Semi-additive measures are measures whose data is not aggregated as sum or count to over the various levels on a hierarchy. The semi-additive aggregate functions for measures are ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty, LastNonEmpty and None. For example, assume you have a Time hierarchy and the measure Sales value to be rolled up average of the sales of it's children. Assume the levels in the Time hierarchy are Year, Half Year, Quarters, Months and Date. If you have a member Quarter 1 of the year 2004 whose children are months July, August and September then the value for Quarter 1 will be average of of the sales values for the three months.

Using the Business Intelligence Wizard you can change the behavior of the Aggregation Function of various measures in the cube. Launch ...