0845 643 64 63

Scope Problems with MDX Calculated Members

We were recently investigating a problem for a client regarding the use of Scope within MDX calculated members. The code in question was similar to this:

CREATE MEMBER
   CURRENTCUBE.[Measures].[Test Measure To Date]
   AS "NA", VISIBLE = 1;
Scope([Date].[Calendar].MEMBERS);
    [Measures].[Test Measure To Date] =
      SUM(NULL:[Date].[Calendar].CurrentMember,
        [Measures].[Test Measure]);
End Scope;
Scope([Date].[Fiscal].MEMBERS);
    [Measures].[Test Measure To Date] =
      SUM(NULL:[Date].[Fiscal].CurrentMember,
        [Measures].[Test Measure]);
End Scope;

Essentially the warehouse was providing a transaction table with credits and debits, this calculated measure was supposed to provide the current balance, summing all transactions to date (not just the current year/period etc, but the entire history). Scope is used to enable the calculation to work across two different date hierarchies, calendar and fiscal.

The problem was that even when the [Date].[Calendar] hierarchy was selected, the code still used the fiscal hierarchy to calculate the value.

This is caused by the fact that [Date].[Fiscal].MEMBERS includes the member [Date].[Fiscal].[All]. Consequently, even when the Fiscal hierarchy was not included in the query, its [All] member was effectively still within the scope. Thus the fiscal calculation was overriding the calendar calculation no matter what was selected.

The solution to this is to exclude [All] from the scope, which can be done by changing the code to the following:

CREATE MEMBER
   CURRENTCUBE.[Measures].[Test Measure To Date]
   AS "NA", VISIBLE = 1;
Scope(DESCENDANTS([Date].[Calendar],,AFTER));
    [Measures].[Test Measure To Date] =
      SUM(NULL:[Date].[Calendar].CurrentMember,
        [Measures].[Test Measure]);
End Scope;
Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
    [Measures].[Test Measure To Date] =
      SUM(NULL:[Date].[Fiscal].CurrentMember,
        [Measures].[Test Measure]);
End Scope;

DESCENDANTS(xxx,,AFTER) is a simple way of identifying every descendent of the hierarchy AFTER the current member, which is [All] when not specified.

Problem solved, Frog-blog out.

40 Responses to Scope Problems with MDX Calculated Members

  • Hi,

    ive used the above method in the following context:

    – two time dimensions(A, B), one calculated measure
    – for time dimension A, calc measure should display its value
    – for time dimension B, calc measure should = NULL

    – for time dimension A, the calc measure returns the correct values.
    – for time dimension B, the descendant levels return NULL. the [All] level however returns the calc measure’s total.

    How can I make this NULL also, without breaking the correct functionality as far as dimension A is concerned?

    Many thanks,

    Peter

    • Hi Peter

      The problem you have is that if time dimension B is not selected as part of a query, it is essentially selecting the [All] level by default. So if you scope the calculation to be null when Time B is [all] then it will always result in null, no matter whether [all] is physically specified or not.

      Does that make any sense?… Essentially you will have to allow the calculation to take place for [All], otherwise the calculation can never take place under any circumstances.

      Alex

  • Hi,

    this post was very helpful and it helped in resolving a similar issue in my project, and it has imporved the performance too. thank you very much for this.

    I have a requirement where the grand total needs to be not affected by anyfilters applied in the WHERE clause. is this techinically feasible?
    for e.g.
    With member MEASURES.Test AS
    SUM([Dispatch Country].[Country].Allmembers,Measures.[Value])
    SELECT MEASURES.Test on 0 from [ots]

    would give me the correct sum but when i filter it the value will only be the filtered one

    With member MEASURES.Test AS
    SUM([Dispatch Country].[Country].Allmembers,Measures.[Statistical Value])
    SELECT MEASURES.Test on 0 from [ots]
    where [Commodity].[Commodity Hierarchy].[HS4].&[Cheese];

    in fact i am using the SCOPE stament in my calculated measure, i do calculations at each level to have the leaf level values, but the grand total should always include the true total without the filtered. any help much appreciated

    thank you
    rakesh

    • Hi Rakesh
      If you’re filtering [Commodity].[Commodity Hierarchy] in the WHERE clause, you can make a calculated measure ignore this by specifying [Commodity].[Commodity Hierarchy].ALL in the measure calculation. The following does what you want inthe Adventure Works (2008R2) cube

      With member [Measures].[Full Order Count] AS
      ([Product].[Product Categories].[All Products],[Measures].[Order Count])
      SELECT {[Measures].[Full Order Count], [Measures].[Order Count]} on 0
      FROM [Adventure Works]
      WHERE [Product].[Product Categories].[Subcategory].&[1];

      Even though the WHERE clause is filtering the Product Categories hierarchy, the Member expression is overriding this by specifying [Product].[Product Categories].[All Products].

      Avoid using SUM(Hierarchy.AllMembers, Value) – you don’t need to use SUM, just use the single value of the ‘ALL’ member. It’s faster and more reliable as AllMembers will return the aggregated levels of the hierarchy as well as the leaf values, causing you to double count values.

  • Hi Alex,

    thanks for the reply, my issue is we use a Pivot control from devexpress to browse the cube and it doesnt have options to send mdx query nd fetch the values. however i shall try adding a calculated measure like this – ([Product].[Product Categories].[All Products],[Measures].[Order Count]) and then use that measure, need to see how it could be integrated with the pivot though

    also i was scope statements like this
    Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
    [Measures].[Test Measure To Date] =
    SUM(NULL:[Date].[Fiscal].CurrentMember,
    [Measures].[Test Measure]);
    End Scope;

    there is a set of rule i need to apply on the leaf level for which the above query works fine, but i also need to have to apply a separate set of rule for calculation on the Total – ALL level if i add another SCOPE with Dimension.Members it will not work since all the leaf members too will apply the same rule. any suggestion on how this can be handled.
    basically i want to have another scope that acts only on the ALL level, not on the leave Nodes

    any help much appreciated

    thanks
    rakesh

  • I have tried adding the code (with small mod for my hierarchy defs) to the XMLA for my cube, but it always returns only the 2nd scope item correctly. If I change the order i.e. put [Year-Week_Day] as second block, the 2nd one is now OK while 1st is not. Here’s my modified code:

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Subscribers Test] AS “NA”,VISIBLE = 1;
    SCOPE (DESCENDANTS([Dim Date].[Year-Week-Day],,AFTER));
    [Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Week-Day].CurrentMember,
    (IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
    END SCOPE;
    SCOPE (DESCENDANTS([Dim Date].[Year-Qtr-Mth-Date],,AFTER));
    [Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Qtr-Mth-Date].CurrentMember,
    (IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
    END SCOPE;

    What am I doing wrong? Also, when I try and build it within the BIDS environment, the calculated measure will fail.

    BTW, I will be a very happy camper if I can get this to work!

    • Hi Paul

      Sorry for the delay in replying, only just back after Christmas holidays.

      The syntax looks ok so it seems like both hierarchies are being selected in the query – do you have default members set for the hierarchies?

      Alex

      • Hi Alex

        How to show first month of any year to selected month while browsing a cube based on year hierarchy.
        could you please help me on that

        Thanks

  • I left blank – doesn’t that mean it will default to ‘ALL”? Or do I need explicitly set?

    • ALL is the default, so that’s not the problem. There must be something else interfering with it.

      I’d need to take a more detailed look to investigate – I’ll happily investigate if you can get me a backup of the cube, drop me an email at Alex at purple frog systems dot com if you want me to take a look.

      Alex

  • I am experiencing the same problem as Paul above.

    Do you recall a solution to the problem?

    • Hi David,
      I don’t think we ever investigated further. There are a large number of ways of approaching this, depending on the nature of the cube and the other calculations involved. If you want to send me a cut down sanitised cube, and your query then I’d happily take a look.
      Alex

  • Alex,

    I have a problem a little like Rakesh’s which I am struggling to overcome.

    My cube contains a calculated measure (headcount) which is defined like this:

    SCOPE([Measures].[Headcount]);
    — By Fiscal Year hierarchy
    SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Year].[Year].MEMBERS);
    THIS = Sum(
    null : [Date].[By Fiscal Year].CurrentMember,
    [Measures].[Starters] – [Measures].[Leavers]
    );
    END SCOPE;
    END SCOPE;

    I have simplified slightly as it is aggregated over three different date hierarchies but that’s not relevant for this issue.

    My problem is that we have a Boolean “Last 12 periods” attribute in the date dimension allowing users to create Excel pivot tables, etc. showing just the last 12 periods. Trouble is, if it is used as a filter, it appears to effectively create a subcube containing just the 12 periods and THEN applies the above scope – so the result is just the headcount movement over that time period, not the actual totals the user expects.

    Any thoughts on how to overcome this?

    Thanks.

    • Hi Nigel
      The problem, as you said, is that the [Last 12 periods].[True] member is being applied to the calculated measure.
      Therefore you just need to force the calculation to override this and use all dates. Something like this…

      SCOPE([Measures].[Headcount]);
      — By Fiscal Year hierarchy
      SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Year].[Year].MEMBERS);
      THIS = Sum(
      null : [Date].[By Fiscal Year].CurrentMember,
      ([Measures].[Starters], [Date].[Last 12 periods].[All])
      – ([Measures].[Leavers], [Date].[Last 12 periods].[All])
      );
      END SCOPE;
      END SCOPE;

      This changes the Starters and Leavers measures into tuples that forcibly scope the measure to all dates, within the last 12 periods and those outside it.

      Alex

  • Alex,

    That’s weird – I’m certain I tried just that the other day but without success. Your reply prompted me to have another go (I’ve also had a few days away from the problem which may have helped!) and that solution works fine.

    Must have made an error before without realising it – thanks very much for your suggestion.

    Regards,
    Nigel

    • Ah, no, now I’ve realised the difference…

      Trying this out quickly this morning, I used the VS cube browser rather than Excel. I applied the filtering in the Dimension/Hierarchy/Operator/Filter Expression section at the top of the browser which returned the expected result.

      Having just tried it (unsuccessfully) in Excel, I returned to the cube browser and, after re-confirming the result as I tried it this morning (just to make sure I’m not going mad!), I then applied the filter in the “Drop Filter Fields Here” section instead. This must be the equivalent of Excel’s “FILTERS” section as, like the latter, it produces a result only considering the cut-down cube, so again returns the headcount movement, rather than running totals.

      Presumably which way you apply the filtering affects the query generated by the browser which, in turn, affects the result 🙁

      • Hi Nigel
        This is an interesting topic – and shows off some of the subtle differences between different ways of writing MDX.
        If you wanted to filter by ‘z’ then there are many different ways of doing it, including:

        SELECT x ON 0, y ON 1 FROM [cube] WHERE z
        or
        SELECT x ON 0, y ON 1 FROM (SELECT z ON 0 FROM [cube])

        They look like they do the same thing, but there are some big differences. For example the first sets the context, therefore the cube knows the ‘currentmember’ is z. In the second this is not the case.

        Different cube browsers will generate different styles of MDX, which can materialise in different results. Just one of the many things we have to deal with,

        • Alex,

          Very true – in this case the users are using Excel pivot tables to access the cube data so we are constrained by the way it decides to generate its MDX.

          In fact, I now have a solution to this (with a lot of help from Chris Webb) which is described here:

          http://social.msdn.microsoft.com/Forums/en-US/d2e374de-97c0-48a5-8e4a-89d86996f40e/measure-aggregated-from-day-one-when-filtered-by-time-dimension-attribute-doesnt-work?forum=sqlanalysisservices

          Cheers,
          Nigel

  • I have a requirement to get the calculated member change the results based on the filter. But by doing the below the total is adding the Instances to all the products and environments and not the products and environments that I filter. What should I do. I only want the Calculated member to add the instances for the products and environemtns I filter and should not add everything in the table.

    CREATE MEMBER CURRENTCUBE.[Measures].[Totals]
    AS IIF(ISEMPTY([Measures].[Instances]),null,
    ([DimProduct].[Product].[ALL],[DimEnvironment].[Environment].[ALL],[Measures].[Instances])),
    NON_EMPTY_BEHAVIOR = { [Instances] },
    VISIBLE = 1 ;

    eg:

    FACT TABLE :
    PRODUCT ENVIRONMENT Instances
    A X 50
    A Y 50
    B X 50
    B Y 50
    C X 50
    C Y 50
    D X 50
    D Y 50

    filter
    products :A,B,C
    Environemtns : X,Y

    Product Instances Totals
    A 100 400
    B 100 400
    C 100 400

    the Product D, which is not in the filter has 100 instances.

    Expected Result

    Product Instances Totals
    A 100 300
    B 100 300
    C 100 300

    The mail goal for this is to find the percentage of each product from the total selected. So I wasn thinking of adding another column later and scope it to divide Instances/Totals.

    Please help.

    • Hi Raghav,
      You should be able to get what you need by restructuring your MDX.
      Put your products and environments filter into a subselect, which will limit the scope of the calculation.

      SELECT xxxxx
      FROM (SELECT {productA, productB, productC} ON 0 FROM (SELECT {EnvironmentX, EnvironmentY} ON 0 FROM [cubename]))

      Or something similar. Your total calculations should then execute on the subcube, not the whole cube.
      Alex

  • Good Morning Alex.
    It is second time i read your wonderful blog.

    I have a question…
    The scenario of this calculation , is in a query MDX but how can write that in a cube design ???
    Do i need to insert SCOPE Statement in Script View ???
    Do i need to define measure [Measures].[Test Measure To Date] as a real measure in measure group and then assign a value with SCOPE in Script View or can i define a calculated measure in TAB Calculation ?
    Sorry for silly question..

    I think this post it is very good to solve problems with calculated measure and GRAND TOTALS wrong in Client Application as Excel..

    Regards and sorry for easy question.

    • Yes you add the scope statement to the Calculations tab to include it in the cube.

      You can create the initial measure in two ways:
      1) Create a real measure in a measure group, sourced from the DSV
      2) Use ‘CREATE MEMBER’ in the Calculations script to create a new measure

      Either way, you can then apply scope to override the value of the measure in different situations.

      So in my examples above, there are actually two stages:
      CREATE MEMBER – creates a new measure which doesn’t already exist
      SCOPE – override the new measure with a different value.

      If you already had the measure defined in a measure group, then you would only need the 2nd part; the scope statement.

      And there’s no such thing as a silly/simple question. The only thing that would be silly would be not asking a question. If you don’t understand something, then there will certainly be other people who also don’t understand, so thank you for helping them 🙂

      • Mauro,

        This is another area where there are subtle differences between two approaches.

        While the values produced using a real member and one created in the Calculations script will be the same, bear in mind if you will need to control access to the measure through roles’ “Dimension Data” tab settings that only the real measures will appear – those created in the Calculations script will be available to all users.

        If needs be, create a real measure in the DSV (with a Null value) then override it in Calculations with a scope statement.

      • Hi Alex.
        Many thanks for your clear explanation….
        I think your blog is very useful to improve MDX and SSAS calculations.
        Thanks again…

        Regards.

      • Hi Alex.

        Many thanks.

        I think this reply is very useful to understand to solve ‘huge’ problem of wrong grand total for a calculated measures…

        Thanks agains.

        PS. I find this blog very useful indeed.

  • Hi Alex,

    As usual excellent post!! I’ve a question related to Scope and Year calculation in my cube and would be really helpful to get your suggestions.

    I’ve the DateTool Implemented in my Cube and enhancing the same I’ve created another member called “Outlook” (forecast) which has values 1+11, 2+10……11+1. For instance, when user selects 9+3, the measure should display 9 (Jan to Sep) month’s actual value and 3 (Oct to Dec) month’s outlook (forecast) value. To identify the source, I’ve Source Profile dimension which has Actual and Outlook values (1+11, 2+10……11+1). I managed to bring the Actual if Outlook (forecast) is not available. But it is showing for entire Calendar Years (2013, 14 & 15 whereas I’ve outlook only for 2014 year) 🙁 I want to show the actual only if Outlook (Forecast) is available for that year or else Null.

    I’m stuck with this for the past few days and would be great help if you could point me to the right direction.

    Thanks a lot for your help!

    SCOPE ([DateTool].[Outlook].[9 + 3]);

    THIS = IIF( IsEmpty( Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[9 + 3]}))

    ,Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Profile Level01].&[Actual]})

    ,Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[9 + 3]}));

    FORE_COLOR(THIS) = IIF( IsEmpty(Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[2 + 10]}) ), RGB(0,0,192), 0);

    END SCOPE;

  • Hi Alex – great post. I did try DESCENDANTS(xxx,,AFTER) and works for a part of my solution. Here’s my issue, I have a Time hierarchy (Year-Quarter-Month-Date) and also a “current date” attribute (which is not part of the time hierarchy) in the time dimension. I am looking to create a calculated member called MTD Sales where i should have the option to select Current date and gross Sales or Date from the time hierarchy and gross Sales. This way the user has just one Measure field but has the flexibility to choose either the current date and get MTD Sales as of current date or any prior date from the time hierarchy so the MTD sales is as of that date. I have a scope statement and when i choose the date from the time hierarchy it works just fine. however when i try current date null value is returned.

    CREATE MEMBER CURRENTCUBE.[Measures].[TestMTDGrossSales]
    AS NULL,
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Fact Transaction View’ ;

    SCOPE(Measures.[TestMTDGrossSales]);
    SCOPE(DESCENDANTS([Time Dimension].[Time Hierarchy],,AFTER));
    THIS=sum(periodstodate([Time Dimension].[Time Hierarchy].[Month],[Time Dimension].[Time Hierarchy].currentmember),[Measures].[Gross Sales Amount]);
    END SCOPE;
    SCOPE(DESCENDANTS([Time Dimension].[CURRENT DT],,AFTER));
    THIS =Aggregate (MTD([Time Dimension].[CURRENT DT].currentmember),[Measures].[Gross Sales Amount]);
    END SCOPE;

    END SCOPE;

    Any input is greatly appreciated.

  • Hi Alex,

    Great posts! One question related to this post, I faced a similar case which use scope to compute the cumulated metrics. May I know how you handle this cumulated [Test Measure To Date] on ALL level finally ? In your last example which exclude the [ALL], then this metric will be always null on [Date].[Calender].[All]/[Date].[Fiscal].[All] which is un-expected for reports users right?

    Thanks,
    Tom

  • Hi, first sorry for my English. I hope you can help me. I have a cube with a dimensión (Parámetro) which have two members (Agua) and (Aire). I have written the next MDX sentence:

    Scope([MP – PARÁMETRO].[Agua – Aire].[Agua],[Measures].[Num. Muestreos]);
    this=iif([MP – PARÁMETRO].[Agua – Aire].[Agua].currentmember Is [MP – PARÁMETRO].[Agua – Aire].[Agua].[Total],null,[Measures].[Num. Muestreos]);
    end scope;

    I want that if I browse Agua, the result will be NULL but if I browse Agua and Aire like a hierarchy, I need Num Muestreos.

    If I do this in rows and columns, I have the result but if I do this only in rows like a hierarchy, the result is null because the .currentmember is active.

    Hope you’ll help me.

    Thanks!!

    • Try using

      Scope([MP – PARÁMETRO].[Agua – Aire].[All],[Measures].[Num. Muestreos]);
      this=null;
      end scope;

      This just says that if you are browsing the All member then show null instead. The scope will not trigger when browsing the dimension members in a hierarchy, so will leave them unchanged.

      Regards
      Alex

      • Hi Alex,

        I try this sentence but I have the same problema. If I browse [MP – PARÁMETRO].[Parámetro] on rows and [MP – PARÁMETRO].[Agua – Aire] on columns, I have the correct result but if I put both on rows, the result crashes.

        Im trying to upload a photo with the problema but it’s impossible.

        Have you understand me?

        Thanks!

        Regards!

  • Please help. I have inherited a cube with scope calculation and need add a new dimension but it does not work for me. Here is a the structure.
    1. I have a dim table that is generated by this code:
    SELECT 9 AS DateFilterKey, ‘Rolling 12 Month’ AS DateFilterName, 9 AS SortOrder

    2. The table is added to the cube as a dim table but there is not a like to the fact table.

    3. Within the the Calculation, there is this scope statement
    SCOPE([Date Range].[Date Range].&[9]); // Rolling 12 Month
    SCOPE(EXCEPT([GL Date].[Month].[Month], STRTOSET(‘{[GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(12) : [GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(1)}’)));
    THIS = NULL;
    END SCOPE;
    END SCOPE;

    I thought I can add this script to the table generating script
    Union ALL
    SELECT 10 AS DateFilterKey, ‘Rolling 18 Month’ AS DateFilterName, 10 AS SortOrder

    and add a new scope statement just updating the month range
    SCOPE([Date Range].[Date Range].&[10]); // Rolling 18 Month
    SCOPE(EXCEPT([GL Date].[Month].[Month], STRTOSET(‘{[GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(18) : [GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(1)}’)));
    THIS = NULL;
    END SCOPE;
    END SCOPE;

    I can process the dim table by itself successfully, but when I process the whole cube, I get the error:
    The dimension ‘[Date Range]’ was not found in the cube.

    I am new to SSAS. Have I missed a link some where? Thank you in advance

    • Hi Thanh
      It looks like the original should also generate the same error, can you confirm whether the original code processes ok?

  • 2. The table is added to the cube as a dim table but there is not a link to the fact table.

  • I have a hierarchy Establishments in the warehouse location dimension
    (Establishment/Warehouse/Warehouse Location).
    I want to exclude the lowest level for a calculated measure and use a SCOPE statement

    CREATE MEMBER CURRENTCUBE.[Measures].[Occupied Locations] AS NULL,
    VISIBLE = 1 , DISPLAY_FOLDER = ‘Location Occupancy’ ;

    SCOPE(Descendants([Warehouse Location].[Establishments].[Establishment]
    , [Warehouse Location].[Establishments].[Warehouse Location]
    , BEFORE));

    [Measures].[Occupied Locations] = FILTER([Warehouse Location].[Warehouse Location].Members
    ,[Measures].[Occupied YN]=1).count;

    END SCOPE;

    But when I deploy the script to the database I get an error ‘The END SCOPE statement does not match the opening SCOPE statement. An arbitrary shape of the sets is not allowed in the current context..

    When I replace the Descendants function with e.g. [Warehouse Location].[Warehouse Location].Members , it works fine.
    What am I doing wrong ?

  • I’m so happy that I’ve stumbled upon your blog. I have a better understanding of my issue now, and was hopeful that your solution would help resolve my issue. However, I’m still experiencing the same issue. That is, the second calculation is the only one that works, so depending on how I order them, I can get one or the other to function, but not both.

    I am attempting to get the prior period value to use in a downstream calculation.

    The measure I am creating:

    CREATE MEMBER CURRENTCUBE.[Measures].[Net Actuals Prior Period]
    AS NULL,
    VISIBLE = 1,
    DISPLAY_FOLDER = ‘Net Actuals’,
    ASSOCIATED_MEASURE_GROUP = ‘Actuals’;

    And the actual calculation:

    // Net Actuals Prior Period Calculation
    SCOPE( [Measures].[Net Actuals Prior Period] );
    SCOPE(DESCENDANTS([DateTime].[Weekly],,AFTER));
    THIS = ([DateTime].[Weekly].CurrentMember.PrevMember,
    [Measures].[Net Actuals]);
    END SCOPE;
    SCOPE(DESCENDANTS([DateTime].[Fiscal Year],,AFTER));
    THIS = ([DateTime].[Fiscal Year].CurrentMember.PrevMember,
    [Measures].[Net Actuals]);
    END SCOPE;
    END SCOPE;

    If I add the “NULL:” to my calculation, I get nothing but #:Value errors.

    any insight would be greatly appreciated.

  • Hi Alex,

    I have added 3 new attributes to the dimension in my SASS cube. When ever i am using my calculated measure with these new attributes in excel, it is eating up the server ram and throwing a memory pressure error. I have increased the RAM of my system from 8 GB to 32 GB but didnt get any luck. The calculated measure is having the below definition.

    CREATE MEMBER CURRENTCUBE.[Measures].[# Devices]
    AS ([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[IRS Eligible])
    +
    ([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[IRS Eligible (no collections)])
    +
    ([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[STaTS Eligible])
    +
    ([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[Excluded Device]),
    VISIBLE = 1 ;

    is there any issue with design of my calculation(it was working fine earlier with other attributes).

    • There are a couple of things to look at here:
      – The most likely problem is an error in the script. If so it will likely try and calculate for every member in the cube and not make use of non empty functionality/performance. To test this just comment out most of the calculation and replace with a single value and check if it works, then gradually expand the calculation until you hit the problem.
      – It could be a corruption in the cube, try redeploying and reprocessing.
      – Do you have any aggregations using the FOR MEASURECAL dimension? If not try and add some. Although this would impact disk IO more than ram.

      I’m also assuming # Units is a basic sum measure and not a calculation? If it’s a calculation then you’d need to include its functionality into the complexity assessment.

  • Hi Alex,

    I have the below statement.

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Test Net Sales]
    AS [MEASURES].[Net Sales]
    , FORMAT_STRING = “#,#;(#,#)”, VISIBLE = 1;

    SCOPE [Measures].[Test Net Sales];

    SCOPE([Date].[Retail Week].&[FY15 Wk 4]);

    this=NULL;

    END SCOPE;

    END SCOPE;

    When I run a SELECT like

    SELECT [Measures].[Test Net Sales] ON 0,
    [Date].[Retail Hierarchy].[Retail Week].Members ON 1
    FROM Cube

    The output is supposed to show all the members of [Date].[Retail Hierarchy].[Retail Week] and [Test Net sales] measure except for the member [FY15 Wk 4] (It supposed to show NULL).

    Example:

    FY15 Wk 1 231315
    FY15 Wk 2 548645
    FY15 Wk 3 343434
    FY15 Wk 4 (null)
    FY15 Wk 5 784256

    But its not working as expected. In my DEV environment I have 2 AS DBs one with full implementation and a test cube with less number of dimensions and measures but both the cube has the same Date dimension with the same hierarchies and everything. Strangely the above SELECT returns the expected result in test cube but not in the actual cube.

    Given that both has the same DATE dimension, how could this give different results.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

343,413 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon