April 3, 2012 at 6:53 am
Hi all,
I have a strange problem with with the results of the Total value in SSAS.
Basically, I have a calculated measure named Remaining Support Hours as per Agreement and when I drag this calculated measure in the cube browser, the Totals value is incorrect as per below:
Remaining Support Hours as per Agreement
MainCategory
SubCategory1
SubCategory2
SubCategory311.00
Total 7.50
The definition of the calculated measure is the following:
CREATE MEMBER CURRENTCUBE.[Measures].[Remaining Support Hours as Per Agreement]
AS iif ( isempty([Measures].[Support Hours Bought]) OR [Measures].[Support Hours Bought] = 0, NULL, [Measures].[Support Hours Bought] - [Measures].[Duration in hours]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Category' ;
The Total is incorrect because it is ignoring the IIF statement in the calculation.
Can anyone pleae shed some light on how to solve this problem?
Thanks!
Jon
April 3, 2012 at 11:44 pm
How do you know, it is ignoring the IIF.
Can you give some more samples?
Raunak J
April 3, 2012 at 11:52 pm
Hi Raunak,
It is ignoring the IIF because if I create another calculated measure with same expression but without the IIF as per:
CREATE MEMBER CURRENTCUBE.[Measures].[RSH]
AS [Measures].[Support Hours Bought] - [Measures].[Duration in hours],
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1 ;
I get the following result set:
Remaining Support Hours as per Agreement RSH
MainCategory
SubCategory1
SubCategory2 -3.5
SubCategory3 11.00 11.00
Total 7.50 7.50
Do you know why this is happening?
Thanks
April 4, 2012 at 12:03 am
Hello Jon,
Can you give your complete MDX? It would be lot easier to understand. Also, how are you populating the "Total" field and why are there two numerical columns in the image
Raunak J
April 4, 2012 at 12:20 am
Hello Raunak,
This is the complete MDX for the calcualted members. There are 2 numeric columns because I am adding 2 calculated members, named Remaining Support Hours as Per Agreement and RSH (one with IIF and one without).
CREATE MEMBER CURRENTCUBE.[Measures].[Remaining Support Hours as Per Agreement]
AS iif ( isempty([Measures].[Support Hours Bought]) OR [Measures].[Support Hours Bought] = 0, NULL, [Measures].[Support Hours Bought] - [Measures].[Duration in hours]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
NON_EMPTY_BEHAVIOR = { [Duration in hours], [Support Hours Bought] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Category';
CREATE MEMBER CURRENTCUBE.[Measures].[RSH]
AS [Measures].[Support Hours Bought] - [Measures].[Duration in hours],
FORMAT_STRING = "#,##0.00;-#,##0.00",
NON_EMPTY_BEHAVIOR = { [Duration in hours], [Support Hours Bought] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Category' ;
Thanks again.
Jon
April 4, 2012 at 1:02 am
Jon consider a case where Support Hours Brought is NULL, in this case the expression would evaluate to NULL - {numerical value}.
Change the true part of IIF to "0"
Raunak J
April 4, 2012 at 1:31 am
Hi Raunak,
I have changed the part of the expression you pointed out to:
iif ( isempty([Measures].[Support Hours Bought]) OR [Measures].[Support Hours Bought] = 0, 0, [Measures].[Support Hours Bought] - [Measures].[Duration in hours])
but the Total still shows the incorrect value, ie 7.5 instead of 11.
Thanks
Jon
April 4, 2012 at 3:05 am
Managed to solve this using SCOPE although not perfect.
Cheers
April 12, 2012 at 8:29 am
Jon,
Do you understand why you had to use a SCOPE to get your total to give you the answer that you desired? If you do not, then ask yourself this: what is the value of [Measures].[Support Hours Bought] and of [Measures].[Duration in hours] if the [Category] hierarchy (or whatever you call it) were not included in your query?
You say that the SCOPE is not perfect. What's wrong?
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
April 12, 2012 at 10:19 am
Hi Chris,
SCOPE definately helped since the calculated member will be evaluated when the Category is selected on the axis.
When the Category is not selected, on its own, the calculation [Measures].[Support Hours Bought] - [Measures].[Duration in hours] would not really makes sense as [Measures].[Support Hours Bought] is an integral part of Category.
If Category were not selected, and simply query the calculation [Measures].[Remaining Support Hours as Per Agreement], the desired returned value would be something like Null or "Not Applicable".
What I am not entirely convinced is that if the attribute User (the employee who made use of the suppost hours) is queried with [Measures].[Remaining Support Hours as Per Agreement], it should return a value even though Category is not explicitly queried.
I would appreciate your views.
Thanks,
Jon
April 12, 2012 at 12:25 pm
Jon,
Unless you have explicitly set up a default member for the Category dimension(not something I typically do), SSAS should use the [ALL] member as default. Therefore, [Category].[Categories].[ALL] is exactly what the "Total" in your previous queries is representing. If the desired behavior for the ALL member of Category to always be NULL, what you are really asking for is this: the only time [Measures].[Support Hours Bought] will ever contain a value is when sliced by any non-all member of any Category Hierarchy.
To illustrate the concept, I'll use your numbers and substitute the behavior for [Category].[ALL] you have described here: "When the Category is not selected, on its own, the calculation [Measures].[Support Hours Bought] - [Measures].[Duration in hours] would not really makes sense as [Measures].[Support Hours Bought] is an integral part of Category."
For now, let's deal only with the measure named [Support Hours Bought]. We can expand from there later.
[Category] [Support Hours Bought]
MainCategory1 11
SubCategory1 null
SubCategory2 null
SubCategory3 11
Total null
In the above example, "Total" is NULL because of your desire to over-ride the aggregation at the [ALL] member of category. Well that is not what you want for sure. If you over-ride [Category].[ALL], you are affecting a vast swath of the cube. So your suspicion is confirmed. If you SCOPE [Category].[ALL], here is what you should expect to see when you write a query that omits it. For example:
Select [Users].allmembers on rows,
[measures].[Support Hours Bought] on columns
From yourCube
[Users] [Support Hours Bought]
Bgates null
Jmallia null
Suser null
Total null
Why? Because you have over-ridden the behavior of the intersection between [Category].[ALL] and [measures].[Support Hours Bought] (or at least for argument's sake you have).
What I thought you wanted was the following:
[Category] [Remaining Hours as Per Agreement]
MainCategory1 11
SubCategory1 null
SubCategory2 null
SubCategory3 11
Total 11
Where the -3.5 is ignored because apparently there is no purchase agreement for the 3.5 hours incurred.
If that is the case, you would necessarily need to accept that any time category is not in scope (which is to say that [Category].[ALL] IS in scope) you will see the SCOPE override behavior of 11. The reason you were getting 7.5 is because the first condition in your iif evaluated to false. When [Category].[ALL] intersects [Support Hours Bought], the value of course is neither null nor 0, it is in fact 11. Therefore, the iif evaluates to FALSE shunting you over to the ELSE condition of [Bought] - [Duration] and leaving you with 7.5.
I would be tempted to define [Remaining Hours as Per Agreement] as follows
CREATE MEMBER CURRENTCUBE.[Measures].[Remaining Support Hours as Per Agreement]
AS [Measures].[Support Hours Bought] ,
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Category' ;
Below that, I would write a scope statement acting on everything which is not [Category].[ALL]. Something like this:
SCOPE ( [Measures].[Remaining Hours as Per Agreement],
(EXCEPT( [Category].Members,
[Category].[ALL])
);
THIS = ( [measures].[Support Hours Bought] - [measures].[Duration in Hours]);
In this way, you should have your calculation set up to simply show the number of support hours bought everywhere except when sliced by individual (non-all) category members. Caution: It looks like you have a level above sub category in the [category] hierarchy, named Main Category. You will have to deal with that as well since it has the same problem as the ALL member of category. The intersection of [Category].[Main Category].[MainCategory1] and [Measures].[Support Hours Bought] is neither null nor zero, so your scoped calculation will spit out 7.5 at that level of aggregation.
Sorry for the long response. Could not think of a more efficient way to communicate my thoughts. I hope this helps.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
April 13, 2012 at 12:04 am
Hi Chris,
Thanks for your valuable insights and for your time writing this excellent post.
You are spot on on what I was looking for and how you suggested in creating the member and SCOPE really make sense.
My attempt at the SCOPE was like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Remaining Support Hours as Per Agreement]
AS iif ( isempty([Measures].[Support Hours Bought]) OR [Measures].[Support Hours Bought] = 0, 0,
[Measures].[Support Hours Bought] - [Measures].[Duration in hours]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
NON_EMPTY_BEHAVIOR = { [Duration in hours], [Support Hours Bought] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Category';
SCOPE([Measures].[Remaining Support Hours as Per Agreement],[Category].[Category].members);
This =sum(
Descendants([Category].[Category].CurrentMember,,LEAVES),
iif ( isempty([Measures].[Support Hours Bought]) OR [Measures].[Support Hours Bought] = 0, NULL,
[Measures].[Support Hours Bought] - [Measures].[Duration in hours])
);
END SCOPE;
How do you suggest go about modifying SCOPE to also cater for the level above category Main Category?
Can it be altered to something like this?
SCOPE ( [Measures].[Remaining Hours as Per Agreement],
(
EXCEPT(
(EXCEPT( [Category].Members, [Category].[ALL]))
, [Category].[MainCategory1].[ALL])
)
;
THIS = ( [measures].[Support Hours Bought] - [measures].[Duration in Hours]);
Cheers,
Jon
April 16, 2012 at 6:53 am
Jonathan Mallia (4/13/2012)
How do you suggest go about modifying SCOPE to also cater for the level above category Main Category?Can it be altered to something like this?
SCOPE ( [Measures].[Remaining Hours as Per Agreement],
(
EXCEPT(
(EXCEPT( [Category].Members, [Category].[ALL]))
, [Category].[MainCategory1].[ALL])
)
;
THIS = ( [measures].[Support Hours Bought] - [measures].[Duration in Hours]);
Jon,
I am not certain about the nested EXCEPT, but if that does work I see a problem with your second member statement "[Category].[MainCategory1].[ALL]". I suspect that you would like to modify the behavior of all members at the Main Category level not just the member named "MainCategory1". If that attribute is named "MainCategory" you can simply call it by it's attribute name and you will affect all MainCategory members "[Category].[MainCategory]". I further suspect that you have a UDH defined for this multilevel hierarchy. Assuming the UDH is named "Categories" you could call it like this: "[Category].[Categories].[MainCategory]". "[Category].[ALL] refers to a specific member, the "ALL" member. Again, if you have a UDH, you should reference the "ALL" member using that UDH. (i.e. [Category].[Categories].[ALL])
Regarding the nested EXCEPT: If that does not work, I'd just make a second scope statement.
Good Hunting!
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
April 16, 2012 at 7:35 am
Hi Chris,
Unfortunately I cannot test the nested Except as I do not have access to the system at the moment, so it was more like a shot in the dark, however technically should work.
How do you propose having two SCOPE statements with regards to priority of the SCOPE when having more than one assignment?
You rekon something like this should hold?
SCOPE ( [Measures].[Remaining Hours as Per Agreement],
(
(EXCEPT( [Category].Members, [Category].[ALL]))
)
;
THIS = ( [measures].[Support Hours Bought] - [measures].[Duration in Hours]);
SCOPE ( [Measures].[Remaining Hours as Per Agreement],
(
(EXCEPT( [Category].Members, [Category].[Categories].[MainCategory].[ALL]))
)
;
THIS = ( [measures].[Support Hours Bought] - [measures].[Duration in Hours]);
Thanks,
Jonathan
April 16, 2012 at 8:19 am
That's not going to work after all Jon. Bad suggestion, sorry. You are correct, the second scope may over-ride the first if it is not carefully crafted. Your second scope would have to operate on the MainCategory level ONLY. Otherwise you'll reset the ALL member behvior.
Rather than using the nested EXCEPT, you can include a set in the second paramater of the EXCEPT() function. In fact, the syntax is EXCEPT({set1}, {set2}). Your first set is what you are operating on, the second is the set of members you wish to exclude.
Try something like this EXCEPT([Category].members, {[Category].[ALL], [Category].[MainCategory]})
Which is to say, retuen all members of the [Category] dimension, EXCEPT the "ALL" member and any member belonging to the [MainCategory] attribute.
Give that a try!
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply