MDX in Analysis Services: Measuring Change over Time - Page 4
July 22, 2003
Analyzing the Effects of Time with MDX Expressions
MDX provides for the analysis of values over time with a large and robust group of functions that are well suited to the purpose. In this section, we will explore the ways we can use these functions to achieve our time-based analysis objectives.
Let's first look at a common scenario: the calculation of change over a period of time. As the basis of our study, we will use the calculated members we have created in the immediately foregoing section to expose the handling of this basic time consideration.
Analyzing Change in Values from a Prior Measurement Period
Near and dear to virtually any participant in business, but to a perhaps unnatural extreme to those in Accounting and Finance (just kidding - I'm a recovering CPA myself) is the concept of analyzing change over the prior operating period, particularly over the prior month. MDX handles time like any other dimension - within the familiar structure of the hierarchies and members that it handles so effectively. Let's add a time consideration to our existing project, and get a feel for the ease with which MDX enables the support of the time-based analysis needs of our information consumers.
At this point, we will use our new calculated members within an exploration of the ways we can use MDX time functions to achieve our time-based analysis objectives. After first making adjustments to help them to perform in a more adaptive manner, we will explore the steps required to build in time-based analysis features.
1. Double-click the USA member of the Store Country level (Rows axis), once again, to contract the USA Store State view, and to "drill up" to USA only.
The Data Viewing pane should appear as shown in Illustration 12.
2. Drag the Time dimension from the top pane down to replace the dimension in the Rows axis. The heading will appear as Year, with the levels 1997 and 1998 appearing just underneath.
3. Expand (by double-clicking) the 1997 and Q4 levels in the Row axis.
Compare the result set to that shown in Illustration 13.
We see immediately that, while the Amount measure changes to reflect our "slice and dice" operation, the Units Sold calculated member remains fixed - for all rows. We need to adjust our Units Sold calculation to reflect the new dimensions of our display. The Avg Total Cost Per Unit measure should, of course, adjust itself automatically, subsequent to our modifications to the Units Sold calculation; it is
merely derived from the Units Sold measure and the Amount measure, after all.
4. Right-click the Units Sold calculated member, once again.
5. Select Edit to open the Calculated Member Builder again.
6. Change the Value Expression for the calculated member to the following:
LookupCube("Sales","([Unit Sales],"+[Time].CurrentMember.UniqueName +")")
7. Click OK to apply the new expression.
The new dataset is returned, and appears as shown in Illustration 14.
We have simply replaced the [Store] dimension with the [Time] dimension in the expression to make our results "dimension -sensitive," as it were, and causing them to behave as expected for the different Time levels and members in the respective rows of the display.
Now let's add a calculated member that uses a time function - and presents a measure that is the equivalent of the existing Amount measure, with which it can be compared side-by-side to illustrate its nature.
8. Select All Account in the filter field for the Account dimension.
9. Select Insert --> Calculated Member
The Calculated Member Builder appears.
10. Type Prior Amount into the Member Name box.
11. In the Functions tree, expand the Member folder.
The present view within the Calculated Member Builder should appear as shown in Illustration 15.
12. Double-click the PrevMember function within the Member folder.
13. Single-click the <<Member>> token, within the Value Expression box, to select it.
The <<Member>> token should highlight to indicate selection, as partially shown in Illustration 16.
14. Double-click the CurrentMember function within the Member folder.
15. In the Value Expression box, single-click the newly appearing <<Dimension>> token to select it.
The <<Dimension>> token should highlight to indicate selection, as partially shown in Illustration 17.
16. Double-click the Time dimension within the Data tree.
The Value Expression box of the Calculated Member Builder now appears as shown in Illustration 18.
NOTE: We explore the .PrevMember and .CurrentMember functions, as well as other concepts that we have encountered in this series, in depth in the sister DatabaseJournal series, MDX Essentials. Please see the appropriate article in the series for an explanation of the respective MDX function(s).