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 5

By William Pearson

10.  Type the following into the Member name box:

Rolling Average . 4 Pd

While the name of the calculated member can obviously be anything that is useful in the environment in which we are creating it, we need to make the name intuitive to the users. We used the term "4 Pd" versus "4 Qtr" (or similar) for reasons we will discuss shortly.

11.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

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

The MDX expression above is identical to that which we assembled in the Sample Application, where we constructed and tested the MDX to meet the business requirement. We simply take the expression in the WITH MEMBER section and remove the single quotes surrounding it. We use a different name for the calculated member than we did in the Sample Application, because we want to differentiate it from additional members we will create in subsequent articles, which will also be designed to return rolling averages.

The Calculated Member Builder appears with our input as depicted in Illustration 7.

Illustration 7: Calculated Member Builder with Complete MDX Expression (Compressed View)

12.  Click the Check button to perform a syntax check.

A message box appears, as shown in Illustration 8, informing us that the syntax is acceptable.

Illustration 8: Testing Positive for Syntax Correctness

13.  Click OK to close the Calculated Member Builder, and to save our new calculated measure.

The new Calculated Measure appears in the tree within the Calculated Members folder, as depicted in Illustration 9.

Illustration 9: The New Calculated Measure in the Calculated Members Folder

While no other calculated members appear in the illustration above, this view may differ from your own, depending upon differing activities that have been conducted with the Warehouse cube in your own environment. As another matter, although the actions we have performed to this point alone do not require it, let's process the cube to make sure we are all in a similar "processed" state.

14.  Select Tools --> Process Cube to process the Sales cube.

15.  Click Yes on the Save the Cube dialog that appears next, as shown in Illustration 8.

Illustration 10: Click "Yes" to Save the Cube

16.  Click No on the dialog that appears next, as shown in Illustration 11; we will not design aggregations at present.

Illustration 11: Click "No" to Designing Aggregations

The Process a Cube dialog appears, as depicted in Illustration 12, where we want the processing method set to Full Process. Full processing for the Warehouse cube will be relatively quick, so we will perform it to ensure that all is refreshed.

Illustration 12: Full Process Selected in the Process a Cube Dialog

17.  Ensure that the Full Process radio button is selected on the Process a Cube dialog.

18.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 13.

Illustration 13: Indication of Successful Processing Appears (Compact View)

19.  Click Close to dismiss the viewer.

20.  Click the Data tab in the Cube Editor, if necessary.

Cube data is retrieved.

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