Introducing the SQL Server 'MDX in Analysis Services' Series

Monday Dec 2nd 2002 by William Pearson

Hitch your wagon to the power of MDX in Analysis Services! Join Bill Pearson in the start of a new series 'MDX in Analysis Services'. In this lesson, we will get up to speed with the fundamentals of MDX via Analysis Manager.

About the Series ...

This is the first tutorial of a new series, MDX in Analysis Services, that I hope will help new users get up to speed quickly with multidimensional expressions (MDX). The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services (to which I will refer in most cases as simply "Analysis Services" to save time and space). Our primary focus will be the creation of MDX expressions based upon single and multiple values, the objective of which will be to create queries for use with multidimensional data sources. Each installment in the series will progressively add more features, allowing us to create and apply MDX queries in a variety of scenarios designed to meet specific real-world needs.

In the MDX in Analysis Services series, we will focus upon building a practical foundation, based upon a pragmatic approach that "exercises" each newly explored concept in a way that I hope will give it practical meaning; we will move through a discussion of the fundamental components of basic MDX queries, and perform steps to use MDX enough to gain a good understanding of how it allows us to retrieve information from OLAP cubes. Our tutorials will rely heavily upon the samples provided when we installed MSSQL 2000 Analysis Services, allowing us to focus on grasping MDX as our primary objective, without being distracted by the rather vast subject matter surrounding cube design and building, Analysis Services in general, and other equally fascinating (but voluminous) domains that lie outside the scope of our introduction to MDX.

The MDX in Analysis Services series will:

  • Introduce basic MDX concepts, focusing on the calculation of single values with MDX expressions;
  • Progress to more advanced MDX features, demonstrating some of their practical applications;
  • Explore the creation of MDX queries through the use of MDX sets, where we'll expand our understanding of MDX to include the creation of multiple value expressions;
  • Focus upon the creation of calculated members;
  • Explore the creation and uses of calculated cells;
  • Explore the implementation and uses of drill-through functionality;
  • Introduce performance tuning procedures and other efficiency tips for using MDX;
  • Introduce other MDX features and nuances at appropriate junctures.

In this session, Concepts and Navigation, we will gain a broad understanding of the components and capabilities of an MDX expression. We will:
  • Use MDX to navigate the structure of an OLAP data source (our cube);
  • Obtain information about the dimension hierarchy and its members with MDX expressions;
  • Create a Calculated Member via an MDX expression, focusing our initial efforts on using constant values;
  • Use MDX to perform conditional tests and comparisons within expressions;
  • Query the multidimensional data source (our cube) and return values.

What We Need to Complete the Series

The MDX in Analysis Services series presupposes that you have MSSQL Server 2000 installed on your local PC, which needs to have at least the Workstation Edition of Microsoft Windows NT 4.0 (Service pack 5 or later) or Microsoft Windows 2000, Professional Edition. Other applications will be briefly touched upon, including Microsoft Excel 2000 and other Microsoft Office 2000 components. While many combinations of PCs, networks, and network versions of the foregoing applications will work, the applications and basic PC described are suggested minimums. Obviously, the PC must meet the system requirements for a MSSQL Server 2000 installation. (These requirements are readily available at the Microsoft Web site, along with a library of highly useful information, as most of us are aware.)

For all of the tutorials that we undertake in this series, we will be using the Data Sources and Samples provided in the Typical installation of MSSQL 2000 Analysis Services.

Page 2: Fundamental MDX Concepts

See All Articles by Columnist William E. Pearson, III

Fundamental MDX Concepts

The Multidimensional Expression (MDX) language was created with the primary purpose of allowing us to manipulate Multidimensional data in Microsoft SQL Server 2000 Analysis Services. MDX is defined in the OLAP extensions and OLE DB. It is used with Analysis Services as a query language, and is used by various Client components and applications to return data from OLAP cubes. A second important characteristic of MDX is its role as an expression language. Its functions can be used in Analysis Services to define calculated members, as well as to build local cubes and to query cube data using the Pivot Table Service with OLE DB and Microsoft ActiveX Data Objects (ADO). MDX also allows us to create and register flexible user-defined functions for our specific needs that accept arguments and return values in the MDX syntax.

Comparing and Contrasting MDX with SQL

For those of us familiar with the Structured Query Language (SQL), the MDX syntax will appear similar in many facets. It may appear, as we proceed with our introduction to MDX, that many of the actions we can perform with MDX can be handled (though perhaps not as well) with SQL. MDX exists to make this functionality more efficient and intuitive, with specific respect to multidimensional data and objects.

The SELECT clause (representing a data request), the FROM clause (representing a "starting point" for data selection), and the WHILE clause (in essence, a filter) are requirements for both MDX and SQL. MDX uses other keywords to query cubes and return data that we can analyze, as well as providing functions that serve as tools to manipulate returned data. Finally, MDX is further enhanced with the support of user-defined functions that allow us to extend and customize the tool to fit specific business needs that might not lend themselves to "off the shelf" solutions.

MDX also resembles SQL in its provision of Data Definition Language (DDL) syntax for managing our data structures. Commands exist, for example, for creating, modifying and deleting cubes, dimensions, measures, and other structural objects in the OLAP environment.

Most data definition/manipulation languages, including SQL, are constructed to return and manipulate data in two dimensions, commonly seen as a row dimension and a column dimension. A partial picture of a two-dimensional (or relational) database may appear as shown in the following Illustration:

Illustration 1: Database Schema for the Sample Budget Cube

The two-dimensional data represented in each of the tables depicted above contain fields, or single data elements that exist at the intersection of each row and column. As most of us know, a SQL query would specify columns with a SELECT statement, with a starting point defined in a FROM clause. The specific rows to be returned by the query would be specified by a WHERE clause, which acts in the capacity of a filter.

Basic MDX Terminology

The domain of MDX is multidimensional data, which, by its nature, is housed within structures (called cubes) of greater than two dimensions. A dimension is an organized hierarchy of categories (levels) that typically describes data in a fact table; I liken dimensions to "perspectives" or "views" of data. Instead of fields, the element(s) of data that are stored at the intersection of the cube's dimensions are called measures. There can be more than one measure stored at the intersections.

Illustrated below is an example cube I have constructed, based loosely upon the more elaborate "Budget" cube supplied as a sample with the installation of Analysis Services. The cube is composed of three dimensions: Stores, Accounts and Time. It stores two measures, Actual and Budget amounts. Each dimension is broken down into different, hierarchical levels, each of which is broken down further into members. For example, the Stores dimension is divided into State levels (seen here as Georgia and New York), which are composed of City levels (here Atlanta, Augusta, New York City and Albany). The Cities are subdivided into the individual Stores themselves, which are the members of the Stores dimension.

The intersect points of the local Stores, together with the General Ledger Accounts (the members composing the Income Statement levels of the Accounts dimension) and the operating months (the members making up the Quarter levels of the Time dimension), represent the point (cells) at which the measures are collected.

To identify and extract the data, whether it is for a single cell or multiple cells, MDX uses a reference system based upon the concept of tuples. Tuples list dimensions and members to identify individual cells, as well as groups of cells, in the cube. Because each cell is an intersection of all the dimensions of the cube, tuples can uniquely identify every cell in the cube.

Illustration 2: Example Cube, showing various Hierarchical Relationships

Tuples identify sections of the cube, called slices, composed of more than one cell. An ordered collection of tuples constitutes a set. In an MDX query, axis and slicer dimensions are composed of these sets of tuples. In addition, it is possible to create a named set. A named set is a set with an alias, used to make our MDX queries easier to understand and, if especially complex, more efficient to process.

When we use SQL, it is usually necessary to filter the potentially large amounts of data returned from a query on a table or tables. Specifying columns in the SELECT statement, and restricting the rows that are returned based on specific criteria with a WHERE statement, allow us to apply filters.

The concept of filtering is equally important in MDX, where a SELECT statement is used to select the axis dimensions (the dimensions and members to be returned in MDX - expected to return data for multiple members). The WHERE statement is used to restrict the returned data to a slicer dimension (which is composed of specific dimension and member criteria, and is expected to return data for a single member). The terms "axis dimension" and "slicer dimension" distinguish dimensions of the source cube cells of the query, indicated in the FROM clause, from the dimensions of the result cube cells, which can be composed of multiple cube dimensions.

Two more terms to consider in our review of fundamental MDX terminology are Calculated Members and User-defined Functions. Calculated members are derived members; that is, they are members that have no basis in the data itself but are created via evaluated expressions in MDX. They are returned by the same processes as a standard member, and can be created via a rich set of functions that are present in MDX. Calculated members extend our capabilities to manipulate multidimensional data. User-defined functions also extend those capabilities by allowing us to create custom functions to manipulate multidimensional data and to register those functions, giving us the flexibility of calling these functions from within the Calculated Member Builder (which we will introduce later in the session), MDX queries, and data definition language (DDL) statements that support MDX. User-defined functions can be programmed in any language that supports Component Object Model (COM) interfaces and are capable of accepting arguments and returning values in the MDX syntax.

Page 3: Fundamental MDX Syntax

See All Articles by Columnist William E. Pearson, III

Fundamental MDX Syntax -- Building Simple Expressions

Having briefly explored the terrain that we can expect to find in a multidimensional cube, and having discussed the role of the MDX language in obtaining information from the cube, we are ready to begin using MDX to retrieve data from a data source.

We will start Analysis Services and navigate to the Warehouse cube, which is supplied as a sample when we install Analysis Services.

  1. Start Analysis Manager (Start -> Programs -> Microsoft SQL Server -> Analysis Services -> Analysis Manager), expanding the Analysis Servers folder by clicking the "+" sign to its left.

  1. Expand your server (typically named the same as your PC, but determined by the installation/setup), and then expand the FoodMart 2000 database.

As we learned earlier, MDX acts as a tool for extracting information from our OLAP cubes. In addition to the data query role, MDX can generate calculated single values. In its capacity as an expression language, MDX allows us to create complex expressions that can be used to serve a wide range of functions within a cube. Among these functions is the creation of calculated members, custom actions, and custom formulas (and variations upon each of these), as well as other functions.

We will begin our exploration of MDX with the creation of a simple calculated member.

Multidimensional Data and MDX

Earlier, we touched upon the meaning of the term "member" in our cube environment. In addition to the standard member, a calculated member (a member generated via MDX) can range in sophistication from the simplest specifications (a constant value) to a complex formula that is customized to meet a complicated business need. To familiarize ourselves with some of the characteristics of members, we will examine their properties and how they interact with dimensions in general. We will first create a calculated member using a constant value. Our exploration will progress to more advanced calculations later, but for now the point is to gain an understanding of

  • The nature of members and how they fit into the OLAP scheme of things;
  • The mechanisms used to create a calculated member;
  • The navigation of members, including the display of properties and other information; and
  • The interaction between dimension members and MDX expressions.

Page 4: Simple Calculated Members

See All Articles by Columnist William E. Pearson, III

A "Platform for Exploration:" Simple Calculated Members

Let's create a simple calculated member to get a look at its properties in a general way. We will also use our newly created calculated member to explore other facets of MDX as we progress through the tutorial. From the Analysis Manager console, and at the Cubes folder within the FoodMart 2000 database sample,

  1. Expand the Cubes folder (seen in the illustration below) by clicking the "+" sign to its left.

Illustration 3: Sample Cubes provided with the Analysis Services Installation

  1. Right click the Warehouse cube, then click Edit from the flyout menu.
  2. When the Cube Editor screen appears, click the Data tab at the lower left (on the right half of the screen).

The preview pane is activated.

  1. If "Product Family" does not appear in the lower pane area, drag the Product dimension to the rows area (the left column of the lower pane section).

The Cube Editor preview pane should appear as shown below in Illustration 4.

Illustration 4: The Cube Editor Preview Pane

  1. Select Insert Calculated Member (top drop down menu) under Insert. A picture of the associated toolbar button, an alternative means of initializing the Calculated Member Builder, appears below.

Illustration 5: The Insert Calculated Member Toolbar Button

The Calculated Member Builder appears.

  1. Type MyCalcMem into the Member Name box. In the Value Expression box, input the phrase "Check for Updates" (quotation marks required). The Value Expression box should appear as shown below.

Illustration 6: The Value Expression Dialog Box with Inserted String

  1. Click OK.
  2. Review the newly created MyCalcMem column, comparing it to the illustration below.

Illustration 7: The New MyCalcMem Column

To change the expression for MyCalcMem, go to the Calculated Members folder in the Cube tree in the left pane of the Cube Editor, as partially shown below.

Illustration 8: MyCalcMem now appears in the Calculated Member Folder

  1. Click MyCalcMem within the Calculated Members folder.
  2. Right click and select Edit.
  3. Type 11+2.
  4. Click OK.

Standard mathematical operations apply here. For details, see the Online Books for Analysis Services/MSSQL Server 2000.

  1. The number 13 fills the MyCalcMem column cells, replacing the "Check for Updates" string.

To perform a text concatenation,

  1. Select the Properties pane at the bottom of the left side of the Cube Editor, ensuring that MyCalcMem (in the Calculated Members folder of the Cube tree) remains highlighted.
  2. Select the Value property for MyCalcMem to edit its properties.
  3. Click the ellipsis (...) button, and, when the Calculated Member Builder appears, clear the Value Expression box.
  4. Type in the following:

"MyCalcMem" + "-" + "REVISION1"

The contents of the Value Expression box should resemble those depicted in Illustration 9 below.

Illustration 9: Text Concatenation in the Value Expression Box

  1. Click OK. Compare the results to the partial illustration shown.

Illustration 10: Text Concatenation Results for MyCalcMem in the Preview Pane

As we have seen, the result of placing "+" between two numbers in an MDX expression is the sum of the numbers, while the "+" sign used between two strings returns a concatenated string. (Remember that strings need to be enclosed in quotation marks.)

Page 5: "Getting Familiar with the Membership" with MDX Expressions

See All Articles by Columnist William E. Pearson, III

"Getting Familiar with the Membership" with MDX Expressions

MDX expressions can be used to retrieve information about members. The functions used can easily be added to our expressions from the Calculated Member Builder. As we've seen, expressions can return strings or numbers. We will explore the use of MDX to display member-related information; our exercises will demonstrate the concepts behind the workings of MDX.

The Functions tree of the Calculated Member Builder contains a list of many functions that are available within MDX. We can view the types of functions as the folders that compose the tree. Each folder name reflects the Category of return values (the types of values returned by the functions within the respective folder).

To illustrate, take the following steps:

  1. Select the Value property (go to Properties at the bottom left corner of the Cube Editor) once again for the MyCalcMem Calculated Member.
  2. Select the ellipses button.
  3. When the Calculated Member Builder initializes, clear the Value Expression box.
We can select functions to build our expressions in the Value Expression box. Functions are added to the Value Expression box by dragging the selected function to the box, by selecting the desired function and clicking the Insert button, or by double-clicking the desired function. A partial view of the Member folder within the Functions tree (expanded by clicking the "+" sign to the folder's left) is shown below.

Illustration 11: Partial View of the Functions Tree

We will demonstrate the way we use functions by taking the following steps:

  1. Expand the String folder in the Functions tree
  2. Double-click the UniqueName--Member function
  3. Click the <<Member>> token (a placeholder) in the Value Expression box, highlighting it, so that the Calculation Member Builder dialog looks like the illustration below.

Illustration 12: Highlight the <<Member>> Token in the Value Expression Box

As we are working with members, we now go to the Member folder within the Functions tree and expand it. (We can collapse the String folder by simply clicking the "-" sign to its left, to free up real estate).

  1. Expand the Member folder in the Functions tree.
  2. Double click the CurrentMember function.

A <<Dimension>> token appears in front of our expression in the Value Expression box, as shown below.

Illustration 13: The Value Expression Box, after adding the CurrentMember Function

  1. Click the <<Dimension>> token to highlight it, then double-click Product in the Data tree (to the left of the Functions tree) to change the expression to:


  1. Click OK.

Page 6: MDX Expressions Continued

See All Articles by Columnist William E. Pearson, III

The results that are returned show the MyCalcMem column to be populated with the Unique Name of the current member, which includes its hierarchy (in brackets, and delimited by single decimals). This represents a sort of "qualified name" whereby no member of a cube is without unique identification.

To repeat in similar fashion, we will perform the same process with the Name-Dimension function.

  1. Select the Value property (again, bottom left corner of the Cube Editor).
  2. Click the ellipses button.
  3. Clear the Value Expression box when the Calculated Member Builder appears.
  4. Expand the String folder in the Functions tree.
  5. Double-click the Name-Member function (<<Member>>.Name should appear).
  6. Click the <<Member>> token in the Value Expression box to highlight it.
  7. Expand the Member folder in the Functions tree.
  8. Double-click the CurrentMember function.

Our Value Expression box should display the following expression:


  1. Click the <<Dimension>> token to highlight it, and then double-click Product in the Data tree.

The expression becomes as shown below:

Illustration 14: The Product Dimension in the Expression

  1. Click OK, and observe that the MyCalcMem column now lists the Member Name, as partially shown below (exploded view):

Illustration 15: The new MyCalcMem Values, using the Member Name Function

  1. Now, let's drag the Store dimension from the top pane down to replace the Product dimension in the row axis (you can drop the icon that appears over the "Product Family" headings currently in place - a small, double-headed arrow appears at the "drop" point), "swapping" Product (to the top) with Store (to below, in its old place).

Notice that the context of MyCalcMem has become "for All Products;" if we look above at the (newly positioned) Product dimension, which is now serving as a part of the filter list, we can see it is set to All Products.

  1. Select Alcoholic Beverages in the Product filter list, by scrolling down the hierarchy that appears, as shown below.

Illustration 16: The Product Hierarchy as an Exploded Selection

The Current Member of the Product dimension is now Alcoholic Beverages, so the MyCalcMem value becomes Alcoholic Beverages. ("Current Member" is the value that appears on the column or row axis, assuming that the dimension itself appears on the column or row axis, respectively - if not, and the dimension appears in the filter section, the Filter box displays the "Current Member.")

  1. Return the Product dimension to its original position in the row axis, simply "swapping" it again with the Store dimension, which, in turn, returns to the top.
  2. Double-click the Product Family hierarchy. This exposes the Product Department level to the right of the Product Family level, as partially shown in Illustration 17 below (scroll over to the far right; the row axis remains fixed).

Illustration 17: The Product Department appears as the First Level of Drill Down

Notice that MyCalcMem has assumed the values at the Product Department level (except for rollups/totals, where it displays the Product Family names). The Current Member of a dimension can exist at any level of the hierarchy.

Page 7: Rudimentary Conversion Functions

See All Articles by Columnist William E. Pearson, III

Rudimentary Conversion Functions

Conversion functions become necessary anytime we need to convert data to a different data type. Real-world examples abound. For the purposes of our tutorial, we will assume a business need that will use a member property (which we have already discovered to be a string data type) as a number in an expression. MDX is not endowed with conversion functions of this sort, but allows us access to external functions. (For information about the external functions that are available for use with MDX, see the Analysis Services Books Online.)

If we attempt simply to use member properties as numbers in our MDX expressions, MDX will return a syntax error. To avoid errors, when we need to use a member property in an arithmetic context, we use an external function, such as CDbl in VBA (an example of a function that converts a string to a number), to convert our string to a working number data type.

With the MyCalcMem Calculated Member that we have created, we will attempt to display individual store square footage after a decrease of approximately 300 square feet for each store that has been budgeted for development as local receipts staging rooms. The requirement in this simple example is that we subtract 300 square feet from each store's total square footage (a detail-level property of the Store dimension members, called Store Sqft, whose data type is currently string.)

We will enhance our understanding of member properties, as well as look forward with a conditional element in our expression building, by taking the following steps to accomplish our objective:

  1. To prepare for the example, drag the Store dimension from the top pane down, once again, to "swap" its position with the Product dimension in the row axis. The lower pane of the Cube Editor (Data Tab) should resemble that shown in Illustration 18.

Illustration 18: The Store Dimension replaces the Product Dimension in the lower Data Tab

  1. Select the Value property for MyCalcMem.
  2. Click the ellipses button and change the expression to the following:

[Store].CurrentMember.Properties("Store Sqft")

  1. Click OK and ensure that the information returned resembles the following:

Illustration 19: The Initial Consequences of the Use of Strings as Numbers

If we drill down to the individual store (by double-clicking each level under Canada; for instance: Canada -> BC -> Vancouver and Victoria), as we show in Illustration 20, we see that the square footage appears as numbers for the Store members. This is because the "numbers" that we see are actually detail-level member properties for the Store dimension members. As strings, they appear at the Store 19 and Store 20 level, in our example.

Illustration 20: The Square Footage Member Property appears correctly at the Store Level

Let's remove the "#ERR" values for the rollup members (these occur because, while numbers roll up to summary lines, non-additive strings cannot do so). A less confusing way to present this might be to show square footage for non-rollup values only. To do this, we will add a function to filter out the rollup values.

To build into the Value property a filter that will make possible the display of "all except rollup values," we will proceed as follows:

  1. Type the following into the Value Expression box:

IIF(Store.CurrentMember.Level.Name="Store Name", Store.CurrentMember.properties("Store Sqft"),"")

  1. Click OK. We have now achieved our objective of screening out rollup levels, as seen below in Illustration 21. Now we need to convert the square footage string so that we can perform a mathematical operation upon it.

Illustration 21: The #ERR Result is Screened Out by the Addition of a Conditional Statement

  1. Return to the Value Expression box, and change the Value property to the following:

IIF(Store.CurrentMember.Level.Name="Store Name",CDbl(Store.CurrentMember.properties("Store Sqft")),Null)

  1. Click OK. Compare the result set to that illustrated below.

Illustration 22: The Result Set with Number Data Types

While the results appear the same as those in Illustration 21 above, the MyCalcMem column now contains a number data type instead of a string data type. Our changes were simply to 1) add conversion function CDbl, with its parenthesis around the property value it is converting to a number, and 2) to place a Null in the function instead of an empty string (""). Since the CDbl function is registered on our systems as a VBA function, we can include it in an MDX expression.

  1. Let's finalize the Value Expression for MyCalcMem by placing -300 (the projected square footage reduction for the proposed layout changes we outlined earlier) at its end. Our expression should now resemble the expression shown below:

Illustration 23: The Expression as Required to Modify the Stores' Floor Space

  1. Click OK, and compare the result set to that shown in Illustration 24 below:

Illustration 24: Partial Result Set depicting the Projected Floor Space Reductions

Page 8: More on MDX Expressions

See All Articles by Columnist William E. Pearson, III

More on MDX Expressions

As we discussed earlier, CurrentMember is an MDX function that returns a member. Just as we can use the CurrentMember function to, in turn, retrieve properties of the associated Member, we can also use it indirectly to return information about other members. We can take advantage of member functions to locate and return members that exist at relative positions to the Current Member, or at specific levels of the hierarchy, with the Current Member as a defined starting point.

Use a Member as a Starting Point

To illustrate, we will utilize the Parent function to retrieve the Parent of the Current Member. Leaving the Store dimension where we placed it for the last exercise, we do this by taking the following steps:

  1. Close the lower levels of the Store dimension by double-clicking the "Store Country" heading.
  2. Select the Value property of the MyCalcMem Calculated Member, and click the ellipses button once again.
  3. Once the Calculated Member Builder appears, clear the contents of the Value Expression box.
  4. Expand the String folder in the Functions tree.
  5. Select the Name-member function inside the folder.
  6. In the Value Expression box, select/highlight the <<Member>> token.
  7. Expand the Member folder in the Functions tree.
  8. Double click the Parent function within the Members folder.
  9. Click the <<Member>> token, and then replace it with CurrentMember by double-clicking.
  10. Click/highlight the <<Dimension>> token.
  11. Double-click the Store dimension in the Data tree to replace the <<Dimension>> token.

Our expression should resemble that shown below.


  1. Once we have inspected the new expression, we click OK. Our results at this point should replicate those shown below.

Illustration 25: Partial Result Set showing the Parent Name for the Top Level in the Store Dimension ("All Stores")

  1. If we expand the Store Country level by double-clicking, we see that the new Parent of the Current Member (Store Country) is displaying in the MyCalcMem column. This is reflected in Illustration 26.

Illustration 26: The Parent function adjusts to display the Parent in the Drill Down

The Ancestor function is very similar to Parent, except that it to be used to display the "ancestor" whose number of levels away is specified by the user. Parent is a simplified version of this, the same results for which might be obtained by creating an Ancestor function whose position is a single level away. Functions exist for Descendants, Siblings, and others, as well. The Analysis Services Books Online provide in-depth information surrounding these and other functions, and their respective uses.

Page 9: Perform Conditional Tests and Comparisons

See All Articles by Columnist William E. Pearson, III

Perform Conditional Tests and Comparisons

We will conclude our tutorial with a review of an expression that contains conditional testing, coupled with a comparison test, to illustrate these important concepts.

  1. "Swap" the Product dimension with the Store dimension, so that the Product dimension returns to the row axis. The Store dimension once again returns to the top portion of the Cube Editor.
  2. We once more select the Value property of the MyCalcMem Calculated Member.
  3. We click the ellipsis button, and, at the Value Expression box, we modify our most recent expression to the one below: Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread"
  4. We accept the modification by clicking OK. The data returned should appear as partially shown below, after drilling down past the "Bread" Product Category. (The drill down path is Food -> Baked Goods -> Bread -> ... and so forth)

Illustration 27: The Results of the Application of a Comparison

We see that the MyCalcMem member contains a 1, equivalent to True, or a 0, meaning False with regard to the respective Product's membership in the Bread category.

This presents an opportunity to introduce a conditional test within the IIF To begin this, we will establish three arguments, the first of which is used by the IIF function as a conditional test (a test whose result is either 1 or zero, denoting True or False, respectively). The next argument provides the value assigned by the function if the conditional test results in a 1/True scenario. The value assigned can be a number or string; we will assign it the term "Restrict," as we wish, for the purposes of our example, to create a list of bread-laden foods to provide to customers who want to know which products that are not suggested for gluten-intolerant individuals.

Finally, the third argument assigns a value for any data that evaluates as False/0 in the conditional test of the first argument. In our example, these products will be classified as unrestricted with regard to consumption by gluten-intolerant individuals.

In this simplified example, we will take as a general rule any product that belongs to the Bread category to be a "Gluten-Watch" item. We will assume, for purposes of our comparisons and conditional expression, that any product for which "Bread" is not an assigned category name, the food is Unrestricted. To summarize, we are 1) performing a conditional test (via the argument found in the IIF function) to ascertain whether the Product Category equals "Bread," and 2) if the Product Category is "Bread," are placing the term "Gluten-Watch" in the MyCalcMem column; if the word "Bread" does not appear as the name of the Product Category, we want to assign the member under examination an "Unrestricted" status.

To achieve our objectives, we perform the following steps:

  1. Clear the Value Expression box for the MyCalcMem Calculated Member.
  2. Type the following into the Value Expression box:

IIF(Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread","Gluten-Watch","Unrestricted")

  1. Click OK. Review the values that are returned, a part of which is depicted below.

Illustration 28: Assigning Products to Reporting Classes based upon Conditional Tests

Now, say want to increase the scope of our nutritional list to further include the classification of products containing eggs, which might be bad news for people who suffer adverse reactions from various components found in eggs. The use of the OR statement results in the return of a 1 /True if either of the two statements joined by the "OR" is true (conversely, AND requires both to be true to return a 1 /True). We want to build in flexibility here as well and simply label the potentially restricted products as being under "Intolerance Watch," so as to allow us the capability to add other items as more such food groups are inevitably deemed risky for various individuals. Let's continue our example in practical form.

  1. Select the Value property, clicking the ellipses button.
  2. Navigate to the Value Expression box, and clear the expression. Type in the following expression:

IIF(Ancestor([Product].CurrentMember,[Product].[Product Category]).Name = "Bread" OR Ancestor([Product].CurrentMember,
[Product].[Product Category]).Name = "Eggs","Intolerance Watch", "Unrestricted")

  1. Click OK. Review the values that are returned, a part of which is depicted below.

Illustration 29: Extending our Expression with the addition of OR to the Conditional Statement

As we have seen, OR (as well as AND) can add even more power to our MDX expressions.

Page 10: Coming Up in Our Series...

See All Articles by Columnist William E. Pearson, III

Coming Up in Our Series...

In this, the first tutorial of the MDX in Analysis Services series, we attempted to present an introduction to MDX by exposing and discussing fundamental concepts and terms. We explored basic MDX syntax, with practical examples of the use of the language to return information from multidimensional data sources. Using illustrated examples, we covered the return of information about numbers, Basic conversion functions, building expressions that return information about related members based upon using the Current Member as a starting point, and the use of simple conditional tests and comparisons in basic expressions.

Our next tutorials deal with more involved MDX concepts, and with MDX queries, as opposed to MDX expressions, such as those we explored in this first tutorial. MDX queries can select many values from cubes, as opposed to the single values we see generated by separate MDX expressions. We will discuss the purposes of MDX queries as well as new terms that come into play.

We will create basic MDX queries together to get a good feel for the steps and considerations involved in query creation. We will explore the process of returning values with MDX, specifically focusing on the specification of dimensions. Moreover, will revisit member reporting, and expose further means of calculating members based upon the Current Member, as well as the process of selecting empty members to suit various needs.

See All Articles by Columnist William E. Pearson, III