Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 17, 2003

MDX in Analysis Services: Calculated Members: Introduction

By William Pearson

About the Series ...

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.


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:

1.  Go to the Start button on the PC, and then navigate to Microsoft SQL Server --> Analysis Services, then to the MDX Sample Application.

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

Illustration 1: The Connect Dialog for the MDX Sample Application

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

2.  Click OK.

The MDX Sample Application window appears.

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

Illustration 2: The MDX Sample Application Window (Compressed)

6.  Type the following query into the Query pane:

{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], [Measures].[Units Shipped]} 
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.

The results dataset appears as shown in Illustration 3:

Illustration 3: Results Dataset from Our First 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):

{[Measures].[Warehouse Cost] , [Measures].[Warehouse Sales], [Measures].[Units Shipped]} 
NonEmptyCrossJoin([Store].[Store State].Members, [Product].[Product Family].Members) ON ROWS
FROM Warehouse

to the following:

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

The results dataset appears as depicted in Illustration 4:

Illustration 4: Results Dataset after Modifications

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM