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 6

By William Pearson

Verification and Use

With data appearing in the Data view, we will take this opportunity to verify the proper operation of our new calculated measure, before handing it over with instructions for use to the intended audience.

21.  Position the Measures as the column axis.

22.  Position the Store dimension as the row axis.

23.  Drill down to display the U.S. Store States.

24.  Select 1998 - Q1 in the selector to the right of the Time dimension, in the upper half of the Data view.

The Data view, with our arrangements, appears as depicted in Illustration 14.

Click for larger image

Illustration 14: The Data View with Specified Arrangements - Compressed View

We are able to see the values that appear for all measures (my illustration above is compressed, to focus on Warehouse Sales and the new Rolling Average - 4 Pd, while conserving space). We see that the Rolling Average - 4 value for the California stores is 14,812.33 (circled in Illustration 14 above), the same value that we saw, and verified, in our development of the measure's MDX in the Sample Application earlier.

We note, as well that we gain rollup capabilities, something we might find useful as requirements for the new rolling average functionality grow. Let's look at another consideration, to which we alluded as we were naming our new calculated measure.

25.  Drag the Time dimension to the immediate right of the newly placed Store dimension columns, to effect a "crossjoin." When the cursor appears as shown in Illustration 15, drop the Time dimension.

Illustration 15: Cursor Indicates Appropriate Drop Point

The final arrangement should appear as partially depicted in Illustration 16.

Illustration 16: Arrangement in the Data View - Partial Row Axis

26.  Double-click the Year column heading to drill down to the member quarters of years 1997 and 1998.

27.  Double-click the Quarter column heading to drill down to the member months of each

28.  Scroll to focus upon the Warehouse Sales measure and the new Rolling Average - 4 Pd calculated measure, juxtaposed against California stores (CA) and 1998 in the row axis, as shown in Illustration 17.

NOTE: I have removed the measures columns, between the Warehouse Sales measure and the Rolling Average - 4 Pd calculated measure, to display the measures side-by-side in a more compact illustration.

Illustration 17: Arrangement in the Data View - Partial, Composite View

Through a verification process similar to the one we applied to the Quarter level values of the Rolling Average - 4 Pd calculated measure earlier, we can verify that the calculation is working at the month level, as well. This is why we chose to insert "4 Pd" in the name of the calculated measure, instead of "4 Qtr," or any other such restrictive designator. We will find that the rolling average works in a manner that is "scope sensitive" based upon its placement.

For instance, if we add the Warehouse Sales values (circled in red in Illustration 17 above) for the months of 3, 4, 5, and 6 of 1998, (2,477.67, 2,449.35, 4,602.99, and 5,637.52, respectively), we obtain a total of 15,167.53. Divided by four (4), this delivers an average of 3,791.88, the value that the Rolling Average - 4 Pd calculated measure displays (I have highlighted it with a red rectangle in Illustration 17 above) for month 6 of 1998.

While there are ways to enforce use of the moving average we have created at one specific level of the time dimension, we will leave the calculated measure as it is; once we explain its use to the information consumers, from whom we obtained the more limited, initial business requirement, we can be confident that the added value our solution provides cannot help but meet with their approval.

29.  Select File --> Exit to close the Cube Editor, saving as requested, if desired.

30.  Exit Analysis Services, as desired.


In this article, we introduced "rolling averages," a relatively common business requirement. We discussed the nature of these aggregations and the results they are intended to provide, and then discussed a business requirement, within a hypothetical scenario, in which a group of information consumers have requested a particular moving average capability for analysis purposes.

Using the Sample Application that accompanies an installation of MSAS, we constructed the MDX required to support a calculated member, upon which the consumers might rely to produce the rolling average values for which they expressed a need. Having tested the capabilities of the MDX, we created a calculated member in Analysis Manager to provide a permanent means by which the intended audience might retrieve rolling averages, in reports and browses of the cube, and other queries. We verified accuracy of operation once again, from the data view within the cube, demonstrating that the rolling average calculated measure operates in a "contextually sensitive" way, from the perspective of the time dimension levels at which it is used.

In our next article, we will continue our examination of moving averages, and examine an additional approach to achieving the same result.

» See All Articles by Columnist William E. Pearson, III

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