Download the Code iconAs you become more familiar with MDX, you'll realize that calculated members are one of MDX's most powerful features. Let's introduce the idea of calculated members by discussing last month's MDX Puzzle. The puzzle question was, "How would you write an MDX query that returns the average year-to-date Unit Sales for all months in 1997?" Search through the reference material available on MDX for information on year-to-date or for average calculations, and you'll probably find the MDX Avg() and Ytd() functions. These powerful functions solve our puzzle.

Anyone can probably figure out what the Avg() function does, but what about Ytd()? It accepts a single time member and returns the set of members from the start of the year up to and including that member. So Ytd(\[May\]) is equivalent to the set \{ \[Jan\], \[Feb\], \[Mar\], \[Apr\], \[May\] \}. We're assuming a calendar year, but OLAP Services can start a fiscal year at any time boundary. Also, Ytd() is simply a shorthand way of using the PeriodsToDate function. Remember to use this important function if you need to do time-based summaries on a period to date level other than year.

Let's formulate an MDX query and see what happens when you try to run it.

SELECT Avg( Ytd( Time.CurrentMember),
      \[Unit Sales\] ) ON COLUMNS,
      Descendants( \[1997\], \[Months\[KM1\]\] ) ON ROWS
FROM \[Sales\]

As Screen 1 shows, this query fails and the message is not very informative. This failure demonstrates an important difference between SQL and MDX. An item in the select clause cannot evaluate to a numeric value, but this item has to be a reference to a member in the cube. Because of the way the MDX language is defined, the axis specification (the expression that defines what is on the rows or columns of the result) must consist of a set of members. One reason for this requirement is that most browsing tools take advantage of the dimension and level information surrounding a member. If this list of items includes only numeric expressions, enabling navigation functions such as drill down or drill up is difficult.

Calculated members solve the problem in the query. A calculated member is a new dimension member whose value is defined by a formula and calculated at runtime. You can create a new member in any dimension (remember the measures dimension where you store your facts is logically just another dimension) you want at any position and give it the formula you devised. Here is an example that solves the problem:

WITH MEMBER \[Measures\].\[Avg Ytd Sales\] AS 'Avg( Ytd( ), \[Unit Sales\] )'
   \{\[Unit Sales\], \[Avg Ytd Sales\]\} ON COLUMNS,
   Descendants( \[1997\], \[Month\[KM2\]\] ) ON ROWS
FROM Sales

Screen 2 shows the results. This query returns the Unit Sales for each month and the year-to-date average for each month. The WITH clause defines calculated members for the scope of the query.

The average listed in this result set might not be what you expected. It is the average monthly Unit Sales value, instead of average Unit Sale value for all sales transactions (what you might have expected). OLAP Services does not automatically retain the count of the individual transactions loaded. For example, the third month might have had 1000 sales transactions, and to do the transaction average, you need the sum of all sales divided by the count of all sales. People often create a count measure in a cube to perform this type of average. Note that this theme is common in OLAP applications. One question often has more than one right answer. Thus, having meta data describing the information in your cube is important so that end users will know how the numbers have been calculated.

Note that we didn't include Time.CurrentMember within the Ytd() function. We left out Time.CurrentMember because it is the default parameter for the Ytd() function.

So what does CurrentMember mean? CurrentMember is an MDX keyword that works on a dimension to return the member that is currently selected. You use it when your MDX query is iterating through a set of members in that dimension. In this example, the expression Descendants( \[1997\], \[Month\]) is iterating through the months in 1997. So, for each month in 1997 (on the rows) the column expression including Time.CurrentMember is evaluated. This concept is similar to the idea of a correlated subquery.

Now let's look at the third query in the OLAP Services MDX Sample program, Listing 1, which uses some of these features. This query creates a calculated member called \[Store Sales Last Period\], Screen 3, which is based on the Time.PrevMember. PrevMember works similarly to CurrentMember except that it returns the previous member at the same level in the dimension hierarchy as the current member. In this case, the query calls out \[1998\] as the current member in the time dimension. So in this query, Time.PrevMember will always return \[1997\].

An important thing to note about the way we are using calculated members is that the lifetime of a member that you define in the WITH clause is the lifetime of the current query. If you immediately execute another query that uses the same member, OLAP Services will not remember it. You must include the definition for every query that uses it. But this common problem has an answer. You can create a member and have OLAP Services remember it for your entire session. Use the create member command as in this example:

CREATE MEMBER \[Sales\].\[Measures\].\[Store Sales Last Period\]
AS '(\[Measures\].\[Store Sales\], Time.PrevMember)'

If you run this command with the MDX sample program, it adds the member \[Store Sales Last Period\] to the measure dimension. If you are trying this command, you can verify that it worked by expanding the Measures dimension to see the available members, as Screen 4 shows.

The create member command is handy when you're developing client-side applications because members can be added without server access. Also, this command localizes the definition of the member to one place in your application. During the current session with OLAP Services, you see only the member you have created. If you want to create a calculated member that all clients can access, use the OLAP Manager edit cube capability. (The keyword, GLOBAL, in the create member syntax will define a member beyond the scope of the client session. The first release of OLAP Services does not support this keyword.)

Note that you can access the Decision Support Object (DSO) COM model, which OLAP Services exposes to programmatically build a global and persistent calculated measure from within your client-side application. DSO is architecturally similar to the Distributed Management Object (DMO) COM model, which SQL Server supports. You need administrative privileges to access DSO.

Another important feature in OLAP Services is related to creating members, which lets you specify formatting information for a new member. You may have noticed that when you view the results of MDX queries in a grid (such as the MDX Sample), the numbers are all nicely formatted to match the type of the measure. When you ask for Unit Sales, the numbers include commas and decimals that match currency formatting. If you ask for Sales Count, you don't get a decimal point.

MDX provides powerful format masks for displaying data. In fact, any valid Visual Basic (VB)-style format string is acceptable.

In the OLAP Manager utility, you can view the format information for a measure by right-clicking a cube name and selecting Edit. In the Edit Cube dialog box, you can select a measure, and open the Advanced tab of the Properties window. This approach is great for a loaded measure or a server-defined calculated measure, but we need formatting for client-defined calculated measures. The fifth query in the MDX Sample program, Listing 2, demonstrates this capability.

Screen 5 shows that the calculated member \[Store Profit Rate\] is formatted as a percentage with two decimal places. You can set the format property on a create member statement in the same way. Most third-party OLAP applications know how to add the formatting syntax for you, but it helps to know how this formatting works under the hood. Once you start working with OLAP Services and start solving real business problems, you will quickly discover that calculated members are an indispensable part of MDX. A real business application is rarely as simple as it first seems when you get to the gory details. Many solutions require groups of calculated members and some build on others. See the OLAP white papers at com/sql/olap for examples of complex business problems, such as basket analysis, that can be solved with MDX.