The current GI function loads direct to the worksheet. It can hold maximum 1,048,576 rows. This is usually sufficient for most purposes. occasionally there is a need for more. Recently I had a client with about 40,000,000 GLTran records. The Excel Data Model supports 2 billion rows. Unfortunately OData kept timing out, but Velixo GI was faster and more robust, but could only manage the 1 million rows.
In addition once you have the data in the Data Model then you can do all sorts of relationships and DAC formula.
0 Votes
1 Comments
Gabriel Michaudposted
5 months ago
Admin
Another advantage the =GI() function in Velixo has is the Smart Refresh support. By doing incremental/delta refreshes you can significantly cut down the refresh time. If you're not familiar with it, I suggest you watch this video: https://www.youtube.com/watch?v=TEZSIN4Qwag
Two additional caveats I would like to point out:
Smart Refresh shouldn't be used with inquiries that include groupings
At this volume I strongly advise looking at the Request Profiler in MYOB Advanced / Acumatica to ensure that you're not using fields that generate subselects (some selector-based fields used to be highly inefficient and would result in thousands of additional SQL queries behind the scenes -- this has been addressed in the current versions of MYOB Advanced / Acumatica)
Note that we plan to enable loading of generic inquiries into data tables, but that is not going to allow you to go beyond 1M rows. Unfortunately there's no way for us as far as I know to extend the Excel Data Model and access it programatically, and I'm afraid we can't do much.
Is there any possibility for you to aggregate the data and reduce the number of rows you need to work with at any given time? Or could you split the load into multiple sheets (ex: one sheet per year), then combine everything into a data model?
0 Votes
The current GI function loads direct to the worksheet. It can hold maximum 1,048,576 rows. This is usually sufficient for most purposes. occasionally there is a need for more. Recently I had a client with about 40,000,000 GLTran records. The Excel Data Model supports 2 billion rows. Unfortunately OData kept timing out, but Velixo GI was faster and more robust, but could only manage the 1 million rows.
In addition once you have the data in the Data Model then you can do all sorts of relationships and DAC formula.
0 Votes
1 Comments
Gabriel Michaud posted 5 months ago Admin
Another advantage the =GI() function in Velixo has is the Smart Refresh support. By doing incremental/delta refreshes you can significantly cut down the refresh time. If you're not familiar with it, I suggest you watch this video: https://www.youtube.com/watch?v=TEZSIN4Qwag
Two additional caveats I would like to point out:
Note that we plan to enable loading of generic inquiries into data tables, but that is not going to allow you to go beyond 1M rows. Unfortunately there's no way for us as far as I know to extend the Excel Data Model and access it programatically, and I'm afraid we can't do much.
Is there any possibility for you to aggregate the data and reduce the number of rows you need to work with at any given time? Or could you split the load into multiple sheets (ex: one sheet per year), then combine everything into a data model?
0 Votes
Login or Sign up to post a comment