Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 5, 2006

MDX Numeric Functions: The .Ordinal Function - Page 3

By William Pearson

Procedures: Satisfy Business Requirements with MDX

For purposes of our practice example, we will assume that we have received a request for assistance from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance with designing queries to support organizational reporting efforts. As a part of our relationship with Adventure Works, as well as with other clients, we provide on-site augmentation for business requirements gathering and training, as well as combined development workshops / "train the trainer" events.

In a brief discussion with members of the Reporting department, we learn that a need has arisen to craft MDX queries for some new analysis and reporting requirements. First, a requirement has been identified to generate datasets, from the Adventure Works cube, to support parameter picklists in interactive reports. The client has implemented the integrated Microsoft BI solution, and, in addition to using Analysis Services as an OLAP data source, they use Reporting Services as an enterprise reporting solution. The MDX we explore together, we are told, will thus be adapted for ultimate use within Reporting Services, in multiple parameterized reports.

In addition to the need to support parameter selection within reports, the client representatives want to take advantage of our visit to address another need: Management has recently expressed interest in a report that shows an already existing cube measure, Internet Sales Amount in one column, with a calculated moving average (to be labeled as "Monthly Moving Avg," and to be based upon a rolling six months' activity) in the column alongside the Internet Sales Amount. They want the rows (the "Y-axis") to represent year, half-year, quarter and month, so the Internet Sales Amount appears at each Date dimension level. However, they want the rolling average to appear only on rows representing months – with the rows for date levels other than months to display "N/A," rather than a blank space or a zero (they reason that this will eliminate confusion). Finally, the group tells us that, although the presentation will likely change at the reporting layer, once the report is approved, the prototype we create should focus on Calendar Year 2003. (This particular year has a full twelve months' activity in the existing cube, which, they surmise, will mean more ready testing will be possible for the accuracy of the moving average calculation.)

After gaining an understanding of the foregoing needs, we briefly consider various options before concluding that we have happened upon a great opportunity to both assist the client in meeting its immediate needs and to provide examples that leverage the MDX .Ordinal function, with which we are told that the author / developer team has had little experience.

We convince the authors that they might best become familiar with the .Ordinal function by examining an introductory example, where we employ the function to generate a straightforward list that shows clearly the data we can use it to present. We can then build upon that example to illustrate a way to support a parameter picklist dataset. Finally, we can undertake an example to show how we might approach the presentation of the data containing the "conditional moving average," which is calculated and presented at the month level only, within the results dataset.

We confirm our understanding of the business requirements, and then begin our efforts to meet them in the procedural sections that follow.

Procedure: Use the .Ordinal Function to Generate a Self-Explanatory "Contents" Results Dataset

Let's construct a simple query to provide a conceptual "starting point" for the query that we are next to provide for parameter picklist dataset support. The idea is to generate a dataset that displays the Name of the level and the zero-based number (or index) for each level, for a given dimensional hierarchy within the Adventure Works cube. This initial display will show the concepts behind using the .Ordinal function and, we hope, make clear some of the ways we can employ it effectively. Once we have accomplished our immediate goal in this section, we will further evolve these concepts in meeting the business requirement for parameter picklist support in the procedure that follows it.

1.  Type (or cut and paste) the following query into the Query pane:

--MDX047-001 Initial "Starter Query" to Present a Hierarchical 
-- Level Name and Number Display, using .Ordinal
 '[Sales Territory].[Sales Territory].CURRENTMEMBER.NAME'
 '[Sales Territory].[Sales Territory].CURRENTMEMBER.LEVEL.ORDINAL'
 {[Measures].[SalesTerrName], [Measures].[SalesTerrLevNo]} ON AXIS(0),
 {[Sales Territory].[Sales Territory].MEMBERS} ON AXIS(1)
 [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 7.

Illustration 7: Our Initial Query in the Query Pane ...

The above query sets the stage for our demonstrations of some of the uses of .Ordinal. The idea is to simply generate a dataset that illustrates exactly the data that we can expect to see, so as to activate the concepts in the minds of our client colleagues.

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 8.

Illustration 8: Click Execute to Run the Query...

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

Illustration 9: Results Dataset – Initial "Listing" Scenario

In the returned dataset, we see that the names, and respective index numbers, of the hierarchical levels appear as expected. This simple dataset provides a great "beginner" basis for picklist support, as the picklist display labels appear within it. The level values can serve as a basis for ordering the picklist display, and with minimal alteration .Ordinal can be used (in conjunction with a little more logic in the query) to provide indentation based upon level, etc. (We won't do that here, but see Create a Cube-Based Hierarchical Picklist, a member of my Database Journal MDX in Analysis Services series, to get some ideas in this regard, if such are useful.)

3.  Select File --> Save MDXQuery1.mdx As ..., name the file MDX047-001, and place it in a meaningful location.

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the .Ordinal function. We will undertake using the function again in our next steps, to a large degree to expand upon its use in the first example.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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