Creating Calculations by Using Aggregation Functions

In Chapter 6, you created calculated members in a cube by using arithmetic operators and discrete values. Now that you know how to use sets, you can create calculated members that use aggregation functions. First, however, create a simple calculated member within an MDX query statement. The calculated member created as part of an MDX statement works the same as one created in a cube, except that when stored in the cube the member is permanent and is available to all users of a cube. Sometimes it can be effective to create calculated members in a query statement as you develop them and then copy the expressions to a cube when you’re satisfied with them.

Create a Calculated Member as Part of a Query

To see how to create a dynamic calculated member in an MDX query, create an Average Price member.

1.
In the MDX Sample application, add a new query that looks like the following:

									SELECT
									[Measures].Members ON COLUMNS,
    [Product].[Subcategory].Members ON ROWS
									FROM [Sales]

2.
Click the Run Query button to see the results of the query, and save the query file. The query shows the two measures from the cube—Sales Units and Sales Dollars—as column headings.

For a calculated member, you must specify a parent. For the Measures dimension—which has only one level—use the dimension name as the parent. You can enter almost anything as the name of the member, but it must be enclosed in brackets following the same guidelines as any member name. The expression for a calculated member consists of instructions to generate a new value.

3.
Before the Select keyword, insert the following:

									WITH MEMBER [Measures].[Average Price] AS '[Sales Dollars]/
									[Sales Units]'

In the main query, the set for the column axis is [Measures].Members, which would seem to include all measures, but see what happens when you run the query after defining a new calculated measure.

4.
Click the Run Query button. Nothing changes. Calculated members are not retrieved by the Members function of a dimension or level.

5.
Change the expression [Measures].Members to [Measures].AllMembers. The AllMembers function is like Members, except that it also includes calculated members. Click the Run Query button to see the calculated member.

6.
Add a comment to the query, and save the query file.

To dynamically create a calculated member, add a MEMBER clause at the beginning of the MDX query. You can add more than one MEMBER clause to a query. The word WITH must precede the first MEMBER clause. You do not put commas between successive MEMBER clauses.

Create a Calculated Member of a Nonmeasure Dimension

The most frequent calculated member you’ll create is a calculated measure—a member of the Measures dimension—but you can also create a calculated member of any dimension. When you create a calculated member of a nonmeasure dimension, you must specify where in the dimension hierarchy you want the new member to go. Suppose, for example, that you want to create a new member that shows the total Sales Units and Sales Dollars for all countries other than the United States—that is, for the total of Canada and Mexico.

1.
Create a new query, insert the following MDX query statement, and then run the query:

									SELECT
									[Measures].Members ON COLUMNS,
    [State].[Country].Members ON ROWS
									FROM Sales

The query shows three countries on the row axis.

2.
Before the SELECT keyword, insert the following clause:

									WITH
									MEMBER [State].[North America].[Non-US] AS
									'[Canada]+[Mexico]'

Specifying [State].[North America] as the parent of the new member puts the new member at the Country level—the same level as Canada, Mexico, and USA. This is the level already specified in the query.

3.
Run the query to see the results. The new member does not appear. As with the Measures dimension, the Members function does not retrieve calculated members of nonmeasure dimensions.

4.
On the row axis, change the expression [State].[Country].Members to [State].[Country].AllMembers. Then run the query again.

The new member is now included on the row axis.

Think through the tuple that generates the 8,881.00 value in the result grid. The Product, Employee, and Time dimensions do not appear in the MDX statement and thus use the default members. The column axis supplies the member from the Measures dimension (Sales Units), and the row axis supplies the member from the State dimension (Non-US). The Non-US member is a calculated member. That calculated member’s expression requires two values. The expression overrides the State dimension member for each of those two values, inheriting the member for each of the other three dimensions from the cell in the result grid.

Creating an aggregate member by using a plus sign to add values works fine when you have only two or three values to aggregate, but it’s cumbersome if you have multiple values. For example, suppose that you want to create a query that calculates the total for all states other than Washington.

5.
Save the query file, copy the entire contents of the query pane, create a new query, and paste the copied query. Change the query to look like this (changed portions are in bold):

  WITH
    MEMBER [State].[South West].[Non-WA] AS
      'Sum([State].[State].Members)'
  SELECT
    [Measures].Members ON COLUMNS,
    [State].[State].AllMembers ON ROWS
  FROM Sales

The parent is now [South West] rather than [All Product]. Making the South West region the parent puts the new member at the State level. Since South West is the last region, this puts the new member at the end of the list of states. For the time being, the expression includes all the states and doesn’t exclude Washington.

6.
Run the query. The states appear, with Non-WA at the bottom of the list. The values of the two Non-WA cells include the total of all the states.

You still need to exclude Washington from the set of all the members of the State level. MDX has a set function that creates a new set by taking all the members of one set except those that belong to a second set. The function is named, appropriately enough, Except.

7.
In the member definition, change the expression ’Sum([State].[State]. Members)’ to ’Sum(Except([State].[State].Members, {[Washington]}))’, and run the query. While Washington still appears on the row axis—the row axis definition does not exclude the member—the totals for the Non-WA member no longer include Washington.

See “Specify the aggregation function for a measure” in Chapter 3, “Dimension and Cube Editors,” for details about assigning an aggregation function for a measure.


The Non-WA calculated member uses the same expression for each measure. Since both Sales Units and Sales Dollars have Sum as the member’s aggregation function, using the Sum function in the Non-WA member works fine with either measure. Suppose, however, that the cube contained a measure such as Minimum Price, defined with the Min aggregation function. Using Sum to aggregate that measure would create an inappropriate value. MDX does have a Min function, but it would be extremely unwieldy to create a different Non-WA member for each measure. To resolve this problem, MDX has an Aggregate function. The Aggregate function simply aggregates each measure using the aggregation function defined for the measure. In general, when you create a calculated member on a nonmeasure dimension, you should use the Aggregate function rather than the Sum function.

When you create a calculated member on a nonmeasure dimension, you should use the Aggregate function rather than the Sum function.


Note

MDX has three related functions that manipulate two sets (Set1 and Set2) to produce a third set (Set3). The Union function puts into Set3 all the members in either Set1 or Set2. The Intersect function puts into Set3 only those members in both Set1 and Set2. The Except function puts into Set3 only the members from Set1 not in Set2. Each of the three functions strips duplicate values from Set3, unless you include the keyword ALL as an optional third argument.

8.
In the Non-WA member definition, change the function name Sum to Aggregate and run the revised query. The values in the result grid should not change. Save the query file.

When you create a calculated member on a nonmeasure dimension, that member will, by definition, intersect with all the members of the Measures dimension. Each of those measures already has an aggregation function defined. Using the Aggregate function takes advantage of that previously defined aggregation function.

Create a Cumulative Total

The Sum (or Aggregate) function is often used to create a calculated member on a nonmeasure dimension. You can also use the Sum function to create a new calculated measure. This is often done to create a cumulative total.

1.
Create a new query, and insert the following MDX query statement:

									SELECT
									[Calendar Month].Members ON COLUMNS,
    [Measures].AllMembers ON ROWS
									FROM Sales

2.
Run the query. This displays all the months as column headings and the two measures—Sales Dollars and Sales Units—as row headings. The AllMembers function allows you to see new calculated measures as you create them.

3.
Before the SELECT keyword, enter the clause WITH MEMBER [Measures].[Cumulative] AS ’’ and put the insertion point between the single quotes. Then expand the Numeric group of the Syntax Examples tree, and double-click the Sum function.

[View full size image]
The Sum function syntax shows two arguments. The first argument, «Set», is required; this is the argument you used when you created the Non-WA measure in the preceding section. The second argument, «Numeric Expression», is surrounded by square brackets, indicating that it is optional. If you create a member on a nonmeasure dimension (such as Non-WA), you’ll almost never use the «Numeric Expression» argument. If you create a member on the Measures dimension (such as Cumulative), you’ll almost always use the «Numeric Expression» argument. The «Numeric Expression» argument tells you which measure to use as the base for the new measure.

4.
Replace the «Numeric Expression» token with [Sales Units], and delete the optional brackets. For the «Set» argument, you need a set that begins with the time period of the first column and ends with the time period of the current column. You can use a colon (:) to indicate a range of members.

5.
Select the «Set» token. In the Metadata tree, select the Calendar Month level of the Time.Calendar dimension. Then double-click the first Jan member in the member name list. Click after the [Jan], type a colon, press enter, and then type [Time].[Calendar].CurrentMember. The final definition of the member before the SELECT word should look like this:

  WITH
    MEMBER [Measure].[Cumulative] AS
      'Sum([Time].[Calendar].[All Time].[1997].[Qtr1].[Jan]:
      [Time].[Calendar].CurrentMember, [Sales Units])'

6.
Run the query. The result grid shows the monthly values along with the cumulative values.

[View full size image]
Calculating the cumulative values from the beginning of a particular time period is an extremely common MDX task, so MDX provides functions to simplify the process. The most important is the PeriodsToDate function. This function creates a set much like the range you created in the Cumulative calculated member: the ending point of the set is the current member of the dimension flagged as a Time dimension (the first one, if a cube contains more than one dimension flagged as Time). The default beginning member of the set is the first member that shares the same parent as the ending member. For example, in a normal calendar-year hierarchy with Calendar Year, Calendar Quarter, and Calendar Month levels, a current member of August would produce the set July:August. (Qtr3 is the common parent.) A current member of [1998].[Qtr3] would produce the set [1998].[Qtr1]:[1998].[Qtr3]. ([1998] is the common parent.)

See “Working with Time Dimensions” in Chapter 3 for details about flagging a dimension as a Time dimension.


If you want to go back to a different common ancestor, simply specify the level of the ancestor you want. For example, with a current member of [August], specifying Year as the level produces the range [January]: [August]. You can use the (All) level to go back to the beginning of the entire level.

Note

The PeriodsToDate function behaves very similarly to the ParallelPeriod function described in Chapter 6. The ParallelPeriod function returns a single member, and the PeriodsToDate function returns a set of members that can be used in an aggregation function such as Sum.

7.
Change the set portion of the Cumulative member expression to PeriodsToDate([Time].[Calendar].[Calendar Year]), run the query, and scroll to see the Cumulative value drop back to match the Sales Units value in January.

8.
Remove the argument from the PeriodsToDate function altogether—leaving only the empty parentheses. Run the query, and see the Cumulative value resynchronize with the Sales Units value every quarter.

9.
Give the query a comment, and save the query file.

You can use functions that aggregate multiple values when you create a new member for either the Measures dimension or for a nonmeasure dimension. On the Measures dimension, specify a Measure member to aggregate; you must use an explicit aggregation function—Sum, Min, Max, or Count. On a nonmeasure dimension, do not specify a Measure member (since the measure will intersect all the possible measures); you generally use the Aggregate function to apply the appropriate aggregation for whichever measure is currently being calculated.

The Sum Function Creates a Subquery

Consider how MDX calculates the value for a member using the Sum function. For each value, the Sum function creates a subquery behind the scenes. The set you provide as the first argument to the Sum function serves as the set for the row axis.

When you use the Sum function to create a nonmeasure member, the Measure member comes from the main query—from the row or column axis, or from an actual or implied filter. For example, you could use the following MDX statement to replicate the subquery used to calculate the Non-WA value for the Sales Units column in “Create a calculated member of a nonmeasure dimension”:

SELECT
  {[Sales Units]} ON COLUMNS,
  Except([State].[State].Members,{[Washington]}) ON ROWS
FROM Sales

The result grid of that subquery would look like this:

The Sum function then simply sums all the values in the subquery result grid and returns that single value to the main query. The Sum function for the Sales Dollars column creates an analogous subquery to produce its single value.

When the Sum function is used to create a member of the Measures dimension, it still creates a subquery, but this time the Measures member cannot come from the main query since the Measures member of the main query is, by definition, the measure currently being calculated. That’s why the Sum function requires a «Numeric Expression» argument when used with a Measures member. The «Set» argument to the Sum function still serves as the set for the row axis of the subquery. The «Numeric Expression» argument serves as the lone member of the column axis of the subquery, and all other dimensions are inherited from the main query. For example, you could use the following statement to simulate the subquery needed to calculate the cumulative May value from the query in “Create a cumulative total”:

SELECT
  {[Sales Units]} ON COLUMNS,
  {[Time].[Calendar].[All Time].[1997].[Qtr1].[Jan]:
   [Time].[Calendar].[All Time].[1997].[Qtr2].[May]} ON ROWS
FROM Sales

The result grid of that subquery looks like this:

Once again, the Sum function simply summarizes all the numbers and returns that single value. An analogous subquery is created for each cell of the Cumulative row in the main query. Fortunately, most of the values used in each subquery are stored in the client cache, so each subquery executes quickly.


Calculate Discount Dollars by Using a Member Function

The Sales Dollars values in the Chapter 7 OLAP database are net of discounts. In other words, if you multiply the Sales Units for a product by the official list price of the product as stored in the Product table of the data warehouse, you’ll get a number larger than the Sales Dollars amount. The difference is in various discounts that can be applied. To calculate the Discount Dollars at every level of summarization on all dimensions involves several steps. In this section, you’ll learn those steps.

The first step is to retrieve the price for a product. The Product dimension includes Price as a member property. You can create a calculated member that retrieves the value of that member property.

The Price member property was added to the Product dimension in “Create a detail-level member property” in Chapter 3.


1.
Create a new query, and insert the following MDX query statement:

									SELECT
									[Measures].AllMembers ON COLUMNS,
    Product.Members ON ROWS
									FROM Sales

2.
Run the query. This extracts the Sales Units and Sales Dollars for all levels of the product hierarchy. You can now create a calculated measure that retrieves the Price member property.

3.
Insert the following calculated member:

									WITH
									MEMBER [Measures].[Base Price] AS
									'[Product].Properties("Price")'

This is the same calculated member you created in the cube in “Use an external function to convert a string to a number” in Chapter 6.


4.
Run the query. When you retrieve a member property for a dimension, you get a value only for the level at which the member property was defined. At any other level, the member property returns an error value.

The Price member property was defined at the product level. A member property is always stored as a text string, even if it is a numeric value such as the price. To use the price, you must convert the string to a number. As explained in Chapter 6, MDX doesn’t have a built-in function to convert a string to a value, but you can use the Microsoft Visual Basic for Applications (VBA) function CDbl to convert a string to a double (that is, a number that can include a decimal place).

5.
Change the expression for the Base Price member to ’CDbl([Product]. Properties(“Price”))’. Run the query. Prices exist only at the Product level. The error values that previously appeared at the higher summary levels are now some strange large numbers. You can ignore them for the time being. The prices at the product level values don’t appear to change, but you can now use them in arithmetic calculations.

If you want to calculate the Gross Dollars for a product, you must multiply the Price value by the Sales Units. To calculate the Gross Product for a product category or subcategory, you must sum up the Gross Dollars for all the products in that category or subcategory. For example, suppose that you want to calculate the Gross Dollars for the Bagels product subcategory. You must first find all the products that belong to that subcategory. For each product, multiply the Base Price by the Sales Units to calculate the Gross Dollars for the product. Then sum the Gross Dollars for all the products to get the Gross Dollars for the subcategory.

6.
Add the following additional calculated member to the query:

									MEMBER [Measures].[Gross Dollars] AS
									'Sum(Descendants([Product].CurrentMember,[Product Name]) ,
									[Base Price]*[Sales Units])'

The expression for this member uses the Sum function. Think through the subquery the Sum function generates for the Bagels product. The first argument of the Sum function specifies a set for the row axis of the subquery. In this case, you define that set by using the Descendants function. The Descendants function is like the Children function, except that it can retrieve values at any level. The Descendants function starts with the Bagels member. It finds all the descendants of Bagels at the Product level—the row axis of the subquery contains all the members of the Product level that are descendants of the Bagels member. The second argument of the Sum function specifies the column axis of the subquery. In this case, the subquery includes the Base Price (derived from the Price member property) and the Sales Units (as stored in the cube). The members of the other dimensions are inherited from the main query. The subquery for the Bagels member would look like this:

  WITH
    MEMBER [Measures].[Base Price] AS
      'CDbl([Product].Properties("Price"))'
  SELECT {[Base Price], [Sales Units]} On Columns,
    Descendants([Bagels],[Product Name]) On Rows
  From [Sales]

And the results of the subquery for the Bagels member would look like this:

The Sum function then multiplies the values in the first column by those in the second column and sums the results.

7.
Now run the query with the Gross Dollars calculated member.

As expected, the Gross Dollars are slightly higher than the Sales Dollars at each level of summary. At the Product level, the subquery performed by the Sum function is small—only a single row. At the All Product level, the subquery includes all the products in the entire hierarchy. Percolating a member property up the hierarchy can be a slow operation on a large hierarchy.

8.
To calculate the Discount dollars, add the following calculated member, and then run the query, add a comment, and save the query file. (Then try varying the set on the row axis—use sets from other dimensions.)

									MEMBER [Measures].[Discount Dollars] AS
									'[Gross Dollars] - [Sales Dollars]'

You can use a member property—particularly one that has a numeric value—within calculated members. You must remember to convert the member property to a numeric value before using it in a calculation, and you must use the member property only at the level at which it was created, using aggregation functions to percolate the values up the rest of the hierarchy.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.98.208