Follow us today>

Blog

Get industry insights, product updates, event invites, articles and latest NAV release to your inbox.

 

Power BI and Microsoft Dynamics NAV 2015: The Perfect Match.

Posted: 27/06/2016 3:27:19 PM by Dynamic Business IT Solutions

The benefits of the new Power BI mean that Microsoft Dynamics NAV users worldwide can now leverage their existing technology to get more insight from the data in their NAV application.

In this blog post we will take a look at how Microsoft Dynamics NAV can take advantage of the benefits, and expose data to enable the building of a dashboard like the one shown below.

Expose refreshable data in PowerBI.com

For the data to be refreshable from PowerBI.com, the instance of Microsoft Dynamics NAV that is used for this must be configured to use Username/Password authentication.

To begin, a dataset from Microsoft Dynamics NAV is needed. Ideally you should use a query which provides a performant way of reading all sized datasets in Dynamics NAV, to do this.

You’ll see below a simple query called CustomerAnalysis:

After the query is saved and compiled with the object ID 50000, it must be exposed as a web service for the outside world to be able to access the data. This is done in the Web Services page:

The query can then be consumed outside Dynamics NAV using Excel.

The first step within Excel, is to add a data source:

This particular query is exposed as an OData web service, so choose OData Data Feed in Excel. This will bring up the Data Connection Wizard that will help configure the data source for the Excel worksheet:

When choosing the location, specify the OData URL from the Web Services page in Dynamics NAV for this web service. It is essential that you specify the user name and password so that the data in Dynamics NAV can be connected to. To see the dataset in the web service as configured in Dynamics NAV, click NEXT:

By clicking NEXT you will be taken to the configuration of the data connection file:

You must select save the password in file field to allow the data to be refreshable from PowerBI.com. By doing this, the connection between the dashboard and reports will be made in the context of the user that created the spreadsheet every time the data gets refreshed. Whilst all users of the spreadsheet still need to be licenced users of Dynamics NAV, the exposed data will be available to everybody that is able to connect to the PowerBI.com dashboard. In turn, this means that Dynamics NAV security does not apply to what is in the spreadsheet at this point. Anyone who has access to the spreadsheet also has access to the data that is in the sheet.

It is now time to import the data into Excel.

Create a Power View Report and click OK to upload the data, and then you will be able to design a Power View report in Excel:

A basic report with three clustered column charts – one with Sales_LCY per Country/Region code, one with Sales_LCY per Salesperson_code and one with Sales_LCY per Customer_Posting_Group - is created.

Now save the spreadsheet with the name CustomerAnalysis to your local computer and you will be ready to upload the report to PowerBI.com:

Open PowerBI.com in your browser and log in. Click GET DATA to import the spreadsheet.

Select the Excel Workbook, and then choose Connect. Once you have located the saved workbook choose Connect to upload your workbook. When it has uploaded you will have access to your dataset and report:

If you are the owner of the data, you can now right-click the data source to gain the access to refresh the data in the report and dashboard.

For more information on how Microsoft Dynamics NAV and PowerBI.com can form the perfect match, including how to set up the Dashboard, contact Dynamic Business IT Solutions.