Until now, you have already created the entire data architecture and visualization in a BI Tool viz. Power BI. So, it means that your team is now able to drive decisions dynamically from raw data. This is a good complete step for BI implementation. However, there are many more nuances for complex needs.
Why you need Security filters?
Let’s take an example: You run an accounting firm and you have 100s of clients. Your team prepares a monthly report for each of your clients in a similar format and structure. Now, you have implemented BI and you are able to get Dashboards and monthly reports dynamically. One way is that you create a data model for each client separately. But that would not be the best utilization of your resources as all the reports are similar. In such a case, how would you ensure that the report can be delivered on the web in such a way that data relevant to that client is shown up? Creating such access based on the user account is what we are referring to as applying security filters for User access.
How to implement security filters for User access?
If we are using a tool like Power BI, we can easily do the same in Power BI desktop. However, if the reporting needs are more complex and the entire data model lies with Analysis services. As an example, let’s quickly see on how once can easily create security filters for different users.
Let’ s have a look at the data model. There is a company which has several departments. The Department Table has one too many relationships with the PL_ Actual ( Profit & Loss) Table and Forecast Tables ( Sales_ F 1, Expense_ F 2 ). At a business level, It means that the entire Profit & loss details can be filtered by a particular Department. Now, we would like to create user access in such a way that the data when accessed by Department “ Store 1 ”, the same does not show up any data other than Store 1.
To implement this, we can create row-level security at a Department level which would filter the entire data model since there is an existing relationship between the Department table and all other Tables as shown below.
In order to test it, you can now view the same dashboard with different roles. Here is an example of how the dashboard looks when viewed as Store 1
Comments