Page 126 - DCAP606_BUSINESS_INTELLIGENCE

Unit 8: Advanced Measures and Calculations

A query on the Sales measure places each retail store on the x-axis, nests products under Notes
product categories on the y-axis, and slices by All Customers. It returns the result as
shown in Table 8.8.

Count

If a measure’s Aggregate Function property value is Count, the measure value for a cube cell is
calculated by adding the number of 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 the number of Sales transactions.
A query on the Sales measure for customer A, retail store A, and product A returns 3.

A query on the Sales measures for customer A, retail store A, and product category AB
returns 4.
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.9.

Table 8.9: Result of Query

All Retail Stores A B C D
All Products 12 5 2 2 3
AB 8 4 2 1 1
A 5 3 1 1
B 3 1 1 1
CD 4 1 1 2
C 2 1 1
D 2 1 1

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

Self Assessment

Fill in the blanks:
3. .......................... = SUM([Profit]/ [Sales])
4. The division is performed after each measure is ..............................

5. 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 ....................................
6. If a measure’s Aggregate Function property value is .............................., the measure value
for a cube cell is calculated by taking the lowest value in the measure’s source column.
7. If a measure’s Aggregate Function property value is Max, the measure value for a cube
cell is calculated by taking the .............................. value in the measure’s source column.

8.3 Retrieving Data from a Cube Using MDX Queries

To use facts and figures from an analysis Services cube in your report, you should define an
Analysis Services data source and create one or more report datasets. When you define the data

LOVELY PROFESSIONAL UNIVERSITY 121