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

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.

As others have said, combining the two queries into a view could be the simplest way to retrieve all the data from the 2 entities (may be able to suggest more if knew what the structure/data to be retrieved from the entities were).

As far as using the Export to Excel functionality, you may be able to get over the limitation of query entities by pulling the dataset in batches (e.g. pull 500 records at a time), assign the records to a process variable and increase the increment each time until the dataset is complete. This approach may run into issues depending how big or efficient the view you'd pull from would be, but you would have access to all the data from both entities if you can accomplish that.

How big of a dataset are we talking about?
What is the exported data used for? There may be better formats/solutions than Excel.

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?

Hi Chandhinir,

I tried this approach as this is Appian recommended for large datasets. The problem i'm facing is that i am unable to use more than one filter for the filter input node in this Export Datastoreentity to excel / Export CDT to excel smartservice.

Let me know if you can help.
Thanks

FYI,
Query entity cannot fetch more than 2000 records. So, this won't help. Already tried this approach.

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.

Create a view having proper mapping of both tables. Please have a look at JOINS in SQL and make proper view. You can map CDT with already existing View (we can do this with table as well).

Once you map CDT with view you are ready to fetch data. Before exporting, if needed you can write query entity to get data filtered.

Use process model and call smart service EXPORT CDT TO EXCEL.

Hi Mohit,

As other suggested, You can use view for that. If you have huge data then you can have show the confirmation UI to the user saying that "Data loading into excel is in progress. You will get an email or task assigned once it complete. So that you can trigger the Export to Excel process asynchronously. Once data is ready then you can either email to user or assign fresh task to user with the document.

Create a view by combining the two tables and can use Export Datastoreentity to excel / Export CDT to excel smartservice to export the data