Microsoft Dynamics NAV and Power BI – Schedule Report Refresh

In this blog, I would like to show how the excel reports (which are using the NAV OData feed) in Power BI site can be setup for refresh of the data.

1. In Microsoft Dynamics NAV 2013/2015, create a Query/Page object with all the required data for your Power BI report. For example, NAV 2015 create a Query with Sales Invoice details as shown below in NAV 2015:

2. Publish the Query/Page as a Web service in Microsoft Dynamics NAV:

3. Test the availability of web service: Copy the OData URL from the NAV web services window and paste it in web browser and make sure the data from NAV is available.

4. It is important to remove/get rid of the certificate error on your client machine. To do this, open the web browser as Administrator and paste the link again. Click on Certificate error and Install Certificate.

5. Open Microsoft Excel 2013 and Install Power BI add-ins (Power Query, Power Pivot, Power View and Power Map) and import data using Power Query.

Note: PowerPivot and Power View will be available by default in Excel 2013, but you will have to download and install the Power Query and Power Map add-ins first (These add-ins are available for free). Once these 2 add-ins are installed, you can open Excel 2013 and click on File –> Options –> Add-ins –> COM Add-ins (Manage drop down) –> Go –> Enable all 4 add-ins here. Two new tabs called Power Query and Power Pivot will be visible in Excel after enabling the add-ins.

Open Power Query tab and click on From Other Sources and select From OData Feed.

6. Enter the NAV web service URL:

7. Select the URL:

8. Next choose the authentication. Example: If the NAV authentication is NAVUserPassword, Open Basic tab and enter the NAV user ID & Password and click on Save.

9. The Query Editor opens up with all the web service data from NAV. You can modify the data here to suit your report requirement:

10. After all the changes in the Query editor, close and load the data to excel worksheet or Power Pivot. In this example, I have selected Close and Load To.. option. :

11. When you try to load the data to Power Pivot, you need to specify few parameters before loading successfully. Click on Load button to load the data to Power Pivot.

12. Next, you can create a report using this data. Foe example, create a simple PowerView chart to showcase Country wise sales as shown below:

13. Click on new sheet in excel workbook and copy Connection string and save it in Notepad (We will use this later):

14. Save this excel report on your computer (SampleChart.xlxs)

15. Create Gateway: Login to Power BI site and open Power BI admin center on the NAV server machine (It is important to login to Power BI on NAV server machine). Start with creating a newGateway:

16. Specify a Gateway name and click on Next:

17. Click on Next on Gateway instances tab.

18. On the install and register tab, click on download to download the Data gateway Management Client and install it on the NAV server machine.

19. Copy the Gateway Key into a Notepad

20. Run the Data Gateway Management Client (Installed in Step 19) on the NAV Server machine and enter the gateway key copied in previous step.

21. Click on Register

22. Select Use Service generated Certificate to create a new certificate. [Enter password and store the certificate]

23. Click on Finish to complete the creation of Gateway.

24. Create Data Source: In Power BI admin Center, create a new Data source:

25. Select Power Query from the dropdown:

26. Enter/Paste the connection string (Copied in step 13) and click on Next

27. Under data source info, specify a name, select the newly created gateway and click on Set credentials.

28. Select credential type as Basic and enter the NAV credentials and test connection.

29. Click on Save and Next

30. Under users and groups, you can add the users that are allowed to access the data source to refresh Power Query worksheets.

31. Finish the creation of Data source

32. Open Power BI site and drag and drop the Excel file (Samplechart.xlsx) into Power BI site

33. Schedule refresh for the report [on your report, click on the small 3 dots and select Schedule Data Refresh] and setup the refresh as per your requirement. To test, you can also click on save and refresh report to refresh data immediately.

34. Click on History to check the status. Here you will be able to check if the report was refreshed successfully or not.