question

RichardShillinglaw-7746 avatar image
0 Votes"
RichardShillinglaw-7746 asked LukasYu-msft commented

SSAS RLS/Connecting to Cube(s) via Excel

I've taken over some cubes, making some developments to them including implementing dynamic Row Level Security. General access to these is provided by some AD groups.

On connecting to these via Excel after processing, having implemented the OlapQueryLog table, there are numerous records added to the table relating to the low level RLS table and this is returned for all cubes regardless of the one being used within the Excel spreadsheet, a "connecting to data source" message also appears on the status bar within Excel. So if cube2 is accessed, it reports cube1 and 3-4 as well. This happens for each user and then it will be fine for the rest of the day until processing runs again. The "connecting to data source" message lasts for a few minutes. I've attached a sample output.96311-olapquerylog.txt from the QueryLog table.

My RLS/bridging table is based on a low level site code, users have access to one or many sites, this table is quite large and I have tried limiting this down to the very specific users concerned rather than all but this hasn't helped.

I've looked at cache warming thinking I needed to calculate the security after processing but haven't been to do this especially for each user. Appreciate that security has to be worked out somehow but there isn't a great deal of info from what I've seen on the effects of implementing RLS and having a large number of users.

Can anyone help/explain the behaviour being observed?

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered

The question seems quite complex , I could not fullly comprehend now. May I know how the issue is affecting the server? Was it slow performance or it causes other thing ?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichardShillinglaw-7746 avatar image
0 Votes"
RichardShillinglaw-7746 answered LukasYu-msft commented

This is what I'm trying to understand for myself.....

If I start searching for fields in the Pivot Table field list (in Excel) for the 1st time after processing it is slow, after entering the 1st character I get the "connecting to data source" and then it outputs to the OLAPQuerylog table referring to the Row Level Security structures and this runs across all cubes in the database. It is then quick until the next time the cubes are processed, this will happen for each user.

I've attached the OLAPQuerylog data for what I've generated today and the connecting to data source message in the status bar, these are the distinct entries and this came from a pivot table linked to cube3.

If there is a cube (or cubes) with:-
Dynamic Row Level Security applied with multiple users having access
Those users have variable levels of access with the Row Level Security (user1 has access to 10 sites, user2 access to 5 sites etc)
Using Excel to connect

What is Excel/Analysis Services doing at that 1st connection by that user between processing windows. If this is Excel/Analysis Services working out the Row Level Security, how/could this be calculated ready for each possible user connection to be made should there be one? This would take place after processing has completed.

Hope this explains the scenario. 96589-connectingtodatasource.jpg96658-olapquerylog.txt



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you done other investigation ? Agree with Alexei's opinion that these could be just caching.

0 Votes 0 ·

Have you resolved this issue?

0 Votes 0 ·
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

At the first connection, Excel is waiting for SSAS to return the results of the query, and SSAS is calculating and caching security data. Caching is done for a combination of username/customdata/role(s) as specified by the connection. I must admit I don't remember seeing a way to warm this particular cache, but do remember seeing warnings that it could consume a lot of memory. I think it might be warmed for another user with a query via a connection made with the 'effectiveusername' property.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.