locked
URGENT : Dynamic Set RRS feed

  • Question

  • Hi,

    I created a dynamic set to capture all the selections the user is making in an excel sheet. The below is the code.

    create hidden dynamic set currentcube.[Set1]
    as {
        
           descendants([Hierarchy].&[LevelinHierarchy1],,LEAVES)
        
       };
    
    create hidden dynamic set currentcube.[Set2]
    as {
        descendants([Hierarchy].&[LevelinHierarchy2],,LEAVES)
        
       };
    
    
    SCOPE( [Hierarchy].&[MemberinHierarchy]);
      THIS = SUM(Set1,[Measures].[X])
            - SUM(Set2,[Measures].[X])
        ;   
            
    END SCOPE; 

    Now when the we execute the script we are able to execute it properly without any problems ( we are the server admins ). However when someone from the end users try to  access the cube  they get an error :

    "Could not evaluate dynamic set Set1 in the current query scope".  When we gave server admin level access to the end users it works for them as well but obviously that is not possible. 

    I am confused therefore anything  will be helpful and this is URGENT.

    Thanks,

    Nimish




    • Edited by Nimish Rao Wednesday, April 15, 2015 4:03 PM
    Wednesday, April 15, 2015 4:02 PM

Answers

  • Hi Ed,

    Actually it is, but we are trying to push it back to business.

    Considering its a tool limitation. Though why it is not working for me but working with the other client is beyond me.

    But i think for now we can close this thread.

    Thanks,

    Nimish

    Monday, May 11, 2015 5:20 AM

All replies

  • Hi,

    I created a dynamic set to capture all the selections the user is making in an excel sheet. The below is the code.

    create hidden dynamic set currentcube.[Set1]
    as {
        
           descendants([Hierarchy].&[LevelinHierarchy1],,LEAVES)
        )
       };
    
    create hidden dynamic set currentcube.[Set2]
    as {
        descendants([Hierarchy].&[LevelinHierarchy2],,LEAVES)
        )
       };
    
    
    SCOPE( [Hierarchy].&[MemberinHierarchy]);
      THIS = SUM(Set1,[Measures].[X])
            - SUM(Set2,[Measures].[X])
        ;   
            
    END SCOPE; 
    

    Wednesday, April 15, 2015 3:40 PM
  • Hi Nimish,

    According to your description, when other end users access the cube, it throw the error about evaluating the dynamic named set. If you give the users admin permission, it can access properly without any error. Right?

    In Analysis Services, when creating a named set, if you set HIDDEN, the set can't be either visible or not to other users who query the cube. So when end user can't evaluate the set unless it has administrator permission. Please remove HIDDEN in create statement.

    Reference:
    CREATE SET Statement (MDX)

    Best Regards,


    Simon Hou
    TechNet Community Support


    Thursday, April 16, 2015 6:51 AM
  • Hi Nimish,

    you didn't ask any question :-). So what's your problem?

    Cheers

    Martin

    Thursday, April 16, 2015 11:39 AM
  • Hi Simon,

    This doesn't help and for another project in my organization the same hidden dynamic set method works like a charm.

    As an alternate I tried creating two hidden measures and used these two measures in the the final scope which also didn't help as this seriously hampers the performance.

    Any other suggestions.

    Thanks,

    Nimish

    Monday, April 20, 2015 11:52 AM
  • :) Thanks for this Martin. But my question is why are the users not able to use the hidden dynamic set when they are not server admins.
    Monday, April 20, 2015 11:57 AM
  • Nimish, is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, May 11, 2015 4:58 AM
  • Hi Ed,

    Actually it is, but we are trying to push it back to business.

    Considering its a tool limitation. Though why it is not working for me but working with the other client is beyond me.

    But i think for now we can close this thread.

    Thanks,

    Nimish

    Monday, May 11, 2015 5:20 AM