Dynamic Security in SQL Server Analysis Services

I was talking to Steven Gibbs from UNSW the other day and he told me how he improved query time for users from a cold cache by a factor of 10. UNSW redesigned some cubes so they can use attribute hierarchies (moving away from parent child hierarchies). Everything is running smoothly until he came to incorporate dynamic security.  As Steve told me “it all goes pear-shaped”.

To understand the issue, here are the requirements:

1. Security is organisationally based
2. The organisation tree is a ragged hierarchy
3. There are currently three different organisation hierarchies, last year, this year and next year.  Security needs to cover all of these and be able to grow as time progresses.
4. Setup needs to support a couple of different levels of security being:
   1. Read-only access to Finance information based on entity
   2. Write access to Finance based on entity
   3. Read-only access to Human Resource information based on entity
   4. Write access to Human Resource based on entity.

This means users could potentially have high-level access to Finance data to read, with  lower-level access to write (this isn’t so important for our MDX security but UNSW want to be able to utilise the info in with CALUMO’s write back engine) and then a separate permission for Human Resource data. Based on this, the fact table that links users dimension with entity dimension and a couple of measures, Finance and Human Resources in the ‘Fact User Entity’ measure group that have their AggregateFunction property set to Min.

The entity dimension key members include keys for aggregate members in the current year’s, so the entity dimension has a regular relationship to the ‘Fact User Entity’ measure group based on the key, and records in the fact table look something like this:

USER                            EntityName                                         FIN     HR
FINANCE\user1                UNSWConsol                                      1           0
FINANCE\user3                FACBUS                                             1           0
FINANCE\user4                UNSWConsol                                      1           0
FINANCE\user5                UNSWConsol                                      1           0
FINANCE\user6                UNSWConsol                                      1           1
FINANCE\user7                ControlledEntity                                 1           1
FINANCE\user7                CENTCTRL                                         1           1
FINANCE\user7                CENTRAL1                                         1           1
FINANCE\user8                UNSWConsol                                      2           1
FINANCE\user9                UNSWConsol                                      1           0
FINANCE\user11               D_ENG_ADMIN                                   1           1
FINANCE\user11               D_MINE                                             1           1

Next, MDX used to allow member sets giving a user access to read data at the levels including and below those they have permissions for.

In a parent-child setup this is pretty easy, but UNSW ran into a few performance issues with the attribute hierarchy. Every attribute in every hierarchy needs to have explicit permissions set. To get around this, UNSW generated a list of leaf-level members that the user would have permission to see and make this set the allowed members for the key attribute of the dimension.

Initial efforts produced some reasonable running times for the MDX, but the actual implementation caused SSAS to go spinning off into a myriad of sub-cube queries and performed woefully when visual totals was switched on. Simply logging into CALUMO or BIDS (SSAS) took 50 seconds and upwards if the cache got dropped (restart, reprocess etc.).

The optimum MDX Steve came across for the UNSW implementation looks, as Steve puts it “like a dog’s breakfast”.

exists(
	{[Entity].[Entity Name].members - [Entity].[Entity Name].[all]}
	,strtoset("{[Entity].[Entities].[" +
		generate(
			filter(
			[Entity].[Entity Name].members - [Entity].[Entity Name].[all]
			,(strtomember("[Users].[USER].[" + username + "]")
			,measures.fin)>0)
		,[Entity].[Entity Name].currentmember.name
		,"],[Entity].[Entities].["
	)+"]}"
	)
)

Running times vary depending on the level of access a user has, generally the more members that need to be generated, the slower it was. For our high-level users, on a cold cache, this runs in 90ms and warm it takes 36ms.

The actual log-in time that the users experienced is a slightly different matter and taking between 3-5 seconds if the cache gets rebuilt, nothing noticeable after the first log-in. That’s not anything to do with CALUMO either, as similar times happen when switching to a different user in the BIDS browser.

But this was not perfect.  It was just too slow. Throw more users at it and it is very painful.

The biggest lesson learned is to try and keep the Username() function out of Filter(), Generate() or similar. If these functions are used like above MDX, SSAS uses it’s formula engine (slower) rather than the calculation engine (fastest) during each iteration of the hierarchy attributes to apply the necessary security.

While the MDX above ran quickly on it’s own, when SSAS was applying it in security, it slowed – and slowed even more markedly when we added attributes and hierarchies to the dimension.

So to make SSAS dynamic security work fast, Steve used the existing fact table joined to a view generating a combination of each parent level in our hierarchies and every leaf-level beneath it. That then gave Steve access to a fact table with every leaf-level member that the users have access to.

This change increased the processing time for the user security tables and it let Steve simplify the MDX from the code to this:

NONEMPTY(
	[Entity].[Entity Name].[All].children
	,{strtomember("[USERS].[User].[" + USERNAME + "]")}
	* MEASURES.FIN
	)

Now SSAS is very very fast even with the sophisticated security UNSW require.

If you want more information, leave a comment or email me:wleitch@calumo.com.