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.
Illustration 12: The
Results in the Data Viewing Pane
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.
Illustration 13: The Results in the Data Viewing Pane
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.
Illustration
14: The Results in the Data Viewing Pane with Unit Sales Modified Expression
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.
Illustration 15:
Preparing to Add the Expression (Compressed View)
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.
Illustration 16: Select
the <<Member>> Token
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.
Illustration 17: Select
the <<Dimension>> Token
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.
Illustration 18: The
Expression Takes Form
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).
» 
Comment and Contribute
