Question
Saltech
GB
Last activity: 31 Aug 2018 10:00 EDT
Export a large number of records (100,000+) to excel from OOTB report browser
We have a requirement to export a large number of records to excel (100,000+).
We are using PEGA 7.2.0 and office 2010 with IE browser
Currently to get the data we are using the OOTB report browser and a report definition. The initial limit is set to 10,000 rows and if the export to excel option is selected from the options it works in an acceptable amount of time, however this limitation isn't acceptable to the business. If the limit is increased in the report definition then very quickly the performance becomes unacceptable. Once the limit is set to 50,000 we are looking at a time of about 15 minutes. There appears to be several factors to this time.
We have looked at the SQL query time and this is between 4 to 7 seconds.
1. The amount of time taken before the option to open, or save the file is about 7 minutes.
2. Once saved then the amount of time taken to open the file by excel is about 7 minutes.
The file size produced is very large (roughly 75 MB). If this is opened in excel and resaved as an excel file then it is about 4.5MB.
Longer term Pega will not be used as the Strategic supporting solution but as of now it is the only option we have, we do not have the option yet of using something like BIX.
So I have the following questions:
We have a requirement to export a large number of records to excel (100,000+).
We are using PEGA 7.2.0 and office 2010 with IE browser
Currently to get the data we are using the OOTB report browser and a report definition. The initial limit is set to 10,000 rows and if the export to excel option is selected from the options it works in an acceptable amount of time, however this limitation isn't acceptable to the business. If the limit is increased in the report definition then very quickly the performance becomes unacceptable. Once the limit is set to 50,000 we are looking at a time of about 15 minutes. There appears to be several factors to this time.
We have looked at the SQL query time and this is between 4 to 7 seconds.
1. The amount of time taken before the option to open, or save the file is about 7 minutes.
2. Once saved then the amount of time taken to open the file by excel is about 7 minutes.
The file size produced is very large (roughly 75 MB). If this is opened in excel and resaved as an excel file then it is about 4.5MB.
Longer term Pega will not be used as the Strategic supporting solution but as of now it is the only option we have, we do not have the option yet of using something like BIX.
So I have the following questions:
1. Is it possible to generate the file in the excel format initially (so it is 4.5MB)? We do not have any requirement to use chrome or Safari. As I understand it the file generated is effectively HTML and it is done this way for X browser compatibility. Since we do not have this limitation can we exploit this somehow?
2. Is there another way of increasing the performance?
Note same issue is discussed here:https://pdn.pega.com/community/product-support/question/export-excel-report-definition-need-more-10000-records
The solution here where the item is queued for an agent and then emailed won't work here as the file sizes will get large and be blocked. Its also not an ideal user experience.
Saltech Consulting
HU
Under normal circumstances Pega BIX is the solution to do large exports.
In case that's not available for you, you can look at the database's export utilities. Oracle, Postgres and most RDBMS have such tools.
Saltech
GB
Thanks for the suggestions. Unfortunately the approach of getting the data from the database directly has already been suggested and rejected.
The requirement is that any of the users can run the report at any time to get the latest information. It is also required that this can be triggered ad-hoc from the UI.
BIX is not available and I dont believe it is intended to ever purchase it.
TATA Consultancy Services
IN
You can change your logic like incremental export, that will reduce the row count and improve your performance.
Cognizant Technology Solutions
US
We achieved by using Java methods to invoke a shell script and drop to a location and expose the location.
Java snippet wil use RunTIme and Process Proc class methods to excute the shell script.
Shell script we can design to pass params and extract the report just like Pega does on Database.
Pegasystems Inc.
IN
Thank you! I updated the same on the post that we branched for you: Anyone successfully implemented solutions for 100k or more results for export to excel?