Considerations and Comments
For purposes of this
exercise, we will create a calculated measure to house the MDX that
returns a moving average. We will be working within the Warehouse cube in the FoodMart
2000 sample database, which accompanies the installation of MSAS, along with
several other samples. If we want to keep our sample cube in its pristine,
original condition, we can simply discard our calculated measure upon the
conclusion of our session, or at any convenient time thereafter.
While the calculated
measure creation process is simple enough to follow, ensure that you have the
authority, access and privileges needed to accomplish the process, and that performing
these operations within the FoodMart 2000 database presents no other
issues in your environment.
If the sample MSAS database
was not installed, or was removed prior to your beginning this article, please
see the MSAS documentation, including the Books Online, for the
straightforward procedure to restore the database from the archive (.cab)
file containing the samples. As of this writing, a copy of the archive can be obtained
from the installation CD or via download from the appropriate Microsoft
site(s).
Hands-On Procedure
Preparation
Before
creating a calculated member in MSAS, be it for a dimension or a measure (as in
our present case), I often assemble the MDX involved in a more "direct"
environment. While I use many applications for this purpose, the one that
works best from the perspective of my articles is the MDX Sample Application
that installs along with MSAS, primarily because anyone with access to MSAS
probably has access to this tool. The Sample Application is useful as a
platform from which to construct and execute the MDX we examine, and to view
the results datasets we obtain.
We
will craft the MDX for a rolling average here, so as to get a focused view of
how it works, before installing the MDX into a calculated measure in Analysis
Services. To do this, we will take the following steps:
1.
Start the MDX
Sample Application.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the Warehouse
cube in the Cube drop-down list box.
Having received our
instructions from the information consumers, we will first create the core MDX
query. We will add the rolling average calculated measure into the core query next.
The core query might be viewed as a "frame" within which we are casting
the rolling average. We want to set that frame up in a way that we can quickly
verify operation of our MDX, before we permanently "install" the
calculated measure into the Warehouse cube structure.
The business
requirement involves quarters (at least in the initial specification),
across which the average of Warehouse Sales will move. In addition, the
moving average needs to be displayed specifically for U.S. Store States
(keeping in mind that we might additionally like to present the rolling average
for other dimensions at a later point in time).
Let's
construct the core query next.
5.
Type the
following query into the Query pane:
-- MXAS18-1, Preparation for Creation of Rolling Average Calculated Measure
SELECT
CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]})
ON COLUMNS,
{[Warehouse].[All Warehouses].[USA].Children} ON ROWS
FROM
[Warehouse]
6.
Execute the
query by clicking the Run Query button in the toolbar.
The Results pane is populated by Analysis
Services, and the dataset shown in Illustration 2 appears.
Illustration 2: Core Query
Populates the Results Pane
We see the four
quarters of 1997 and 1998, respectively (the two years in the
Warehouse cube), together with the Warehouse Sales measure, populating
the columns across, and the U.S. Store States (from the Store dimension)
appearing on the row axis. The core query thus provides the "frame"
we need to ascertain that the calculated measure we create next 1) accumulates
the base measure, Warehouse Sales, correctly, 2) over the window of the quarters.
7.
Select File
-> Save As, name the file MXAS18-1,
and place it in a meaningful location.
8.
Leave the
query open for the next section.
We will
use the Avg() function, in combination with the PeriodsToDate() function,
in the definition of the rolling average calculated measure. (For details
surrounding the PeriodsToDate() function, see my articles MDX
Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred
Functions and MDX in
Analysis Services: Mastering Time: Period - to - Date Aggregations).
We take
the following steps to create the calculated member, and place it within the
results dataset "frame" we have prepared.
9.
Within the
query we have saved as MXAS18-1, replace the top comment line of the
query with the following:
-- MXAS18-2 Rolling Average Calculated Measure Create and Retrieve
10.
Save the query
as MXAS18-2, to prevent damaging MXAS18-1.
11.
Type the
following into the Query pane, between the top comment line and the SELECT
statement already in place:
WITH
MEMBER
[Measures].[Rolling Avg]
AS
'Avg (LastPeriods (4, [Time].CurrentMember), [Measures].[Warehouse Sales])'
12.
Append the calculated
measure created in the WITH statement above, [Measures].[Rolling Avg],
to the first line of the SELECT statement, changing the following line:
CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]})
ON COLUMNS,
to
the following
CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales],
[Measures].[Rolling Avg]}) ON COLUMNS,
The
Query pane
appears as shown in Illustration 3, with our modifications circled.
Illustration 3: The Query
with Our Modifications
13.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated, and the dataset depicted in Illustration 4 appears.
Illustration 4: Result
Dataset - The Rolling Average Appears
» 