More
about Calculated Members
Our introduction
to calculated members in our last session, together with the sampling of MDX
that we exposed in our practice exercises, has prepared us to evolve our
understanding further, and to look at more advanced concepts in the
construction of calculated members. Much as we did in our last lesson, we will conduct
our lesson with a practice example that will allow us to efficiently achieve
dual purposes: to review fundamentals we have introduced in previous lessons,
and to expose further considerations and perspectives of working with
calculated members.
We
will work through several steps of an exercise that will build into a multi-faceted
query illustrating several perspectives of working with calculated members. For
purposes of illustration, we will say that a group of information consumers has
asked us to assist with building a query to show the percent change in
the total gross margins for the state warehouse operations of the current
quarter (we'll imagine we are in the second quarter of 1997 to
fit the data in the FoodMart sample database) over the quarter before (that
is, Quarter 1, 1997). The query will rely upon several concepts that we will
discuss as we progress.
Let's begin
by taking the following steps:
1.
Open the MDX
Sample Application, and navigate to the main window as we have in previous
lessons.
The MDX
Sample Application window appears.
2.
Clear the top
area (the Query pane) of any remnants of queries 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.
The MDX
Sample Application window should resemble that shown in Illustration 1,
complete with the information from the Warehouse cube displaying in the Metadata
tree (left section of the Metadata pane).
Illustration 1: The MDX
Sample Application Window (Compressed)
5.
Type the
following simple query into the Query pane:
-- MXAS08: Tutorial Query - Step 1
SELECT
{[Time].[1997].[Q2], [Time].[1997].[Q1]} ON COLUMNS,
{ [Measures].[Warehouse Sales], [Measures].[Warehouse Cost]} ON ROWS
FROM Warehouse
Recall from our
previous lessons the use of the comment line. Our intent in the above query is
to set up the basis for further exploration. The - Step 1 comment is to
indicate the step of our build, and we will repeat this process at each step,
saving each before continuing, to have a "fallback" position, in case
we get off track and need to return to the "last known good configuration."
This has saved me many hours in lost re-creation time when just such "derailments"
have occurred, particularly if I am building logic on the fly, and
experimenting with each step, to achieve a specific result.
The
query we have typed above makes use of sets, indicated by the braces ( { }
), as we learned in Lesson
6, Using Sets in MDX Queries, to build the column and row axes.
6.
Execute the
query, by clicking the Run Query button.
The
results dataset appears in Illustration 2.
Illustration 2: The Query
- Step 1 Results Dataset
The
basics of calculating a total margin value have been retrieved: Even a
non-accountant can understand that Warehouse Sales and Warehouse Cost
will allow us to derive Warehouse Gross Margin (a term for "gross
profit" or "net sales minus cost of goods sold"), which
has not yet been defined, as a calculated member in the Warehouse cube.
For
purposes of our illustrative example, let's assume that we next need to break
the sales and cost information by state (the immediate requirement being for USA warehouse locations only).
7.
Save the query
as MXAS08-1-1.
8.
Select File
--> New to open a new query window.
9.
Type the
following into the Query pane:
-- MXAS08: Tutorial Query - Step 2
SELECT
{[Time].[1997].[Q2], [Time].[1997].[Q1]} ON COLUMNS,
CROSSJOIN([Store].[USA].Children ,{ [Measures].[Warehouse Sales], 9
[Measures].[Warehouse Cost]}) ON ROWS
FROM Warehouse
The
complete, modified query should appear in the Query pane as shown in Illustration
3.
Illustration 3: Modified
Query in the Query Pane
The query
above is simply a modification of Step 1. For some of us it is simply
easier to retype the entire query, although the alterations could certainly
have been handled using a copy of MXAS08-1-1.
10.
Execute the
query, by clicking the Run Query button.
The
results dataset appears in Illustration 4.
Illustration 4: The
Query - Step 2 Results Dataset
As we can
see, our query retrieves Warehouse Sales and Warehouse Cost data,
but this time it is broken out by the USA - State level. We managed
this nesting effect with a CROSSJOIN() function, which we explored in
detail in Lesson
Six.