MDX Numeric Functions: The .Ordinal Function

Tuesday Sep 5th 2006 by William Pearson
Share:

MSAS Architect Bill Pearson introduces the MDX .Ordinal function, as a means for generating lists and for conditionally presenting calculations. We also examine the use of the function in creating datasets to support report parameter picklists.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In this lesson, we will expose another useful function in the MDX toolset, the .Ordinal function. The general purpose of the .Ordinal function is to return the ordinal value of a specified dimensional level. The .Ordinal function is particularly useful within scenarios where we employ it with the IIF() function to determine the position of a level within a dimensional hierarchy for various reasons, as we shall see in our practice session.

The .Ordinal function can be leveraged in activities that range from generating simple lists to supporting sophisticated conditional calculations and presentations. We will introduce the function, commenting upon its operation and touching upon the creative sorts of effects that we can employ it to deliver. As a part of our discussion, we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

The .Ordinal Function

Introduction

According to the Analysis Services Books Online, the .Ordinal function "returns the zero-based ordinal value associated with a level." .Ordinal has many applications, and, as is the case with many MDX functions, pairing it with other functions allows us to leverage its power. As an illustration, we often use .Ordinal with the IIF() logical function; we will see an example of this combination within our practice exercises later.

We will examine the syntax for the .Ordinal function after a brief discussion in the next section. We will then explore some of the "conditional logic leverage" it offers the knowledgeable user, within practice examples constructed to support hypothetical business needs. This will allow us to activate what we explore in the Discussion and Syntax sections, where we will get some hands-on exposure in creating expressions that employ the .Ordinal function.

Discussion

To restate our initial explanation of its operation, the .Ordinal function, when acting upon a level expression, returns the zero-based index of the level expression to which it is appended with the period (".") delimiter. .Ordinal can be used for a great deal more than the support of simple lists of level indices, as we have intimated. When coupled with other functions, as we shall see, we can leverage .Ordinal to deliver a wide range of analysis and reporting utility.

Let's look at some syntax illustrations to further clarify the operation of .Ordinal.

Syntax

Syntactically, in using the .Ordinal function to return the associated level value, the level upon which we seek to apply the function is specified to the left of .Ordinal. The function takes the level expression to which it is appended as its argument, and returns a zero-based value for the level specified. The general syntax is shown in the following string:

<<Level_Expression >>.Ordinal

Putting .Ordinal to work is straightforward. When using the function to return the value of the level with which it works, we simply append it to the right of the level under consideration. As an example, within a query executed against the sample Adventure Works cube, for a dimension named Sales Territory (with a hierarchy of the same name), with three levels, named Sales Territory Group, Sales Territory Country, and Sales Territory Region, the following pseudo-expression:

[Sales Territory].[Sales Territory].[ Sales Territory Group].[North America].ORDINAL

returns 1, the zero-based value (or index) of the Sales Territory Group level (the top Sales Territory level itself – or "All Groups" – is level 0). As another example, the following:

[Sales Territory].[Sales Territory].[ Sales Territory Region].[Central].ORDINAL

returns 3, the numeric value of the Sales Territory Region hierarchical level. As is probably obvious, the .Ordinal function can be best leveraged by combining it with other functions, particularly "relative" functions, to generate lists of names, and so forth, as we shall see in short order.

NOTE: For information on several of the "relative" functions, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the .Ordinal function in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the .Ordinal function in three queries that illustrate its operation. We will do so in simple scenarios that place .Ordinal within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent, as in all the practice sessions of this series, is to demonstrate the operation of the .Ordinal function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 1.


Illustration 1: Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 2.


Illustration 2: Exposing the Analysis Services Databases in the Object Browser ...

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 2 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 3.


Illustration 3: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 4.


Illustration 4: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 5.


Illustration 5: Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 6.


Illustration 6: Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among my other series' at Database Journal, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct. For more information on the use of the Query Editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series' explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

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


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.

Procedure: Use the .Ordinal Function to Generate a Dataset to Support a Report Parameter Picklist

The point of our next effort is to generate the fields we need for report parameter picklist support, this time for the Geography dimension of the cube. The authors / developers have asked specifically for all geographical levels to show up as selections within the parameter picklists for various reports in Reporting Services.

When we venture upon parameter picklist support in an OLAP scenario (at least via this method), we need to provide two main ingredients within the returned dataset: a name that consumers can select at report runtime and the MDX-equivalent, qualified name, which is passed to Analysis Services via placeholders in the underlying dataset query. An index (again, the zero-based numeric value of a given dimensional level), can also be useful for grouping, sorting, and other presentation purposes. As we shall see in the steps that follow, the .Ordinal function is again useful in helping us to meet the business need.

1.  Select File ---> New from the main menu, once again.

2.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 10.


Illustration 10: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

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


--MDX047-002 Parameter Picklist Support using .Ordinal;  Display Name
-- for Report Parameter selector - Unique Name for passage to 
-- Analysis Services as MDX-intelligible equivalent.
WITH 
MEMBER [Measures].[LevelNo] 
AS 
   '[Geography].[Geography].CURRENTMEMBER.LEVEL.ORDINAL' 
MEMBER 
   [Measures].[DisplayName] 
   
AS 
   '[Geography].[Geography].CURRENTMEMBER.MEMBER_CAPTION' 
   
MEMBER 
   [Measures].[UniqueName] 
   
AS 
   '[Geography].[Geography].CURRENTMEMBER.UNIQUENAME' 
   
SELECT 
   {[Measures].[LevelNo], [Measures].[DisplayName], 
      [Measures].[UniqueName]} ON AXIS(0) , 
   {[Geography].[Geography].MEMBERS} ON AXIS(1)
FROM 
  [Adventure Works]

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


Illustration 11: Our Second Query in the Query Pane ...

4.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially depicted in Illustration 12 appears.


Illustration 12: Results Dataset (Partial View) – Report Parameter Picklist Support

In the returned dataset, we see the columns we have discussed. Of primary importance are DisplayName and UniqueName. While we might certainly simply display the UniqueName to consumers for selection, as well as for insertion into our MDX query. (I see this done often when a solution is developed by a practitioner new to MDX, or perhaps to components of the Microsoft integrated BI solution, particularly when they are attempting to generate the parameter support wholly within Reporting Services, etc.) More user-friendly names (hopefully those chosen for the member captions during development of the Analysis Services components) tend to meet with more consumer acceptance.

5.  Select File --> Save MDXQuery2.mdx As ..., name the file MDX047-002.mdx, and place it in the same location used to store the earlier query

The client developers and report authors express satisfaction with the results, and confirm their understanding in the operation of the .Ordinal function within the contexts we have presented thus far. All agree that we are ready to examine the next, somewhat different scenario, which likely has far-reaching potential: the conditional presentation of a value based upon "where we are in a hierarchy" (in this case, where we are in the Date dimension, Calendar hierarchy). We will 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, while the pre-existing measure is shown at all hierarchical Date levels down to month in the same results dataset.

Procedure: Use the .Ordinal Function as a Basis for Conditionally Displaying a Value

Recall that, in addition to the need to support parameters, the client representatives have asked that we craft a query to generate a dataset that shows an already existing cube measure, Internet Sales Amount in one column, with a calculation, "Monthly Moving Avg," (based upon a rolling six months' activity) alongside it. With the levels of the Calendar Year dimension (for 2003 only, in our example) – that is months, quarters, half-years and year - as the "Y" axis, the business need is to show total Internet Sales Amount for each Date dimension level, but to show the rolling average only on rows representing months, as the average is (at least to the management audience) only relevant at the monthly level. Finally, recall that, instead of displaying a blank space or a zero within the rows where Monthly Moving Avg does not appear, the specification expresses a preference for the display to be "N/A."

The report authors / developers express appreciation for the fact that "showing different values at different levels" might be a useful capability in a wide range of (potentially much more sophisticated) scenarios arising within their environment. They listen as we emphasize that the conceptual mechanics to support such conditional presentations might, indeed, be extrapolated well beyond the dimensions and values we are currently using, as well as beyond the use of the .Ordinal function itself within this context.

Having obtained consensus on the proposed target dataset, we are ready to set about constructing the query.

1.  Select File ---> New from the main menu.

2.  Select Query with Current Connection from the cascading menu that appears next, as we did to begin the query in the last section.

Another new tab again appears, with a connection to the Adventure Works cube, in the Query pane.

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


--MDX047-003 Conditional presentation of a calculation, based upon an 
-- existing measure and the MDX .Ordinal function
WITH
MEMBER
   [MEASURES].[Monthly Moving Avg]
AS
 'IIF( 
  
      [Date].[Calendar].CURRENTMEMBER.LEVEL.ORDINAL = 
         [Date].[Calendar].[Month].ORDINAL,
  
             AVG(LASTPERIODS (6, [Date].[Calendar].CURRENTMEMBER), 
         [Measures].[Internet Sales Amount]), 
   
      NULL)', 
   FORMAT_STRING = "$#,##0;0;0;\N\\\A\"
  
SELECT
 {[Measures].[Internet Sales Amount], [MEASURES].[Monthly Moving Avg]} 
      ON AXIS (0),
 
 {DESCENDANTS([Date].[Calendar].[Calendar Year].&[2003],
      [Date].[Calendar].[Month], SELF_AND_BEFORE )}
          
      ON AXIS(1)
             
 FROM
 
 [Adventure Works]

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


Illustration 13: Our Final Query in the Query Pane ...

4.  Execute the query by clicking the Execute button in the toolbar.

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 14 appears.


Illustration 14: Results Dataset – Conditional Generation and Display of Calculation

In the returned dataset, we see that the moving average appears only at the monthly levels, thanks to the comparison we perform through the use of the .Ordinal function. We suggest to our client colleagues that the year might be parameterized, that we might build in the capability to switch from Calendar to Fiscal Year, and that we might add myriad other capabilities within the ultimate reporting dataset query. Suffice it to say that, assuming a thorough knowledge of the various layers of the Microsoft integrated BI solution, one can obtain many powerful capabilities and features, and knowing "where to put the intelligence" within the sometimes multiple choices can mean highly tuned performance and effective solutions for consumers throughout our organizations. For more of my observations on this subject see Multi-Layered Business Solutions ... Require Multi-Layered Architects.

5.  Select File --> Save MDXQuery3.mdx As ..., name the file MDX047-003.mdx, and place it in the same location accessed to store the earlier query files.

6.  Select File --> Exit to leave the SQL Server Management Studio, when ready.

The client representatives inform us that their immediate goals have been met, and that the examples we have shared illustrate the principles of operation behind .Ordinal, as a part of helping them to support the expressed business requirements.

Summary ...

In this article, we explored the MDX .Ordinal function, which can be called upon in activities that range from generating simple lists and supporting parameter picklists to constructing a basis upon which we can drive conditional generation and display of calculations and other values. We introduced the function, commenting upon its operation and touching upon the creative effects we can deliver through its use.

We examined the syntax involved with .Ordinal, and then undertook illustrative practice examples of business uses for the function, generating queries that capitalized upon its capabilities. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed, as well as extending our discussion to other possible options and uses for the concepts we exposed.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved