locked
SET context RRS feed

  • Question

  • I have two mdx query scoped expressions:

    member x as
    
    Count
    
    (
    
    	NonEmpty(
    
    	[MyDim].[MyDim].[Name],
    
    	[Measures].[Test Count])
    
    )
    
    
    
    

    and

     

    set y as
    
    	NonEmpty(
    
    	[MyDim].[MyDim].[Name],
    
    	[Measures].[Test Count])
    
    
    
    member x as
    
    Count
    
    (
    
    y
    
    )
    
    

    they both evaluate to different numbers.  The first one is correct. Why the difference?

     

    (There are slicers in the actual query that uses them.)




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Tuesday, December 7, 2010 9:16 PM

Answers

  • Hi Javier,

    The first version would evaluate the non empty inthe context of each cell of your report, while the second version only evaluates it once, only taking the slicer but not the current members on the axes into account.

    Frank

    • Marked as answer by Javier Guillen Monday, December 13, 2010 4:58 PM
    Tuesday, December 7, 2010 9:21 PM
  • If you want modularity a .Net stored procedure would work, but it would come at a big performance cost. .Net procedures have to be evaluated in cell-by-cell mode and there is a large overhead to the unmanaged to managed marshalling that has to occur. It is not considered a best practice to call a .Net stored procedure from a calculated measure.

    Unfortunately if you want the best performance you may have to sacrifice some modularity.

    If you are doing the identical operation over all the measures then one pattern that may work is to create some calculated measures pointing to the raw measures that you want to calculate over and then use a scope to apply the correct logic to the set of calculated measures. Below is an example with 3 calculated measures. I haven't tested it myself, but I think it should work.

    eg.

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 1] as [Measures].[Raw Measure 1];

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 2] as [Measures].[Raw Measure 2];

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 3] as [Measures].[Raw Measure 3];

    SCOPE ({Measures.[MyCalc 1],Measures.[MyCalc 2],Measures.[MyCalc 3]});

       this = Count (

      NonEmpty(

                     [MyDim].[MyDim].[Name],

      [Measures].CurrentMember)

                 );

    END SCOPE;


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Javier Guillen Thursday, December 9, 2010 2:57 PM
    Wednesday, December 8, 2010 2:53 AM

All replies

  • Hi Javier,

    The first version would evaluate the non empty inthe context of each cell of your report, while the second version only evaluates it once, only taking the slicer but not the current members on the axes into account.

    Frank

    • Marked as answer by Javier Guillen Monday, December 13, 2010 4:58 PM
    Tuesday, December 7, 2010 9:21 PM
  • Thanks FrankPl

    I want to reuse a set, as it is the basis for a number of calculations.  How can I configure the set to behave like the first option above?




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Tuesday, December 7, 2010 9:26 PM
  • The short answer is that you can't. If you want a set to dynamically pick up the context from the rows/colums and not just the where clause the it needs to be defined inline in your expression.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Wednesday, December 8, 2010 1:18 AM
  • Thanks Darren

    Given I will have 6 or 7 calculations that will use the exact same set with the only difference that it evaluates against a different measure each time, I was wondering what the best way is to wrap this functionality in a modular way (would be great if we could create mdx functions that take parameters)

    Perhaps a .Net stored proc? In that way, I could take measure member as a parameter and then emulate the inline behavior




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, December 8, 2010 2:36 AM
  • If you want modularity a .Net stored procedure would work, but it would come at a big performance cost. .Net procedures have to be evaluated in cell-by-cell mode and there is a large overhead to the unmanaged to managed marshalling that has to occur. It is not considered a best practice to call a .Net stored procedure from a calculated measure.

    Unfortunately if you want the best performance you may have to sacrifice some modularity.

    If you are doing the identical operation over all the measures then one pattern that may work is to create some calculated measures pointing to the raw measures that you want to calculate over and then use a scope to apply the correct logic to the set of calculated measures. Below is an example with 3 calculated measures. I haven't tested it myself, but I think it should work.

    eg.

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 1] as [Measures].[Raw Measure 1];

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 2] as [Measures].[Raw Measure 2];

    CREATE MEMBER CURRENTCUBE.Measures.[MyCalc 3] as [Measures].[Raw Measure 3];

    SCOPE ({Measures.[MyCalc 1],Measures.[MyCalc 2],Measures.[MyCalc 3]});

       this = Count (

      NonEmpty(

                     [MyDim].[MyDim].[Name],

      [Measures].CurrentMember)

                 );

    END SCOPE;


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Javier Guillen Thursday, December 9, 2010 2:57 PM
    Wednesday, December 8, 2010 2:53 AM
  • Looks like an interesting way of solving the issue. I will try it out.  Thanks Darren!




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, December 9, 2010 2:57 PM