Scenario:
The scenario we are dealing with is that we have an Azure SQL database from which there is a need to create Power BI reports and view them in a custom application built on .NET core. The database contains data from different companies, and hence, there is a requirement to create a company-based filtered report. But the reports should be live and optimized, and therefore comes the need for a middle layer like the Azure-based analysis services where all the data pre-processing, modeling, and shaping is performed, and a live connection is built to Power BI Desktop where visualization happens. Also, row-level security in Power BI reports must be implemented for company-based filtering. But the problem here is when there is a live Azure Analysis services connection in Power BI, row-level security cannot be implemented in Power BI Desktop using the normal routine process of USERPRINCIPALNAME(). Also, this row level security should be visible in the embedded Power BI report in the application. This article discusses the approach followed to implement this complex but-if scenario.
Pre-requisites:
- Microsoft Power BI Desktop (preferably 64-bit should be installed)
- Azure Subscription
- Azure Active Directory (AD), where user emails are stored, will be used in Row Level Security (RLS).
- Azure app registration for Azure AD app.
- Azure Analysis services instance with a developer edition, which is the basic license required. In the Azure analysis services instance, the AD app should be the admin.
- A .NET framework/core.
- A Power BI service account/workspace Embedded license or PPC license.
- Visual Studio with SSDT toolkit.
9. In Visual Studio, to create Analysis services models or Tabular models as per the vocabulary of a developer, there is a requirement to install its SQL Server Analysis Services. The steps here are:
- Open Visual Studio and select Continue without Code
- Click on Extensions and select Manage Extensions.
- In the Visual Studio Marketplace, search for Analysis services and install the extension if not installed.
10. A dataset on which dynamic row-level security needs to be applied.
11. Microsoft excel for testing the dynamic row level security.
Azure Analysis Steps:
- Open Visual Studio and create the tabular model with different data sources.
- There should be a table or column in which email id’s should be provided. These email id’s should be a part of the Azure Active Directory.
- Create an Azure AD app by going to app registration and include this app id as part of the Azure Active Directory. This will act as the service principal for our Power BI workspace.
- In Visual Studio, right-click on Roles.
5. In the role manager pop-up window, provide the name of the role and define the appropriate permissions.
6. In the same pop-up window, under the role details, in the row filters, type the DAX expression against the table in which the row-level security needs to be applied. The DAX expression will be applied on the column and would be as below:
= table_name[column_name] = CUSTOMDATA()
7. Then click on the members section and click on Find Users.
8. A Microsoft pop-up box will open, prompting the user to log in. Login with ID, which is a part of the Azure Active directory. Type the name of the user, click on search, and then click add.
9. Once the login is completed, search for the users/groups which need to be included for row-level security implementation. Also include the AD app created here.
10. Once completed, click on the OK button.
11. Once completed then in the solution explorer, right click and select properties. Provide the server name which the Azure based analysis server on which the model needs to be deployed. Also, provide the database in case the user does not want the solution name as the database name. Click OK.
12. Once done, in the solution explorer, right click and select deploy. It will prompt the user to enter credentials. Complete the sign in and let the model deploy. Once completed, click on Close.
Power BI Steps:
1. Once the deployment is complete, open the Power BI and go to Get data and search for azure analysis services. A pop-up window will open to type in the server name and select the connection mode. Select Live and click OK. It will open Microsoft authentication window. Authenticate the credentials and click OK. Select the model and select OK.
2.Create the report as needed.
3.Publish the report in the workspace which has the Embedded license or PPC license.
4.Open the Power BI workspace on the Power BI service and go to manage access. The service principal i.e. AD app needs to be added as the admin of the workspace.
Â
.NET Application:
1. Open Visual Studio and create a new ASP.NET web application project. (This article uses the ASP.NET framework; however, this can be built on any other .NET framework as well.)
2. To display the Power BI report, an embed token needs to be generated. This can be accomplished using the function GenerateToken, which is present in the namespace Microsoft.PowerBI.Api. In this function, the parameters that are required to pass include Power BI workspace/group ID, Power BI report ID, and a token request.
3. The token request can be created using the function GenerateTokenRequest, which uses the parameters – the Power BI report access level, which can be (View/Edit/Create) and the dataset id. This function is present in the namespace Microsoft.PowerBI.Api.Models. Â
4. To display the Power BI report using row level security (RLS), an EffectiveIdentity object needs to be created using the in-built function EffectiveIdentity() which is defined in the namespace PowerBI.Api.Models.
4. This EffectiveIdentity() uses parameters : username, roles, customdata and datasets.
5. To use this function, we need to extract its value from the Azure AD app created. Open the Azure AD app in app registrations on Azure. Beside the title managed application in local directory open the app name.
6. There will be an Object ID, which will be used as the username when you open the application.
7. The role, i.e., the row-level security applied in the Analysis services, is static and defined in the XML file of the code.
8. The customdata parameter takes the row level security (RLS) applied email address.
9. The dataset is the Power BI report database, which is basically the live Azure Analysis model.
10. To Generate the embed token we are creating a request using a function called GenerateTokenRequestV2 which uses the parameters – report id, dataset id, and target workspace id.
11. Once all the parameters are set, run the application and the Power BI report will open with row-level security applied.
Â
Using these steps, the live Azure Analysis dataset with row-level security can be viewed inside an application. The bookmarking retention is created separately in Part II of this article. This solution has helped various customers whom we have partnered in sharing their data across multiple their customers/partners. This solution benefits them by showing partner-based data to their partner with no license to be acquired for each user.
Microsoft Power BI is one of the leading tools for analyzing data and generating insights. PowerBI is a unified, scalable platform for self-service and enterprise level business intelligence (BI) tool which helps in connecting to and visualizing any type of data and generating insights from it which helps in making better
Authored by
Deepanshi Ranka
Deepanshi Ranka is a Senior Associate in BI & Analytics team at Advaiya. She is a Microsoft certified Data Analyst and has an experience of over 3 years. She specializes in analytics, reporting and analytical tools that work seamlessly with business intelligence, data warehousing, architecture, data modelling, and cloud solutions to create effective solution models and optimize the operations.
