In this introductory tutorial, we only scratch the surface of what’s possible. In a follow-up (coming soon), we will demonstrate complex scenarios including rendering user-specific data.
Table of Contents
You will need the following software:
Clone the tutorial project from GitHub: https://github.com/algattik/powerbi-transactional-tutorial.
Azure SQL Database
In the Azure Portal, deploy a new Azure SQL Database. As database name, enter advworks. Choose a unique server name and choose a login and password. Leave all settings default.
Click Next: Additional Settings, and click Sample. This will load the AdventureWorksLT sample data into the database. Click Review+Create, then Create.
After deployment completes, access your database in the Azure Portal. Open the Query editor pane and connect using the database credentials you just defined. Paste the content of the file assets/salesorders-extensions.sql and click Run.
Power BI Report
Open assets/salesorders.pbit in Power BI Desktop. You are asked to enter the database name and server name of the database you just created.
When prompted for credentials, select Database, enter the database admin credentials and click Connect.
The report is populated with data from the database.
Select Publish, save the report to a new file when prompted. Select My workspace as destination. Ignore any messages about a missing enterprise gateway. Click the Link Open … in Power BI.
Once logged into the Power BI website, in My Workspace, navigate to Datasets, locate your dataset, click on “…” and click Settings.
Click Edit credentials. As Authentication method, select Basic. Enter the database credentials you set up earlier, and click Sign in.
Navigate back to My Workspace, click Reports and click on your report.
In the report, click File > Embed.
Copy the report link into the EMBED_URL constant in scripts/settings.js file of the cloned repository.
Navigate to https://dev.powerbi.com/apps. Sign in and click Register.
Enter any application name. As Home Page URL and Redirect URL, enter http://localhost:8080 .
In the API access section, check Read all reports.
Copy the Application ID
Copy the Application ID into the CLIENT_ID constant in scripts/settings.js file of the cloned repository. You won’t need the Application secret.
То enable the client-side application to log in, an extra step is required. In the Azure Portal, navigate to Azure Active Directory > App Registrations. Set the filter to All Apps, locate the application you just created, open it and click Manifest. In the manifest, set oauth2AllowImplicitFlow to true and save.
Testing the report integration
We’re still missing the configuration of the Logic App, but let’s already test the report integration in the web page at this point.
Now verify that when you click a CustomerID or ProductID in the lower section of the report, the labels above the report update to reflect the selection.
Before we can actually create a Sales Order, we need to set up the backend integration component.
In the Azure Portal, create a new Logic App. Select any name, resource group and locatio, and click Create.
In the Azure Portal, create a new Template Deployment (not a Logic App!). Select Build your own template in the editor.
Paste the content of the file assets/logicapp-templates.json into the textarea and click Save. Select a Resource Group and click Purchase to deploy the template.
After template deployment copletes, locate the resource AdventureWorksConnection within your resource group. Click Parameter value missing.
Fill in the connection information to the Azure SQL Database you deployed. You can leave Authentication Type and SQL Connection String blank. Click Save.
Locate the resource SalesOrderInsertLogicApp within your resource group. Open Logic app designer. Click on the first step to expand it. Copy the URL into the LOGIC_APP_ENDPOINT constant in scripts/settings.js file of the cloned repository.
Return your web browser to http://localhost:8080 and do a full refresh (Ctrl+F5) to ensure the browser picks up the modified settings script.
Click on a name in the left-side filter pane.
Click on a CustomerID and ProductID on the bottom pane. Click Create Sales Order. After a second or two the report is refreshed with the new Sales order.
Notice that your filters remain selected upon refresh. You will need to wait 15 seconds before you can create a new sales order, as the Power BI API doesn’t allow more frequent refreshes.