Alan Mak is a Business Intelligence Consultant at Stellar Consulting. Currently, he is working on a project for a globally renowned company.
The essence of his work in this project is a Power BI Report with the composite model. It is a combination of direct query and import mode.
The project is immensely rewarding, though there are some challenges: “It has very specific business requirements. Sensitive data needs to be masked based on the user’s role as data is not permitted, to be imported into the Power BI report for security reasons”, says Alan.
It is different to row-level security as it is not based on rows. Alan and the Stellar team must overcome the direct query performance issue and toggle the maximum row limit to make this successful.
The Stellar team let Power BI Gateway pass the user authentication to the database. “The direct query will run against the database view. It contains logic to check if the user belongs to the client’s Active Directory (AD) Group with permission to view the sensitive data. The view will return the sensitive data to the user with permission”, explains Alan, “otherwise, it will replace sensitive data with a blank value”.
Alan and the Stellar team have designed the data model specifically to avoid reaching the row limit. They have created the dataset to suit the visual and to reduce the number of queries sent to the database. It is necessary to persist the data and support it with indexes to optimize the Power BI query. As a result, the report will present a different dataset based on user permission levels.
