About the Series ...

This is the eighth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("Analysis Services,"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Introduction

In our last tutorial, Calculated Members: Introduction, we saw through practice that we can easily create and display calculated members within our queries to meet various business needs. We discussed the two main ways of handling the creation of calculated members within MDX, focusing on the use of the WITH operator to create a dynamic calculated member in an MDX query. Then, we practiced the creation of a straightforward calculated member, deriving a useful value from existing measures, and formatting the new calculated measure to meet an illustrated objective.

Our introduction to dynamic calculated members set the stage for the more advanced functionality and processes of calculated members that we will begin to explore in this and following lessons. We will reinforce our knowledge of calculated members, and then broaden our understanding through an examination of multiple additional perspectives of these highly useful components in our MDX toolset. In addition, we will explore a means for controlling the order in which calculated members are solved, discussing why precedence of calculation might be critical to generating the results we expect.

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:

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.

Now let's build the first of two calculated members. We need to generate the total Warehouse Margin for each state, and so need a calculated measure that gives us total Warehouse Sales less total Warehouse Cost.

WITH 

MEMBER [Measures].[Warehouse Margin] AS

'[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'

	,[Measures].[Warehouse Margin] 

The intent here is simply to add the new calculated member, Warehouse Margin, ([Measures].[Warehouse Margin]), into the SELECT statement so as to display it.

The complete, modified query, Step 3, should appear in the Query pane as shown in Illustration 5 below:

The results dataset appears in Illustration 6.

We will handle the formatting later. The important thing at present is that we appear to have achieved the desired result, and have derived and displayed the needed Warehouse Margin amounts with our new calculated measurer. Let's add calculation of the next part of the requirement, the Percent Change in margin between Quarter 2 (Q2) and Quarter 1 (Q1) of 1997, to our query next.

	MEMBER [Time].[% Change] AS

	  '([Time].[1997].[Q2] - [Time].[1997].[Q1])/[Time].[1997].[Q1]'

>[Time].[% Change]

The complete, modified query, Step 4, should appear in the Query pane as shown in Illustration 7.

The insertion we have made within the WITH clause (put in place for a calculated member in our last step) creates a calculated member called % Change; the addition of the newly defined calculated member in the SELECT clause instructs the application to return it as an additional column in our result set.

The results dataset appears in Illustration 8.

As we see above, formatting aside, we approach even more closely the objectives of the targeted audience. We now have a column that presents the % Change calculation between the quarters.

I make it a practice to save formatting for last when creating queries, writing reports, or anything related to the extraction / derivation, and subsequent presentation of, information, because much rework can be saved by avoiding the inevitable changes associated with adding to or modifying a report. While I may find it hard to resist the urge to "go ahead and clear up the picture," years of experience have made it clear that this is a key kind of discipline to have, especially with elaborate queries and complex reporting.

That being said, let's take a look at a basic formatting techniques. We can begin by making the % Change cells more compact for ease of use in the design.

	'100  *

The modified query should appear in the Query pane as shown in Illustration 9.

The result set appears as shown in Illustration 10.

We see the % Change column for the first two rows more closely resembling a percent figure, to which most of us can relate, with the presentation of the percent sign a matter for later resolution. This was easily accomplished by the simple addition of the multiplier - one of numerous ways (and not necessarily the most optimal in a Production setting) to accomplish the same task.

We will practice the addition of a couple of different strings, to format the percent and dollar value fields, in our remaining section below. For the time being, however, we need to focus on a more substantive consideration that arises in a case similar to the one we have traversed in our example. Our creation of two calculated measures, via the WITH statement in our last few steps, and the resulting value that appears at the intersects of the calculated measures - that is, in each of the cells formed by the % Change / Warehouse Margin calculated members' intersection - brings to light a consideration that is somewhat weightier than mere formatting concerns.