Export to excel for large data set which is to be retrieved from 2 data entities

Question

I have a need to use export to excel functionality feature for a report where data is supposed to retrieved from multiple(2) cdt. 
 Also, the dataset is extremely large. 
 What approach should i go for?

Amit Mishra · Answer

Hi Mohit, 
As you mentioned dataset is extremely large. So, just creating a view will not solve your purpose here. I would suggest to use 'Store Procedure ' here to avoid time-out or performance issue. 
Or, try to split data-set with some filter and put into different sheet in the excel file, if your business requirement allow to do so. Let us know you thought on this.Thanks.

Mike Schmitt · Answer

Export DSE to Excel claims to allow either an array of a!queryFilter() items, or a single a!queryLogicalExpression() (which can contain multiple filters and subsequent logicalExpression() calls nested, perhaps, indefinitely). If neither of these methods are working for you, you might need to open a support case as it would indicate an issue with the OOB functionality. 
 
Would you be able to post the code you're passing into the node for the Filters parameter here?

aloks0189 · Answer

Hi @mohitshah 
 
 As per my understanding, Materialized view would be the best choice here. 
 As you said, you are going to deal with huge amount of data hence i wouldn't recommend you to go for View(as this prepares the data on demand by applying various joins and other set of logic) instead try to create a Materialized view to perform the query. 
 You can follow below mentioned steps to achieve your requirement to avoid any performance issues: 
 
 Create a Materialized View and once it's created then refresh the MV for the first time, so that it has all the required data. 
 Create a Scheduler Process which should take the data export(using Export Data Store Entity to Excel Smart Service) on everyday midnight/non-business hours and store the export into the KC(Having it's name appended with Date and Time, denotes when was this export taken) 
 Prepare an Interface, and define two links

Download Extract : User can click on this to download the latest extract(which was performed last night and currently available in KC) 
 Export Data ( On-Demand ) : User can click on this to trigger the above process in order to start the fresh export On-Demand(By Showing a Warn Message stating, this might impact the performance) and override the existing file in KC and also send the exported file via an email to the requester.

Hope this will help you.