Power BI with Dynamics NAV – Demo Install

By Warren Young | March 24, 2016

Power bi with dynamics nav – demo install

This post will cover how to integrate your Dynamics NAV installation (NAVDEMO instance will be used for examples) with Power BI, using the Power BI Gateway – Personal.

Power BI with Dynamics NAV Background

You can find some information on the background of Power BI with Dynamics NAV at this link: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-microsoft-dynamics-nav/

This post is useful, but glosses over the process of making your OData URL accessible to Power BI. There are actually a few methods to do that:
1. On Premise:  Power BI Gateway – Personal
2. On Premise:  Configure an inbound-rule to your Firewall to expose the OData URL
3. Azure/Hosted:  Configure an endpoint to expose the OData URL

I want to give proper credit to this blog post by Steven Renders: http://thinkaboutit.be/2015/12/connecting-the-power-bi-dynamics-nav-content-package-to-a-local-nav-2016-instance/   I found Steven’s post helpful in getting our tests up and running though we did find some differences or changes (possibly in the Power BI Gateway itself) that are worth clarification.

With all that in mind, our post will focus on the minimum requirements to connect Dynamics NAV to Power BI. We’ll specifically focus on using the Power BI Gateway – Personal method, and cover the quickest and lightest footprint to try out Power BI with Dynamics NAV.

Prerequisites for Power BI in NAV Demo Install

1. Office 365 Account
2. Dynamics NAV “NAVDEMO” installation or equivalent. These steps will generally apply to any NAV installation, but the details and examples are used against a NAVDEMO database. We are considering a separate post to cover the intricacies of a “Production” database.

Contents
– Setup OData for the Dynamics NAV Service Tier
– Verify OData for the Dynamics NAV Service Tier
– Download and Install Power BI Gateway – Personal
– Power BI Get Data – Connect to Microsoft Dynamics NAV
– Addendum: CRONUS USA, Inc. Data Setup

Setup OData for the Dynamics NAV Service Tier
1. Open Dynamics NAV Administration
2. On the left-hand pane, click on the Server Instance (DynamicsNAV90)
3. Expand the OData Services tab:
a. Click the “Edit” button
b. “Enable Odata Services” = Yes
c. “Save”
4. Restart the Server Instance (DynamicsNAV90)

Dynamics NAV Server

Change Dynamics NAV Server Instance

 

Verify the OData URL is Accessible
1. Open a web browser and enter the following URL:
a. Syntax:  http://://OData/
b. Example:  http://localhost:7048/DynamicsNAV90/OData/
2. This is the “generic” OData URL that is not company-specific.
Encoding in Dynamics NAV

3. To verify the specific OData Company URL:
a. Syntax: http://://OData/Company(‘’)/ Note that companyname is case-sensitive.
b. Example: http://localhost:7048/DynamicsNAV90/OData/Company(‘CRONUS USA, Inc.’)/

 

Note: Most versions of IE will give a message “Cannot display this feed.”

Internet Explorer Error in Dynamics NAV

 

Using another browser such as Chrome will work to display/verify the feed:

Local Host in Dynamics NAV

Download and Install Power BI Gateway – Personal

1. Go to www.powerbi.com, sign-in with your Office 365 credentials.
2. Prior to grabbing the NAV package from Power BI, Go to Downloads > Power BI Gateways (https://powerbi.microsoft.com/en-us/gateway/)
3. Download and install the Power BI Gateway – Personal.
4. Walk through the installation Wizard:

Power BI Gateway

1. Accept the license terms and click Next
2. Use the default installation location (can be changed if needed) and click Next
3. You’ll be informed when installation is complete:

Power BI Gateway

 

1. Click Launch to run the Power BI Gateway – Personal
2. Sign-in to Power BI using your Office 365 credentials

Power BI Gateway Personal

Microsoft Azure

 

After this sign-in, Power BI should ask to set up data sources.

Power BI Gateway Personal

 

Power BI Get Data – Connect to Microsoft Dynamics NAV

With your Dynamics NAV OData Service accessible and the Power BI Gateway – Personal installed and running, you are ready to connect Power BI to Microsoft Dynamics NAV:
1. Go to www.powerbi.com (sign-in with Office 365 credentials)
2. Get Data > Services

Power BI

 

3. Search for Dynamics NAV, click on Microsoft Dynamics NAV and click Connect.

Microstof Dynamics NAV in Power BI

 

4. In the OData Feed URL, enter the URL you tested in the “Verify the OData URL is Accessible” step.

Connect to Dynamics NAV

 

5. After clicking next this will ask for an Authentication Method.  Choose “Windows” and click Next.

Connect to Dynamics NAV

Importing Data in Dynamics NAV

Success with Importing Data

 

Once imported, your Dynamics NAV dashboard will be accessible on Power BI.

Power BI with Dynamics NAV

Enjoy!

Addendum: CRONUS USA, Inc. Data Setup
Note:  We didn’t encounter this issue every time we connected a database to Power BI, but we have run into it multiple times.

Issue – Error When “Importing” to Power BI

Error with importing data from Dynamics NAV

Solution – Add Accounting Periods for 2014, 2013, and 2012

1. Open Dynamics NAV (RTC)
2. Open the Company you are connecting to Power BI (CRONUS USA, Inc.)
3. Use the “Search” bar to go to Accounting Periods
4. From Accounting Periods, click Create Year.
5. In the “Starting Date” field, enter 01/01/14 (leave No. of Periods and Period Length with their default values)
6. Click OK, then Yes when prompted to generate the year.
7. Repeat these steps for 2013 and 2012.

For the sake of consistent data, create for 2014 first, then 2013, then 2012.

 

Create Fiscal Year

Microsoft Dynamics NAV

 

After adding these Accounting Periods, the Import should run successfully.

Related Posts

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X