Posts Tagged ‘Dynamic Security’

SSAS – Tabular Dynamic Security

October 1, 2012 1 comment

It is very common to have data security implementation in BI projects either at databases or Cubes and sometimes this security implementation and maintenance goes out of control due to the dynamic flow of business information.
Here discussing simple dynamic security implementation at SSAS tabular model using an external security table but we can integrate this security table into the existing tables based on particular source data at production implementations.

Step 1: Created a simple Excel file with manual entry of information for this demo but in production system we can either integrate this with existing data or maintain any additional table. Here manually entered the Sales Territory based on existing data from Warehouse.

Step 2: Imported Excel source Security table into tabular model and I logged on to machine as Administrator so it has only access to 5 Territories as

Step 3: Analysing Territory table to see the Sales Territory ID and related Regions

Step 4: Create the relations from Security table to Sale Territory table using Territory ID and the security will propagate from Security table to entire model as

Step 5: Created role “Analysts” for testing and enter the DAX formula having LOOKUPVALUE(), USERNAME() and FALSE() functions to corresponding tables as
Sales Territory Table:

='Sales Territory'[SalesTerritoryKey]=LOOKUPVALUE('SecurityTable'[Sales Territory ID], 
 'SecurityTable'[Login ID], USERNAME(), 
 'SecurityTable'[Sales Territory ID], 'Sales Territory'[SalesTerritoryKey])

Security Table:


Step 6: Select Excel icon to test, select Role “Analysts” and I logged on to machine as DOAMIN\Administrator

Step 7: On drag and drop, we can see only Territories information related to Administrator access hence it is slicing the information based on security table information

Step 8: Another test to see the existing data in the model using different credentials as

Step 9: On drag and drop, we are getting all Territories information as

Points to consider:

1. Used external table with manual entry of data for this demo but we can use any existing tables information for production implementations
2. Manually mapped the security data to use Administrator login for this demo and can use any logins for testing
3. Dynamic security has many advantages like ease of maintenance while having problems to compare one person’s information with rest of organisation to the restricted security

Categories: SSAS Tags: