Cube Claculations

  • SSAS 2012

    Bit of a weird issue here. Created some cube calculations.

    CREATE MEMBER CURRENTCUBE.[Measures].[SLl]

    AS

    CASE WHEN [Measures].[R]= 0 THEN 0

    ELSE ([Measures]./[Measures].[R] )

    END,

    FORMAT_STRING = "00.00%",

    VISIBLE = 01 , ASSOCIATED_MEASURE_GROUP = 'LS' ;

    for some reason when I have the dimension Product on rows then the Measures I and R may be null (no fact records) , but there is a row for the Product dimension member.

    I have tried this In cube browser and Excel 2007. I feel like I am missing something here. When I have done this previously (too many years working in SSIS and not enough SSAS) this work perfectly.

    What am I missing.

    Cheers

    E

  • Should say I know this can be filtered out when querying but I want it to be right in the cube in the first place.

    Thanks

    E

  • A few things could be causing this....

    Firstly your code:

    CASE WHEN [Measures].[R]= 0 THEN 0

    This returns a zero rather than a NULL so any NON EMPTY precedents in the queries (these are there by default in the cube browser and Excel) will not work as expected. This may be causing your issue.

    Secondly the form of your code is not considered the best in terms of performance. Try:

    IIF( [Measures].[R]=0, NULL, [Measures]./[Measures].[R] )

    The reasons for this are discussed here: http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx


    I'm on LinkedIn

  • Many thanks,

    I have been playing with this for a little this morning and have changed it so that if [Measures].[R] = 0 THEN [Measures].[R]

    Not happy with that but it does the trick for removing the rows that should be NULLS. Just having a look at the IIF statement link.

    Thanks

    E

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply