MDX Numeric Functions: The .Ordinal Function - Page 3

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.

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

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

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.

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

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.)

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.