Hello GP community!

I wanted to share updated information regarding Power BI and OData, covering basic information regarding requirements, setup and its limitations.

Setup

Power BI

Much like my previous blog post regarding Power BI, I will strictly cover only the steps for Power BI access and allowing users to display Power BI reports on the user home page.

First you will need to register a native client app and generate a Client ID on the Power BI App Registration Tool. Once signed into the site, you will be presented with a form with the following fields:

 
App Name Use something that will allow you to easily identify the app such as GP Power BI Native
App Type Select Native app.
Redirect URL Enter http://login.live.com/oauth20_desktop.srf
APIs to Access At the bare minimum select Read All Dashbooards. Typically there is no harm to select all access.
 

Once the fields are filled in, click the Register App button which will generate a new native client app Client ID. Take note of the ID, as you will need this for a later step of the setup.

Next, you will need to register a server-side web client app and generate a Client ID and Client Secret on the Power BI App Registration Tool. Keep in mind that this step can be skipped if Web Client is not deployed in your environment. You will be presented with a form with the following fields:

 
App Name Use something that will allow you to easily identify the app such as GP Power BI Server-Side
App Type Select Server-side Web app
Redirect URL Enter http:// <your web client host/fqdn> /GP/OAuthRedirect
Home Page URL Enter http:// <your web client host/fqdn> /GP
   APIs to Access  At the bare minimum select Read All Dashbooards. Typically there is no harm to select all access.
 

Once the fields are filled in, click the Register App button which will generate a new server-side web app Client ID and Client Secret. Take note of the ID, as you will need this for a later step of the setup.

Once you have the client IDs generated, open Dynamics GP and navigate to Microsoft Dynamics GP » Tools » Setup » System » Reporting Tools Setup and click Power BI. You will be presented with a form with the following fields:

 
Client ID Enter the native app client ID.
Using Web Client Check this whether or not Web Client is deployed on your environment. If Web Client is not deployed on your environment, you can keep the Application ID, Key blank.
Application ID Enter the server-side web app client ID.
Key Enter the server-side web app client secret.
Reply/Redirect URL Enter the same Redirect URL you entered when generating the server-side web app client ID and client secret. If Web Client is not deployed on your environment, enter 
http://login.live.com/oauth20_desktop.srf instead.
 

Additional information regarding setup can be found in the following articles:

https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/12/06/microsoft-dynamics-gp-2016-r2-power-bi-on-web-client-home-page

https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/05/18/microsoft-dynamics-gp-2016-power-bi-feature

OData

Installation of the OData service is fairly straight forward. You can find the installer in the Dynamics GP installation media setup menu labeled GP OData Service, or under AdProd\ODataService\.

You will need to know the Dynamics GP SQL server, have a credential that can access SQL, as well AD credentials that will be used at the OData service's service account.

The service also requires a SSL certificate for its endpoint. This can be a self-signed certificate, but do keep in mind that you will avoid a lot of headache if you are planning on using the Power BI Gateway to sync OData with a Power BI report. See Limitations below for more information regarding this.

Issues/Limitations

Power BI

Certain Power BI reports may not display correctly in Microsoft Dynamics GP. It is unable to handle a full report, full fidelity Excel tiles and models. You will also need to add the report objects to a Power BI dashboard to make it available in Dynamics GP.

 

  • I do not see any available Power BI reports selectable in Dynamics GP when customizing the user home page. I get an error stating that it's unable to connect.
    You must have at least one report object added to a Power BI dashboard.

  • I'm receiving a script error on the Dynamics GP homepage when loading a Power BI report or when I hover my cursor over a Power BI report
    There is currently a known issue with displaying Power BI objects on the Dynamics GP homepage where you may receive the following script error:

    Script Error
    An error has occurred in the script on this page.
    Line: xxxx
    Char: xxxx
    Error: Assignment to read-only properties is not allowed in strict mode.
    Code: 0
    URL:
    https://app.powerbi.com/13.x.xxxx.xxx/scripts/powerbiportal.dependences.externals.bundle.min.js

    If this is on a server OS, having IE Enhanced Security Configuration enabled can prevent the Power BI from loading correctly. You may also need reset Internet Explorer settings if you are encountering the issue after disabling enhanced security.

    In addition to this, you may also need to update an registry entry for Dynamics.exe under HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION from 10000 to 11000.

    This issue is slated to be fixed in the midyear update.

  • I'm unable to scroll around the PowerBI report.
    There are no scroll bars for report objects on a Power BI Dashboard. As a result, there will also not be a scroll bar on any report object displayed on the Dynamics GP user homepage. In order to scroll, you will need to click on the report to load it in separate web browser window, then click on the report object on the dashboard to load the full report. The scroll bar will then be available.

  • Is there a PowerBI content pack for Dynamics GP?
    The Microsoft Dynamics GP Content Pack for Power BI has been released.

    As noted in the above article, the Content Pack template file is designed to be used in the Power BI Desktop application. There was intention to also make the Content Pack available directly at PowerBI.com. However, the Power BI team noted “Content packs are limited to data sources in that can be accessed over the internet. On-premise data will simply not load onto a content pack”.  This is a limitation of the Power BI On-Premise Gateway.

OData

The Dynamics GP 2016 R2 OData service utilized the v3 OData protocol, which will be soon deprecated. There is also a know performance issue with large data sets with this version. Any issue reported for this version will likely not be fixed. Plan to use the Dynamics GP 2018 OData service instead, which supports the v3 and v4 OData protocols.

  • I'm receiving a 500 Internal Server Error when loading a large dataset from OData. Setting OData flags does not seem to help.
    OData has flags that can be added to its GET query in order to modify the query.

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

    But when loading a large amount of data, even when limiting the search result number via the OData flags it still results in a 500 Internal Server Error.
    This error occurs depending on the number of records and columns aggregated, for example when loading more than 60K of records from the SalesTransaction view.

    The workaround will involve creating a custom view limiting the number of columns, and publishing this custom view to OData.