Microsoft Dynamics NAV, Microsoft Power BI

Connecting Dynamics NAV 2013/2015 to the NAV content pack in PowerBI.com


PowerBI.com has a Dynamics NAV content pack which connects to Dynamics NAV 2016 out of the box. I’ve discussed about it here.

Customers using NAV 2013 or NAV 2015 can also connect to this NAV Content pack in powerbi.com.

Power BI expects NAV to expose certain web services, irrespective of the NAV version. The required web services are shown in the following picture (NAV 2016 Web Services Page).

Capture1

Let’s consider NAV 2013 installed on premise where authentication is based on windows logins. we’ll connect NAV 2013 to Power BI content pack.

Step 1: In NAV 2013 version, we have to make sure all of the required objects are available.

  • The required Page objects (Page: 197 – Account Schedule KPI Web Service and 42 – Sales Order) are available in NAV 2013 out of the box.
  • We require 6 Query objects (numbered 100 to 105 in NAV 2016). You can create these queries manually or import the query objects that I’ve uploaded here. The newly created/imported queries are now with ID: 50000 to 50005.

Step 2: Expose these objects as OData web services in NAV 2013. Open the Web services page and publish these objects as web services. It is very important here to provide the service name exactly like in NAV 2016 (refer to the service name in the above picture. Even the case has to match exactly!).

Step 3: Make sure the OData web service is enabled in the NAV 2013 Admin console.

Step 4: Sign in to http://www.powerbi.com

Step 5: [This step is required when NAV is using windows authentication]

  • In Powerbi.com, click on download button which is available on right top. Download and install the Power BI Gateway on NAV server.
  • Capture2
  • Run/launch the Power BI gateway and sign in to powerbi.com when prompted. Next, Enter your Windows user name and password and click on Next. This should start the gateway.
  • Capture3
  • Click on finish to close the gateway window.

Step 6: In Powerbi.com, click on Get Data at left bottom –> Click on Get button under Services –> choose Microsoft Dynamics NAV from the list of services –> Click on Connect

Step 7: Enter the OData URL (http[s]://navserver:Odataport/instance/OData/(‘company’)), Select the authentication method (Select windows if NAV is using windows authentication) and click on Sign in.

Your Dataset, Reports and Dashboard is now ready!

Capture5

Thank you 🙂

Advertisements

29 thoughts on “Connecting Dynamics NAV 2013/2015 to the NAV content pack in PowerBI.com”

  1. we have been trying this on NAV 2015. it seems our services ODATA,& SOAP was not configured by default. any advise on this…and how can we make it work. is it that NAV 2016 is much better….after installing…with the services we need to connect to BI…all are installed by default.?? pls…clarify this for me

    Like

    1. Hi Bidey,

      The required web services are published by default in NAV 2016 cronus demo database only. If you want to integrate NAV 2013/2015 with Power BI, as described in this blog, you have to import the required objects and publish them as OData web services.

      Like

  2. Hi Suvidha,
    Apparently my OData web service is enabled. But I get this error when trying to get data from PowerBi.com: “OData: Request failed. The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure cannel”
    Any idea?
    Thanks a lot

    Like

    1. Hi Javier, Try to Disable the SSL for OData services in the Admin console and try.
      If it works with this, then I believe it is something to do with the security certificates.

      Like

      1. Hi Javier, In NAV web services page, click on all the Odata links and make sure they are all exposed without any issues. Also, if you’re trying to connect NAV 2013/2015 with Power BI, the service name in web services page should be exactly like in NAV 2016. The Spelling and the case of the service name should match. Check this carefully.

        Like

  3. Hi Suvidha , I have done everything , my odata service is running fine but i am getting error at time of connect in Power BI ,

    We couldn’t import data from Microsoft Dynamics NAV
    Make sure you’re entering the information correctly.
    Activity ID3cee638c-2b0d-4d5f-89af-1f3c9173394f
    Request IDd1d77494-7e56-ded7-3eb4-ecf68d1c2cce
    Status code400
    TimeWed Feb 10 2016 17:26:53 GMT+0530 (India Standard Time)
    Version13.0.800.429
    Cluster URIhttps://wabi-south-east-asia-redirect.analysis.windows.net

    Like

    1. Hi Abhishek, Make sure the service name specified in NAV 2013/2015 is exactly as given in NAV 2016. It is case sensitive as well.

      Like

  4. Hi Suvidha,
    i am trying to connect powerbi with nav 2013 r2 AT ….
    All Queries are working and deliver correct values, also odata is running correctly.
    i can Import all data via Excel odata Connection correctly in Excel.
    but if i try to add Service in powerbi i get the following error message:
    Fehler beim Verarbeiten der Daten im Dataset.

    PersonalGateway_DataSourceKindLabel

    Fehler bei der DatenquelleThe column ‘No’ of the table wasn’t found.

    Aktivitäts-ID5cad948c-fa29-9a09-f445-28950395dc78

    Anforderungs-IDdcb574e2-2e10-68eb-4605-0d541f97b3de

    UhrzeitTue Mar 8 15:56:31 UTC+0100 2016

    Version13.0.800.588

    Cluster-URIhttps://wabi-north-europe-redirect.analysis.windows.net

    There must be some changes done in the dataset ?
    Any hints to solve this Problem ?

    Regards,
    Robert

    Like

    1. Hi Robert,

      Not sure what the issue is here as, I don’t understand the language in the error message 😦
      If you’ve downloaded the objects from the link (http://1drv.ms/1SuOVEt), it should not have any issues.

      Check the following points:
      – Make sure the Service Names are exactly matching (Spelling and the case of the name) as in NAV 2016 demo DB.
      – If you’re using NAV on-premise with windows login, make sure the Power BI Gateway is running with the same windows login.

      Like

    2. Hi, I came across a similar error “The column ‘No’ of the table wasn’t found” and this was because there was no data in one of the web services. To connect to NAV Content pack, all the NAV web services should have data in them.

      Liked by 2 people

  5. Hi,

    Great job !

    I am getting the following error while data importing is taking place:

    Failed to import data
    Do you want to delete the dataset and report?
    Please try again later or contact support and provide the following details to help resolve your issue faster.
    Activity ID1df1e88d-54e7-4807-8bd6-09b67d17c5a3
    Request ID756b70e2-edb4-51c3-10c3-a726b152d890
    Correlation IDad5a6b80-69bf-9f9a-5070-acba8f87c59b
    Status code200
    TimeTue Apr 26 2016 18:02:41 GMT+0300 (Arab Standard Time)
    Version13.0.1100.422
    Cluster URIhttps://wabi-west-europe-redirect.analysis.windows.net

    Like

    1. Hi, Please make sure all your web services are published and accessible. Also, make sure the Service name matches perfectly as in the Cronus demo database (1st picture in this blog)

      Like

  6. Hello, thank you for your blog. i have all the items completed but when i try to login selecting basic i get and error my login doesn’t work. DOES the NAV instaces have to be NAVUSER. current we are setup with WINDOWS. must i change it to NAVUSER… ??

    Like

    1. HI Carolyn, Sorry for the delay in my response.
      If you’re using windows login in NAV, please select the login as Windows in Power BI as well.
      In this case, Its important to have the Power BI gateway setup.

      Like

  7. Hi Suvidha,
    Thank you for the information.
    I’m still getting the same Error of importing data faild and I have checked all the objects of the web service are matched with NAV2016 by the name and also I have done the publish from the web service setup for the web service name (powerbifinance).
    Any thing else I have to check it??

    Like

    1. Hi Ahmad, Please check the following too:
      1. If you’re using NAVUserPassword/Basic authentication, check if your OData web service URL is accessible over the internet. If not, you need to fix that first.
      2. Check if the user has Super rights in NAV
      3. Disable SSL in NAV service and try connecting

      Hope this helps

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s