Page 124 - DCAP606_BUSINESS_INTELLIGENCE

Unit 8: Advanced Measures and Calculations

Another cube’s dimension tables, Retail_Stores, are shown below. Notes
Table 8.4: Dimension Table Retail_Stores

Retail_ Retail_Store_ Retail_Store_ Retail_Store_
Store_ID Name Address_Line_1 Address_Line_2
1 A 1 A Avenue Atown, AA 55555
2 B 2 B Avenue Btown, BB 55555
3 C 3 C Avenue Ctown, CC 55555
4 D 4 D Avenue Dtown, DD 55555

Source: http://msdn.microsoft.com/en-us/library/ms365396.aspx
The cube’s final dimension table, Products, is shown here.
Table 8.5: Dimension Table Products

Product_ID Product_Name Product_Description Product_Category
1 A aaaa aaaa aaaa AB
2 B bbbb bbbb bbbb AB
3 C cccc cccc cccc CD
4 D dddd dddd dddd CD
Source: http://msdn.microsoft.com/en-us/library/ms365396.aspx

Sum

If a measure’s Aggregate Function property value is Sum, the measure value for a cube cell is
calculated by adding the values in the measure’s source column from only the rows for the
combination of members that defines the cell and the descendants of those members.
The following examples return values that represent accumulated Sales:

A query on the Sales measure for customer A, retail store A, and product A returns 800.
A query on the Sales measures for customer A, retail store A, and product category AB
returns 900.
A query on the Sales measure places each retail store on the x-axis, nests products under
product categories on the y-axis, and slices by All Customers. It returns the result as
shown in Table 8.6.

Table 8.6: Result of Query

All Retail Stores A B C D
All Products 4250 1600 380 620 1650
AB 1740 900 380 110 350
A 1440 800 290 350
B 300 100 90 110
CD 2510 700 510 1300
C 1060 510 550
D 1450 700 750

Source: http://msdn.microsoft.com/en-us/library/ms365396.aspx

LOVELY PROFESSIONAL UNIVERSITY 119