Power BI for Office 365 is a service that enables organisations to share important analytical data with the help of Excel based reports.
This along with Excel add-ins like Power Query and Power Map provide a very powerful tool for creating useful reports from all sorts of data.
In this blog post we will explore how you can use Microsoft Dynamics NAV OData services to create reports in Excel and publish them using Power BI for Office 365.
We will start with installing Power Map and Power Query as we will be using them for the example. Note that these add-ins are not a requirement for using Power BI or NAV integration, but they provide a lot more features then basic excel data integration capabilities.
Once installed, you will have to enable these add-ins. On the excel options (File -> Options) window. Select Add-Ins from the menu on the left. Select COM Add-Ins from the “Manage” drop down and press “Go”. Enable the following and press OK.
1.Microsoft Office PowerPivot for Excel 2013
2.Microsoft Power Map
3.Microsoft Power Query for Excel
4.Power View
We will create a very simple Sales Dashboard that shows the information based on the posted sales invoices in NAV. The first step is to expose the NAV data to be consumed by excel to show on the dash board. Create the following simple query object in NAV:
We then expose this query as a web service. In addition to this we also expose the Countries/Regions and Salespeople/Purchasers pages as we want to use Country and Salespeople’s names instead of codes. We will use this to demonstrate how to setup relationships between entities.
That’s it on the Dynamics NAV side. Lets see how we consume this data in Excel now.
Open an excel workbook. Select the Power Query tab. On the Get External Data section on the ribbon menu, click on From Other Sources and then click From OData Feed.
Enter the OData URL of the SalesInvoices web service and press OK.
Provide the Dynamics NAV credentials to connect to. This will give you the following screen with the data fetched from the server.
To be able to use date and decimal fields we need to set the data types correctly, so select the Order Date column and set the data type to Date.
Similarly, select the Amount column and set the data type to decimal.
Set the name of the query to SalesInvoices. From the Close and Load menu select the Select and Load To option. On the Load To window select the Only Create Connection and check the Add this data to the Data Model and click Load.
Repeat the same process for the Customers and Countries web services.
Now go to the POWERPIVOT tab and click on Manage. On the new window that opens, select the Diagram view. Right click on the Code field on the Sales Person table and click Create Relationship. Set the relationship to the Salesperson_Code field of the SalesInvoices table.
Repeat the process for Countries table and associate it to the Ship_to_Country_Region_Code on the Salesinvoices table. This sets up our data model. Now let’s create some reports.
Go to the Insert tab and select Power View. This will add a new Power View sheet. Add the title Sales Overview.
From the Power View Fields select Amount and Order Date fields. And on the Design tab select Other Chart-> Line.
This gives us a line graph showing Sales over Time.
Create a Stack Bar graph using the Salespeople Names and SalesInvoices Amount. Add a Pie graph by selecting Country Names and SalesInvoices Amount. Now we have great looking Sales Overview Dashboard.
Let’s insert another Power View sheet. Select Country Name and Sales Invoice’s Amount field and select MAP from the Design Menu. This will give us Global Sales shown over the world Map.
Save this excel workbook on your desktop.
Login to the Office 365 portal and click on Sites.
Click on Team Site and Select Site Content -> Power BI.
Drop your excel sheet under Documents.
Click on the sales Dashboard. This will load the our Dashboard in the Power BI portal.
Now you are ready to share this with your team members!
