Unanswered: SSAS Tabular - Dynamic Security and Intermittent Visibility of Data in Excel
This is an SSAS Tabular issues regarding dynamic security and intermittent visibility of data in Excel to end users. I’ve given a background below to help explain the scenario.
I’m using dynamic security on two tables, ‘Sales’ table and ‘Margin’ table. This is because not all users that can see sales data are allowed to see margin information. I’ve used a ‘Security’ table that holds users Active Directory user names with what they are allowed to see (Sales/Margin), with DAX expressions at the ‘Row Filters’ area for each of the two tables.
I’ve tested the security for each user within Visual Studio using ‘Analyze In Excel’, this works as expected. Users can see all of the data that they are allowed to see based on the ‘Security’ table. I’ve then deployed the cube and the cube is rebuilt every week. This also usually seems to work fine, where users can see both ‘Sales’ and ‘Margin’ where they have been given permission to see them.
Intermittently, some users seem to loose visibility of the ‘Margin’ table. So a user will have saved an Excel file with ‘Sales’ and ‘Margin’ data in it. When they go back to the saved file and refresh the file, or try and add another measure, the margin data disappears and they are left with just the field headers.
Processing the ‘Security’ table on the SSAS server using ‘Process Full’ seems to fix this and users are able to see the margin data again. This is confusing, because there are no changes made to the cube or data in this time. Also, the users do not lose the ‘Sales’ table information, only the ‘Margin’ table data. I’ve also used MDX to query the cube on the server where the username is equal to the username in the ‘Security’ table on the server, this returns data from the ‘Margin’ table for users that are not able to see ‘Margin’ data in Excel until the ‘Security’ table is re processed.
Any suggestions with what the problem might be would really be appreciated as I’ve run out of ideas. Could it be the way I am processing the cube each week? Or the DAX at the 'Row Filters' area for the 'Margin' table?