Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Different aggreation results with and without a dummy WITH clause Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 8:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
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.
Post #1426576
Posted Monday, March 4, 2013 10:59 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:48 PM
Points: 482, Visits: 889
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

Post #1426601
Posted Tuesday, March 5, 2013 11:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
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].
Post #1427186
Posted Wednesday, March 6, 2013 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
@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].
Post #1427193
Posted Wednesday, March 6, 2013 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
@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.
Post #1427198
Posted Wednesday, March 6, 2013 5:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:48 PM
Points: 482, Visits: 889
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?


Post #1427707
Posted Sunday, March 10, 2013 10:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
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 ISERROR	Net Lost Commitments TEST CALC	Net 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.
Post #1429071
Posted Monday, March 11, 2013 12:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:48 PM
Points: 482, Visits: 889
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!
Post #1429093
Posted Monday, March 11, 2013 1:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:26 PM
Points: 31, Visits: 59
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?
Post #1429100
Posted Monday, March 11, 2013 5:54 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:48 PM
Points: 482, Visits: 889
At this point it might be worth asking an MDX master like Chris Webb (@Technitrain)
Post #1429546
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse