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