Creating a Calculated Measure
Using Data from Two Different OLAP Cubes
Next, let's meet
another business need, and create another expression to compute the average
total revenue (or "gross sales") per unit sold as a "quick
and dirty" metric to use in perhaps identifying significant outliers
(realizing, of course, that this would only be a rough indicator, that would
perhaps serve to identify further, more precisely designed analysis
opportunities).
22. Select Insert (top menu).
Select calculated member on the drop-down menu.
The Calculated
Member Builder appears.
23.
Type Avg
Rev Per Unit Sold into the Member Name box. In the Value Expression
box, type the following expression:
[Amount]/[Sales Units]
24.
Click OK
to accept the expression entered.
The Data
Viewing pane appears as shown below.
Illustration 9: The
Sales Units Data with the Avg Revenue per Unit Sold Calculated Member
NOTE: If, at this juncture or at any
point within the lesson, the column order of the results obtained differs from
that shown in the illustrations, simply drag the calculated member field(s) to
the correct position in the cube tree
We obtain
the desired results for Avg Revenue per Unit Sold for the USA Stores,
The Canadian and Mexican stores contain no data at the levels under
consideration.
25.
Click OK
to apply the new expression.
For the purpose of general "beautification," let's
make a couple of format adjustments.
26.
Ensure the Avg Revenue per Unit Sold calculated member is selected in the Cube tree.
27.
Select the Basic
tab of the Properties pane, which appears underneath the Cube tree,
if not already selected.
28.
Select the Parent
Dimension property of Avg Rev Per Unit Sold, on the Basic
tab.
29.
Ensure that
the Measures dimension is selected (via the drop-down arrow).
30.
Click the Advanced
tab in the Properties pane.
31.
Select the Format
String property of Avg Rev Per Unit Sold.
32.
Click the
drop-down arrow.
33.
Select Currency
as the format for the string.
The Advanced
tab in the Properties pane appears as shown in Illustration 10.
Illustration 10: The
Format String Setting in the Advanced Properties Pane
34.
Press Enter.
35.
Select Sales
Units in the Calculated Members folder for the Budget
cube.
36.
Select the Basic
tab in the Properties pane.
37.
Select the Parent
Dimension property of Sales Units, on the Basic tab.
38.
Ensure that
the Measures dimension is selected (via the drop-down arrow).
39.
Click the Advanced
tab in the Properties pane.
40.
Select the Format
String property of Sales Units.
41.
Click the
drop-down arrow.
42.
Select #,#
(whole units with thousands separator - no decimal placed) as
the format for the string.
43.
Press Enter.
The Calculated
Member Builder closes, and the new dataset returned appears as shown in Illustration
11.
Illustration 11: The
Dataset with New Formatting Refinements
We see both the Sales
Units and the Avg Rev Per Unit Sold calculated members appear,
complete with our formatting specifications.
Next
in Our Series ...
In this
tutorial, we exposed the use of the MDX LookupCube
function within Analysis Services, as a means of retrieving values from
multiple cubes simultaneously. This offers us the often useful option of
accessing multiple OLAP data sources together for analysis and
reporting, an example real-world scenario of which we presented in the lesson. In
addition, we demonstrated how we could compute a per-unit average,
within the context of providing a Revenue per Unit Sold measure, based
upon values retrieved from two separate OLAP data sources, further exploring
and practicing the use of calculated members as part of the process.
In our
next lesson, Measuring Change over Time, we will explore the use of MDX functions that
incorporate the concept of time, within the context of expression design. We
will practice incorporating support for time-based analysis, such as the
quantification of change in values over time, with MDX functions that are
suited for that purpose. We will perform exercises to reinforce these concepts
within the context of our OLAP data sources. Finally, within the perspective of the subject
matter, we will practice further the use of calculated members, including the
creation of a variance calculation.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
