Slow dynamic security in SSAS

Question

We have implemented dynamic security in our SSAS cube. We are using SQL Server 2012.

There is an employee dimension with the following hierachy (not parent child)

Region
  Corporation
    Department
      Team
        Employee

We have a bridge table, with the measure [User Employee Access Count] defined between our user table and the Employee table.

On the Cube Dimension Employee, in the Team hierachy we have the following MDX for allowed set.

NONEMPTY 
    (
        [Employees].[Team].[All].Children,
        (
            StrToMember("[User].[Username].&["+coalesceempty(customdata(),username)+"]"), 
            [Measures].[User Employee Access Count] 
       )
    )

This is what I have found to be the default way of implementing dynamic security, and it works.

The problem is that there is long latency when a user first accesses the cube. There are about 40.000 members of the Employee dimension, and the MDX takes about 2-3 seconds to run when I use it in a query, but when a user first logs int, if she has access to most or all employees, the first query hangs for up to 90 seconds, before it is finished.

What the query is is not important, and subsequent queries have no performance hit.

My guess is that SSAS uses the time to build the internal security structure. Like I said qhen I define the above MDX as a set in a MDX query and run it in SSMS, it takes 2-3 seconds to return all rows, even when the user has access to 40.000 dimension members, and this is including the overhead by SSMS to print out all rows.

I have tried to make a combination of security setting. Most users have access to only a small part of the employees, and they have a latency of less hten 30 seconds, which is slow, but just acceptable, so we tried making a group for users with access to many Employees and just make a denied member set, but the administration is cumbersome, and we still experience high latency on first login.

Currently I have set up a job that loops through the users with the highest latency, and the most frequent users, and just perform a simple MDX query against the cube every 30 minutes. This warms up the dynamic security, and minimizes the impact on the user, but occasionally they still have to wait on first login, and ideally I would like to do away with the first query latency.

Does anyone have experience with dynamic security in environments with large dimensions, and a large number of report users?

By the way - the "customdata()" is present because sometimes users access the cube through a custom component that logs in as a service user, but includes the username as custom data. — Søren Kongstad
If I understand you limit which teams users can see. Is it acceptable for a user who has permissions to Team 1 and Department 1 to see the Department totals for Department 2 but no team totals for other teams? If they look at the total for Department 1 should it show just the Team 1 total or the total of all teams in Department 1? I'm asking because I suspect that unchecking the Visual Totals box in the dimension data security tab in the role may improve performance some. It is worth a try if that would meet your requirements. — GregGalloway

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-03-21T07:50:02

NonEmpty() is a calculation, that costs when it come to huge dimensions and you have to run every time which isn't good. What about storing sets column in your dimension table? Like:

|=======================================================|
|UserID | UserName | EmployeesSet                       | 
|=======================================================|
| 1     | Adam B   | {[Employees].[Employees].&[Adamb]} |
|=======================================================|

Then you can import it as a key value and parse it with StrToSet() instead of the running NonEmpty() function.

Slow dynamic security in SSAS

1 Answers