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 Oct 28, 2003

MDX in Analysis Services: Calculated Members: Further Considerations and Perspectives - Page 2

By William Pearson

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

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