Background
A creative marketing agency engaged us 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 midst 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 resided in on-premise SQL Server databases. We used Power BI to present the data, enabling self-service across the business.
Objectives
Our key objective was to review options for Power BI security and broader data management. The primary goals were to restrict customer, area, and team data and grant access based on user roles and individual responsibilities.
The client sought to establish a strategy to achieve and maintain security from SQL databases to Power BI
Process
After reviewing the requirements with the client we presented the different options available. We presented these in working models using the business’s 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 the rows a user can access. These policies ensure that users interact only with the data they are authorized to see.
These policies transmit in views and are not maintained in tables. This means multiple functions need to be used to ensure maintenance across tables.
2. Power BI with Security inherited from SQL
We supplied working examples of Power BI workbooks that inherited row-level security from SQL Server. This approach is ideal because it requires maintaining security in only 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 source system executes the query rather than the Power BI engine. This results in slower query performance compared to importing data into Power BI. This issue is a significant factor, 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. An import query extracts the main data. 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 an appropriate role in the Power BI service. This results in row-level security been enforced when the report is published to the Power BI service. This ensures that users only see the data pertinent to their assigned role.
Outcome
Select Distinct was in a great position to provide advice for the client and offer working examples. This allowed the client to quickly accept and implement the recommendations.
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
We choose our Business Analytics Timesavers from our daily 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
