Dynamic Security in SSAS Cube

Technorati Tags: SSAS,Cube,Security,Dynamic Security,username,Analysis Services,SQL Analysis Services

For implementing dynamic security in your SSAS cube you need make little change in the Dimension model.

Steps:

1. Assuming you already have a dimension to store the employee credentials for whom you want to apply the security. If such dimension is not there then create one.In AdventureWorksDw we have such a table called DimEmployee

2. Decide what you want to secure, let’s say Customer dimension members

3. Create a fact table (factless) relating the Customer and Employee dimensions

clip_image002

4. Create / modify the cube to include the following

clip_image004

clip_image005

5. Add the test data (in employee table)

clip_image006

6. Test it

Connect to the cube through any client (Cube browser/SSMA/Excel) as a test user and you will see the dynamic security working.

If anybody need this sample write to me at azazr@microsoft.com