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 of the Power BI Campaign
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!
Optimising your data infrastructure involves more than just great visualisations; it requires secure, scalable architecture.
Discover how we balance performance with protection in our Power BI Security Case Study, or browse our wider portfolio in Our Work.
Frequently Asked Questions
What is Row-Level Security (RLS) and why is it important?
Row-Level Security allows us to publish a single report that shows different data to different people based on their role or region. For example, a Regional Manager only sees their own branch data, while the CFO sees the entire estate. This ensures data privacy without needing to create dozens of separate report files.
How do you ensure sensitive financial data isn't shared externally?
We implement strict tenant-level settings and workspace permissions within your Power BI environment. By utilising Microsoft Entra ID (formerly Azure AD) groups, we ensure that only authorised internal users can access specific reports, preventing accidental sharing or data leaks to external parties.
Does implementing high security slow down report performance?
Not if it is architected correctly. Our "Software Second" approach ensures that security filters are applied at the most efficient level of the data model. By optimising the underlying DAX code and data structure, we maintain lightning-fast report performance while keeping your data locked down.
Can we track who has viewed or exported our data?
Yes. Power BI provides detailed audit logs and activity tracking. We help you set up and interpret these logs so you can monitor usage patterns, see exactly who is accessing your reports, and identify if any sensitive data has been exported or printed.
We use a mix of Pro and Premium licences; does security differ?
While the core security features like RLS are available on both, Premium offers enhanced capabilities like "Service Principal" support and more granular capacity management. We help you map out the most cost-effective licensing strategy that meets your specific security and compliance requirements.