In this Blog, we will take you through how we can achieve row level security using Power BI. As a business user Power BI is a great data visualization tool. With its ease-of-use capabilities Power BI allows a user to create charts and visualization using many data sources. In an enterprise scenario, there may be multiple users using the same report. However, if they belong to different departments or regions, you may want to give them access to data specific to their department or region. Row level security allows you to specify which data is supposed to be accessed by which specific user.

To get row level security in Power BI for different database instances we need to create a DB-link in our database. DB-link is a one-way communication path between two different database servers which is used to access data from database either present on a single server or multiple servers. Power BI is a BI tool in which row level security can be achieved easily.

To create a link between two different database servers we need to fire a command in our database that will enable the database for DB connection (In our scenario we are considering the database as PostgreSQL.)

1) The command is – “Create extension dblink;”
The above command will load some new extensions in the database with the name as “dblink”. After firing the above command we will be able to connect to the database we want to connect with.  E.g. Fire the above command in database A so the database A  is now open for DB-link connection. Now we want database A to be connected with database B, for this to happen we need to create a connection from database A to B. The next command is for creating the connection between two different databases.

2) The command is – “select dblink_connect(‘myconn_B’,’hostaddr=<IP of database B> port=5432 dbname=<Name of database we want the connection with> user=<user name of the database> password=<Password for the database>);”

The above command will create a one-way connection between database A and database B.

The next step is to access the data from database B. We can do this by querying the database A that will access the data from database B. Since we are dealing with two databases, we need to have a unique identifier in both the databases that separates the two. If there is no such field which is different in both the databases then just create as pseudo field in our SQL statement as;  – “B” as branch in database B and “A” as branch in database A.

3) The SQL statement will be like:

 (

Select

“A” as branch,

category as category,

customer_name as customer,

location as Location,

lost_date as Lost_Date,

product_name as Product_Name

from lost_sales

)

union all

(

SELECT * FROM dblink(‘myconn_B’,’select

“B” as branch,

category as category,

customer_name as customer,

location as Location,

lost_date as Lost_Date,

product_name as Product_Name

from lost_sales’

) as

 t1(branch character varying, category character varying, customer character varying, Location character varying, Lost_Date as date, Product_Name character varying)

)

After creating and testing the above query with the output, go to your Power BI desktop application and in modelling section select the option manage roles. Create a new role, give it an appropriate name and click on the name of the dataset. From there select the field (in this case we will select the branch field) for users who only need to see the data for branch A. For those users select the appropriate data set name and select branch type [Branch]=”A” and to those for whom we just want to show data for branch B, type [Branch]=”B”.

Next step is to enable cross-filtering feature in Power BI.

4) Go to Files then Options and Setting and from that select Options.

In left panel, you will find various options available select Preview Features from those options and check the box which says Enable cross-filtering in both the direction for the direct query.

Restart Power BI desktop application before start using this feature.

5) Publish the report in Power BI app service (app.powerbi.com). In dataset menu on the left panel of the screen, click on appropriate dataset name, select Security.

There will be a list of all the roles created. Select the appropriate role and enter the email address of the person you want to share your data with. Create a dashboard with that report and share that dashboard by entering the email address of that very individual. The dashboard shared with all the individuals will be able to see the data specific to their role.

This is how row-level security can be achieved in Power BI. We hope you will like the blog and share with your network.

Please reach out to sales@bistasolutions.com for any query pertaining to Power BI, business intelligence or analytics solutions.