locked
Named Sets Static vs Dynamic RRS feed

  • Question

  • Hi

    I would like to create a simple named set e.g.

    CREATE STATIC SET [Adventure Works].[MySet]
    AS
      '{
        [Geography].[City].&[London]&[ENG]
        , [Geography].[City].&[Birmingham]&[ENG]
        , [Geography].[City].&[Liverpool]&[ENG]
        , [Geography].[City].&[Exeter]&[ENG]
      }'

    SELECT [MySet] ON 0
    FROM [Adventure Works]
    WHERE ([Measures].[Reseller Order Count])

    The member [Geography].[City].&[Exeter]&[ENG] does not exist, but I want to include it so that if I do get any orders from Exeter, the query will include these. If I do this in an MDX query it works fine. However if I create the set in the cube at design time then I get an error when I try to deploy:

    MdxScript(Adventure Works) (8, 5) The level '&[Exeter]&[ENG]' object was not found in the cube when the string, [Geography].[City].&[Exeter]&[ENG], was parsed.

    Unless... I set the set to be dynamic rather than static, then it deploys fine and returns the correct results from MDX queries. However I need my cube to be compatible with SSAS 2005, so I can't use dynamic sets, is there any other way around it?

    Julia.

    Thursday, June 18, 2009 3:05 PM

Answers

  • "I think this MDX Missing Member Mode may be the key. .." - unfortunately, according to the paper below, that property may not pertain to calculations created in the cube MDX script (like your named set). So the thinking seems to be that ignoring unrecognized names in the script is undesirable - could you handle these on the client side instead?

    What is 'MDX Missing Members Mode' in Analysis Services 2005 ?
    By Mosha Pasumansky, June 2005
    ...
    Therefore in the recent CTP builds, we hardcoded Mdx Missing Members Mode to always be Error when evaluating server side calculations, security, KPIs etc.
    ...

    There is a ScriptErrorHandlingMode cube property, but setting that to IgnoreAll would be a drastic solution, since all script errors would then get ignored:

    SQL Server 2008 Books Online (May 2009)
    Cube Properties
    ...

    ScriptErrorHandlingMode

    Determines error handling. Options are IgnoreNone or IgnoreAll


    - Deepak
    • Proposed as answer by Raymond-Lee Friday, June 26, 2009 5:26 AM
    • Marked as answer by JuliaJulia Friday, June 26, 2009 8:01 AM
    Sunday, June 21, 2009 4:30 AM

All replies

  • If there is just 1 member which may not exist, you could test it using IsError(), like:

    CREATE STATIC SET [Adventure Works].[MySet]
    AS
      '{
        [Geography].[City].&[London]&[ENG]
        , [Geography].[City].&[Birmingham]&[ENG]
        , [Geography].[City].&[Liverpool]&[ENG]
        , iif(IsError(StrToMember("[Geography].[City].&[Exeter]&[ENG]")),
        {}, {[Geography].[City].&[Exeter]&[ENG]})
    }'


    - Deepak
    Thursday, June 18, 2009 4:08 PM
  • Thanks, Deepak.

    Actually in the real cube, there are 7 members specified and any of them may not exist. I don't really understand why it will work as a dynamic set, but not as a static set. In my understanding of the difference between static/dynamic sets their behaviour in this instance shouldn't be different.

    Is using IsError() really the only way to handle this scenario?

    Julia.


    Thursday, June 18, 2009 4:56 PM
  • ".. I don't really understand why it will work as a dynamic set, but not as a static set. In my understanding of the difference between static/dynamic sets their behaviour in this instance shouldn't be different. .." - I think that this has to do with the SSAS connection property: MDXMissingMemberMode. The default behavior differentiates between cube initialization (missing members cause error) and query time (ignore). The Adventure Works query below generates no error by default; but if MDXMissingMemberMode=Error, the following error is generated: "The level '[Racks]' object was not found in the cube when the string, [Product].[Category].[Racks], was parsed." Changing this property to Ignore is an alternative to IsError() - but it would apply globally, which would ignore other errors.
    With
    Set [ProdCat] as
    {[Product].[Category].[Bikes],
    [Product].[Category].[Racks]}
    select
    {[Measures].[Sales Amount]} on 0,
    [ProdCat] on 1
    from [Adventure Works]

     

     



    MDX Missing Member Mode

    Values: Default/Ignore/Error
    Indicates whether missing members are ignored in MDX statements. Default option uses value generated by Analysis Services service.
    Example: MDX Missing Member Mode=Error;


    - Deepak

    • Edited by Deepak Puri Thursday, June 18, 2009 5:28 PM
    Thursday, June 18, 2009 5:17 PM
  • Thanks, Deepak.

    Actually in the real cube, there are 7 members specified and any of them may not exist. I don't really understand why it will work as a dynamic set, but not as a static set. In my understanding of the difference between static/dynamic sets their behaviour in this instance shouldn't be different.

    Is using IsError() really the only way to handle this scenario?

    Julia.



    Another option would be to just add these 7 members to the dimension... :)
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Friday, June 19, 2009 2:01 AM
  • I think this MDX Missing Member Mode may be the key. Although I'm not entirely sure how to set it. I found the property on the Geography dimension and I set it to Ignore there. However I still get same error when I try to deploy the solution.

    Could you give me more detail on exactly how I set MDXMissingMemberMode for my SSAS DB via BIDS?

    Darren - I know on the surface of it the solution to my problem is ridiculously easy (just add the missing members), but in my real cube the dimension I'm using is more at the item level and the attribute I'm referencing is of the 'category' variety, so to add the missing members would mean adding dummy members to these categories, which would mess up my aggregates. Please let me know if you think I'm not fully grasping your point though.

    Julia.
    Friday, June 19, 2009 9:31 AM
  • "I think this MDX Missing Member Mode may be the key. .." - unfortunately, according to the paper below, that property may not pertain to calculations created in the cube MDX script (like your named set). So the thinking seems to be that ignoring unrecognized names in the script is undesirable - could you handle these on the client side instead?

    What is 'MDX Missing Members Mode' in Analysis Services 2005 ?
    By Mosha Pasumansky, June 2005
    ...
    Therefore in the recent CTP builds, we hardcoded Mdx Missing Members Mode to always be Error when evaluating server side calculations, security, KPIs etc.
    ...

    There is a ScriptErrorHandlingMode cube property, but setting that to IgnoreAll would be a drastic solution, since all script errors would then get ignored:

    SQL Server 2008 Books Online (May 2009)
    Cube Properties
    ...

    ScriptErrorHandlingMode

    Determines error handling. Options are IgnoreNone or IgnoreAll


    - Deepak
    • Proposed as answer by Raymond-Lee Friday, June 26, 2009 5:26 AM
    • Marked as answer by JuliaJulia Friday, June 26, 2009 8:01 AM
    Sunday, June 21, 2009 4:30 AM
  • Thanks, Deepak.

    I guess the short answer is that there is no way align the behaviour of static named sets at run time to their behaviour at cube initialisation, in regard to the MDXMissingMemberMode property. Which is frustrating. I think my only option is to change the dimensional model to overcome this problem.

    I appreciate your help,

    Julia.
    Friday, June 26, 2009 8:01 AM