MDX in Analysis Services: Retrieve Data from Multiple Cubes - Page 4
June 24, 2003
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:
24. Click OK to accept the expression entered.
The Data Viewing pane appears as shown below.
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.
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.
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.