Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Jennifer Wheeler]

I got Odata installed for GP2018 and when I go into Power BI and try and connect to the published source I get this error message.

Anybody know what the issue is?  I have been searching and can’t figure out how to get past this.

Also, the OData security roles aren’t showing up in GP.  Any idea of what to do to get them to show up?

——————————
Jennifer Wheeler
——————————

[Derek Albaugh]

Hello Jennifer,

As mentioned in the following blog, we frequently see 500/Internal Server Errors with OData when attempting to load a large dataset. For example, when loading more than 60K of records from the SalesTransaction view.

Verify the number of records you’re attempting to load via the OData feed and see if limiting the number of columns and/or setting up a custom view to OData doesn’t help resolve these errors.

Thanks,

——————————
Derek Albaugh
Sr. Support Engineer
Microsoft
Moorhead MN
——————————
——————————————-

[Derek Albaugh]

Sorry, the blog I referenced is here:
https://community.dynamics.com/gp/b/dynamicsgp/posts/getting-started-with-power-bi-and-odata-in-dynamics-gp

——————————
Derek Albaugh
Sr. Support Engineer
Microsoft
Moorhead MN
——————————
——————————————-

[Jennifer Wheeler]

Thanks for the reply .  We are just starting to use Power BI and GP and we had only published the customers view which only has 300 records but I did create a new view of just one customer record to see if it would work and now I am getting a different error.

I am doing some searching but not finding much on how to get past this error.

Thanks!

?

——————————
Jennifer Wheeler
——————————
——————————————-

[Derek Albaugh]

If you’re getting an ‘Unable to connect’ / ‘remote name could not be resolved’ message, it may be an issue with the certificate that OData is using, as you shouldn’t have any issue getting 300 records to show via OData whether you’re viewing it in Power BI Desktop or Excel.

I know with my GP 2018 R2 OData environment, I’m able to view the AccountTransactions view in Excel, and that view in the TWO sample database returns around 22,000 records if I remember.

>One thing you can try is to give the service account for OData access to the GP databases.

>Make sure the port number being used by OData isn’t being blocked by anything.

>If using a third-party certificate with OData, maybe test with a self-signed certificate created in IIS Manager to see if there’s any change, indicating the issue may be with the original certificate.

>Make sure the OData Feed URL being used is what is mentioned in the ‘Publish OData’ window in Dynamics GP where you published the tables/views.

>If you’re using a self-signed or CA certificate with the OData service, you may have to import it onto the machine(s) you’re working with OData on, similar to what we do with Web Client certificates.

A Fiddler trace may also show the communications between OData and the SQL Server/Dynamics GP databases, and show what is causing the 500/Internal Server Errors and/or this ‘Unable to connect’ message.

Here’s the steps I’ve used, that you can look through and make sure there isn’t anything you may have missed or could setup differently:

<ODATA>

1. Deploy the ‘GP OData Service’ from the Dynamics GP DVD media. You’ll need a certificate for the OData service.

       2. In Dynamics GP, under Administration > Setup > System > OData, there are three windows:
           
           a. Configure OData Service – Under the Odata tab, enter the Service URL which will be the https://hostname.domainname.com/ URL from the certificate being used.  **NOTE: The trailing slash (/) is required at the end of the URL.

           b. Data Sources – select the tables and/or views you want to be available in OData and to publish.

           c. Publish Odata – You should see the views/tables you selected in the ‘Data Sources’ window here. Mark the ‘Publish’ checkbox for each object and click OK.

      3. In my case, I created a new user named ‘ODATA1’ and tied my Windows account to it, as that is what OData uses. I then gave this new user access to this company database I’m setting up OData in, and then for User Security, I assigned this user all of the OData security roles and saved changes.

      4. At that point, I launched a blank spreadsheet in Excel, then clicked on ‘From Other Sources’ under the ‘Data’ tab, and then clicked ‘From OData Data Feed’, which opened the Data Connection Wizard.

           In the wizard, I entered the URL from the Publish OData window, i.e. https://HostName.DomainName.com/GPOData/TWO/, to include my company database name and trailing slash, then for the logon credentials, I entered the Windows account user name and password that I tied to my ‘ODATA1’ GP user I setup in step 3 above.

           Clicking Next, it then brings up the ‘Select Tables’ window, which showed the tables/views I chose and published via the ‘Data Sources’ and ‘Publish OData’ windows in Dynamics GP.

     5. Marking one of the listed views and clicking Next, it then brought me to a ‘Save Data Connection File and Finish’ window, where I clicked Finish, and then it brought up an ‘Import Data’ window, which I left default to ‘Table’ and ‘Existing Workbook’ and clicked OK.  The data from the view I chose then showed up in the Excel workbook/spreadsheet as I’d expect.

I would maybe verify you can get the OData published views and/or tables to show data within a local Excel workbook, indicating that we have OData setup correctly.

Then at that point, try viewing the same data in Power BI Desktop or Power BI to see if you see the same data or still run into issues.  If you do, then maybe it may be an issue with Power BI.

The Event Viewer logs may also have some related information that is useful to the OData communication issues and worth looking through.

Let me know what you are able to find and we’ll see if we can’t figure this out.

Thank you,

——————————
Derek Albaugh
Sr. Support Engineer
Microsoft
Moorhead MN
——————————
——————————————-

[Jennifer Wheeler]

Thanks for the all great information.  It seems that everytime I go and try something I come up with a different issue.  So, I went back in and wanted to verify that the GP account had a directory account assigned and now nothing is showing for the directory account (I know I assigned one yesterday). 

When I try and assign a directory account it says it can’t find it.  I also decided to just uninstall and re-install the OData service and I still can’t assign a directory account.  Any ideas of what else to look at to figure out why I can’t now assign the directory account?

Thanks!

——————————
Jennifer Wheeler
——————————
——————————————-