InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 22:41:51 GMT20http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxIsError() 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.ChrisWed, 13 Mar 2013 03:57:10 GMTChris Webb-392142http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxWow, 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.Tue, 12 Mar 2013 21:34:56 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxThe 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)>1HTH,ChrisTue, 12 Mar 2013 03:14:02 GMTChris Webb-392142http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxThanks. Chris has given some input on another aspect of this problem in another forum and I am following this up with him too.--Matt.Mon, 11 Mar 2013 18:01:42 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxAt this point it might be worth asking an MDX master like [url=http://cwebbbi.wordpress.com/]Chris Webb[/url] (@Technitrain)Mon, 11 Mar 2013 17:54:41 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxLike 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 all2. The THEN part of the IIF statement3. Something going wrong in the subtraction within the ELSE part of the IIF statement.So I tried this:[code]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; [/code]... and then ran this query ...[code]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 rowsfrom Compass3[/code]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?Mon, 11 Mar 2013 01:17:24 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspx[quote] that occurs any time I add *any* WITH clause. [/quote]Oh yeah I forgot about that. How about adding these tests as calcs into the cube rather than on-the-fly calcs? [quote] [Acquisitions (during period)] and [Growth in Commitments (during period)] are themselves calculated measures, but both they and their constituents always have correct values. [/quote]Well that rules them out.[quote] 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!!![/quote]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.[quote]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. [/quote]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!Mon, 11 Mar 2013 00:01:24 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxOK, I tried this (a slight variation on your suggestion):[code]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 rowsfrom Compass3[/code]... with this result ...[code] 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,0522005\06 19,175 14,392 0 4,783 4,783[/code]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].prevMemberSo how's this for weird ...[code]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 rowsfrom Compass3 Net Lost Commitments (during period) Net Lost Commitments (during previous period)2004\05 (null) 3,2552005\06 4,783 4,052[/code]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.Sun, 10 Mar 2013 22:55:08 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxAh 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).[code="other"]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 rowsfrom Compass3[/code]More questions:Do you get any error when using the [i]Calendar[/i] 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?Wed, 06 Mar 2013 17:50:49 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspx@davoscollective (I keep wanting to write DavrosCollective, with Dr Who overtones:-)) asks to see the calculation that defines [Measures].[Net Lost Commitments].[code]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; [/code]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.Wed, 06 Mar 2013 00:19:32 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspx@davoscollective suggested running this query (I've changed it to reflect the relevant years):[code]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 rowsfrom Compass3[/code]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].Wed, 06 Mar 2013 00:06:19 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxI'll answer @davoscollective's three questions separately.First, the query ...[code]select [Measures].[Net Lost Commitments] on columns , ([Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].Allmembers) on rowsfrom Compass3[/code]... 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].Tue, 05 Mar 2013 23:55:09 GMTmclarke 40933http://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxCan 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?[code="other"]select[Measures].[Net Lost Commitments] on columns , ([Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].Allmembers) on rowsfrom Compass3[/code]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:[code="other"]select [Measures].[Net Lost Commitments] on columns , {[Date Dimension].[Fiscal Year].[Fiscal Year].[2009\10],[Date Dimension].[Fiscal Year].[Fiscal Year].[2010\2011]} on rowsfrom Compass3[/code]Mon, 04 Mar 2013 22:59:36 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1426576-17-1.aspxI'm trying to diagnose a bug in a calculated measure in a SSAS cube, and while debugging I get this weird behaviour ...[code]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 rowsfrom Compass3[/code]...returns the following:[code] Net Lost Commitments 2009\10 (null) 2010\11 9,937[/code]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 ...[code]with member [Measures].[test] as nullselect [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 rowsfrom Compass3[/code]...returns the following:[code] Net Lost Commitments 2009\10 8,783 2010\11 9,937[/code]Have you ever seen that before? What could possibly be the cause?--Matt.Mon, 04 Mar 2013 20:38:27 GMTmclarke 40933