We require to fetch all the form records from TM with tracking codes for a month to generate a report. It may be around 50k or more. What is best and optimize way to achieve that?
What kind of data to you need in your reports? Transaction metadata or PII data ?
For transaction metadata reports on finished transactions, I would recommend you use the REST Transaction History API to pull the data into your system (Transaction History API), or use the Transaction History Publishing services to push this data into a data warehouse data injection pipeline.
For reports which require a mixture of transaction meta data and PII data I would recommend you create a report using the submission data extracts facility. Note however if you are doing monthly reports you this may not line up with your systems PII data retention policies, with the PII data being deleted before the monthly report. To manage this either increase the reporting frequency, or review the the PII data retention policy.
I would require a report with PII data and tried to use the submission extracts but the issue is that TM can only fetch around 3500 records once and their are around 50k records from which we need to extract the information. In that case, we have to loop through all the records, which takes lot of processing and time to execute.
If you need to create a reporting including PII data you will need to do this in a DWH and TM is not designed to support this type of reporting.
All the PII data in TM is strongly encrypted with unique transaction encryption keys, this makes TM unsuitable for BI reports with PII data. TM will also purge this data very aggressively depending upon the configuration (e.g. 7 days).
If you need these types of reports I would recommend delivering this data into a DWH which can hold PII and transaction metadata for longer periods for time for BI reports.
You can use BIRT. Refer to the guide at Developing Reports
I went through the BIRT but I can only find some fixed fields that can be added to reports, In case if I require some form level properties to add to the report that doesn't seems possible.