MDX in Analysis Services: Mastering Time: Introduction to Moving Averages - Page 3

August 23, 2004

Considerations and Comments

For purposes of this exercise, we will create a calculated measure to house the MDX that returns a moving average. We will be working within the Warehouse cube in the FoodMart 2000 sample database, which accompanies the installation of MSAS, along with several other samples. If we want to keep our sample cube in its pristine, original condition, we can simply discard our calculated measure upon the conclusion of our session, or at any convenient time thereafter.

While the calculated measure creation process is simple enough to follow, ensure that you have the authority, access and privileges needed to accomplish the process, and that performing these operations within the FoodMart 2000 database presents no other issues in your environment.

If the sample MSAS database was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure


Before creating a calculated member in MSAS, be it for a dimension or a measure (as in our present case), I often assemble the MDX involved in a more "direct" environment. While I use many applications for this purpose, the one that works best from the perspective of my articles is the MDX Sample Application that installs along with MSAS, primarily because anyone with access to MSAS probably has access to this tool. The Sample Application is useful as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

We will craft the MDX for a rolling average here, so as to get a focused view of how it works, before installing the MDX into a calculated measure in Analysis Services. To do this, we will take the following steps:

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants 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.

Having received our instructions from the information consumers, we will first create the core MDX query. We will add the rolling average calculated measure into the core query next. The core query might be viewed as a "frame" within which we are casting the rolling average. We want to set that frame up in a way that we can quickly verify operation of our MDX, before we permanently "install" the calculated measure into the Warehouse cube structure.

The business requirement involves quarters (at least in the initial specification), across which the average of Warehouse Sales will move. In addition, the moving average needs to be displayed specifically for U.S. Store States (keeping in mind that we might additionally like to present the rolling average for other dimensions at a later point in time).

Let's construct the core query next.

5.  Type the following query into the Query pane:

-- MXAS18-1, Preparation for Creation of Rolling Average Calculated Measure
CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]}) 
   {[Warehouse].[All Warehouses].[USA].Children} ON ROWS

6.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 2 appears.

Illustration 2: Core Query Populates the Results Pane

We see the four quarters of 1997 and 1998, respectively (the two years in the Warehouse cube), together with the Warehouse Sales measure, populating the columns across, and the U.S. Store States (from the Store dimension) appearing on the row axis. The core query thus provides the "frame" we need to ascertain that the calculated measure we create next 1) accumulates the base measure, Warehouse Sales, correctly, 2) over the window of the quarters.

7.  Select File -> Save As, name the file MXAS18-1, and place it in a meaningful location.

8.  Leave the query open for the next section.

We will use the Avg() function, in combination with the PeriodsToDate() function, in the definition of the rolling average calculated measure. (For details surrounding the PeriodsToDate() function, see my articles MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions and MDX in Analysis Services: Mastering Time: Period - to - Date Aggregations).

We take the following steps to create the calculated member, and place it within the results dataset "frame" we have prepared.

9.  Within the query we have saved as MXAS18-1, replace the top comment line of the query with the following:

-- MXAS18-2 Rolling Average Calculated Measure Create and Retrieve

10.  Save the query as MXAS18-2, to prevent damaging MXAS18-1.

11.  Type the following into the Query pane, between the top comment line and the SELECT statement already in place:

[Measures].[Rolling Avg] 
'Avg (LastPeriods (4, [Time].CurrentMember), [Measures].[Warehouse Sales])'

12.  Append the calculated measure created in the WITH statement above, [Measures].[Rolling Avg], to the first line of the SELECT statement, changing the following line:

CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales]}) 

to the following

	CrossJoin({ [Time].[Quarter].Members },{[Measures].[Warehouse Sales], 
		[Measures].[Rolling Avg]}) ON COLUMNS,

The Query pane appears as shown in Illustration 3, with our modifications circled.

Illustration 3: The Query with Our Modifications

13.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset depicted in Illustration 4 appears.

Illustration 4: Result Dataset - The Rolling Average Appears

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers