MDX Functions | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

MDX functions can be used in MDX expressions or in MDX queries as you carve up axis dimensions like a Thanksgiving turkey. MDX forms the bedrock of Analysis Services 2005; BIDS builds MDX expressions that typically include MDX functions to retrieve data from the Analysis Services database based upon your actions like browsing dimensions or cubes. MDX functions help address some of the common operations that are needed in your MDX expressions or queries. Including ordering tuples in a set, counting the number of members in a dimension, and string manipulation required to transform user input into corresponding MDX objects.

This section splits the MDX functions into various categories and provides some basic examples. The best way to learn MDX functions is to understand their use in business scenarios so that you can apply the right MDX function in analogous cases. In this book, you will often see MDX which the product generates; paying attention to and experimenting with such MDX is critical to your transition from basic understanding of Analysis Services 2005 to complete mastery — and though it is a profound challenge, mastery is attainable. You can do it. Again, when you slice a dimension in any cube-viewing software, like Office Web Components, it is MDX that is generated and executed to back-fill the newly exposed cells. Also, when you create a report based on a cube (UDM) using Excel (to be seen in Chapter 15) or using Reporting Services (Chapter 17) it is MDX that is created behind the scenes to capture the contents with which to populate the report. Almost all these MDX queries or expressions generated by BIDS or by client tools use various MDX functions; some of which you will learn about in detail as you work through this book.

In Chapter 10 you learn about the new stored procedures support in Analysis Services 2005 and how you can write your custom functions in .NET programming languages that can be called within your MDX expression or queries. For example, the following MDX query contains a custom function MyStoredProc that takes two arguments and returns an MDX object.

     SELECT MyStoredProc (arg1, arg2) ON COLUMNS     FROM CorporateCube

What we expect will get you even more excited about Chapter 10 is that the .NET assemblies themselves can contain MDX expressions within them due to an object model that exposes MDX objects! It should be obvious if you are experienced with Analysis Services 2000 that the new version opens up whole new approaches to problem solving in the BI space. Because MDX functions are so central to successful use of Analysis Services 2005, it is best if you jump right in and learn some of the functions. Putting those functions together to accomplish more meaningful tasks will come later in the book. For now, please snap on your seatbelt; it's time to learn about MDX functions.

MDX Function Categories

MDX functions are used to programmatically operate on multidimensional databases; from traversing dimension hierarchies to calculating numeric functions over the data, there is plenty of surface area to explore. In this section the MDX functions have been categorized in a specific way for you to understand the MDX functions efficiently. You also see some details on select functions of interest, where interest level is defined by the probability you will use a given function. You can see all of the MDX functions in detail in Appendix A. We have categorized the MDX functions into several categories very similar to the product documentations on MDX functions. The category set functions, which makes it the largest category. MDX functions can be called in several ways:

Function (read dot function)

Example

<Dimension>.Name returns the name of the object being referenced (could be a hierarchy or level/member expression). Perhaps this reminds you of the dot operator in VB.NET or C# programming — that's fine. It's roughly the same idea.
```
     WITH MEMBER measures.LocationName AS [Customer].[Country].CurrentMember.Name     SELECT measures.LocationName ON COLUMNS,     Customer.country.members on ROWS     FROM [Adventure Works] 
```
Function

Example

Username is used to acquire the username of the logged-in user. It returns a string in the following format: domain-name\user-name. Most often this is used in dimension or cell security related MDX expressions. The following is an example of how username can be used in an MDX expression:
```
     WITH MEMBER Measures.User AS USERNAME     SELECT Measures.User ON 0     FROM [Adventure Works] 
```
Function ( )

Example

The function CalculationCurrentPass ( ) requires parentheses, but takes no arguments. More on what CalculationCurrentPass ( ) can be seen in Appendix A.
Function (with arguments)

Example

OpeningPeriod ( [Level_Expression [ , Member_Expression] ] ) is an MDX function that takes an argument that can specify both level_expression with member_expression or just the member_expression itself. This is most often used with Time dimensions, but will work with other dimension types. This function returns the first member at the level of the member_expression; for example, the following returns "Day 1":
```
     OpeningPeriod (Day, [April]) 
```

Set Functions

Set functions, as the category title suggests, operate on sets. The take sets are arguments and often the result of the set functions is a set. Some of the widely used set functions are Crossjoin and Filter which we are quite sure you would be using in your MDX queries. Hence these two functions are discussed here with examples.

Crossjoin returns all possible combinations of sets as specified by the arguments to the crossjoin function. If there are N sets specified in the crossjoin function, this will result in a combination of all the possible members within that set on a single axis. You see this with the following example:

     Crossjoin ( Set_Expression [ , Set_Expression ...] )     SELECT Measures.[Internet Sales Amount] ON COLUMNS,     CrossJoin ( {Product.[Product Line].[Product Line].MEMBERS},     {[Customer].[Country].MEMBERS}) on ROWS     FROM [Adventure Works]

This query produces the cross product of each member in the Product dimension with each member of the Customer dimension along the sales amount measure. Following are the first few rows of results from executing this query:

		Sales Amount
Accessory	All Customers	$604,053.30
Accessory	Australia	$127,128.61
Accessory	Canada	$82,736.07
Accessory	France	$55,001.21
Accessory	Germany	$54,382.29
Accessory	United Kingdom	$67,636.33
Accessory	United States	$217,168.79
Components	All Customers	(null)
…	…	…

Sometimes the result of the combination of the members of the set might result in values being null. For example, assume that there is one product that is sold only in Australia. The sales amount for this product in other countries and the sales amount for other products in Australia are going to be Null. Obviously you are not interested in the empty results. It does not help in any business decisions. Instead of retrieving all the results and then checking for null values, there is a way to restrict these on the server side of the Analysis Services instance. In addition to this, Analysis Services optimizes the query so that only the appropriate result is retrieved and sent. For this you use nonemptycrossjoin function or the nonempty function. The syntax for these two functions are

     NonEmptyCrossjoin (             Set_Expression[ ,Set_Expression...][ ,Crossjoin_Set_Count ] )     NonEmpty (             Set_Expression[ ,FilterSet_Expression])

Hence in order to remove the empty cells in the query using Crossjoin shown above you can use one of the following queries which use the nonemptycrossjoin and nonempty functions. While using the nonemptycrossjoin function you need to apply the filter condition on Internet Sales Amount and then retrieve the crossjoin of members from the first two sets. This is due to the fact that the default measure for the Adventure Works cube is not Internet Sales Amount and hence if the measure is not included as a parameter in the function nonemptycrossjoin function will be using the default measure. While using the nonempty function you first do the crossjoin function and then filter out the tuples that have null values for the Internet Sales amount as shown in the second query below. The nonempty MDX function is new in Analysis Services 2005.

     SELECT Measures.[Internet Sales Amount] ON COLUMNS,     NonemptyCrossJoin ( {Product.[Product Line].[Product Line].MEMBERS},     {[Customer].[Country].MEMBERS},Measures.[Internet Sales Amount],2 ) on ROWS     FROM [Adventure Works]     SELECT Measures.[Internet Sales Amount] ON COLUMNS,     Nonempty (CrossJoin ( {Product.[Product Line].[Product Line].MEMBERS},     {[Customer].[Country].MEMBERS}),Measures.[Internet Sales Amount]) on ROWS     FROM [Adventure Works]

Most users and client tools interacting with an instance of Analysis Services use the nonemptycrossjoin function extensively. You see more details on this function in several chapters of this book.

Another MDX function that is quite useful is the Filter function. The Filter function helps restrict the query results based on one or more conditions. The Filter function takes two arguments: a set expression and a logical expression. The logical expression is applied on each item of the set and returns a set of items that satisfy the logical condition. The function argument for the Filter function is:

     Filter( Set_Expression , { Logical_Expression | [ CAPTION | KEY | NAME ]     = String_Expression } )

The result of the example query shown for the Crossjoin function results in 43 cells. If you are only interested in the products for which the sales amount is greater than a specific value and are still interested in finding out the countries, you can use the Filter function as shown here:

     SELECT Measures.[Internet Sales Amount] ON COLUMNS,     Filter (CrossJoin ( {Product.[Product Line].[Product Line].MEMBERS},     {[Customer].[Country].MEMBERS}),[Internet Sales Amount] >2000000) on ROWS     FROM [Adventure Works]

This query filters out all the products for which the sales amount is less than 2,000,000 and returns only the products that have the sales amount greater than 2,000,000. The result of execution of this query is as follows:

		Sales Amount
Mountain	All Customers	$10,251,183.52
Mountain	Australia	$2,906,994.45
Mountain	United States	$3,547,956.78
Road	All Customers	$14,624,108.58
Road	Australia	$5,029,120.41
Road	United States	$4,322,438.41
Road	All Customers	$3,879,331.82

Member Functions

Member functions are used for operations on the members such as retrieving the current member, ancestor, parent, children, sibling, next member, and so on. All the member functions return a member. One of the most widely used member functions is called ParallelPeriod. The ParallelPeriod function helps you to retrieve a member in the Time dimension based on a given member and certain conditions. The function definition for ParallelPeriod is

     ParallelPeriod ( [ Level_Expression [ ,Numeric_Expression [ , Member_Expression ] ]     ] )

Figure 3-5 shows an illustration of ParallelPeriod function. ParallelPeriod is a function that returns a member from a Time dimension (you will learn about time dimension in Chapter 5) relative to a given member for a specific time period. For example ParallelPeriod ([Quarter], 1, [April]) is [January]. You might be wondering how this result came about. The following steps show the execution of the ParallelPeriod function and how Analysis Services arrives at the result:

The ParallelPeriod function can only be used in conjunction with time dimensions. For the illustration shown in Figure 3-5 assume you have a time dimension with a hierarchy Calendar which contains levels Year, Semester, Quarter and Month.
The Parallel Period function first finds the parent member of last argument, April, in the specified level Quarter which is the first argument. It identifies that level of April is Quarter2.
The sibling of [Quarter2] is then evaluated based on the numeric expression. A positive number indicates that the sibling of interest exists as a predecessor to the current member in the collection of members at that level. A negative number indicates that the sibling of interest is a successor of the current member. In this example, the sibling of interest is [Quarter1] because the numeric expression is 1.
Next, the member at the same position as that of member [April] is identified in [Quarter1], which is January.

image from book
Figure 3-5

The ParallelPeriod function is used to compare measure values relative to various time periods. Typically a customer would be interested in comparing Sales between Quarters or over Years, and this function really comes in handy when you want to make relative comparisons. Most of the client tools interacting with Analysis Services use this function.

Numeric Functions

Numeric functions come in very handy when you are defining the parameters for an MDX query or creating any calculated measure. Note that there are plenty of statistical functions in this group, including standard deviation, sample variance, and correlation. The most common of the numeric functions is a simple one called Count along with its close cousin, DistinctCount. The Count function is used to count the number of items in the collection of a specific object like Dimension, Tuples, Set, or Level. The DistinctCount, on the other hand, takes a Set_Expression as an argument and returns a number that indicates the number of distinct items in the Set_Expression, not the total count of all items. Here are the function definitions for each:

     Count ( Dimension | Tuples | Set| Level)     DistinctCount ( Set_Expression )

Please take a look at the following query:

     WITH MEMBER Measures.CustomerCount AS DistinctCount (     Exists ([Customer].[Customer].MEMBERS,[Product].[Product Line].Mountain,     "Internet Sales"))     SELECT Measures.CustomerCount ON COLUMNS     FROM [Adventure Works]

The DistinctCount function counts the number of distinct members in the who have purchased products of the product line Mountain. If a customer has purchased a product twice then the distinct count function will just count the customer once. The MDX function Exists is introduced in Analysis Services 2005 and is used to filter customers who have only purchased product line Mountain through the Internet. You will learn the Exists function in Chapter 7. The result of the Exists function is a set of customers who have purchased product line Mountain. The result of the above query is 9590.

Dimension Functions, Level Functions, and Hierarchy Functions

Functions in these groups are typically used for navigation and manipulation. Here is an example of just such a function, the "Level" function from the Level group:

     SELECT [Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].LEVEL ON COLUMNS     FROM [Adventure Works]

This query actually results in a list of all the quarters displayed in the results. The reason is because [Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].LEVEL evaluates to [Date].[Calendar Year].[Calendar Semster].[Calender Quarter]. From this, you get the list of all quarters for the relevant calendar year.

String Manipulation Functions

To extract the names of sets, tuples, and members in the form of a string, you can use functions like MemberToStr ( <Member_Expression> ) and to do the inverse, take a string and create a member expression, you can use StrToMember (<String> ). Consider the following case, in which there is a client application that displays sales information for all countries. When a user selects a specific country, you need to extract the sales information for the specific country from Analysis Services. Because the countries are represented as strings in the client application, you need to translate this string to a corresponding member, and then you can retrieve the data. String manipulation functions are useful while accepting parameters from users and transforming them to corresponding MDX objects. However there is a significant performance cost involved while using string manipulation functions. Hence we recommend you try to use these functions only if necessary.

     SELECT STRTOMEMBER ('[Customer].[Country].[Australia]') ON COLUMNS     FROM [Adventure Works]

Other Functions

Four other function categories exist: Subcube and Array both have one function each. The final two categories are logical functions, which allow you to do Boolean evaluations on multidimensional objects, and tuple functions that you can use to access tuples. In addition to that, Analysis Services 2005 has introduced several new MDX functions. You have seen some of them in this chapter such as Nonempty and Exists. You will learn more about these in Chapter 7 and Appendix A.