Hello GP community!
I wanted to share updated information regarding Power BI and OData, covering basic information regarding requirements, setup and its limitations.
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:
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:
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:
Additional information regarding setup can be found in the following articles:
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.
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.
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.
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.