Shared SalesTerritory with dynamic security

We have a table which contains the sales territory Areas of Sale of a company . We want to share the areas between the two agents.  Furthermore, we want the agent that queries the cube tabular can see only the areas of its competence.

First we’re going to go into our database and add a table that we can use to map to what we’re securing by. For this example, I’m going to map between the Security dimension and the sales territory dimension.

Now let’s see the security table

DimSecuriry

DimSecuriryWithData

this is a parameter table is useful when you want to add a slicer to a PivotTable and make it modify the result of some calculation, injecting parameters into DAX expressions. To use it, you must define a table that has no relationships with any other table. Its only scope is to offer a list of possible username for security. The windows account MARPOZ is a agent 1 and the LUCROS account is agent 2 but impersonate 1 because shared the same areas.

Now let’s see the sales territory table

SalesTerritory

SalesTerritoryData
So we can’t relate to our security table. If I create a relationship between DimSalesTerritory and DimSecurity i  get a many-to- many relationship that does not fit into the  tabular model.  A snapshot of this piece of the model looks like:

Relazioni

 

Create a New Role, and give it a name “Accesso”, giving read access under the permissions.  

 

role

Go back to the Row Filters tab, and find the  find the DimSalesTerritory table, and type in the following formula:

=DimSalesTerritory[Id_Agent] = LOOKUPVALUE(DimSecurity[ID_Agent],DimSecurity[Username], USERNAME())

What this formula does is to say, “for each value in the DimSalesTerritory[Id_Agent] column, find the current logged in Windows user name, and match it to the DimSecurity[ID_Agent] column. And only show rows where they match”

 

Comments are closed.

Switch to our mobile site