none
Performance issue on large dimension security in SSAS RRS feed

  • Question

  • Hi,

    We are trying to implement security on our OLAP cubes. Security limitations should be applied on a large +50mln members Customer dimension. Roles are created using Allowed members filtering on DataGrp attribute from Customer dimension (example: {[Customer].[DataGrp].&[1]}). DataGrp atribute contains out of ~30 distinct values, but single value might filter out ~15mln-20mln records from Customer dimension.

    Then problem occurs when user from that role connects to the cube for the first time (on cold cache). Even before starting reading the data Analysis Services is building some kind of security context and for users it takes 2min to 10min. Profiler shows multiple "Calculate Non Empty Current" events and only after long freeze (in Excel if end users is connecting via Excel) SSAS reads the data and completes the query.

    Question: is this standart SSAS behaviour being so slow when setting up security when there is a need to limit data on +10mln dimension members? If so is there any other way to improve performance here?
    Cache warmer is not really an option as we update our cubes multiple times during the day and cache is gone when new members are added into the Customer dimension.

    Thanks

    Wednesday, December 4, 2019 10:46 AM

All replies

  • Hi edie1982,

    Thanks for your question.

    For such dimension security, you could consider creating partitions for related fact tables in cube, so that front-end users could access cube data more quickly. If possible, create necessary indexes on fact tables.

    How to optimize the dimension security performance using partitioning in SSAS Multidimensional

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 5, 2019 6:30 AM
  • Will, thanks for your answer. 

    We have our facts partitioned by month. In addition we could include additional partitioning by DataGroup attribute from the Custmer dimension, but I doubt that this could solve our problem.
    Our roles with Customer dimension data restrictions have "Enable visual totals" off. Meaning that we actually still need to read all data from restricted customers, but we just need to hide restricted Customer dimension members from being listed when Customer dimension is used.

    Lets say we have following sample fact records:
    CustID    CustDataGrp      Amount
    Cust01         1                              10
    Cust02         1                              20
    Cust03         2                              30

    Then we create a role for the user which is allowed to see only CustDataGrp=1 customers. When he uses Customer dimension, he is able to see this in the cube:

    Cust01         10
    Cust02         20
    Grand Total   60

    In ideal scenarion business would love to have following view, but I do not know is this even possible in SSAS to acomplish:

    Cust01                  10
    Cust02                  20
    <No access>         30
    Grand Total            60

    Regards,
    Darius


    • Edited by edie1982 Thursday, December 5, 2019 9:54 AM
    Thursday, December 5, 2019 9:53 AM
  • Hi Darius,

    You need to create another calculated measure member like this.

    CREATE MEMBER [Measures].[NEW AMOUNT] AS
    IIF([CustID].[CustID].currentmember is [CustID].[CustID].[ALL], SUM(EXISTING([CustID].[CustID].[CustID]),[Measures].[Amount]),[Measures].[Amount])

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 6, 2019 7:38 AM