Power BI with Dynamics NAV – Demo Install

by | Updated August 15, 2016 | Business Intelligence, Dynamics NAV

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://[servername]:[ODataPort]/[serverinstance]/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://[servername]:[ODataPort]/[serverinstance]/OData/Company(‘[companyname]’)/ 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

6 Comments

  1. Jessie

    Power BI personal gateway does not available for download any more. The new On-premises Data gateway doesn’t work the way as you stated. And I wasn’t able to connect to Power BI online with Odata URL of NAV.

  2. Brandon Carmichael

    Hi Jessie:

    Power BI personal gateway does not available for download any more. The new On-premises Data gateway doesn’t work the way as you stated.
    You are correct that the Power BI Gateway – Personal is not available as a standalone download. However, it is still available but is an option and bundled with the on-premise gateway.

    If you follow these instructions, you’ll be able to download the Personal Gateway option. Once installed, the instructions for using the Personal Gateway are the sameas the original blog post. (I just verified this in a demo environment to be sure.)

    • Go to https://powerbi.microsoft.com/en-us/gateway/
    • Download Gateway
    • Run the installer
    o (Simple Demo) Choose “Personal Gateway (Power BI only)”
    o Next
    On-premises data gateway installer
    • The Power BI Gateway – Personal update will download. When prompted, click Next
    Note: At this point the instructions are the same as the original blog post…

    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:

    One more thing…

    I did notice that after logging into the Personal Gateway, that when presented with the “Connected: You’re good to go message” I was only able to select “Cancel” until I unchecked “Help improve Power BI Gateway – Personal by sending usage information to Microsoft” — that looks like a bug to me but one that can be gotten around.
    Power Bi Gateway - Personal
    And I wasn’t able to connect to Power BI online with Odata URL of NAV.
    I suspect with the personal gateway installation this will resolve itself. To successfully install and configure the gateway must be connected to the internet.

    If it doesn’t, I would suggest some additional troubleshooting:
    1. Verify you are signing into the Gateway with a valid Office 365 login (You should ultimately see the “Connected: You’re good to go” message shown in the screenshot above.)
    2. Verify your OData URL is published from NAV and accessible for the Gateway (See “Verify the OData URL is Accessible”)
    3. Two things to remember about your OData URL
    a. Your OData feed may be HTTP and not HTTPS
    b. The syntax is http://[navserver][Port]/%5Binstance%5D/OData/Company(‘%5BCompanyName%5D’) and [CompanyName] is case-sensive.

  3. Mark

    Hi,

    Great post but I am still having issues connecting. I have confirmed the odata service is running and installed the online data gateway (personal mode) on my laptop and connected to Office 365 with it. I added an inbound rule to allow http://localhost:7048 through the firewall on my laptop and added port forwarding on my router to send port 7048 requests to my laptop. From Power BI online, I try connecting to Microsoft Dynamics NAV using the same URL I tested successfully using Google Chrome and WindowsWithoutImpersonation for the authentication mode, but it just hangs and never makes a connection. I have tried completely disabling the firewall but have the same results. Any thoughts on what the issue may be?

    Thanks,
    Mark

  4. Brandon Carmichael

    Hello Mark,

    This one is a bit tough not knowing how far you were actually able to successfully setup.

    One point of clarity is that the Power BI Gateway bypasses the firewall altogether. You shouldn’t have to both setup the Power BI Gateway and disable the firewall. One or the other should suffice. For simplicity, I would ignore the firewall and ensure I can get to the “Success!” message/screenshot under the “Download and Install Power BI Gateway – Personal” section of the blog post.

    If you are able to provide screenshots or anything of what you are currently see at the end of each section I might be able to offer more insight.

    Thanks,
    Brandon

  5. Brandon Carmichael

    Hello Nanyo,

    I’d have to do some testing, but my gut tells me that the information provided on the Power BI forum is misleading. The On Prem version of NAV can connect to Power BI online. Some of the confusion is probably that there are default dashboards for Dynamics 365 Business Edition, but there is also a dashboard specifically for Dynamics NAV. Most issues I have seen getting on-prem NAV connected are related to the personal gateway / firewall for actually having the web service accessible.

    The suggestion of using Power BI Desktop (which in turn can be uploaded to Power BI online) is valid, and when building complex queries Power BI Desktop is a more-capable interface.

    Something I would have to test: I do wonder if Microsoft has updated the Dynamics NAV dashboard in a way that breaks support with NAV 2016. It might be that only NAV 2017 works. – I don’t have any knowledge of that specifically, but it is possible.

    Thanks,
    Brandon

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

02sep10:00 am10:30 amThe Modern Manufacturer - Cycle Count Management

02sep12:00 pm12:30 pmBest Practices for Work Order Planning and Routing for Field Service Companies

09sep10:00 am11:00 amWhat is Levridge? An Overview of the Ultimate Ag Solution

09sep12:00 pm12:30 pmOverview of Microsoft Promotion for NAV and GP Clients

10sep11:00 am12:00 pmConfab with Stoneridge - Livestream - Field Service Comparisons

16sep10:00 am10:30 amThe Modern Manufacturer - Product Lifecycle Management

16sep12:00 pm12:30 pmHow to Upgrade to Dynamics 365 Finance and Operations: Examining Custom Code and ISV Implications

16sep2:00 pm2:30 pmScheduling and Dispatching for the Modern Field Service Company

23sep12:00 pm1:00 pmDynamics 365 Business Central Wave 2 Release Preview - An Overview of the Most Exciting New Features Coming in October

24sep11:00 am12:00 pmConfab with Stoneridge - Livestream - The Modern Workplace: Teams, Sharepoint, Outlook, Office, and AI Integration

30sep10:00 am10:30 amThe Modern Manufacturer - Return Management

30sep12:00 pm12:30 pmEmpowering Your Field Service Technicians with a Mobile Solution

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Orders

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X