over Time with MDX Expressions
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
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.
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.
Illustration 1: Sample
Cubes provided with the Analysis Services Installation
the Budget cube.
from the flyout shortcut menu.
When the Cube
Editor screen appears, click the Data tab at its lower left (Data
Viewing pane is activated, retrieving the view that was last saved, or
perhaps the default, if none was saved from previous lessons.
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
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.
Ensure that 1997
is selected in the filter field for the Time dimension atop the Data
contents of the Data Viewing pane now appear as shown below:
Illustration 2: The Data
Viewing Pane Contents after our Modifications (Compressed View)
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
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:
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.
Illustration 3: Select
Gross Sales as the Filter
the USA member of the Store Country
level (Rows axis) to explode to the USA Store State view of the Store
dimension for USA.
result set should be identical to that shown in Illustration 4.
Illustration 4: The
Results in the Data Viewing Pane (Compressed View)
our earlier lesson, we notice that the Budget cube contains data for USA
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.
-> Calculated Member from the
Member Builder appears.
Sold into the Member Name box.
In the Value
Expression box, input the following expression:
Member Builder appears as shown in Illustration 5.
5: The Calculated Member Builder, with New Expression Entered
Click OK to accept the
expression entered as above.
The Data Viewing pane
appears as shown below.
Illustration 6: The
Sales Units Data as Retrieved from the Sales Cube
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.