Microsoft Dynamics NAV and Power BI (with O365) – Schedule Report Refresh


Have you tried Microsoft Power BI? If not go ahead and sign-up for a free trial now. You can watch the Getting Started video that will help you complete the required process and start using Power BI. For further information please visit the Overview and Learning section.

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:

Capture 1

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

Capture 2

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.

Capture 3

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.

Capture 4

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.

Capture 5

6. Enter the NAV web service URL:

 

Capture 6

7. Select the URL:

Capture 7

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.

Capture 8

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:

Capture 9

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. :

Capture 10

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.

Capture 11

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:

Capture 12

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

Capture 13

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 new Gateway:

Capture 14

16. Specify a Gateway name and click on Next:

Capture 15

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

Capture 16

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.

Capture 17

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.

Capture 18

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

Capture 19

25. Select Power Query from the dropdown:

Capture 20

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.

Capture 21

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

Capture 22

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.

Capture 23
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

Untitled

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.

Capture 24

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

Capture 25

Similarly, you can create multiple Power BI reports (which are visually rich, interactive and easy to share) using NAV OData feed & Excel and you can schedule these reports to refresh everyday in Power BI site to show the up to date reports.

Thanks for reading and I hope it was helpful 🙂

Advertisements

5 thoughts on “Microsoft Dynamics NAV and Power BI (with O365) – Schedule Report Refresh

  1. Pingback: Microsoft Dynamics NAV and Power BI – Schedule Report Refresh | Pardaan.com

  2. Pingback: Dynamics NAV with Power BI | Roberto Stefanetti NAV Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s