Power Pages: Security - Power BI embedded with RLS
skyshon
245
Like
Share
ReportI've been on something of a Power BI kick lately...
Just as Pages is the tool of choice to quickly extend your Dataverse deployment to the public in the form of a website, Power BI is the tool of choice to render elegant data-driven visualizations. The beauty of the Power Platform lies in its ability to seamlessly blend the two. In the past, to render a Power BI report in the Pages, administrators had to publish said report to the web and embed it into an iFrame. Now, even if one did apply Pages permissions to lock down the page with the report, if an individual had the link used to embed the report, anyone would still be able to access the report outside of the website. The purpose of publish to web is exactly that --to allow any individual on the web to consume the data and even reshare the report. Sure, this might make sense to display say, geese migration data.. but a report of which employees, by name, made the most retail sales in the month of April? Not so much.
That's where Power BI embedded comes in. With Power BI embedded, one can contextually serve Power BI components to users, pass automatic filters by using a filter parameter, and enable row-level security capabilities to allow an organization to truly secure data visible to users and only display what they are meant to see.
Scenario
Adventure Works has a monthly report of bike sales broken down by sales associate.
Leadership is rolling out an employee website wherein associates can view this data, but they should only be able to see their own sales. Below are steps to meet this requirement.
Pre-requisites
- Capacity for publishing Power BI embedded content
- An organization workspace in Power BI service
- Tenant global admin rights
- Tenant global admin with Admin role in the Power BI service workspace
- Registration/authentication turned on in the website
- Web page in Pages to embed the report
1. Power BI Desktop
The sales report in Power BI desktop connects to a custom Dataverse table called adv_sales. This table has a N:1 relationship with contact, as adv_sales contains a lookup column (adv_associate) that looks up to contactid. We first need to change this relationship to use bi directional filtering.
We need to create our role to be used for Pages users from within Power BI desktop. I named my role portaluser.
Filter the contact table using the following DAX expression: [adx_identity_username] = username(), then save and publish the report to the organization workspace wherein your tenant global admin is an administrator, in Power BI service.
Note: [adx_identity_username] resides on the contact table and is not an actual username, but references the adx_externalidentity table. This has the guid that is sent to Power BI in the username() function.
2. Pages admin center
Navigate to the Pages admin center and copy the Application ID.
3. Azure portal
Follow steps 1 and 2 here to create a security group and add the portals Azure AD web application as a member to the group.
4. Power BI Service
Follow step 3 here to allow service principals to use Power BI APIs in your tenant and add the Azure AD security group to apply.
5. Pages admin center
From Pages admin center as a global admin, navigate to Set up Power BI integration and click on Enable Power BI visualization. The portal will restart and will be unavailable for a few minutes.
Next, click on Enable Power BI Embedded service. You will be prompted to choose which workspace-- select your organization workspace that contains your report.
6. Pages Studio
From your Pages Studio, navigate to the web page where you will embed the report. Click on an empty section and select + > Power BI.
The right-pane will contain your options for embedding. To use Power BI embedded, choose Embed for your customers under Access type. Below that, populate the appropriate Workspace, Type (Report or Dashboard), Report, and Page.
If you go to the source code editor </>, you will find that liquid code has been generated. This will look like:
{% powerbi authentication_type:"powerbiembedded" path:"https://app.powerbi.com/groups/00000000-0000-0000-0000-000000000000/reports/00000000-0000-0000-0000-000000000001/ReportSection01" %}
To specify the role that you created in Power BI Desktop, click on the embedded Power BI component and on the right-pane, expand Advanced settings. Turn on Apply roles and specify the role name in the text box (I used portaluser).
To see how this has modified the liquid, navigate to source code editor </> and scroll to the right. You will find that the liquid is now:
{% powerbi authentication_type:"powerbiembedded" path:"https://app.powerbi.com/groups/00000000-0000-0000-0000-000000000000/reports/00000000-0000-0000-0000-000000000001/ReportSection01" roles:"portaluser" %}
End result
After clicking Browse website and logging in to the website with Azure AD as my sales associate, Benjamin Spock, as Benjamin I am now only able to see my own sales.
*This post is locked for comments