none
MDX SCOPE question

    Question

  • Hi,

    I have en MDX challenge that I need some help with.

    In my cube I have 3 dimensions: Business Unit (BU), Customer and Project

    I have a calculated measure called [Budg Inv Hours %] = [Measures].[Budg Inv Hours] / [Measures].[Budg Capacity]

    What I would like to archive is when using this calculated measure together with my Customer and Project dimensions then instead of making the calculation it should return 1 ( = 100%) if the [Measure].[Num Hours] return a value for the customer or project and else it should return no value ( NULL )

    I could just create 2 new calculation like IIF(([Customer].[Customer], [Measures].[Num Hours]) = 0, NULL, 1), but I would like to just keep the one calculation (to keep my cube simple for my users) and either by changing this calculation or using a SCOPE get the result I want – if it is possible.

    I have tried with the following SCOPE, but this also make the calculation return 100% when using it together with my Business Unit
    dimension which is not what I am looking for:

    SCOPE ([Customer].[Customer]);
        [Measures].[Budg Inv Hours %] = (IIF(([Customer].[Customer], [Measures].[Num Hours]) = 0, NULL, 1));
    END SCOPE;

    Also tried this:

    SCOPE ([Customer].[Customer].MEMBERS, [Measures].[Budg Inv Hours %]); 
        THIS = (IIF(([Customer].[Customer], [Measures].[Num Hours]) = 0, NULL, 1)); 
    END SCOPE;

    Can someone please guide me a little

     

     


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK


    Tuesday, June 25, 2013 10:05 AM

All replies

  • Have you tried handling this logic in the sql database  at row level? (if it is possible that is). I generally handle it there if it is possible at row level. It also means that this data will be pre aggregated in the cube and render better performance and easier to maintain as making a change in sql is generally a bit easier than in a Cube.

    Tuesday, June 25, 2013 11:05 AM
  • I don't see how I should solve this with SQL in my facts.


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK

    Tuesday, June 25, 2013 11:28 AM
  • Yip - sorry, I misread and misunderstood the question.
    Tuesday, June 25, 2013 11:51 AM
  • Use the below code in calculations,

    SCOPE ([Customer].[Customer].members,[Project].[Project].members); [Measures].[Budg Inv Hours %] = 1;

    END SCOPE;

    You said when you have customer and project together you need the measure value as 1 .  If you want only customer remove project from scope. Create [Measures].[Budg Inv Hours %]  measure before the scope statement.

    http://www.bifeeds.com

    Tuesday, June 25, 2013 11:54 AM
  • Use the below code in calculations,

    SCOPE ([Customer].[Customer].members,[Project].[Project].members); [Measures].[Budg Inv Hours %] = 1;

    END SCOPE;

    You said when you have customer and project together you need the measure value as 1 .  If you want only customer remove project from scope. Create [Measures].[Budg Inv Hours %]  measure before the scope statement.

    http://www.bifeeds.com

    When using this scope the measure also return 1 when combining the measure with the Business Unit dimension.

    Perhaps it is important to state that there is no relation between the measures used to calculate Budg Inv Hours % and the customer / project dimensions. Which is why I want to control the behaviour of the calculation when combined with these dimensions.


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK



    Tuesday, June 25, 2013 12:38 PM
  • I had to read your problem once again to reply this, what I understood now is you want control the behaviour of scope when Business unit dimension in picture, try the below code,

    SCOPE ([Customer].[Customer].members,[Project].[Project].members,[Business Unit].[Business Unit].[All level]);
          [Measures].[Budg Inv Hours %] = (IIF(([Customer].[Customer], [Measures].[Num Hours]) = 0, NULL, 1));
    END SCOPE;

    Above scope considers all level member, that is equivalent to not considering the business unit dimension, change below line in scope according to the dimension

    [Business Unit].[Business Unit].[All level]

    http://www.bifeeds.com

    Wednesday, June 26, 2013 3:08 PM
  • Hi,

    I am not sure that you completely understand my issue yet. In the client tool – in this case TARGIT, but it could be pivot tables in Excel as well – I want to create 3 different tables with this measure and some other measures – but when I combine this measure with the Customer and Project dimension it does not make sense, so therefore I want it to return 100% (1) instead of the original calculation – I have tried to make it more graphical below.

    Business Unit (actually a hierarchy with 3 levels – Business Unit / Team / Consultant:

    Total                       70%

    Business Unit 1        69%

      Team 1                 71%

        Consultant 1        72%

        Consultant 2        70%

        .....

      Team 2                 68%

    .....

    Customer: (currently showing the Total due to no relation)

                                  Current        Wanted

    Customer 1             70%             100%

    Customer 2             70%             100%

    Customer 3             70%             100%

    .....

    Project: (currently showing the Total due to no relation)

                                  Current        Wanted

    Project 1                 70%             100%

    Project 2                 70%             100%

    Project 3                 70%             100%

    When I try to use your scope (slightly adapted to my naming) I get this error:

    The level '[All level]' object was not found in the cube when the string, [Resource_Misc].[BU - Team].[All level]


    Regards, Søren Damgaard Jensen Senior BI Consultant @ Norriq, DK


    Monday, July 01, 2013 9:41 AM