Using MDX with DeepSee
Creating Calculated Measures and Members
[Back] 
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter describes how to create and use calculated measures and members. It discusses the following topics:

Note:
Your cubes might contain additional calculated members that you can use in all queries; see Defining DeepSee Models.
Overview of Calculated Measures and Members
In MDX, you can create a calculated member, which is a member based on other members. You can define two kinds of calculated members: ones that are measures and ones that are not. (Remember that a measure is considered to be a member of the MEASURES dimension.)
Creating a Calculated Member
To create one or more calculated members within a query, use syntax as follows:
WITH with_clause1 with_clause2 ... SELECT query_details
Tip:
Notice that you do not include commas between the clauses.
Where:
Then your query can refer the calculated member by name in all the places where you can use other members.
For example:
WITH MEMBER MEASURES.avgage AS 'MEASURES.[age]/MEASURES.[%COUNT]' 
SELECT MEASURES.avgage ON 0, diagd.diagnoses.members ON 1 FROM demomdx
 
                                    avgage
1 None                                33.24
2 asthma                              34.79
3 CHD                                 67.49
4 diabetes                            57.24
5 osteoporosis                        79.46
Note:
This calculated member is a query-scoped calculated member; its scope is the query. For information on session-scoped calculated members, see CREATE MEMBER Statement,” in the DeepSee MDX Reference.
MDX Recipes for Calculated Measures
This section describes how to create MDX expressions for some commonly needed calculated measures:
Combinations of Other Measures
For a calculated measure, the value expression often has the form of a mathematical formula that combines measure expressions. For example:
(MEASURES.[measure A] + MEASURES.[measure B]) * 100
Or:
(MEASURES.[measure A] + MEASURES.[measure B])/MEASURES.[measure C]
More formally, in this expression, you can use the following elements:
Tip:
The MDX function IIF is often useful in such expressions. It evaluates a condition and returns one of two values, depending on the condition. You can use this to avoid dividing by zero, for example.
Percentages of Aggregate Values
It is often necessary to calculate percentages of the total record count or percentages of other aggregate values. In such cases, you can use the %MDX function, which is a DeepSee extension. This function executes a MDX query, which should return a single value, and returns that value, which is unaffected by the context in which you execute the function. This means that you can calculate percentages with measures defined by value expressions like this:
100 * MEASURES.[measure A] / %MDX("SELECT FROM mycube")
For example:
WITH MEMBER MEASURES.PercentOfAll AS '100 * MEASURES.[%COUNT]/%MDX("SELECT FROM demomdx")' 
SELECT MEASURES.PercentOfAll ON 0, diagd.MEMBERS ON 1 FROM demomdx
 
                              PercentOfAll
1 None                                84.56
2 asthma                               6.85
3 CHD                                  3.18
4 diabetes                             4.89
5 osteoporosis                         2.21
Distinct Member Count
In some cases, for a given cell, you want to count the number of distinct members of some particular level. For example, the DocD dimension includes the level Doctor. We could count the number of unique doctors who are primary care physicians for any given set of patients. To do so, we define a calculated measure that uses the following value_expression:
COUNT([docd].[h1].[doctor].MEMBERS,EXCLUDEEMPTY)
We can use this measure in a query as follows:
WITH MEMBER MEASURES.[distinct doctor count] AS 'COUNT(docd.doctor.MEMBERS,EXCLUDEEMPTY)' 
SELECT MEASURES.[distinct doctor count] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
 
                        distinct doctor co
1 0 to 9                                 38
2 10 to 19                               38
3 20 to 29                               38
4 30 to 39                               40
5 40 to 49                               41
6 50 to 59                               40
7 60 to 69                               33
8 70 to 79                               31
9 80+                                    28
Semi-Additive Measures
A semi-additive measure is a measure that is aggregated across most but not all dimensions. For example, customers’ bank balances cannot be added across time, because a bank balance is a snapshot in time. To create such measures, you can use the %LAST function, a DeepSee extension to MDX.
Consider the following measures:
You can define a calculated measure called LastBalance and use the following value_expression:
%LAST(Date.Day.Members,Measures.Balance)
The %LAST function returns the last non-missing value for a measure evaluated for each member of the given set. In this case, it finds the last day that has a value and returns that value.
Filtered Measures (Tuple Measures)
A normal measure considers all records in the fact table for which the source value is not null. In some cases, you may want to define a filtered measure, which has the following behavior:
For a filtered measure (also informally called a tuple measure), use a value_expression like the following:
([MEASURES].[my measure],[DIMD].[HIER].[LEVEL].[member name])
In this case, value_expression is a tuple expression where:
For example, the Avg Test Score measure is the average test score considering all patients who have a non-null value for the test. Suppose that in addition to the Avg Test Score measure, your customers would like to see another column that just shows the average test scores for patients with coronary heart disease (the CHD diagnosis). That is, the customers would like to have the measure Avg Test Score - CHD. In this case, you can create a calculated measure that has the following value_expression:
(MEASURES.[avg test score],diagd.h1.diagnoses.chd)
For example:
WITH MEMBER MEASURES.[avg test score - chd] AS 
'(MEASURES.[avg test score],diagd.h1.diagnoses.chd)' 
SELECT MEASURES.[avg test score - chd] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
 
                        avg test score - c
1 0 to 9                                  *
2 10 to 19                                *
3 20 to 29                                *
4 30 to 39                                *
5 40 to 49                            78.00
6 50 to 59                            75.75
7 60 to 69                            80.71
8 70 to 79                            83.33
9 80+                                 55.25
Measures for Another Time Period
It is often useful to view the value of a given measure for an earlier time period, while viewing a later time period. As an example, you can define a calculated measure called UnitsSoldPreviousPeriod and use the following value_expression:
([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])
Because of how this measure is defined, it is meaningful only if you use the DateOfSale dimension on the other axis of the query. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS 
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])' 
SELECT {[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
 
                          Units Sold           DateOfSale
 1 Jan-2009                        15                    *
 2 Feb-2009                        10                   15
 3 Mar-2009                        13                   10
 4 Apr-2009                        15                   13
 5 May-2009                        22                   15
...
Notice that the caption of the second column is based on the dimension used within the value expression, rather than the name of the calculated member that we defined. We can use the %LABEL function to provide a more suitable caption. For example:
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS 
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])' 
SELECT {[Measures].[Units Sold],%LABEL([MEASURES].[UNITSSOLDPREVIOUSPERIOD],"Units (Prv Pd)","")} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
 
                          Units Sold       Units (Prv Pd)
 1 Jan-2009                        15                    *
 2 Feb-2009                        10                   15
 3 Mar-2009                        13                   10
 4 Apr-2009                        15                   13
 5 May-2009                        22                   15
 6 Jun-2009                        17                   22
 7 Jul-2009                        24                   17
 8 Aug-2009                        30                   24
...
These examples use a time-based level, because this kind of analysis is common for time levels. You can, however, use the same technique for data levels.
Measures That Refer to Other Cells
It is often useful to refer to the value in a different cell of the pivot table. To do so, you can use the %CELL and %CELLZERO functions. Each of these functions returns the value of another cell of the pivot table, by position. If the given call has no value, %CELL returns null; in contrast, %CELLZERO returns zero.
These functions have many uses. For one example, you can use %CELL to calculate a running total (in this case, the cumulative inches of rainfall):
SELECT {MEASURES.[Rainfall Inches],%CELL(-1,0)+%CELL(0,-1)} ON 0, {dated.year.1960:1970} ON 1 FROM cityrainfall
 
                     Rainfall Inches           Expression
 1 1960                        177.83               177.83
 2 1961                        173.42               351.25
 3 1962                        168.11               519.36
 4 1963                        188.30               707.66
 5 1964                        167.58               875.24
 6 1965                        175.23             1,050.47
 7 1966                        182.50             1,232.97
 8 1967                        154.44             1,387.41
 9 1968                        163.97             1,551.38
10 1969                        184.84             1,736.22
11 1970                        178.31             1,914.53
MDX Recipes for Non-Measure Calculated Members
This section provides recipes for non-measure calculated members for some common scenarios:
Defining Age Members
It is often useful to have members that group records by age. To define such members, use an existing time level and the special NOW member. For example, consider the MonthSold level in the HoleFoods sample. You could define a calculated member named 3 Months Ago with the following value_expression:
[dateofsale].[actual].[monthsold].[now-3]
For example:
WITH MEMBER CalcD.[3 months ago] as '[dateofsale].[actual].[monthsold].[now-3]' 
SELECT calcd.[3 months ago] ON 0, {MEASURES.[units sold], MEASURES.target} ON1 FROM holefoods
 
                              3 months ago
1 Units Sold                             37
2 Target                             254.00
Defining a Hardcoded Combination of Members
In many cases, it is useful to define a coarser grouping that combines multiple members of the same level. To do so, create a non-measure calculated member that has a value_expression of the following form:
%OR({member_expression, member_expression,...})
For example:
%OR({colord.red,colord.blue,colord.yellow})
Each non-measure member refers to a set of records. When you create a member that uses the %OR function, you create a new member that refers to all the records that its component members use.
For example:
WITH MEMBER CalcD.[primary colors] as '%OR({colord.red,colord.blue,colord.yellow})' 
SELECT calcd.[primary colors] ON 0, 
{MEASURES.[%COUNT], MEASURES.[avg test score]} ON 1 FROM demomdx
Defining a Combination of Members Defined by a Term List
Term lists provide a way to customize a DeepSee model without programming. A term list is a simple (but extendable) list of key and value pairs. You can use term lists in the multiple ways; one is to build a set of members, typically for use in a filter.
In this case, you use the %TERMLIST function and the %OR function; create a non-measure calculated member that has a value_expression of the following form:
%OR(%TERMLIST(term_list_name))
Where term_list_name is a string that evaluates to the name of a term list.
For example:
%OR(%TERMLIST("My Term List"))
This expression refers to all records that belong to any of the members indicated by the term list (recall that %OR combines the members into a single member).
The %TERMLIST function has an optional second argument; if you specify "EXCLUDE" for this argument, the function returns the set of all members of the level that are not in the term list.
Aggregating Ranges of Dates
Another useful form uses a range of members aggregated by %OR:
%OR(member_expression_1:member_expression_n)
The expression member_expression_1:member_expression_n returns all members from member_expression_1 to member_expression_n, inclusive. This form is particularly useful with time levels, because you can use it to express a range of dates in a compact form.
For time levels, you can also use the special NOW member. The following expression aggregates sales records from 90 days ago through today:
%OR(DateOfSale.DaySold.[NOW-90]:DateOfSale.DaySold.[NOW])
Or use the following equivalent form:
%OR(DateOfSale.DaySold.[NOW-90]:[NOW])
You can also use the PERIODSTODATE function to get a range of dates. For example, the following expression gets the range of days from the start of the current year to today and aggregates these days together:
%OR(PERIODSTODATE(DateOfSale.YearSold,DateOfSale.DaySold.[NOW]))
Defining a Member as an Intersection of Other Members
In some cases, typically when you define a filter, it is useful to define a member that is an intersection of members. Suppose that you need a filter like the following (which does not show literal syntax):
Status = "discharged" and ERvisit = "yes" and PatientClass="infant"
Also suppose that you need to use this filter in many places.
Rather than defining the filter expression repeatedly, you could define and use a calculated member. For this calculated member, specify Expression as follows:
%OR({member_expression,member_expression,...}
For example:
%OR({birthd.year.NOW,allersevd.[003 LIFE-THREATENING]}
The expression (birthd.year.NOW,allersevd.[003 LIFE-THREATENING]) is a tuple expression, which is the intersection of the member birthd.year.NOW and the member allersevd.[003 LIFE-THREATENING] — that is, all patients who were born in the current year and who have a life-threatening allergy.