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 Aug 23, 2004

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

By William Pearson

As we can see, the calculated measure appears to be producing the desired results. Through the first Q4 (that is, for 1997), we see that it properly divides the total of the Warehouse Sales values by the number of quarters added together. (There are no quarters in the Warehouse cube prior to Q1 of 1997, so the rolling average cannot "look back" to earlier quarters, and thus cannot divide by a full four quarters until it reaches Q4 of 1997). Taking the California (CA) stores as an example, we can see, however, that, in moving to Q1 of 1998, the total Warehouse Sales of Q2, Q3, and Q4 of 1997 (14,734.32, 19,768.15, and 15,076.37, respectively), taken together with the Warehouse Sales of Q1 of 1998 (9,670.46), divided by four (4), gives us the correct value for the average of four rolling quarters at Q1 of 1998 (14,812.33).

14.  Re-save the query, and close the Sample Application, as desired.

The beauty of the way that our calculated measure retrieves the rolling average, based upon the "current" point in time, is that we can use such a calculated measure in a report, within which we design a parameter / prompt (or other mechanism) to supply the "as of" date. Information consumers can thereby reuse the calculated measure prospectively without having to code the query, or, for that matter, even know how to do so. We can give the calculated measure an intuitive name, and add it to the report authors' toolkits as another component that they can leverage with drag and drop ease.

Let's go into Analysis Manager, where we will add the calculated measure permanently to the cube, so that it can be accessed by any reporting application with connectivity to the cube (and, of course, the capability to "see" calculated members).

Procedure: Building the Rolling Average Calculated Measure in Analysis Services


1.  Open Analysis Manager.

2.  Expand the Analysis Server folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Warehouse cube

7.  Select Edit ... from the context menu that appears, as depicted in Illustration 5.

Illustration 5: Select Edit from the Context Menu

The Cube Editor opens.

8.  Right-click the Calculated Members folder within Cube Editor.

9.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.

Illustration 6: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

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