Data Masking and Unmasking Strategy for Power BI Report Testers and Business Users

By: Haroon Ashraf   |   Updated: 2022-11-07   |   Comments   |   Related: > Power BI

Problem

As a Power BI developer, I want to understand how to implement masking and unmasking database strategies for testing and publishing a Power BI reporting solution.

Solution

Using the masking and unmasking database feature for testing, implementation, and viewing the Power BI report ensures that the testers cannot view sensitive information. At the same time, this feature allows business users to view their sensitive data on the production site.

Role of Tester and Business User in a Reporting Solution

The role of a tester and a business user is very much the same in every reporting solution, including Power BI. However, there is a thin line between adopting a standard practice and defining principles that work for you.

We are particularly focused on a simple scenario of testing a Power BI report in this tip, but in no way does this simple scenario limit your capability of porting it to a full-fledged professional life scenario.

Let's discuss the simple roles of the tester and business user in a Power BI-centric reporting solution.

Tester

A Power BI tester is supposed to test a Power BI report to ensure that it serves the purpose, but at the same time, they are not supposed to see the sensitive data contained in the reporting visuals.

One might argue why we cannot use test data for the tester. The answer lies in the scenario itself, as we are in the final stage of testing the report just before deployment to production. The tester must connect with actual data because the report is about to be published to the target side, which is a specific type of scenario.

Business User

The business user is the user who is going to consume the reporting solution by viewing and analyzing the data in the form of a Power BI report.

The Need for Masking and Unmasking Data

Since we want the tester to test the report (data) without seeing the actual data (for specific sensitive visuals) and the business user to view the data without any trouble, we have to switch between masking and unmasking (sensitive) data. The tester does his job, and the report finally gets deployed to the designated workspace for the business user to consume.

Database Strategy to Mask and Unmask Data for Testing and Publishing

Now, masking and unmasking within the database itself achieve the desired objective based on the following points:

1. We need to create a database user who can view the sensitive data in a masked form.
2. The business user is also a database user who can view sensitive data in the unmasked form.
3. The overall process should be transparent for the business user but should work as per the requirement.

Use Case Scenario

An example scenario is when the final checks are being performed. The tester can test the data but cannot view the sensitive column information, while the same column is viewable by the business user once it is deployed to the designated workspace.

Setup Sample Database (WatchesV5) with Tester (PowerBITester) and User (PowerBIUser) Logins

For this example, let's set up an on-premises SQL database but please feel free to get an Azure database setup for yourself if that is easier with Power BI in published mode. For an on-premises SQL database, an on-premises gateway must be installed on your machine (where the SQL instance resides) for your data refresh.

Note: This scenario assumes the reader knows how to connect Power BI with their database either on-premises or an Azure SQL database.

Let's set up a database called WatchesV5 along with two logins for the tester and user by running the following script against the master database:

--USE [master]
-- (1) Create WatchesV5 database
CREATE DATABASE WatchesV5
-- (2) Create PowerBITester Login first in master database
CREATE LOGIN PowerBITester WITH PASSWORD='S3cr3t000'
-- (3) Create PowerBIUser Login first in master database
CREATE LOGIN PowerBIUser WITH PASSWORD='S3cr3t111'

Setup PowerBITester and PowerBIUser Users Based on Logins and Populate Sample Database with Watch Table

Create two users in the sample database based on the logins just created in the master database, and populate the sample database WatchesV5 by running the T-SQL script against it:

--USE WatchesV5
-- (1) Connect to the database and create user for the same login
CREATE USER PowerBITester FOR LOGIN PowerBITester
 
-- (2) Connect to the database and create user for the same login
CREATE USER PowerBIUser FOR LOGIN PowerBIUser
 
-- (3) Creating Watch table with masked Brand column
CREATE TABLE [dbo].[Watch]
(
    [WatchId] INT NOT NULL IDENTITY(1,1),
    [WatchType] VARCHAR(40),
    [Brand] VARCHAR(40) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    [Colour] VARCHAR(20),
    [Price] DECIMAL(10,2)
    CONSTRAINT [PK_WatchType] PRIMARY KEY (WatchId),
);
 
-- (4) Populating Watch table
SET IDENTITY_INSERT dbo.Watch ON
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (1, N'Digital', N'Timex Ironman ', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (2, N'Analogue', N'Casio G-Shock', N'Blue', CAST(200.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (3, N'Smart', N'Garmin Smartwatch', N'Yellow', CAST(300.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (4, N'Sports', N'Timex Expedition', N'Black', CAST(150.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Watch] ([WatchId], [WatchType], [Brand], [Colour], [Price]) VALUES (5, N'Digital', N'Seiko Superrunner', N'Brown', CAST(185.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT dbo.Watch OFF

Mask Data (Watch Table) for Tester and Unmask for Business User

Now, by default, when we created the Watch table with a masked column, the data is masked for both users. But now, we can unmask data for PowerBIUser.

This is achieved by running the following script against the sample database:

--Grant SELECT on Watch table
Grant SELECT ON Watch TO PowerBITester
--Grant SELECT on Watch table
Grant SELECT ON Watch TO PowerBIUser
GRANT UNMASK TO PowerBIUser

Test PowerBI User Access

Let's now test PowerBIUser access by running the following T-SQL script:

-- Testing PowerBIUser access
EXECUTE AS USER='PowerBIUser'
SELECT * FROM dbo.Watch
REVERT

The output is as follows:

Test PowerBI Tester Access

Now, let's see from the view of a PowerBI tester, who is not supposed to view the sensitive information, i.e., the brand of watch in this case:

EXECUTE AS USER='PowerBITester'
SELECT * FROM dbo.Watch
REVERT

The results are as follows:

Database Hosting Choice Before Publishing to Power BI

Next, we will build a report on top of our database and see the difference between when the Power BI Tester uses it and when the Power BI User views it.

Note: Remember you have two options: connect your Power BI report with an on-premises database like the one we built or with an Azure SQL database.

Most importantly, if you want to build an on-premises database for your Power BI report, then after you publish the report, you must enable the on-premises gateway for the Power BI report dataset to comfortably connect with your on-premises database for refreshing purposes. This is a common practice when using on-premises data sources:

Create Power BI Report and Link with Sample Database (WatchesV5)

Note: This tip assumes that Power BI Desktop is installed on your machine and that you are familiar with creating Power BI reports.

Let's build a Power BI report called Watches Report and connect it to the sample database WatchesV5.

Open Power BI Desktop, click on Get Data, and enter the server and database name to get connected:

Next Load data:

Pick Table visual and add it to the canvas to view the data:

On the Home tab, click on Transform data and select Data source settings:

Click Edit Permissions… followed by clicking Edit…as shown below:

Now make changes by filling in PowerBITester details and save the changes as follows:

Refresh the report now to see how the visual is masking the watch brand for PowerBITester after we gave permission to work on the report:

The tester is given this report to test and gives the green signal if all is well.

Publishing the Report

Once the tester gives the green signal, one of two options can happen:

1. Another team publishes the report that replaces the tester credentials with business user credentials who can view all the data of the report, while the tester has no access to that workspace, or
2. The tester can publish the report to a test site, and once the tester is happy, the report can be published to the production site

Once published, the report shows the visual below (as it is using PowerBITester credentials to connect with the sample database):

Replacing Tester Credentials with User

Go to the report workspace. Click on the dataset settings:

Click on Edit credentials under Data source credentials:

Add PowerBIUser credentials:

Refresh the dataset and the report:

Congratulations, you have just learned how to mask and unmask report data for different types of users.

Note: A more resilient data view control can be established through RLS (row level security), especially when you have different levels of business users who will consume the report data.

Next Steps

• Try building test and production sites and implement the above tip to see how it goes.
• Try using SQL Database Project with Git to build your database behind the report.
• Enhance your solution by adding the unit testing for the database object (Watch table) being consumed by the report.
• Go through the tip to understand how to connect on-premises data sources via the personal gateway: Connect to on-premises data sources with Power BI Personal Gateway.
• Please see the tip to Configure and Customize SQL Azure Dynamic Data Masking for further information about dynamic data masking.

Learn more about Power BI in this 3 hour training course.

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips