Skip to main content

Dear partners and customers,

Currently, we have two OData endpoints:

  1. ~/odata, which is GI-based, uses the OData v3 protocol, and has been with us since the 5.1 release; it is widely adopted in various ISV solutions
  2. ~/odatav4, which is DAC-based, uses the OData v4 protocol, and was released in 2020R2.

The OData v3 protocol slowly becomes obsolete in favor of OData v4. For example, some .NET client libraries stop supporting OData v3 at all, and .NET Core/.NET 5.0+ supports OData v3 only in the compatibility mode with some restrictions.

Because of that, we’d like to collect feedback on the OData v3 endpoint usage (GI-based, located at ~/odata route) and ask some question on what protocol-related features you actually use.

 

As you may know, the default payload format for OData v3 is Atom XML (Accept: application/atom+xml or $format=atom). OData v3 also supports JSON in two forms: a regular one (Accept: application/json or $format=json) and a verbose one (Accept: application/json;odata=verbose or $format=jsonverbose).

Typically, non-verbose JSON is a standard choice since it is much simpler than Atom, and reduces the average payload size.

So, the first question is: do you use Atom XML or JSON Verbose in your integrations instead of plain JSON? If you use one of these formats, why do you prefer it over plain JSON?

Secondly, do your integrations depend on some OData v3-specific protocol features? What do you think about migrating the GI-based endpoint to the OData v4 protocol?

 

Any opinions would be much appreciated.

No ISV solutions in place for us.  All in-house customizations as a customer.  We use OData in a very limited capacity to connect Acumatica GI’s to Excel files.  As long as we can keep using it to connect to current versions of Excel, keep the improvements coming.  We’ll roll with it!  If we reach the point that it’s better for the product long term to drop support for Excel, we’ll figure out how to roll with that, too.


  • We don’t use Atom/XML or JSON Verbose. So, if you roll out the v3 compatibility layer without those two, works for us :)
  • Flipping GI ODATA endpoint to ODATA v4 syntax completely is fine with us, too. We actually already “migrate” your result sets to v4 internally after receiving the data.
    • Please make sure the time-zones are respected in the input/output! We had a lot of struggle with that previously; special thanks to Vladimir Panchenko who ensured consistent UTC results sets in the current dataset.

Overall, as long as ODATA is still supported in GIs, we’re fine with either option (compatibility layer or switching v4, or even both if you can expose them in two separate endpoints)

To be clear, having the GI-based ODATA endpoint available is very important to us, we just don’t care too much about the protocol version :)


I mostly use JSON via OData by exposing GI’s and rarely a single DAC.

I have mostly used non-verbose solutions in the past. 

The biggest benefit I have found for the verbose format is the ability to use the uri/id formatting to know how to pull a single json record based on key fields

<Base url>/odata/CS3/BI-CASES_SERV(BusinessAccount='ALBKAH001',CaseID='CASE006243',ContractID='CN00000270',EntityType='Contract') allows me to fetch just that row from a gi in json as long as I have those fields and it would be contained in the gi result set. I find this very useful in key corner cases


Vladimir, all of the ISV solutions that I was dealing with that using OData were using v3 and plain json. Unfortunately it is not too common interface that used almost explicitly for BI type of solutions, as most integrations require to push data back to Acumatica at some point, so choice is for CB API.

If v3 is getting out of support, I guess we have to face it, but frankly I don’t think that we always would be able to build OData v4 request as complex as some GIs could be. If there will be a good way to work this around, that would be great.

 


Vladimir, all of the ISV solutions that I was dealing with that using OData were using v3 and plain json. Unfortunately it is not too common interface that used almost explicitly for BI type of solutions, as most integrations require to push data back to Acumatica at some point, so choice is for CB API.

If v3 is getting out of support, I guess we have to face it, but frankly I don’t think that we always would be able to build OData v4 request as complex as some GIs could be. If there will be a good way to work this around, that would be great.

 

Don’t get me wrong, we're not considering to remove OData v3 as an endpoint, we’re just discussing the possibility of upgrading it to the OData v4 protocol.

We need to separate the logical model (GI-based, DAC-based) and the protocol version (v3, v4); these are two completely unrelated concepts so we can bump up the GI-based endpoint from v3 to v4 without changing its base model.


One additional consideration that I’d like to point out in favour of exposing two endpoints, one purely for GI ODATA, and another one for the current generic DAC-based endpoint:

  • The necessity to provide a “godlike” user role (OData v4 User) for the DAC-based endpoint. Which the customers are scared of, for security reasons. With the GI-based endpoint, they like the fact that they can set up screen-based access rights to GI screens without invoking row-level security.
    • Off-topic: as we discussed with Andrew Boulanov on the Summit, this issue can potentially be tackled by introducing a special security mechanism just for that endpoint, where general access could be forbidden, but various user roles can be assigned access to different “query hashes” or “query prefixes” (filters and selects need to be allowed dynamic for obvious reasons)
  • Much more importantly, the weight of the metadata.
    • Sometimes just accessing the current “generic” OData v4 metadata file times out our requests on a cold site.
    • On the other hand, the current GI ODATA metadata is very lightweight and super-fast, and we depend on it to be fast for our UX.
    • Therefore, I argue in favour of two separate endpoints, “generic” and “GI”, both of which would support the v4 standard syntax. The latter of which could optionally have the v3 compatibility layer to allow a certain transition period.

We only use the JSON format for our integrations and I honestly never had a use case for XML. The ability to access a generic inquiry through OData is very important for us and we wouldn’t be able to live with a DAC-based solution:

  • Customers have created custom inquiries that they expect to be able to easily consume from our application
  • Although we mostly work with simple DACs, there’s a few core inquiries that contain complex join conditions, self-joins that would likely be impossible to represent in a pure OData V4 query

If Acumatica is to deprecate the OData V3 endpoint, we would still expect to have access to GIs so a V4-compatible endpoint would have to be exposed. I don’t think we rely on any V3-specific features, and going to V4 would actually allow us to remove some polyfills we built.

Thanks for taking time to consider our feedback.


I haven’t gotten into OData and generic inquiries much in Acumatica yet. My understanding is that this allows end users to create their own queries on system data, and then OData can be used to synchronize those queries with 3rd party systems.

This seems generally very useful and powerful. However, is there the potential for end users to drag system performance down by creating extremely expensive inquiries? Has anyone seen this happen out in the wild?

I think at some point Acumatica should consider creating a data warehouse attachment to the main database with an eye towards scalability concerns with GI’s, etc. This would separate requests for reporting information from the main day-to-day transactional performance of the database. There was another thread on here where I asked about the REST API, and why there are licensing limits on it. The takeaway was essentially that it’s more about preventing data collisions in the system and less about licensing. The data warehouse could be used to fix some of these types of scalability issues as well, with lazy synchronization of same data elements back to the primary database, depending on the use case.


I haven’t gotten into OData and generic inquiries much in Acumatica yet. My understanding is that this allows end users to create their own queries on system data, and then OData can be used to synchronize those queries with 3rd party systems.

Your understanding is correct for the GI-based OData endpoint: you create a generic inquiry or take an existing one and then consume it in 3rd party systems/tools like Microsoft Power BI, Tableau, various ISV solutions, or even in Excel.

With the DAC-based endpoint, 

This seems generally very useful and powerful. However, is there the potential for end users to drag system performance down by creating extremely expensive inquiries? Has anyone seen this happen out in the wild?

We have a special subsystem in place that monitors all the queries to avoid such issues as much as possible.

I think at some point Acumatica should consider creating a data warehouse attachment to the main database with an eye towards scalability concerns with GI’s, etc. This would separate requests for reporting information from the main day-to-day transactional performance of the database. There was another thread on here where I asked about the REST API, and why there are licensing limits on it. The takeaway was essentially that it’s more about preventing data collisions in the system and less about licensing. The data warehouse could be used to fix some of these types of scalability issues as well, with lazy synchronization of same data elements back to the primary database, depending on the use case.

Thanks for the suggestion. It is a good idea, especially for larger businesses, however, it brings some tradeoffs. For example, the current approach allows you to always have access to real-time data in all kinds of reporting and in external integrations; however, it would be rather hard to achieve with a data warehouse.

We have several ISV solutions in Acumatica Marketplace that provide data warehousing to address these scenarios.


@vpanchenko Certainly for some use cases, you would want to have “real-time” data. For example, if you needed to monitor the credit usage of a high volume customer, you wouldn’t want to miss that they just placed a $20M+ order, and then 5 minutes later try to place another one. So of course there are cases where real-time is required.

However, for what I anticipate is the vast majority of user directed reporting requests, it may be last weeks sales, or yesterdays sales, etc. In these cases, real time data is largely irrelevant. We can think of corner cases where that’s maybe not the case, but in the vast majority of situations the data warehouse is totally adequate for most reporting. As a previous builder of data warehouses, I know that this is often the case.

By exposing a data warehouse api, you could leave such decisions up to the developer as well. However, it would increase the scalability of your solution dramatically. You could probably hammer the data warehouse with thousands of requests per second with little impact. In the current environment, it seems like even relatively low volumes of REST or SOAP API requests have the ability to drag the performance of the system to zero.


@vpanchenko No V3-specific features needed in my world. As long as you keep the ability to expose Generic Inquiries to OData (now V4 OData), I’m happy.


  •  

To be clear, having the GI-based ODATA endpoint available is very important to us, we just don’t care too much about the protocol version :)

We are the same as @pkabir63 


I’m still getting used to the v4 approach to OData, but I’m finding it less flexible than the v3.  The ability to abstract things like complex joins and control of exposed fields into a Generic Inquiry has been a truly beautiful thing to me.  Our primary usecase for OData has been exporting Generic Inquiry data to Excel.  We even go so far as to train our customers on how to do this whenever they have people who are technical enough to build their own Generic Inquiries in house.  This is definitely seen as ‘nice to have’ feature by our customers.  I have to add my voice to the other replies asking that this be kept in some form or another if at all possible.

Also from an integration standpoint, using the OData v3 approach when exporting GI data has been a preferred choice compared to adding the GI to a WS Endpoint.  Using OData allows for things like custom sorting and paging which I understand is not supported in the WS API.  I also like the simplified data structure returned by OData v3 compared to the WS API and OData v4.

As for format, I always use standard JSON.  I’ve never used XML/atom or verbose JSON, and don’t feel I need it unless the Excel export does.

I realize that a depreciating OData v3 protocol is out of Acumatica’s hands, and I certainly sympathize.  Overall excited to see how the Acumatica dev team handles this.


@vpanchenko I don’t have access to the Ideas area anymore so I can’t check it, but, while you’re implementing OData 4 for Generic Inquiries, if you have the opportunity to slip in an enhancement, I have one that I’ve encountered several times and I was just talking with @awilcheck about it today.

The idea is to be able to set values for Generic Inquiry Parameters in the $filter part of an OData URL. Regular field names are referenced normally (eg. RefNbr), but maybe Parameter names could be referenced as something like #MyParameter in the OData URL after $filter, just like filtering on regular fields.

The reason for this is that it’s cumbersome to create complex filtering logic in OData filters. It’s a lot simpler to add complex filtering logic to the CONDITIONS tab of a Generic Inquiry and use Parameters on the CONDITIONS tab as a way to interact with the filter logic which could be as simple as a drop-down with a few options that turns logic on/off. We just don’t have a way to set Parameter default values currently through the OData URL.

This is relevant since OData can be slow and server-side filtering helps to improve performance of the OData queries.


@vpanchenko I don’t have access to the Ideas area anymore so I can’t check it, but, while you’re implementing OData 4 for Generic Inquiries, if you have the opportunity to slip in an enhancement, I have one that I’ve encountered several times and I was just talking with @awilcheck about it today.

The idea is to be able to set values for Generic Inquiry Parameters in the $filter part of an OData URL. Regular field names are referenced normally (eg. RefNbr), but maybe Parameter names could be referenced as something like #MyParameter in the OData URL after $filter, just like filtering on regular fields.

The reason for this is that it’s cumbersome to create complex filtering logic in OData filters. It’s a lot simpler to add complex filtering logic to the CONDITIONS tab of a Generic Inquiry and use Parameters on the CONDITIONS tab as a way to interact with the filter logic which could be as simple as a drop-down with a few options that turns logic on/off. We just don’t have a way to set Parameter default values currently through the OData URL.

This is relevant since OData can be slow and server-side filtering helps to improve performance of the OData queries.

I’ve created a separate Idea item to be able to track the number of votes: 

 


Reply