Part-2: Power BI report development and pinning to Dynamics 365 for operations workspace

This series is continuation of Part-1: Data entity creation, Azure setup and recurring integration setup. We will create new PowerBI report using Azure SQL BYOD data, which originates from Dynamics 365 for Finance and Operations.

Step-1: Power BI app Registration process

1. Sign in to https://portal.azure.com/ using an Azure tenant admin account.

Note: The user who completes this procedure must have Admin rights for the tenant to register applications.

2. Go to Azure Active Directory> App registrations > New application registration.

3. Enter the following values:

• Name – Your app name.
• Supported account type  – Accounts in this organization directory only 
• Application type– Web 
• URL – The base URL of your Finance and Operations client and the OAuth suffix. For example, https://usnconeboxax1aos.cloud.onebox.dynamics.com/oauth

4. Click Create.

5. Copy the Application ID. This will be used in Finance and Operations to connect to the PowerBI.com service.

6. Click View API permission > Add permission > Commonly used Microsoft APIs > Select Power BI service

7. Select all permission check boxes under Delegated permissions > Add permissions

8. Grant Admin consent  for current organization.

9. Click on Certificates and secrets(Keys) > New client secret > Provide name and expiration value.

• Application ID: Client ID of app registration
• Application Key: Client key generated

Step-2: Specify Power BI settings in Finance and Operations

1. In the Finance and Operations client System Administration > Setup > PowerBI.com configuration.

2. Select Edit.

3. Set the Enabled option to Yes.

4. In the Application ID field, enter the Application ID value that you got from Power BI in the previous procedure.

5. In the Application Key field, enter the Application Key value that you got from Power BI in the previous procedure.

6. Click Save.

Complete the steps in the next section to verify the changes and enable PowerBI.com integrations.

Step-3: Pin tiles to a workspace

1. Open Reservation management workspace > Navigate to PowerBI section >Click on GetStarted

2. In Authorize Power BI dialog,provide authorization to Power BI

3. You will receive message “Successfully completed the authentication process” 

Step-4: Create reports in Power BI

1. Open Power BI desktop app
2. Sign up with the credential used for connecting to Visual studio
3. Click on GetData > More… > Azure > Azure SQL database > Connect
4. Provide database name “trainingbyodservertest.database.windows.net” > Next
5. Click on Database and provide database admin user name and password
6. Select the table (staging table) > Load
7. Create a simple report like the one below

Step-5: Publishing and Pinning report to Dynamics 365 dashboard

1. Click on Publish in report screen > Save
2. Open app.powerbi.com and navigate to My workspace > Reports section
3. Click on report created > Click on Pin Live Page option > Pin to dashboard      existing dashboard “BYODConnectivity”
4. Click on PinLive
5. Now navigate to Dynamics 365 Finance and Operations > Workspace > Reservation management
6. Click on Power BI tile > GetStarted > Click on the report > OK

Step-6: Create new record in table and verify in PowerBI

1. Open DYNServiceTable and create a new record. 
2. Since recurring integration is setup, now open Azure SQL database and try to query and check if the new record appears in BYOD database
3. Now open app.powerbi.com and navigate to My workspace > Dataset section > Click on … and Schedule Power BI refresh timing. Data should be refreshed from BYOD to Power BI in few minutes.
4. Open Reservation management workspace in Dynamics 365 to verify if the new record got updated in the loaded report.

Thanks for you time!!!