Dynamics GP & Power BI – Configure OData Service in Dynamics GP

This blog post is part of a series of blogs designed to help a business understand the interaction between Dynamics GP and Power BI, how to setup Power BI with Dynamics GP and what some of the options are for this functionality.  This write up was done using Dynamics GP 2018.
To view the full list of the series, click here.
How to Configure the OData Service in Dynamics GP

Step 1:  Navigate to Reporting Tools Setup

Location: Microsoft Dynamics GP/Tools/Setup/System/OData/Configure OData Service
The only thing you need to do here is update the endpoint that was specified in the previous step.  You can verify what this should be by opening the Microsoft.Dynamics.GP.OData.Host.exe.config file that can be found under C:Program FilesMicrosoft DynamicsOData Service
The line you are looking for starts with <ad key=”OdataServiceName” – here is what mine looked like:  <add key=”ODataServiceName” value=”SDorward.artisinc.local”/>
So, the value I am adding is http://SDorward.artisinc.local/ – it’s very important to include the last slash… I tried this without and it bombed on me… so don’t forget 🙂  Then click Ok.
FYI – This is the same Reporting Tools Setup window found under Tools/Setup/System/Reporting Tools Setup  – you can get to this window using either navigation route.
GPPBI - 3 - 1b1

Step 2:  Select the Data Sources you wish to publish using OData

Microsoft Dynamics GP/Tools/Setup/System/OData/Data Sources
With Dynamics GP 2018, Microsoft has pre-configured MANY views for us to use ‘out of the box’.  In my example, I am using those views – you can also switch Series and/or switch object types and publish specific tables…. or create your own view to use!
Select the checkbox for each View you wish to publish and use through OData.  The views populated here are the views issued by Microsoft.  Do you want to use your own view?  Click here.
Once you have selected the appropriate views, Click OK.  I’m just publishing a few for this walk through.
GPPBI - 3 - 2a1

Step 3:  Publish OData

Microsoft Dynamics GP/Tools/Setup/System/OData/Publish OData
In this step, we will mark the OData URL’s that we wish to publish.  Check the Publish Checkbox and then click OK.
GPPBI - 3 - 3a

Step 4:  Assign user access

The users directory account will need to be connected to a GP User account in order for the user to connect to the OData source.  Without this connection, you will receive errors when connecting to your data source.
GPPBI - 3 - 4

Step 5:  Test connection.

I like to test the connection by taking the URL available in the Publish OData section (Step 4 above) and paste it into a browser.  You can see in my example below, that when I switch to view the ‘Raw Data’ (after visiting the URL from Step 4), I can actually see the data published (If things are not configured correctly, you will not see this).  Another way to test is using an OData source in Excel.
GPPBI - 3 - 5
That’s it!  You’ve configured your OData service in Dynamics GP.
To learn more about “Dynamics GP & Power BI” continue reading this series by clicking here.
To continue to the next blog in this series, Connecting GP OData to Power BI
Thanks for reading!

Shawn Dorward
Microsoft MVP, Business Solutions
LifeHacks365.com | LinkedIn | Twitter | GPUG | MSDYNGP

Original Post: https://lifehacks365.com/2018/02/20/dynamics-gp-power-bi-configure-odata-service-in-dynamics-gp/

Leave a Reply