PowerBI.com and Microsoft Dynamics NAV 2015


Power BI just got even more attractive – Microsoft Dynamics NAV 2015 is ready to take on all the benefits!

At Convergence 2015, Satya Nadella, CEO of Microsoft, announced that the PowerBI.com preview would be released worldwide. This is an exciting opportunity for our Microsoft Dynamics NAV users around the world to leverage their existing technology to get more insight from the data in their Dynamics NAV application.

Not only does PowerBI.com come  at a new attractive pricepoint; it also provides a range of very power full business insight and collaboration tools. You can learn more about PowerBI capabilities here.

In this blog post, we walk you through the steps needed to expose data from Microsoft Dynamics NAV to PowerBI.com to enable the building of a Dashboard like the one shown below. 

We will not go into details on how to set up the Dashboard – If you need information on that please go here for information on designing Dashboards in PowerBI.com.

How to upload refreshable Microsoft Dynamics NAV data to PowerBI.com today

During Convergence 2015, the Dynamics NAV team demonstrated the integration of data from Microsoft Dynamics NAV with visualizations in PowerBI.com. With this blogpost, we will walk through the steps needed to 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.

First a dataset from Microsoft Dynamics NAV is needed. Typically we recommend using a query object to do this. Without going into details on this subject, a query object provides a performant way of reading even large datasets in Dynamics NAV.

For this blog post, we created a simple query called CustomerAnalysis that looks like this:

The query is saved and compiled with the object ID 50000. Once this is done, the query 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:

Once this is done the query can be consumed outside Dynamics NAV using Excel.

In Excel, start by adding a data source:

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

For the location, specify the OData URL from the Web Services page in Dynamics NAV for this web service. You must specify the user name and password so that we can connect to the data in Dynamics NAV. When we click Next, we see the dataset in the web service as configured in Dynamics NAV:

Click Next to get to the configuration of the data connection file:

For the data to be refreshable from PowerBI.com, select the Save password in file field. This means that every time the data gets refreshed from PowerBI.com, the connection between the dashboard and reports will be made in the context of the user that created the spreadsheet. This doesn’t change the fact that all users of the spreadsheet must be licensed users of Dynamics NAV but rather that the data exposed in the spreadsheet will be available to everybody that is able to connect to the dashboard in PowerBI.com. In turn, this means that Dynamics NAV security does not apply to what is in the spreadsheet at this point. Everyone with access to the spreadsheet have access to the data that is in the sheet.

At this point we’re ready to import data into Excel. 

 

To be able to upload this data, choose to create a Power View Report and click OK. We’re now ready to design a Power View report in Excel:

We create 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.

Save the spreadsheet with the name CustomerAnalysis to your local computer. We’re now ready to upload the report to PowerBI.com:

Open PowerBI.com in your browser. Once logged in, you can import the spreadsheet by clicking Get Data.

Choose Excel Workbook, and then choose Connect. Locate the saved workbook and then choose Connect to upload your workbook. Once uploaded, you have access to your dataset and report:

When you right-click the data source, you have access to refresh the data in the report and dashboard, provided that you are the owner of the data. 

If you want to, you can created a dashboard based on the data and visualizations that you first created in the spreadsheet. For more information on how create new visualizations, see this link.

If you would like to see more information on Dynamics NAV and PowerBI.com, leave a message below, or you can go and sign up for the Power BI preview at https://www.powerbi.com/

 

Best regards,

Claus Busk Andersen from the Dynamics NAV team

Comments (7)

  1. waldo says:

    Great blog!  I'm excited (I actually already was … ;-))

    but .. can you include the description of the query (think it's forgotten..)

    thanks!

  2. navteam says:

    Sorry Waldo – the coffee-starved blog admin forgot to add the query. I have now done that (and had a stern talking with myself over a cup of coffee), and we hope that you and everyone else will find using PowerBI with Microsoft Dynamics NAV much easier now.

  3. Salva says:

    You mention that validation must use UserName + Password. Do you know if it will be possible to use Office 365 validation in the future?

  4. raokman says:

    this looks soooo antique, I'm waiting for the grey FoxPro boxes to appear in the write-up.

    Why exactly do we need still another add-on to do what should be an integral part of NAV to begin with?

    You have to go through ALL of those steps above, everytime? and oh yeah, you all have to be licensed users?

    I think we'll be passing on this 'opportunity'.

  5. Pallea says:

    This is soo awesome. Thank you!

    @Roakman I take it that you are not an CFO or "really" into finance stuff????

    I have been working with this Powerbi tool since last october and everyone whom looks at it are crazy with it.  Finance people just loves Excel and with PowerBI on top…you'll have difficulties dragging them away from the computer 😉

    Any mediumsize to large customer would like to install this any day!

  6. Javier says:

    Hi navteam. Thanks for the great post. I would like to ask you if the fact that the NAV server is on a Azure Virtual Machine can affect to the conection between OData Data Feed in Excel and the query published as a web service. This question is because I did not be able to manage the conection.

    Thanks  

  7. Michael says:

    They have just released Power Bi in Australia. We have been waiting and excited it is now here. Any Microsoft Dynamics NAV user want to know more we wrote a blog on it at on it at http://www.dialogdynamics.com.au/…/microsoft-dynamics-nav-and-power-bi-any-data-anyway-anywhere

    hope you find it of help!

Skip to main content