MDX in Analysis Services: Calculated Members: Further Considerations and Perspectives

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:

The MDX Sample Application window appears.

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).

	-- 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.

The results dataset appears in Illustration 2.

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).

	-- 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.

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.

The results dataset appears in Illustration 4.

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.