Background
We were engaged by a creative marketing agency as part of our Power BI consulting service. The client has a large customer base and focusses on design, print management and fulfilment.
The client was in the mist of an ERP system migration and were looking to get advice their Power BI infrastructure. One of the key focus areas was to ensure data security throughout the organisation.
System
The clients infrastructure was in transition as part of the migration. There were numerous source systems across different business units. The majority of the data was held in on premise SQL Server databases. Power BI was used to present data to allow self serve across the business.
Objectives
Our key objective was to review options for Power BI security and the wider data. The key objectives were to ensure customer, area and team data was restricted. Access was to be granted on users roles and individual responsibilities.
The client was keen to establish a strategy of how to ensure security would be met and maintained from the SQL databases as well as Power BI.
Process
After reviewing the requirements with the client we presented the different options available. These were presented in working models using the businesses data and users. The main options proposed were:
1. SQL Server Security
SQL Server Security policies provide working functions of the SQL Server Security policies. This involves introducing row-level security for users. Row-level security involves restricting access to specific rows within a table based on certain conditions. The security policies provide a mechanism to enforce these access controls.
SQL Server Security Policies are implemented using a combination of inline table-valued functions and security predicates. A security predicate is a condition defined within a function that determines which rows a user can access. These policies are applied ensuring that users only interact with the data they are authorized to see.
These policies whilst powerful are transmitted in views but not maintained in tables. This means to ensure it is maintained across tables multiple functions need to be used.
2. Power BI with Security inherited from SQL
We were able to give working examples of Power BI workbooks where the row level security was inherited from SQL Server. On the surface this is an ideal solution as security would only need to be maintained in one place.
There are drawbacks to this solution within Power BI. The first issue is that all Power BI users would need to have user profiles set up on the SQL database. This issue sets and an additional administration burden. The second issue is that for this to operate the users would have to be running the queries. This issue means that the dashboard needs to run using direct query.
Direct queries have a significant limitation is the potential impact on performance. Direct Query relies on the live connection to the underlying data source. The query execution happens on the source system rather than in the Power BI engine. This can result in slower query performance compared to importing data into Power BI. This is especially an issue when dealing with large datasets or complex queries
As part of this solution we also investigated Power BI composite models. In principal the security tables could function as direct query and act as a filter. The main data would then be extracted with import query. Whilst this would remove an improved dashboard performance. It is not possible for the direct query table to filter the import query table.
3. Power BI Row Level Security
Power BI can create Row Level and object level security . In this work we used Row-level security (RLS) for Power BI Security.
Power BI Row-level security (RLS) is a vital feature enabling organizations to finely control data access based on user roles. In Power BI Desktop, administrators create distinct roles, representing different user groups. Within each role, filters are applied to specific tables or columns, dictating the conditions under which a user in that role can access data. These filters might include constraints like department, region, or other relevant criteria. Users are then assigned to appropriate role and are assigned in the Power BI service. When the Power BI report is published to the Power BI service, row-level security is enforced. This ensures that users only see the data pertinent to their assigned role.
Outcome
Select Distinct were in a great position to not only provide advice for the client but also provide working examples. This allowed the recommendations to not only accepted but rapidly also put in place.
Conclusion
Our engagement with the creative marketing agency saw us recommend solutions to secure their data. Our objective was clear: review and recommend data security options that align with the organization’s unique requirements.
We thoroughly examined options, including SQL Server Security and Power BI with Security inherited from SQL, and Power BI Row-level Security (RLS). We were able to review with the client the pros and cons of each method. This enable the client to rapidly build this into their data infrastructure for data and Power BI Security.
Contact us if you want to find out more or discuss references from our clients.
Find out about our Business Intelligence Consultancy Service.
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
Blog Posted by David Laws
