Generic Inquiry - OData is working differently in 20 R1

Hi @naveenb74 ! In this case I believe this is due to the expanded OData grab made by Acumatica to include key linking fields which may have been missed, or other key fields based on the DACs used. There isn’t a way to turn this feature off (at least currently), but there is a product idea which addresses this and seeks to increase the flexibility of the data pull:

Hi @BenjaminCrisman,

Thanks a lot for the response :)

This OData, with Power BI feature, is working fine in 19 R1, were these changes are made in the 20 R1 version? 


 

@naveenb74 I have experienced the same thing, and need to explain all our customers that this behaviour is caused by a change in Acumatica. I don’t like it at all either. I will definitely vote for the enhancement @BenjaminCrisman mentioned.

@naveenb74, @Gabriel Michaud I found this article from the 2020R1 help (looks like a 2019R2 addition):

https://help-2020r1.acumatica.com/(W(4))/Help?ScreenId=ShowWiki&pageid=339f2b58-c593-456a-8bad-a8eb7b7db8d8

Correct, this appeared in 2019R2. The problem is that this algorithm is too “smart”, and thinks that you need to have all the primary keys, including the surrogate keys, for every single table reference in the GI. It should be possible to flag which columns are actually key fields in your GI, instead of having Acumatica guess it for you.

A simple example I have in my app is an inquiry that joins the Account table with the AccountGroup table (we need to return the internal ID from the DB). As far as I’m concerned, AccountCD should be the only key field in my inquiry, however Acumatica auto-generates AccountGroupID_2 for me, and marks it as a key. Since we cannot rely on the metadata returned by Acumatica we had to build a UI to let users pick the actual key fields; we identify the fields Acumatica says are key fields, but in most cases where you join multiple tables together this will be wrong:

The only case AccountGroupID_2 should be appended to keys is if we had a 1:N relationship between Accounts and Account Groups, but here it is 1:1.

Finally, the metadata is *completely* wrong when using groupings. In this case the fields marked as key should be the ones defined in the groupings.

Hi @BenjaminCrisman @Gabriel Michaud,

Understood.
Thanks a lot for providing the details. The above comments have given us a better understanding on the OData feature.

@Gabriel Michaud - Do you run into Generic Inquiries that are now too large to import into power query via the OData feed now? If so, what is your workaround? Do you query the specific inquiry and set ?$Select parameters in the URL? I have found that these OData feeds do not support query folding. If that is correct, that means the only way to filter the size is either on the Generic Inquiry itself or via parameters set in the URL. Or do I have it incorrect?

The problem I’m running into is I have at least one table where the feed is almost too large to begin with. With 10 different joins in the report, the size is probably at least duplicated, if not more. Some of the “primary keys” it is now including are RefNoteID fields from CSAnswers tables, which are massive. I’m trying to run the query now, and it’s running at least 10x slower than it did in 2019R1 with the exact same dataset. 

@hazen35 You should be able to use $Select in the URL to facilitate the data being able to be pulled in. Alternatively, you can create a SQL view which utilizes the joins in the GI which will mean less joins in the GI and it should run much quicker.

If you haven’t already tried this we have a Technical Tuesday on the process (a little older, but should still work):

https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/

@hazen35 how large are the inquiries you are pulling? We’re not running into timeouts or any major performance issue; some of the GIs our customers pull have 150 000 rows and it works quite well. I would definitely recommend using $select to reduce the number of columns being pulled if you don’t need all of them.

Also - have you looked at the Acumatica Request profiler to see if the underlying SQL query that is executed is efficient? It may be worth comparing 2019 R1 with your new version, something may have changed in the query execution that makes it much slower now. I would take the SQL query, and run it manually with SQL Management studio with the “Display Actual Execution Plan” option turned on. If you’re not familiar with this, I recommend reading https://vladmihalcea.com/execution-plan-sql-server/

has anyone experience Odata not refreshing because there are multiple users accessing the same data at the same time?