Today I am writing another post on the topic how to connect power bi with Microsoft dynamics NAV 2017. The agenda of today’s topic will be:
- Steps to integrate
PowerBI is a great reporting tool which allow us to make interactive reports, 3D charts. All power BI need is the Data set. The purpose of this post is once we developed the report on power bi then how we can see in dynamics nav. In Dynamics NAV 2017, Microsoft introduced the functionality to integrate power BI with dynamics NAV.
- You must have Dynamics NAV 2017 installed on a machine. This machine should be available on the internet (it should have open ports for the OData service of dynamics nav service tier ).
- You must have Power BI subscription. If you do not have Power bi subscription then sign up for power bi free version here
- You must have azure subscription on the same id on which you have subscribed power bi. This is important because if you don’t you will not able to register power BI app in Azure App registration portal.
Steps to develop Power BI report
- First of all download and install Power BI Desktop. You can download it from here.
- Go to the Dynamics NAV Administration Console. Under Client Services Fast tab mention the public base URL in the Web Client and Windows Client field and click Save.
- Restart your NAV Service.
- Expose the Objects as an OData Webservice in Dynamics NAV for which you want to make a Power BI report (for instance: Sales Dashboard)
- Copy the URL available in oData field on the Web service page.
- Open Power BI Desktop Application and sign in with your power BI credentials.
- Click on Get Data and select OData Feed.
- Paste the URL you have copied in step 5. Truncate that URL till the company name e.g http://mymachine.com:9147/DynamicsNAV100/OData/Company(‘CRONUS_INTERNATIONAL)
- Once you have get the data set select the table. Apply filters if there is any.
- Drag and drop the chart and select the field you want to display in the report.
- Once the report has been developed, then click on publish buttion. It will publish to the power bi online. Now you can access your report on your PC, Web, Mobile device almost anywhere!
Steps to Integrate Power BI and Azure
- Open Dynamics NAV Windows Client and Search for Assisted Setup.
- Once the Assisted setup page has been opened select the line SETUP AZURE DIRECTORY.
- Click on it and the setup wizard gets started.
- Click on next and you will have two URLs in the textbox which is Home page URL and Reply URL.
- Copy the Reply URL you get on Step 4.
- Now go to Azure Portal (link). On the left sidebar, scroll down and click on More Services.
- Under More Services search for App registrations and open it.
- Click Add and give the name of your app e.g. Power BI
- Select “Application Type” Web API to integrate Power BI in the Web Client or Native App if you want to integrate Power BI in the Windows Client.
- On the Sign-on URL, paste the URL you have copied on step 5.
- Click Create. It will create your app.
- Now Under API Access section select Required Permission Tab. Once the permission page has been opened Click Add. Under API Selection, select “Power BI Service”. This app works as an intermediate authentication layer which communicates with the Power BI and Dynamics NAV.
- Under permissions tab, select all the permission one by one and click add. It will add the permissions to your app.
- Now Under API Access section, click on Keys option, give any name to the key and select the duration you want your key to valid to and click save. It will generate the Key for you. Copy this key and keep it safe because you will not able to see that key again.
- Paste the above key in the Dynamics NAV Setup wizard Key field.
- Copy the Home page URL from Dynamics NAV setup wizard.
- Now go to application properties and paste the URL into Homepage URL field which you have copied in step 16.
- From Properties page, copy the application id and paste it in Dynamics NAV Application ID field setup wizard.
- Click on Finish to exit the setup wizard.
- Now go to Home of your Windows/web Client and click on Get Reports.
- It will show the pop up to authorize the Azure Service. Click on Authorize Azure service and the pop up will open which will ask for your email address and password.
- Provide the same email address and password which you have used for Power BI and Azure Subscription. REMEMBER, it should be same.
- If you have done all the steps correctly your app has been authorized and you will be able to see the report you have published to Power BI online. If the app is not authorized and gives error, then you need to check because there is something wrong.
- Now open your Dynamics NAV web client and on the home page click on Get Report. Select the report and you will able to see your power BI app in Dynamics NAV Web Client.