PowerBI Content Package for Microsoft Dynamics NAV 2016
To help you get started with PowerBI and Microsoft Dynamics NAV 2016, we are providing a package that serves as a proof-of-concept for PowerBI content packages for Microsoft Dynamics NAV.
Content package – dashboard, reports, and dataset
With this package, users can start using and exploring their own data using PowerBI in just a few minutes. The dashboard gives users KPIs and charts on their sales and finance data, as you can see in the following screenshot:
By selecting an element on the dashboard, users can drill into more detailed information. The package contains six sales reports and one finance report.
Each report contains multiple charts for a given set of data.
Users can modify, copy to new, or create new content on the pages to get KPIs and charts from the data that fits their individual needs. Users can pin report elements to their existing dashboard, or they can create a new dashboard.
Data sources in Microsoft Dynamics NAV
The data for the package is pretty basic: sales and finance data. We’re looking into creating richer content in the future, as well as adding more capabilities from within the dataset when used in PowerBI.com
The data sources are OData feeds, such as OData web services from any Microsoft Dynamics NAV solution:
- Sales –Six basic sales queries
- Finance – exposing page 197, using the Account Schedule KPI Web Service Setup feature and the Account Schedules that source the data for some of the charts on the Small Business Role Center:
- To allow for an extended data set, we expanded the options in the Period field to include Current Fiscal Year + 3 Previous Years, so you can pull the general ledger data monthly, for 4 years.
All web services are exposed in the product, ready to go in the demo version of Dynamics NAV 2016:
Note: Page 42 is not used in the PowerBI setup
Setup
The setup consists of three easy steps:
- Sign in to PowerBI.com.
- Select the content package for Microsoft Dynamics NAV.
- Connect a Microsoft Dynamics NAV instance to the content package.
You can find the URL for the OData feeds of the Microsoft Dynamics NAV Server instance in the Web Services list. However, you must remove the specific web service name from the string (the last /wsname part), so that it ends with the company name. Otherwise it will not work as a link for all the needed OData feeds in PowerBI.
It takes less than five minutes to get going.
Working with the Dashboard, reports, and dataset
Note: Depending on the setup and the account type in PowerBI, not all features will be available to all users of a given content package.
Users have the KPIs and charts available and can move them around, resize, and rename them according to their own preferences. From the dashboard elements, users can drill into the originating report.
Reports consist of one or multiple KPIs or charts based on the dataset.
There are no restrictions on how to mix and match the data from the queries in the package, but in typical use, a report focuses on a specific area of the business, such as one report for finance, one for sales, one for purchases, one for warehouse, and so on, or broken down into even more detail.
The report elements can be modified, copied to a new element and then modified, or users can create entirely new elements from the data sources in the package. When changing an existing element or creating a new one, a design pane appears, and the user can choose the data elements for a KPI or chart, choose from a palette of visualization types, set filters, select formatting, and so on. The following screenshot illustrates the design pane (the captions are shown in Danish, but you will see captions according to your system settings):
There is no need to go into all design details here, as there’s plenty of info on this on the PowerBI.com site and the corresponding YouTube channel.
Dataset
The package is based on a dataset from Microsoft Dynamics NAV. The package content can be set to be updated regularly – currently daily or weekly – or the user can update on demand.
Current limitations in the content package
The PowerBI.com content package is currently only a proof-of-concept that we wanted to release for Microsoft Dynamics NAV 2016. This means that the package does not include every facet of PowerBI that we wanted to include.
Date management
PowerBI does not automatically group dates, so the typical ERP views on months or quarters is something that must come from the dataset. We didn’t add that part to the dataset, so the sales data is just a list of all the transaction dates.
When dealing with dates, you also want to look at datasets that might contain empty groups, such as a quarter with no expenses for the KPI you’re looking at. Since raw, aggregated transactional data is missing, this would result in a date series that jumps or skips the gaps.
Dimensions
Although dimensional analysis is an essential part of PowerBI, we didn’t add that for the queries in this proof-of-concept. Obviously, this is one thing we’ll want to add in one of the first updates to the content package.
Domain coverage
We focused on sales and finance in this first version because it was easy to get data. For sales, it’s very simple queries and for finance we could reuse existing features to get some structured data easily. In coming package updates, we want to look into broader coverage of the various domains, such as warehouse, inventory, service management, jobs, and so on.
