March 4, 2013 at 8:38 pm
I'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this weird behaviour ...
select
[Measures].[Net Lost Commitments] on columns
, {[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].[2009\10],[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].[2010\11]} on rows
from Compass3
...returns the following:
Net Lost Commitments
2009\10 (null)
2010\11 9,937
I'm trying to figure out why the value for 2009\10 is null. It should not be and I cannot see why the calculated measure Net Lost Commitments would work for most time periods but not for FY 2009/10. So I've been adding various other calculations via a WITH clause to help track back the problem. But any WITH clause I add changes the results, even if the calculation in the WITH clause is not used. For instance ...
with
member [Measures].[test] as null
select
[Measures].[Net Lost Commitments] on columns
, {[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].[2009\10],[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].[2010\11]} on rows
from Compass3
...returns the following:
Net Lost Commitments
2009\10 8,783
2010\11 9,937
Have you ever seen that before? What could possibly be the cause?
--Matt.
March 4, 2013 at 10:59 pm
Can you post the calculation that defines [Measures].[Net Lost Commitments] in the cube?
Has it perhaps been designed as an accumulated measure?
What happens if you run this query?
select
[Measures].[Net Lost Commitments]
on columns
,
([Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].Allmembers) on rows
from Compass3
Is that actually a hierarchy or is it a standard dimension? Assuming there's a dimension named [Fiscal Year] what happens if you run this:
select
[Measures].[Net Lost Commitments] on columns
, {[Date Dimension].[Fiscal Year].[Fiscal Year].[2009\10],[Date Dimension].[Fiscal Year].[Fiscal Year].[2010\2011]} on rows
from Compass3
March 5, 2013 at 11:55 pm
I'll answer @davoscollective's three questions separately.
First, the query ...
select
[Measures].[Net Lost Commitments] on columns
, ([Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].Allmembers) on rows
from Compass3
... lists all years from 1899\00 to 2024\25, with correct values (as far as I have tested) for all years except 2004\05 which shows (null).
In my original post, I described a (null) value being shown for 2009\10. Since then I have made minor changes and rebuilt the cube. Every time I rebuild, the (null) value(s) move to different time periods. This time round the correct value is shown for [Fiscal Year].[2009\10] but there are nulls for [Fiscal Year].[2004\05] and [Calendar Month].[Dec-2006].
March 6, 2013 at 12:06 am
@davoscollective suggested running this query (I've changed it to reflect the relevant years):
select
[Measures].[Net Lost Commitments (during period)] on columns
, {[Date Dimension].[Fiscal Year].[Fiscal Year].[2004\05],[Date Dimension].[Fiscal Year].[Fiscal Year].[2005\06]} on rows
from Compass3
It returned (null) for [2004\05] and the correct value (4783) for [2005\06].
My [Date Dimension] has two hierarchies: [Fiscal Year Hierarchy] and [Calendar Year Hierarchy]. [Fiscal Year] is a dimension attribute of the [Date Dimension] dimension, and included as the top level of the [Fiscal Year Hierarchy].
March 6, 2013 at 12:19 am
@davoscollective (I keep wanting to write DavrosCollective, with Dr Who overtones:-)) asks to see the calculation that defines [Measures].[Net Lost Commitments].
CREATE MEMBER CURRENTCUBE.[Measures].[Net Lost Commitments (during period)]
AS null
, FORMAT_STRING = '#,#'
, ASSOCIATED_MEASURE_GROUP = 'Commitment Measures'
, DISPLAY_FOLDER = 'Time-based Commitment Measures'
, VISIBLE = true;
SCOPE (DESCENDANTS([Date Dimension].[Fiscal Year Hierarchy],,AFTER));
-- [Net Lost Commitments] can only be calculated if [Growth in Commitments] can be,
-- and that is only possible if just one member is selected from the date dim.
-- So if referring to [Date Dim].currentMember fails (probably coz multiple members have been selected) then return null.
[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)
, null
, [Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)]);
END SCOPE;
SCOPE (DESCENDANTS([Date Dimension].[Calendar Year Hierarchy],,AFTER));
[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Calendar Year Hierarchy].CurrentMember)
, null
, [Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)]);
END SCOPE;
You can see that it is not the most straight-forward measure due to the IIF(ISERROR()) construct. I can explain why that is used if that's helpful and can describe various debugging experiments I have tried. But whatever I consider, I can't understand why it works for 99% of cases, and why the rare cases that return null keeps moving when I rebuild.
--Matt.
March 6, 2013 at 5:50 pm
Ah yes I am a Doctor Who fan, but I wasn't referencing the mastermind behind the Daleks! It's a fine line between economic forum and creatures genetically altered to be evil incarnate, but I digress.
I can see you're scoping the calcs for both hierarchies, avoiding the ALL member trap by using descendents, very good. I am not familiar with using iserror in MDX. I've read a little and it seems to be useful for missing members. I am wondering if something has happened to the 2004/2005 member when you've reprocessed the cube.
The comments you've written in there regarding why you are using iserror (selecting multiple members) confuses me. CurrentMember always expects a member, not a tuple or a set so when would it be applied to multiple members and error? Yes please explain that part. I don't think the iserror is the problem though.
To rule out the iserror you could try a query like this (sorry I haven't checked syntax here, might need to add a sensible format to the calculated measure)
--Note, try some different combinations of the measures in this query, particularly the two non-calculated measures by themselves that make up the calculated measure (Aquisitions & growth).
WITH [Measures].[Net Lost Commitments (during period) TEST ISERROR] as
iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)
, 1
, 0)
[Measures].[Net Lost Commitments (during period) TEST CALC] as
([Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)])
select
[Measures].[Acquisitions (during period)]
,[Measures].[Growth in Commitments (during period)]
,[Measures].[Net Lost Commitments (during period) TEST ISERROR]
,[Measures].[Net Lost Commitments (during period) TEST CALC]
,[Measures].[Net Lost Commitments (during period)]
on columns
, {[Date Dimension].[Fiscal Year].[Fiscal Year].[2004\05],[Date Dimension].[Fiscal Year].[Fiscal Year].[2005\06]} on rows
from Compass3
More questions:
Do you get any error when using the Calendar hierarchy against the calculated measure? I realise the members will be different but I'm curious if any of those calendar year members are null.
Stepping back earlier in the process, if you query the datawarehouse (I'm assuming you have one) directly for fiscal years in your calendar table and the two facts ([Measures].[Acquisitions (during period)] AND [Measures].[Growth in Commitments (during period)]) that make up the calculation, can you confirm all the data is there for that 2004/2005 year? All the data is clean?
March 10, 2013 at 10:55 pm
OK, I tried this (a slight variation on your suggestion):
WITH
member [Measures].[Net Lost Commitments TEST ISERROR] as
'iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)
, 1
, 0)'
member [Measures].[Net Lost Commitments TEST CALC] as
'[Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)]'
select
{[Measures].[Acquisitions (during period)]
,[Measures].[Growth in Commitments (during period)]
,[Measures].[Net Lost Commitments TEST ISERROR]
,[Measures].[Net Lost Commitments TEST CALC]
,[Measures].[Net Lost Commitments (during period)]
} on columns
, {[Date Dimension].[Fiscal Year].[Fiscal Year].[2004\05],[Date Dimension].[Fiscal Year].[Fiscal Year].[2005\06]} on rows
from Compass3
... with this result ...
Acquisitions (during period)Growth in Commitments (during period)Net Lost Commitments TEST ISERRORNet Lost Commitments TEST CALCNet Lost Commitments (during period)
2004\05 14,480 10,428 0 4,052 4,052
2005\06 19,175 14,392 0 4,783 4,783
All those numbers seem correct to me. You'll notice that the value coming from the cube for "Net Lost Commitments (during period)" is no longer null: as I mentioned in the initial post, that occurs any time I add *any* WITH clause.
With regard to checking backwards through the values on which [Net Lost Commitments (during period)] depends, I have not been able to find any errors or missing values. [Acquisitions (during period)] and [Growth in Commitments (during period)] are themselves calculated measures, but both they and their constituents always have correct values.
What's more I have a [Measures].[Net Lost Commitments (during previous period)] defined as:
[Measures].[Net Lost Commitments (during period)],[Date Dimension].[Fiscal Year Hierarchy].prevMember
So how's this for weird ...
select
{[Measures].[Net Lost Commitments (during period)]
,[Measures].[Net Lost Commitments (during previous period)]
} on columns
, {[Date Dimension].[Fiscal Year].[Fiscal Year].[2004\05],[Date Dimension].[Fiscal Year].[Fiscal Year].[2005\06]} on rows
from Compass3
Net Lost Commitments (during period)Net Lost Commitments (during previous period)
2004\05 (null) 3,255
2005\06 4,783 4,052
So Net Lost Commitments (during previous period) for [Fiscal Year].[2005\06] is the correct value of 4,052 even though the value it depends on is shown as null!!!
As you mentioned, the example I give is from [Fiscal Year] and I do also have a [Calendar Year]. I get similar problems with the calendar year hierarchy. At the moment, no [Calendar Year] returns null, by [Calendar Month].[Dec-2006] does return null. Both Fiscal and Calendar hierarchies seem to function 100% OK for all other measures.
March 11, 2013 at 12:01 am
that occurs any time I add *any* WITH clause.
Oh yeah I forgot about that. How about adding these tests as calcs into the cube rather than on-the-fly calcs?
[Acquisitions (during period)] and [Growth in Commitments (during period)] are themselves calculated measures, but both they and their constituents always have correct values.
Well that rules them out.
So Net Lost Commitments (during previous period) for [Fiscal Year].[2005\06] is the correct value of 4,052 even though the value it depends on is shown as null!!!
Now that is weird. Prevmember should just walk back one step on the members of the dimension and do effectively the same tuple as the actual member.
As you mentioned, the example I give is from [Fiscal Year] and I do also have a [Calendar Year]. I get similar problems with the calendar year hierarchy. At the moment, no [Calendar Year] returns null, by [Calendar Month].[Dec-2006] does return null.
A month might legitimately have no result in it. Not much you can do about that apart from force a zero instead of a null but that is usually a bad idea because sparse cubes are quicker.
I don't really have any other suggestions for you but one (grasping at straws) idea is related to the order of calculations. I'm wondering in the calc definitions in the cube in what order these things are defined. If the calc on a calc comes before the calc it depends on, or when re-processing the cube it does the dependent measure after the measures that depend on it then I don't know what could happen.
The other thing that comes to mind is that if you're doing calcs based on other calcs (calcs with calc dependencies), are you able to re-write the calcs so they use all the original dimensions & measures so you remove any order-of-operations issues that might be occurring?
I doubt those things are problems but it's worth being empirical about your testing and trying everything, recording clear notes about your steps as you go.
This one is super strange, I hope you work it out and then let me know what it was!
March 11, 2013 at 1:17 am
Like you, I thought of putting fake values back into the cube's Calculation script rather than using WITH. I wanted to figure out whether null came from:
1. No calculation being performed at all
2. The THEN part of the IIF statement
3. Something going wrong in the subtraction within the ELSE part of the IIF statement.
So I tried this:
CREATE MEMBER CURRENTCUBE.[Measures].[Net Lost Commitments (during period)]
-- AS null
AS 9990
, FORMAT_STRING = '#,#'
, ASSOCIATED_MEASURE_GROUP = 'Commitment Measures'
, DISPLAY_FOLDER = 'Time-based Commitment Measures'
, VISIBLE = true;
SCOPE (DESCENDANTS([Date Dimension].[Fiscal Year Hierarchy],,AFTER));
-- [Net Lost Commitments] can only be calculated if [Growth in Commitments] can be,
-- and that is only possible if just one member is selected from the date dim.
-- So if referring to [Date Dim].currentMember fails (probably coz multiple members have been selected) then return null.
[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)
--, null
--, [Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)]);
, 9991
, 9992);
END SCOPE;
SCOPE (DESCENDANTS([Date Dimension].[Calendar Year Hierarchy],,AFTER));
[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Calendar Year Hierarchy].CurrentMember)
--, null
--, [Measures].[Acquisitions (during period)] - [Measures].[Growth in Commitments (during period)]);
, 9993
, 9994);
END SCOPE;
... and then ran this query ...
select
{[Measures].[Net Lost Commitments (during period)]
,[Measures].[Net Lost Commitments (during previous period)]
} on columns
, {[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Month].AllMembers} on rows
from Compass3
The results were [Net Lost Commitments (during period)] = 9992 for all periods (that's what I expect) -- *except* [Dec-2010] in which case the value shown is 9990.
Comment 1: Every time I rebuild, the null values seem to move around. In this case, the anomaly does not show up at the Year level but at the Month level.
Comment 2: I have just checked (again!) the order of measure definitions. Every measure in the calculation script is defined later in the script than any measure on which it depends.
Comment 3: The value 9990 implies that no calculation is being performed: that value is specified *outside* the SCOPE statements. So why would this one member of the date dimension not be covered by the SCOPE?
March 11, 2013 at 5:54 pm
March 11, 2013 at 6:01 pm
Thanks. Chris has given some input on another aspect of this problem in another forum and I am following this up with him too.
--Matt.
March 12, 2013 at 3:14 am
The problem is almost certainly the part of the calculation that uses IsError(); when you say
[Measures].[Net Lost Commitments (during period)] =
iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember)
What you're actually doing is checking whether the value of [Measures].[Net Lost Commitments (during period)] before you made the scoped assignment contains the error or not, which I don't think is what you wanted.
If you want to check for multi-select, what you should do is something like this:
[Measures].[Net Lost Commitments (during period)] =
iif(count(existing [Date Dimension].[Fiscal Year Hierarchy].Members)>1
HTH,
Chris
March 12, 2013 at 9:34 pm
Wow, so iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember) does not test whether the CurrentMember function returns an error? MDX is still very counter-intuitive to me in many ways.
Thanks for offering the alternative formulation. Over the past few days I have re-arranged measure definitions so that the need to check for more-than-one date range is no longer necessary. So the problem has (well, seems to have) gone away.
--Matt.
March 13, 2013 at 3:57 am
IsError() tests whether a cell value contains an error, not whether a function returns an error. That might happen when CurrentMember returns an error, but it's not quite the same thing.
Chris
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply