Microsoft Dynamics GP 2016 – OData service

Hey everyone! With the release of GP 2016 there is a new OData service that you can deploy to democratize your GP data. It will allow you to manage access to your GP data for GP users so that they can build rich interactive reports in Power BI and Excel. You can find the documentation on this new feature here:

Microsoft Dynamics GP 2016 Detailed Feature Documentation

There is also a video of setting up OData here - https://youtu.be/bHFmRRb9JZ4

You’ll want to review the documentation and be familiar with the setup\deployment options before continuing.

I wanted to address some of the road blocks you may run into when attempting to deploy this. We’re hoping to see several improvements\updates to the OData service in the R2 release of GP 2016.

First off, make sure you’ve installed the update for the OData service found HERE.

When you install the OData service the user account you specify to run the service will not automatically be granted permissions to the GP SQL data. You will want to go in and grant that user DYNGRP on the GP databases. I also recommend giving the user the ‘rpt_all user’ role against the DYNAMICS database specifically. If you run into a “400 Bad Request error” while attempting to connect to the OData service, you should try running Grant.sql against all of the databases.  (C:\Program Files (x86)\Microsoft Dynamics\GP2016\SQL\Util)

When you add Data Sources to OData it is adding these objects to the DYNAMICS..SY40900 table. You can always query this table to see which Data Sources you’ve made available for publishing. There is an issue where if you switch companies and add more Data Sources it can wipe out the Data Sources you’ve previously added for another company. I recommend that you add the Data Sources for multiple companies all at one time while logged into a single company. The ‘Data Sources’ window allows you to do this through the ‘Database:’ drop-down.

If you get an error like the following: An item with the same key has already been added.
This issue can be caused by the GP user being granted access to a Data Source in more than 1 Security Role.  You can run a script like the following to see what it is looking for and if it is returning the same Data Source multiple times.

SELECT SY40900.SECRESTYPE,
SY40900.RESNAME,
SY40900.DSPLNAME
FROM dbo.SY40900
INNER JOIN SY10000
ON SY10000.DICTID = SY40900.DICTID
AND SY10000.SECRESTYPE = SY40900.SECRESTYPE
AND SY10000.SECURITYID = SY40900.SECURITYID
AND INTERID = 'TWO'
AND SY10000.USERID = 'TOM'
AND CMPANYID = -1
AND Published = 1 

The INTERID, USERID and CMPANYID will need to be updated for your environment.

You will want to edit or create new Security Roles so that overlapping access is not being granted.  We've submitted this to the Development team to be addressed.

One last item to note, although System tables are available for selection (ACTIVITY for example) they will not show up in the list of available objects in Power BI or Excel. You will only be able to see objects in the company databases.

As I mentioned above, we’re hoping to see some improvements to the OData service released in conjunction with the R2 release this fall\winter. We are aware that there is a performance issue that can cause timeouts if you attempt to return too much data through the service.