MDX in Analysis Services: Measuring Change over Time - Page 2
July 22, 2003
Measuring Change over Time with MDX Expressions
As our next topic in meeting the business requirements of information consumers, we will explore the Time dimension, a component of the vast majority of cubes that are created. Financial and other business reporting needs almost always contain a time element, the purposes of which typically include the isolation of results to a specific reporting period, providing a basis for comparison of the current period results to those of the immediately preceding period (be it a year, quarter, month, or whatever), or providing a means for comparing parallel periods (Analysis Services terminology for the current month, but in the prior year, as an example) to build in seasonality factors for the business, ascertain the effectiveness of new initiatives (such as marketing campaigns) begun in the current quarter over the last quarter, and so on.
Preparing for our Examination of the Time Functions: A Review of Calculated Members
To prepare for our exploration of the MDX time functions let's open Analysis Manager (see previous tutorials if you need a refresher on Analysis Manager, or if you are joining the current series with this article), and create a new calculated member within the Budget cube. This will provide a "fresh start," and allow us a review of a few steps we accomplished before, in keeping with the desire to maintain our lessons as standalone tutorials, wherever possible, and for the purpose of continuity and portability.
1. From the Analysis Manager console, within the FoodMart 2000 database sample, expand the Cubes folder (seen in the Illustration 1), by clicking the "+" sign to its left.
2. Right click the Budget cube.
3. Click Edit from the flyout shortcut menu.
4. When the Cube Editor screen appears, click the Data tab at its lower left (Data Viewing pane).
The Data Viewing pane is activated, retrieving the view that was last saved, or perhaps the default, if none was saved from previous lessons.
5. Drag the Store dimension from the top pane down and over to the current heading atop the Rows axis. (You can drop the icon that appears over the heading currently in place - a small, double-headed arrow appears at the "drop" point), to "swap" the current dimension to the top and Store below (it will appear as Store Country, the label that appears physically above All Stores (technically the top level), in the current dimension's old place).
6. Drag the Measures dimension from the top pane down to replace the dimension in the Columns axis, unless it is already there. The heading will appear as MeasuresLevel, with the measure Amount appearing just underneath.
7. Ensure that 1997 is selected in the filter field for the Time dimension atop the Data Viewing pane.
The contents of the Data Viewing pane now appear as shown below:
Let's create a calculated measure similar to the one we created in the last article of our series, Retrieve Data from Multiple Cubes, computing an average based upon two measures. Once again, we will create a calculated example metric that might be useful to information consumers at a high level, perhaps in 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)
Our first focus will be to retrieve the Sales Units data from the Sales cube (another sample cube that comes along with the Analysis Services installation), just as we accomplished in our last lesson. To that end, we will use a "lookup" function to create a calculated member based upon components in two separate cubes. We will take the following steps to proceed:
8. Click the filter field for the Account dimension, and, from the dropdown hierarchy that appears, click the "+" signs continually to expand to, and select, Gross Sales, as shown in Illustration 3.
9. Double-click the USA member of the Store Country level (Rows axis) to explode to the USA Store State view of the Store dimension for USA.
The result set should be identical to that shown in Illustration 4.
As in our earlier lesson, we notice that the Budget cube contains data for USA Stores only.
Next, let's create a new calculated measure as a conduit from the sample Sales cube, to bring in data that we require for our current activities.
10. Select Insert -> Calculated Member from the top menu.
The Calculated Member Builder appears.
11. Type Units Sold into the Member Name box.
12. In the Value Expression box, input the following expression:
The Calculated Member Builder appears as shown in Illustration 5.
13. Click OK to accept the expression entered as above.
The Data Viewing pane appears as shown below.
For more information on the rationale behind the expression we have entered above, see our last lesson in this series, Retrieve Data from Multiple Cubes.