Using Power BI Desktop With Dynamics GP OData feeds

**Click HERE for the "Creating a Sales Report in Power BI With Dynamics GP Data" blog**

The Power BI Desktop application is a powerful and robust tool that can provide a modern approach to creating reports using your Dynamics GP data.  You can even display your reports directly on the GP homepage, showing the latest data by simply refreshing the page as you go about your work day.

Surprisingly, many customers have hesitated to take the plunge into this excellent method of reporting.  The following guide should help you step into the world of report design with the Power BI Desktop application and Dynamics GP.

You can download the Power BI Desktop application at the following location:

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Once you have installed the application, you will then need to install the OData service for Dynamics GP. The OData service allows Power BI to consume your GP data through a published data source.

Refer to the following blog article from my colleague Chris Bulson, for detailed steps on deploying the OData service:

https://community.dynamics.com/gp/b/dynamicsgp/archive/2016/07/13/microsoft-dynamics-gp-2016-odata-service

With the OData service deployed, you can then perform the following steps to prepare to connect to OData with the Power BI Desktop application:

1) Associate the domain account that you intend to use to authenticate with in Power BI Desktop, to a GP user account that has access to the GP company.  This is done through the Directory Account tab on the User Setup window in GP.

2) Grant the GP user account (from step 1 above) the "PowerUser" role in GP User Security.  I found that if you only grant the "OD_PowerUser" role, the tables/views will not display in the application.  (NOTE:  This issue is resolved in GP 2018)

3) Grant the service account that runs the OData service the "DYNGRP" role on all GP databases, and also the 'rpt_all user' role on the DYNAMICS database.

4) In Reporting Tools Setup in GP, confirm that the URL that you entered matches the "Microsoft.Dynamics.GP.OData.Host.exe.config" at "C:\Program Files\Microsoft Dynamics\OData Service".  In that file, you should see a line like the following:

GP 2018: 

<add key="ODataServiceName" value="servername.domain.com"/>

GP 2016:

<add key="ODataServiceHostAndPort" value="https://servername.domain.com:443/GPOData"/>

So, when you're in the Reporting Tools Setup window and the OData tab you'll want to enter that URL.  With GP 2018, you can simply enter it as it is displayed and add "https://" at the beginning such as:

https://servername.domain.com/

In GP 2016, you should leave off "GPOData" at the end.  For example:

https://servername.domain.com:443/

This step is important, as having the incorrect URL here will then cause the published OData feed URLs to be incorrect as well.

At this point, we are ready to connect with the PowerBI Desktop application.  In the PowerBI app, connect to the URL from the "Publish OData" window in GP with the view/table name removed such as:

https://servername.domain.com:443/GPOData/TWO/

Upon connecting, you will be presented with a list of the published OData feeds:

After selecting the information that you want to report on, the GP data will load into the application.  You can then begin to build reports!

Troubleshooting items to note:

1) If you receive a "400 Bad Request error" when attempting to connect, try running the Grant.sql script against all GP databases (C:\Program Files (x86)\Microsoft Dynamics\GP2016\SQL\Util).  We have also seen some customer environments where we needed to make the account running the OData service a "sysadmin" in SQL. 

2) If you receive the error "OData: Request failed: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.", try running a Repair on the OData service installation via Control Panel.  If the error still occurs after a Repair, then there is likely a problem with the SSL certificate.  Try a different certificate to run the service. 

When you are ready to display your new Power BI reports on the Home Page in Dynamics GP, refer to the following blog article from my colleague Chris Bulson:

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

My colleague Karl Hendrickson also wrote the following article regarding displaying the reports on the Home Page in the Dynamics GP Web Client:

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

Troubleshooting items to note when displaying the reports on the Home Page:

1) You must have at least one object on the Dashboard in PowerBI in order for the connection on the "Customize Home Page" window to work.

2) You must pin individual objects from a Report to the Dashboard (pie charts, graphs, etc.) instead of the actual Report itself.

3) Turn off "Enhanced Security" in Internet Explorer, as it can cause connection issues.  If a Power BI icon or an Owl is displayed instead of the items, this indicates there is a connection problem.

Happy Reporting!