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

Monday Oct 27th 2003 by William Pearson
Share:

Explore further considerations with the creation and use of calculated members to meet business needs. Join author Bill Pearson in an extended practice example, in which he introduces more advanced concepts, such as solve order, in the ongoing examination of calculated members.

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:

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.

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.


11.  Save the query with changes as MXAS08-1-2.


12.  Modify the comment line to read -- MXAS08: Tutorial Query - Step 3.


13.  Save the query with changes as MXAS08-1-3.


14.  Insert the following statement into the Query pane, between the comment line and the SELECT keyword.

WITH 
MEMBER [Measures].[Warehouse Margin] AS
'[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'

15.  Insert the following into the SELECT clause, between [Measures].[Warehouse Cost] and }) ON ROWS:

	,[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:


Illustration 5: Modified Query in the Query Pane

16.  Execute the query, by clicking the Run Query button.

The results dataset appears in Illustration 6.


Illustration 6: The Query - Step 3 Results Dataset

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.

17.  Save the query with changes as MXAS08-1-3.

18.  Modify the comment line to read -- MXAS08: Tutorial Query - Step 4.

19.  Save the query with changes as MXAS08-1-4.

20.  Insert into the Query pane, between the WITH clause (do not place a comma after [Measures].[Warehouse Cost], a common error), added in query Step 3 above, and the SELECT clause, the following:

	MEMBER [Time].[% Change] AS
	  '([Time].[1997].[Q2] - [Time].[1997].[Q1])/[Time].[1997].[Q1]'

21.  After the [Time].[1997].[Q1] portion of the SELECT clause, and within the braces, add a comma, then the following line below it, just before the close brace (}) and ON COLUMNS:

>[Time].[% Change]

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


Illustration 7: Modified Query in the Query Pane

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.

22.  Execute the query, by clicking the Run Query button.

The results dataset appears in Illustration 8.


Illustration 8: The Query - Step 4 Results Dataset

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.

23.  Save the query with changes as MXAS08-1-4, again leaving it open for the next steps.

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.

24.  Insert the following just ahead of the ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' part of the WITH clause (last line of the clause):

	'100  *

25.  Change the comment line to read -- MXAS08: Tutorial Query - Step 5.

26.  Save the query with changes as MXAS08-1-5.

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


Illustration 9: Modified Query in the Query Pane, Step 5

27.  Execute the query.

The result set appears as shown in Illustration 10.


Illustration 10: The Query - Step 5 Results Dataset

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.

28.  Save the query with changes as MXAS08-1-5, leaving it open for the next steps.

Specifying Precedence of Calculation

In the multiple-step example above, we have created two calculated members, Warehouse Margin and % Change, to meet the demands of the information consumers we have referenced throughout our lesson. We placed the % Change member on the column axis, and we made the Warehouse Margin member a row axis member, through our query design.

The two measures intersect, in our example, at the cells whose coordinates are the % Change column and the Warehouse Margin row for each of the three cities, within the result dataset of the MDX query. Our focus at this stage is the behavior of this resulting "corner" cell: Depending on the order in which the two calculations are processed, the result in the "corner" (or intersect) cell is different.

We can easily assume that what the consumers want here is a result that represents the change in overall Warehouse Margin between Q2 and Q1. The number that we see is apparently not correct from that perspective. This brings us to another implicit requirement for our information product: We need to be able to dictate, in this and similar situations, which calculation takes precedence in the design of our query.

MDX provides a solution in the form of the Member Solve Order property. The solve order is specified when the member is created, and thus becomes a feature of the WITH clause we have constructed. Member Solve Order is designated through the assignment of a number, which dictates that the member(s) with the higher assigned number(s) be processed after the member(s) with the lower number(s), based upon the results of the preceding calculation(s). The number is defaulted to zero if no positive integer value is specified. Member Solve Order allows us to control the precedence of calculation of the members, as we shall see.

To restate our objective within this step, we need to derive and present the change in Warehouse Margin between Q2 and Q1, not to present an amount based upon the difference in the Warehouse Sales and Cost delta values. We thus want the Warehouse Margin member to be calculated first, or to have precedence, and the % Change member to be calculated thereafter. We accomplish our objective by taking the following steps within the query that we have saved as MXAS08-1-5 above, and left open for the next actions.

29.  Modify the comment line to read -- MXAS08: Tutorial Query - Step 6.

30.  After the '[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]', portion of the first WITH clause, add a comma, then add the following line below it, just above the MEMBER [Time].[% Change] AS line:

	SOLVE_ORDER = 1

31.  After the '100 * ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' portion of the WITH clause, add a comma, then add the following line below it, just above the SELECT keyword:

	SOLVE_ORDER = 2

The modified query should appear, in its entirety, in the Query pane as depicted in Illustration 11:


Illustration 11: Step 6, with Modifications Noted

32.  Execute the query.

The results dataset appears in Illustration 12:


Illustration 12: Step 6 Results Dataset

The results match our expectations, and allow us to substantially meet the requirements of the information consumers.

33.  Save the query with changes as MXAS08-1-6, leaving it open for the next steps.

We have now completed the derivation of the desired information, and set up the layout for its presentation, through our query design. Provided no further calculations or other design criteria are imposed, this might be a good time to clean up our formats. We will put in place some formatting instructions, which are typically added in the same area of the WITH clause (where the calculated members and their attributes are defined), just behind the MEMBER SOLVE ORDER property values, as we will see in the next steps.

34.  Modify the comment line to read -- MXAS08: Tutorial Query - Step 7.

35.  Remove the 100 * that we inserted just ahead of the ([Time].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' part of the WITH clause (in Step 5 above).

36.  Insert a comma, followed by the text below, after the SOLVE ORDER = 1 member property instruction for the Warehouse Margin calculated member we created in the WITH clause of the query:

	FORMAT_STRING = '#,##0.00;(#,##0.00)'

37.  Insert a comma, followed by the text below, after the SOLVE ORDER = 2 member property instruction for the % Change calculated member we created in the WITH clause of the query:

	FORMAT_STRING = 'Percent'

The modified query should appear, in its entirety, in the Query pane as depicted in Illustration 13:


Illustration 13: Step 7, with Modifications Noted

38.  Execute the query.

The result set appears in Illustration 14:


Illustration 14: Step 7 Results Dataset

We draw nigh unto our objectives in meeting the needs of the intended audience. The recovering CPA in me tends to be accustomed to the amount at the top of a column being preceded by the dollar sign; this and other formatting can be easily accomplished - there are many options available.

39.  Save the query with changes as MXAS08-1-7.

Using the WITH clause, we have created the two calculated members that deliver the desired information specified by the information consumers. Inside the WITH clause lies the MEMBER clause that actually does the work for us (we can also create Named Sets, a subject we touched upon in Lesson Six, and in articles in our other DatabaseJournal series, and which we will revisit in later lessons). The MEMBER clause allows us to create multiple calculated members, as we have seen in our practice exercise.

Throughout our lesson, we have focused on the most common form of calculated member that we can expect to encounter in the business environment, the calculated measure (a member of the measures dimension). We will continue our exploration of calculated members in subsequent lessons, indeed, throughout the entire series.

Summary and Conclusion ...

In this lesson, we resumed our exploration of calculated members where we left off in Lesson Seven, where we set the stage for the more advanced functionality and processes of calculated members that we began to explore in this lesson, and will continue to examine in subsequent lessons. We discussed ways of handling the creation of calculated members with MDX, and practiced adding multiple calculated members in a single MDX query, discussing and exploring additional perspectives of these MDX components along the way, including format considerations. Finally, we explored 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, and examining an instance where the SOLVE_ORDER keyword made it possible to bring about the desired results in a returned dataset.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved