We all have applications which have security implemented at the application access level and at data level. Now if we have cube over this for reporting we would like the data level security to propagate to this layer and subsequently into the cube.
So how do we propagate this?
Lets do this by considering a simple example of a sales data model as shown in the image below.
Given below are the dimensions-
1) Product - Stores all the products
2) Date - Stores all the dates valid in this case I am using only months
3) Employee - Stores the list of all users who can access the application.
Fact table -
1) Sales - Stores all the sales data.
Bridge Table -
1) Employee to Product Mapping - Stores the list of product an employee can see.
For employee in the login make sure you add the proper username which should be in <domain name>\<username> format. If you do not know open command prompt and enter the command whoami to find it out.
Now lets build the cube over this. All the tables mentioned above should be present in the data source view as shown in the image below.
Lets build the cube with 2 measure group.
1) Fact Sales - Contains the sales data
2) Emp Prod Bridge - Contains the count of mapping between users and products which will be used in the dynamic security.
You need to have the below dimensions -
1) Product
2) Employee
3) Date
Once it is done, click on the cube and then click on the dimension usage tab. The usage should be as shown in the image below. If it is not there add the dimension and make sure the connection between dimension and fact is present.
Once this is done process the cube. Then open the cube in SSMS and execute the below MDX
SELECT {} on 0,
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
[MST EMP].[ATTR LOGIN].&[<username which you login>],
[Measures].[EMP PROD BRIDGE Count]
)
) on 1
from [Dynamic Security]
You can see only the products which you have mapped to user who has logged in is being displayed.
Now this is through MDX but how do we enforce this on the cube? For this follow the below Steps -
1) Make sure the users who access do not have administrator rights over the SSAS else this wont be effective since it will take the higher level privilege.
2) Click on Roles as shown in the image below
3) Right Click on roles to add a new Role. Give the role some name and allow only Read Permission.
4) Go to the Membership and add the users or usergroup to this place. The best practise is to use usergroup since the permission to this cube can be handled while creating the user in the AD by admins. This will prevent frequent visits to the SSAS to provide access.
5) Go to Datasources and provide read permission.
6) Go to Cubes and provide Read and if needed drillthrough feature.
7) Now go to dimension data, you can see the list of all the dimensions present in the cube.
Click on Product dimension, go to the Advanced tab and add the code below
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]"),
[Measures].[EMP PROD BRIDGE Count]
)
)
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]") is the code which will filter out the dimension data for the user who is accessing the cube.
If you want to allow the user to access over application or powerbi \ excel pivot \ power pivot use UserName(). If you are using sharepoint over this cube you need to use CustomData().
The permission will look like the image below -
Once you are done with the above step click on ok.
Now you are ready with the permission model. You can check this by clicking on the cube and then on the browse button. Once this is done you will see the user icon as shown in the image below.
Click on the user icon and then click on the user. Provide the username and click ok.
You can then browse the cube where in you can see only those records to which the user has access to.
The cube created above with the permission model can be used via Power BI or Power Pivot or Excel Pivot by the end users for Ad Hoc reporting as well.
So how do we propagate this?
Lets do this by considering a simple example of a sales data model as shown in the image below.
Given below are the dimensions-
1) Product - Stores all the products
2) Date - Stores all the dates valid in this case I am using only months
3) Employee - Stores the list of all users who can access the application.
Fact table -
1) Sales - Stores all the sales data.
Bridge Table -
1) Employee to Product Mapping - Stores the list of product an employee can see.
For employee in the login make sure you add the proper username which should be in <domain name>\<username> format. If you do not know open command prompt and enter the command whoami to find it out.
Now lets build the cube over this. All the tables mentioned above should be present in the data source view as shown in the image below.
Lets build the cube with 2 measure group.
1) Fact Sales - Contains the sales data
2) Emp Prod Bridge - Contains the count of mapping between users and products which will be used in the dynamic security.
You need to have the below dimensions -
1) Product
2) Employee
3) Date
Once it is done, click on the cube and then click on the dimension usage tab. The usage should be as shown in the image below. If it is not there add the dimension and make sure the connection between dimension and fact is present.
Once this is done process the cube. Then open the cube in SSMS and execute the below MDXSELECT {} on 0,
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
[MST EMP].[ATTR LOGIN].&[<username which you login>],
[Measures].[EMP PROD BRIDGE Count]
)
) on 1
from [Dynamic Security]
You can see only the products which you have mapped to user who has logged in is being displayed.
Now this is through MDX but how do we enforce this on the cube? For this follow the below Steps -
1) Make sure the users who access do not have administrator rights over the SSAS else this wont be effective since it will take the higher level privilege.
2) Click on Roles as shown in the image below
3) Right Click on roles to add a new Role. Give the role some name and allow only Read Permission.
4) Go to the Membership and add the users or usergroup to this place. The best practise is to use usergroup since the permission to this cube can be handled while creating the user in the AD by admins. This will prevent frequent visits to the SSAS to provide access.
5) Go to Datasources and provide read permission.
6) Go to Cubes and provide Read and if needed drillthrough feature.
7) Now go to dimension data, you can see the list of all the dimensions present in the cube.
Click on Product dimension, go to the Advanced tab and add the code below
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]"),
[Measures].[EMP PROD BRIDGE Count]
)
)
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]") is the code which will filter out the dimension data for the user who is accessing the cube.
If you want to allow the user to access over application or powerbi \ excel pivot \ power pivot use UserName(). If you are using sharepoint over this cube you need to use CustomData().
The permission will look like the image below -
Once you are done with the above step click on ok.
Now you are ready with the permission model. You can check this by clicking on the cube and then on the browse button. Once this is done you will see the user icon as shown in the image below.
Click on the user icon and then click on the user. Provide the username and click ok.
You can then browse the cube where in you can see only those records to which the user has access to.
The cube created above with the permission model can be used via Power BI or Power Pivot or Excel Pivot by the end users for Ad Hoc reporting as well.
