I'm new to Analysis Services 2005 and MDX. I'm trying to create a calculated member that contains the proportion of cases (using [Measures].
) for each dimension in the cube. The following code gives me the percentage of the total number of members in each dimension. This is what I need for attribute hiearchies (single-level), but for user-defined hiearchies (multi-level) I want the percentage of the above level. For example, in a time dimension, I want the proportion of cases in each year (this works) and the proportion of cases in each quarter of a given year (this doesn't work).
CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].PercentCases
AS CASE
WHEN IsEmpty([Measures].) THEN NULL
ELSE ((
[DimLocation].[DimLocation].CurrentMember,
[DimLevel].[DimLevel].CurrentMember,
[DimDischargeYear].[DimDischargeYear].CurrentMember,
[DimDiagnosis].[DimDiagnosis].CurrentMember,
[DimICUAdmSource].[DimICUAdmSource].CurrentMember,
[DimSeverity].[DimSeverity].CurrentMember,
[DimGlucose].[DimGlucose].CurrentMember,
[DimMeanGlucose].[DimMeanGlucose].CurrentMember,
[DimAlbumin].[DimAlbumin].CurrentMember,
[DimAdmissions].[DimAdmissions].CurrentMember,
[DimDischargeHour].[DimDischargeHour].CurrentMember,
[DimUnitType].[DimUnitType].CurrentMember,[Measures].) /
(Root([DimLocation]),Root([DimLevel]),Root([DimDischargeYear]),Root([DimDiagnosis]),Root([DimICUAdmSource]),Root([DimSeverity]),Root([DimGlucose]),Root([DimMeanGlucose]),Root([DimAlbumin]),Root([DimAdmissions]),Root([DimDischargeHour]),Root([DimUnitType]),[Measures].))
END,
FORMAT_STRING = "#,0.0%",
VISIBLE = 1 ;
In addition I want the user to be able to get cross tabulations among the dimensions. For example, they may want the proportion of cases in each year at a given location or the proportion of cases in each year by product? In this case, I'd need a generalized formula because I'll never be sure which dimension(s) they may cross-tab.
Can anyone help? Any suggestions would be greatly appreciated.
SAP2006
Hi. To get percentage of the above level use the MDX Parent function in the denominator of your calculation. <dim>.<hier>.CurrentMember.Parent. However, there is a hazard using the .Parent function when CurrentMember is the highest level in the hierarchy. When the CurrentMember is the highest level in the hierarchy you reference a space outside the cube which is null, or 0, and you get a divide by zero error. We can avoid divide by 0 error, but it adds a lot of extra checking to our calculated member.
There are two sets of checks in the MDX below which avoid the divide by zero error.
The first set of checks execute before the calculation is done. Just as your ISEMPTY() check on the measure executes. First we check if the <dim>.<hier>.Currentmember.Level.Ordinal is <> 0. Checking the Level.Ordinal property identifies if we're at the highest level in the hierarchy. If we're not at the highest level in the hierarchy (.ordinal <> 0), then we check the tuple (<dim.<hier>.CurrentMember.Parent, Measures.<desired measure>) for the EMPTY condition.
IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))
If both conditions are met we return NULL and avoid the divide by zero error.
The second set of checks occurs in the tuple which defines the denominator of our division. Each <dim>.<hier>.CurrentMember is checked to see if we're at the highest level in the hierarchy:
IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0
If we are at the highest level then we use the DefaultMember value in the hierarchy. If not at the highest level then we use <dim>.<hier>.CurrentMember.Parent in the denominator calculation.
A major drawback to this technique is the detailed checking of each <dim>.<hier> within the nested IIF(). When you have a lot of hierarchies to include the construction/maintenance if the nested IIF() is tedious. However, if you're diligent you can accomplish it.
The example below uses a subset of hierarchies form the Adventure Works DW sample database. I didn't reference EVERY hierarchy in adventure works because it would have been very long and tedious. So my percentage calculations are ONLY valid across the <dim>.<hier> I reference in the calculated member Measures.[Percent Customer Count]
WITH MEMBER Measures.[Percent Customer Count] AS
'
IIF(ISEMPTY([Measures].[Customer Count]), NULL,
IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Date].[Calendar].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Date].[Calendar].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Customer].[Customer Geography].CurrentMember.Parent, Measures.[Customer Count])), NULL,
// Do the calculation
([Product].[Product Categories].CurrentMember
,[Date].[Calendar].CurrentMember
,[Customer].[Customer Geography].CurrentMember
,[Measures].[Customer Count])
/
(IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0, [Product].[Product Categories].DefaultMember, [Product].[Product Categories].CurrentMember.Parent)
,IIF([Date].[Calendar].CurrentMember.Level.Ordinal = 0, [Date].[Calendar].DefaultMember, [Date].[Calendar].CurrentMember.Parent)
,IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal = 0, [Customer].[Customer Geography].DefaultMember, [Customer].[Customer Geography].CurrentMember.Parent)
,[Measures].[Customer Count])
)
)
)
)
' ,SOLVE_ORDER=10, FORMAT_STRING="#,0.0%"
SELECT
{[Measures].[Customer Count], Measures.[Percent Customer Count]} ON COLUMNS
,{[Product].[Product Categories].[All]
,DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Category])} ON ROWS
FROM [Adventure Works]
WHERE(
[Date].[Calendar].[Calendar Year].&[2003]
,[Customer].[Customer Geography].[Country].&[United States]
)
My code copy into this window didn't carry over very well. If you'd like the .MDX file witht he query then drop me a note at paul_goldy@.hotmail.com
Good Luck.
PGoldy
|||
PGoldy,
Thank you...that worked!!! I do have a few questions though.
1) My understanding was that the solve order was no longer necessary in AS2005 because it relies on the order of the MDX script. Therefore, I did not include the solve order=10 because I wasn't sure if 10 was the correct number given the other calculations in the cube. Is it OK to eliminate it?
2) I'm not sure how to ask this question, so please bear with me if it doesn't make sense. Since the calculated percent is for the current dimension the end user is browsing by.... what happens if the first clause of the IIF in the first dimension is true? Does it return NULL and then not move on to the other dimensions? For example, if the following conditions are both met
[Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))
will it return a NULL value for the % when viewing the Product dimension and then STOP or will it continue to evaluate all the other conditions/dimensions as well.
Thanks again.
SAP2006
|||
Hi. Glad to be of help, and thanks for the questions.
(1) You are correct about the SOLVE_ORDER, it is not required and you can eliminate it. I'm afraid I used it out of habit.
(2) Your second question makes sense. Yes, if one of the initial IIF() conditions are met ( .Oridinal <> 0 AND ISEMPTY()) then the expression returns NULL as the result. I'm afraid this is required because if ANY of the denominator values are empty then you get a divide by 0 error - even though the user may only "see" a specific dimension they are drilling up/down on. Sory if that's bad news, but that's how the calculations work.
Good Luck.
PGoldy
|||
I think we will be OK with the nested IIFs. I just wanted to make sure that I understood what the code was doing. Thanks again for all your help.
SAP2006
|||
Hi PGoldy,
The code you provided previously works perfectly if we are only viewing one dimension at a time (column percentages). However, when I do a cross-tab of two or more dimensions, the code is giving me column percentages and what I really need are row percentages. I know I need to change the denominator, but I'm unsure how to write the MDX code given that the denominator could be any dimension in the cube and therefore will be different depending on which dimension the user is browsing. Is there a way to do this?
[DimLocation].[DimLocation].CurrentMember,[Measures].
/
?
where ? could be any of the following:
[DimDischargeYear].[DimDischargeYear].CurrentMember, [Measures].
[DimAdmissions].[DimAdmissions].CurrentMember, [Measures].
.........
[DimLevel].[DimLevel].CurrentMember, [Measures].
Any help would be appreciated.
Thanks, SAP2006
|||
Sorry, but I don't understand why the percentage calculation only shows corretly on columns. The calculation is not specific to rows or columns, but only adheres to the relative position of members in a hierarchy (children, parents). Perhaps you could provide a complete MDX query which shows the problem?
Also, I'm a little confused what you mean by a "...cross tab of two or more dimensions...". Do you mean you have nested dimensions on the rows and columns, or do you use the PAGE Axis and have multiple tabs of data?
PGoldy
|||
Sorry, I didn't explain myself better. You are correct. Currently, BIDS is calculating a percentage relative to the position of members of in the hierarchy (see table 1), for example, 3849/23343=16.5%. What I would like is a percentage of the row (see table 2), for example 3849/7440=51.7%.
By cross-tab, I simply meant a query using two or more dimensions. For example in the query below, I have Location on rows and Discharge Year on columns. There may be instances where I have nested dimensions as well, for example, Location and Admissions on rows and Discharge Year on columns. I hope this clarifies things.
| TABLE 1 | | | | | |
| Discharge Year |
| Location | 2004 | 2005 | Grand Total |
| N | PercentCases | N | PercentCases | N | PercentCases |
| A | 3849 | 16.5% | 3591 | 15.4% | 7440 | 31.9% |
| B | 2887 | 12.4% | 2841 | 12.2% | 5728 | 24.5% |
| C | 5103 | 21.9% | 5072 | 21.7% | 10175 | 43.6% |
| Grand Total | 11839 | 50.7% | 11504 | 49.3% | 23343 | 100.0% |
| | | | | | |
| | | | | | |
| | | | | | |
| TABLE 2 | | | | | |
| Discharge Year |
| Location | 2004 | 2005 | Grand Total |
| N | PercentCases | N | PercentCases | N | PercentCases |
| A | 3849 | 51.70% | 3591 | 48.30% | 7440 | 31.9% |
| B | 2887 | 50.40% | 2841 | 49.60% | 5728 | 24.5% |
| C | 5103 | 50.20% | 5072 | 49.80% | 10175 | 43.6% |
| Grand Total | 11839 | 50.7% | 11504 | 49.3% | 23343 | 100.0% |
Thanks, SAP2006
|||
Hi, and thanks for the clarification. I understand what you're trying to do. If we define what you're looking for...you'd like to get the percentage contribution for unrelated members. In other words, you want to know how much Location A is contributing to Total Years (3849/7440 = 51.7%). This is a tough problem which is usually solved by a client side tool which takes apart the query result cell set and provides the % calcs as a client side activity without using MDX. Tools like Analyzer2005, and ProClarity demonstrate this capability well. I'm sure there are other tools that do as well, but I'm not as familiar with them.
It may be possible to do what you'd like as an MDX calculation, but it involves retrieving the current tuples defined on the axes (rows or columns). A function which allows retrieval of tuples from the axes is the MDX AXIS() function. I haven't taken a look at the AXIS() function since AS2005 came out because it only worked in limited cases in AS2000. You can find more information on the AXIS() function here:
http://msdn2.microsoft.com/en-us/library/ms145531.aspx
I'll also spend some time tinkering with it to see if we can retrieve current tuples on the axes and post any results here, but don't hold your breath. This is a tough problem usually solved by client side applications operating on the resulting cell-set.
PGoldy
|||
PGoldy,
We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:
"The function expects a string or numeric expression for the argument. A tuple set expression was used."
The MDX code I processed was
IIF(IsEmpty([Measures].),NULL,
([DimLocation].[DimLocation].CurrentMember,[Measures].) /
(AXIS(1),[Measures].))
Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.
Thanks, SAP2006
|||
PGoldy,
We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:
"The function expects a string or numeric expression for the argument. A tuple set expression was used."
The MDX code I processed was
IIF(IsEmpty([Measures].),NULL,
([DimLocation].[DimLocation].CurrentMember,[Measures].) /
(AXIS(1),[Measures].))
Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.
Thanks, SAP2006
|||
Hello,
I am new to MDX and I have a similar situation as discussed above i.e. finding % contribution to totals. I am using AS2005. Any idea of how this can be done?
Thanks,
Kulathu Sarma
|||
Hi,
Unfortunately, we haven't figured it out yet. However, in January, we have a consultant coming onsite to help with various issues. I'll post the solution if we find it.
SAP
|||
SAP,
It looks like you're working on a health-related application. We've also just started using SSAS2005 for cancer services data and are currently grappling with the same problem - creating row and column percentage measures that are not tied to specific dimensions. I've tried the Axis function without success; it returns a set that's the same as the current member or cell, so the percentage measure based on it is always 100%. This is probably not how it's supposed to work. Would be grateful for any advice in this regard...
QCCAT
|||
QCCAT,
The following MDX returns row percentages. Good Luck!!
//Begin - Percent Cases
CREATE MEMBER CURRENTCUBE.MEASURES.[Percent Cases]
AS
IIF(Axis(0)(0).Count < 2,
(Axis(0)(0).Dimension.CurrentMember,Measures.N)
/
Sum
(Filter(Axis(0),
InStr(Axis(0)(0).Dimension.CurrentMember.Name,"Total") = 0),
Measures.N),
NULL),
FORMAT_STRING = "0.0%",
VISIBLE = 1;
//End - Percent Cases
