MDX in Analysis Services: Calculated Members: Introduction

This is the seventh 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, Using Sets in MDX Queries, we began an exploration of MDX queries. We discussed the MDX expressions and MDX query statements, and as part of this overview, discussed key MDX concepts and terminology. We followed our overview with an exploration of MDX query building from the ground up, using the MDX Sample Application as a vehicle for crafting our statements and practicing their use. We then delved into set functions, and the creation and use of sets, discussing their importance in our MDX queries. We built the specification of members, and the combination of multiple dimensions, into our row and column axes, to practice the addition of truly multidimensional capabilities into the reports we produce for ourselves, and for the information consumers whom we support.

Through this lesson, we will introduce calculated members as a starting point for a group of articles to come, where we will focus on building and using calculated members within our queries to meet various business needs. We will begin by previewing the creation of calculated members to set the stage for the functionality and processes we will explore together. We will discuss the two main ways of handling the creation of calculated members within MDX, focusing on the use of the WITH operator to specify a calculated member in an MDX query. Finally, we will practice the creation of a calculated member in the article, building our expertise for more advanced subsequent articles.

Introducing Calculated Members

Our knowledge of sets from the previous lesson, Using Sets in MDX Queries has prepared us to create our first, simple calculated member. To this end, we will build a quick "starter" example to introduce fundamentals that will take us further into our exploration of MDX queries. We shall return to calculated members many times throughout the series, often using them to frame the delivery of concepts and processes. We shall also incorporate, as we progress through the series, aggregation and other functions into our calculated members, along with an array of other uses, but, for now, let's see what we can accomplish based upon what we have already learned, coupled with a taste of things to come.

We will start with the query with which we left off in our last lesson, first typing it into the Sample Application from scratch, so that we can begin this lesson completely independently. We will begin by taking the following steps:

We are initially greeted by the Connect dialog, shown in Illustration 1.

Illustration 1 above depicts the name of my server, MOTHER, and properly indicates that we will be connecting via the MSOLAP provider (the default).

3. Clear the top area (the Query pane) of any remnants of queries that might appear.

4. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5. Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown in Illustration 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

6. Type the following query into the Query pane:


SELECT 

{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], [Measures].[Units Shipped]} 

	ON COLUMNS,

NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS

FROM Warehouse

This is the last query that we constructed and ran in the previous article in the series, Using Sets in MDX Queries. For more background on the query itself, or for more explanation of the concepts that we sought to illustrate through its use, please see the article.

7. Click the Run Query button on the toolbar to execute the query.

We will make a change to the query to give it more flexibility in illustrating our current focus topic.

8. Change the query from the following (area to change is annotated in a blue):


SELECT 

{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], [Measures].[Units Shipped]} 

	ON COLUMNS,

NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS

FROM Warehouse

to the following:


SELECT 

[Measures].Members ON COLUMNS,

NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS

FROM Warehouse

In essence, we are only changing the query to retrieve all measures as columns, instead of only the three measures we specified earlier.

9. Type the following into the Query pane, above the SELECT keyword:

	-- MXAS07:  Tutorial Query No. 1

This is a "comment line." A comment line can actually consist of anything that is appropriate and useful to the designer / creator, and perhaps subsequent users, of the query. The double dashes ("--") functionally isolate, or "comment out," the text, so that it does not affect the operation of the query, but allows us to make notes that might be of use. Our present comment is simply used to identify the query. We might, alternatively, have left information here that would be of use to those who inherit or further evolve our work, such as our reasoning in the design and operation of the query, the author's name and location, etc. Moreover, there are additional considerations for the use of comment lines that we will broach at a later time. The present example of its use will serve as a means of identification for queries and expressions - a practice which we will use going forward in the series.

10. Save the query as MXAS07-1.MDX, in an appropriate location, keeping it open.

11. Click the Run Query button.

To the set of measures we have retrieved, we will add a simple calculated member to provide our information consumers with an Average Revenue Per Unit figure that can be used (as in the present case) in very rough comparisons between Store States, as a simple illustration of the steps involved.

12. Modify the query to add an Avg Rev Per Unit calculated member by placing the following statement between the comment line we added above and the SELECT keyword:




WITH MEMBER [Measures].[Avg Rev Per Unit] AS 

'[Warehouse Sales]/[Units Shipped]'

As we learned in our previous lesson, as well as indirectly in other lessons, MDX allows us to perform calculations on ranges of cells, leveraging our programming efforts over those of programs that require us to implant them at the cell level. We are able to place calculations in our query axes, making them new members of the dimensions involved. The cells that appear within these members present the query results.

Within the realm of MDX, calculated members can be created a couple of main ways. The first method of creation, using the WITH operator, will be our focus in this and numerous subsequent lessons. The WITH operator in an MDX query creates a dynamic calculated member; that is, the member, and therefore the calculation that the member performs, only exists during the runtime life of the query. The second means of creating a calculated member is through the use of the CREATE MEMBER statement. Calculated members that are born this way are "permanent," and can be made visible to all clients that can access the cube in which the calculated member is housed. Non-query-defined calculated members are beyond the scope of our current lesson, although we will visit them in later tutorials.

The Query pane display, with modifications in place, should be identical with that shown in Illustration 5.

The results dataset that appears is identical to the result set that we obtained before adding the new calculated member. This presents an opportunity to make a further "adjustment" to the .Members function we added above, and to illustrate how our queries can be altered to make calculated members in a cube visible in a results dataset.

The Query pane display, with modifications in place, now appears as depicted in Illustration 6.

We now see the existing calculated member - a calculated measure (the most common type of calculated member), and therefore a member of the Measures dimension, as we have stated in earlier lessons. The .AllMembers function allows us to see calculated members as well as the regular members.

Our calculated member is dynamically created in the MDX query, as opposed to having been created in the cube structure; as we noted above, its life extends only for the runtime of the query. Calculated members that we create via the CREATE keyword, or, more visually, through the Analysis Services interface, are permanent and available for retrieval by any process with appropriate access.

To finalize, let's format (rounding to one-tenth of a cent) the lengthy strings of digits that have been spawned by the Sample Application. We add the following instruction to the end of the WITH clause we used to create the calculated member, (added expression is shown in blue):

The WITH operator can be used in MDX queries, as we have seen above, to define our calculated members, among other uses. WITH precedes the SELECT clause in the statement, stipulating a section where the calculated member (together with named sets) is defined in the query. We need only use the Member clause, preceded by the WITH keyword, for each calculated member we wish to add. (It is also important to remember that, as we have seen in our practice example, to cause calculated members to appear along with the "regular members," we need to insert the .AllMembers function.) We will practice this again, with variations, as we advance through this lesson and in later lessons.

Next in Our Series ...

In this tutorial, our efforts have shown the relative ease with which we can create and display calculated members within our queries to meet various business needs. Our introduction in this lesson to dynamic calculated members has set the stage for the more advanced functionality and processes of calculated members that we will explore in following lessons. 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.

In our next lesson, Calculated Members: Further Considerations and Perspectives, we will examine multiple additional perspectives of calculated members. In addition, we will explore a means for controlling the order in which calculated members are solved, exposing why precedence of calculation might be critical to generating expected results.

About the Series ...

Introduction

Introducing Calculated Members

Next in Our Series ...