locked
Performance issue while applying dynamic security in SSAS cube. RRS feed

  • Question

  • Hi Everyone,

    We are facing major performance issues while accessing cube for multiple accounts which is related to dynamic security. 

    Summary:

    1. Cube has multiple accounts total of 5500 accounts.
    2. we are trying to access the cube with the user having 2000 accounts access, taking more than 10 minutes  to connect Cube through Excel Services.
    3. We are successfully able to connect the cube within 1 min if we have 10 – 20 accounts access.
    4. We are filtering 4 dimensional table using this dynamic security. Majority of  the dimension having 15 Million records in the table.

    MDX Query being used  for dynamic security: -  

    EXISTS(
    {[Account].[Account Name].[Account Name].members},
    {StrToSet("[User Access].[Dim User Name].[Dim User Name].[" + UserName() + "]")},"User Access")

    This is the link we have followed to apply the dynamic security in SSAS cube.

    http://bidn.com/Blogs/analysis-services-dynamic-security

    Will appreciate your help on this as it has blocked us completely.

    Thanks,

    Shishupal


    Thanks, Shishupal


    Friday, July 1, 2016 5:05 PM

Answers

  • I was thinking of something along the lines of this sort of powershell script

    https://gist.github.com/dgosbell/969cb76ae0f0f45a9fb45942e2fb0f31

    Although in your situation I'm not sure how much it will help. I think the problem is doing per user scans over the 15 million member dimensions. You may need to look at re-structuring your design. Have you considered hiding the detail attribute and only exposing them via drillthrough actions? In this case the users would only see members that relate to the facts which they have permission to see.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 6, 2016 8:45 PM

All replies

  • Hi Shishu Singh,

    I believe its not only dynamic security when it comes to performance, it might so many other factors too. Below document gives more insights on Analysis Services Performance.

    https://www.microsoft.com/en-us/download/details.aspx?id=17303

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Friday, July 1, 2016 6:42 PM
  • Thanks Navin for your response on this however i would like to bring to your notice that it's only causing issue during first connection aftwards it works fine, if i takes out the dynamic security cube performance is very quick for slicing and dicing in excel.

    As per our several round of investigation we have identified this issue.

    Any suggestion on how to improve cube performance having dynamic security where users are having multiple accounts to be accessed.

    We have already optimized our cube bases on the recommendation of Microsoft white papers.

    Thanks,

    Shishupal


    Thanks, Shishupal

    Saturday, July 2, 2016 9:21 AM
  • 4. We are filtering 4 dimensional table using this dynamic security. Majority of  the dimension having 15 Million records in the table.

    Can you explain what you mean by the statement above? Does this mean that you also have bridge tables between the user and these 15million row dimensions? 

    http://darren.gosbell.com - please mark correct answers

    Saturday, July 2, 2016 9:41 PM
  • Hello Darren,

    No.

    I just give you quick structure that we used for this dynamic security implementation.

    1. User Access table (which contains the account mapping against users)

    user access table contains dimSK which directly relates to these 4 dimensions through dimacctSK which also filters out the related fact data.


    Thanks, Shishupal

    Sunday, July 3, 2016 1:47 PM
  • I'm still not sure I understand your structure. Are you saying it's something like the ascii art diagram below? And that you have similar allowed set definitions on the 4 big dimensions?


                                        ---> Account  <---
                                        ---> Big Dim1 <---
    User  <---  User Access  ---> Big Dim2 <---  Fact
                                        ---> Big Dim3 <---
                                        ---> Big Dim4 <---

    If this is the case I don't think you need to secure the big dimensions. Securing the Account dimension will filter the fact. Have you tried the performance with just the account dim secured? 


    http://darren.gosbell.com - please mark correct answers

    Monday, July 4, 2016 8:37 PM
  • Hi Shishupal

    For additional inspiration and troubleshooting, please read this: http://blogs.prodata.ie/post/Anatomy-of-Analysis-Service-Startup-e28093-Slow-connections.aspx

    Brgds
    Jens


    HTH - please mark correct answers

    Tuesday, July 5, 2016 6:46 AM
  • Yes Darren i am following the same pattern that you have defined above in your diagram and you are absolutely right if we just filter the account dimension all fact data will be filtered which is very quick as well within seconds however challenge we have is we also need to secure the dimension data  because that contains some detailed information and also we nee to enable cascading effect in dashboard built upon Excel using slicers so if we didn't filter out the dimension data users will see the dimension data which is not related to them which is issue for business.

    Is there any way i can track the MDX query and warm the cache for dynamic security also as we normally do for normal MDX queries being tracked while doing the slicing and dicing in cube.


    Thanks, Shishupal

    Tuesday, July 5, 2016 8:02 AM
  • Is there any way i can track the MDX query and warm the cache for dynamic security also as we normally do for normal MDX queries being tracked while doing the slicing and dicing in cube.

    There is no query. It's the initialization of the user session that triggers this. I'm not sure if there is anyway to fake this. If you have a service account that is a SSAS server admin you might be able to use the EffectiveUserName in the connection string to loop through all your users and create a connection. I have not tried this myself, but it may work.

    15 million is big for a dimension. How big is the attribute that you are securing? Is it the same magnitude as the account dimension? (eg 5500 members)


    http://darren.gosbell.com - please mark correct answers


    Tuesday, July 5, 2016 9:32 PM
  • Appreciated your response Darren.

    Do you have any article reference which can guide me to implement your suggestion.

    There is no query. It's the initialization of the user session that triggers this. I'm not sure if there is anyway to fake this. If you have a service account that is a SSAS server admin you might be able to use the EffectiveUserName in the connection string to loop through all your users and create a connection. I have not tried this myself, but it may work.


    Thanks, Shishupal

    Wednesday, July 6, 2016 6:35 AM
  • I was thinking of something along the lines of this sort of powershell script

    https://gist.github.com/dgosbell/969cb76ae0f0f45a9fb45942e2fb0f31

    Although in your situation I'm not sure how much it will help. I think the problem is doing per user scans over the 15 million member dimensions. You may need to look at re-structuring your design. Have you considered hiding the detail attribute and only exposing them via drillthrough actions? In this case the users would only see members that relate to the facts which they have permission to see.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 6, 2016 8:45 PM
  • Yes darren we had already hidden higher cardinality attribute from dimension and exposing it through action only.

    Thanks, Shishupal

    Thursday, July 7, 2016 7:52 AM