Tuesday, April 4, 2017
As an executive or manager, you know how important it is to have an accurate and timely information available to you when you need to make decisions, especially those critical to your company’s future. Most successful companies implement Key Performance Indicators (KPIs) to track and evaluate, making key decisions based on those. The easiest way to communicate and visualize KPIs is to have these displayed graphical with the ability to dive in deeper to a particular value.
Why Microsoft Power BI?
Microsoft’s Power BI is an interactive data visualization tool that can be leveraged to organize and visualize a company’s KPIs. Business Intelligence (BI) tools are a dime a dozen in today’s marketplace, but Power BI is made for integration with Microsoft Dynamics NAV, is a part of the Office 365 suite and is readily available with online access.
Close integration with Dynamics NAV brings added benefits to Power BI, like the ability to embed into Dynamics NAV the same reports as in Power BI while still navigating within Dynamics NAV. Plus, there is the option to use the same username and password for Dynamics NAV, Power BI and other applications using the single Microsoft platform (also known as Single Sign On [SSO]). Beyond these added perks, Power BI offers many other advantages and integrations. Find out more on the Power BI website.
Dynamics NAV 2017 with Power BI
To reap all the benefits mentioned above, Dynamics NAV customers need to be current on Microsoft Dynamics 2017, which could be a motivating factor to upgrade.
Having said that, you may also integrate with Microsoft Dynamics NAV 2016, which means use live data directly from Dynamics NAV, but out‑of‑the‑box it is not possibility to embed Power BI reports into a Dynamics NAV page. Luckily, there are websites explaining how to run basic integration using predefined Dynamics NAV web services, including on the Microsoft Power BI site.
This blog post will show you how to easily set up the Dynamics NAV and Power BI integration without using Microsoft Dynamics NAV content pack for Power BI. To reap the full benefits of this integration, I recommend having Dynamics NAV 2017, but Dynamics NAV 2016 is also possible to integrate with Power BI, less embedding reports into Dynamics NAV.
Integrating Power BI with Dynamics NAV
1. Build a simple Item Ledger Page
First, I would recommend building a simple Dynamics NAV page to display some text values, date values and number values. For example, build a page for item ledger entries providing “Item No.”, “Posting Date” and “Quantity”. You can build more complex pages or queries, but this is a good first step to testing integration. After the item ledger entries page is prepared, you will publish it as a web service using a “Web Services” page in Dynamics NAV.
2. Integration from Power BI to Dynamic NAV
Now let’s move to Power BI. To build your own integration from Power BI to Dynamics NAV, I recommend using Power BI Desktop edition. You can download and install the Power BI Desktop edition from Microsoft’s Power BI site at: https://powerbi.microsoft.com.
3. Prepare Dynamics NAV for Power BI Usage
To prepare your Dynamics NAV instance for usage with Power BI it requires you to have a credential type “NavUserPassword”. I recommend that you run it as separate instance from those that are used for users to access to Dynamics NAV using Windows or Web clients. You can test this new instance by using your browser to try accessing OData. The address to your OData service is:
https://<server address>:<port>/<instance name>/OData/Company(‘<company name>’)/
Next, generate a password that will be used to access Dynamics NAV data from Power BI. To generate this password, you need to run your Microsoft Dynamics NAV Windows or Web client. Simply go to the Users page, then find your user and generate “Web Service Access Key”.
Figure 1: Generating a “Web Service Access Key”
This key will be your password to access data from Power BI. Username will be “User name” visible in the same user card General tab.
Having a dedicated instance and user with Web Service Access Key, you are prepared to connect to Dynamics NAV data from Power BI. Run your Power BI Desktop application, then go to: Home - Get Data - OData Feed.
Figure 2: Setting up OData Feed
Use Basic authentication to connect to your Dynamics NAV instance.
Figure 3: Select Basic Authentication in Microsoft Dynamics NAV
In my example, you can see that the Dynamics NAV OData address contain ODataV4. This is ONLY available in Dynamics NAV 2017. If you are using Dynamics NAV 2016, you’ll need to use OData to refer to OData version 3, which can also be used for Dynamics NAV 2017 (if needed). There is not a huge difference when reviewing the integration to Power BI, but the OData version 4 edition has additional benefits if used with Microsoft Excel or other applications.
4. Power BI Authentication
Next, you need to choose Basic Authentication and provide the user name (“User name”) and password established earlier in this blog post.
Figure 4: Connect Basic Authentication in Power BI
5. Set Up Dynamics NAV Data Tables in Power BI
After providing this information, you will be prompted with a list of web services published using Microsoft Dynamics NAV. Find your newly created page and select it by clicking on “Table”.
Figure 5: Set up Dynamics NAV data tables in Power BI
This will select one web service and will become one “table” within your Power BI data. Before closing the Query management page, you should also define types for other text columns. In our case, it would be to define type “Date” for the “Posting Date” column and type “Number” for the “Quantity” column. Press “Close & Apply” to return to report management page.
Figure 6: Define Dynamics NAV data types in Power BI table
6. Report Management in Power BI
Now you can start using all the functionality of Power BI by leveraging data from Microsoft Dynamics NAV. If you would like to access your reports from other devices or share with your colleagues you will need to publish your report to the Power BI online version.
To publish your report online:
- Create an account at: https://powerbi.microsoft.com. If you already have a Office 365 license, you may use the same account to access Power BI online reports.
- After activating your Power BI account, use the “Publish” function within Power BI Desktop edition to publish your report to the Power BI online version. If you choose the paid version of Power BI, then you can also publish those reports for other people within your organization.
7. Embed Power BI Report in Dynamics NAV
The final step is to embed your report into Dynamics NAV. As I mentioned before, out‑of‑the‑box this feature is available in Dynamics NAV 2017. For this I highly recommend referencing the detailed instructions on how to embed a report in the blog “How to Enable Microsoft Power BI in NAV 2017 here.
Please note that you may need to adjust the related web service address to be used for authentication prior to embedding Power BI into Dynamics NAV. In my example, I set up a separate instance for Power BI, so I needed to change authentication address for Azure AD. This can be done by changing field “Redirect URL” within table 6300 “Azure AD App Setup”. The adjusted address should look like:
https://<server name>:<port>/<instance>/WebClient/OAuthLanding.htm
Partnering Power BI with Microsoft Dynamics NAV
Leveraging this blog post, I hope you’ve been able to successfully configure Power BI to embed reports in your Dynamics NAV instance, through integrating Dynamics NAV data into Power BI. Establishing your first basic embedded report is your first step to capitalizing on the interactive data visualization advantages Power BI delivers with integration into Microsoft Dynamics NAV.
If you need assistance to integrate Power BI with Microsoft Dynamics NAV running on 1ClickFactory Self‑Provisioning platform, please contact us at service@1clickfactory.com.
Stay tuned to our blog to discover more advanced means of leveraging Power BI with Dynamics NAV!