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

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?

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer

    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:

    1. 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.
    2. 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)
    3. 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.