Question:
How to extract more than 10 million integration status 2 records from Veeva Align?
Answer:
The Vault Loader extract limit can be set to a maximum of 10 million records in any Align Vaults. In the event, that there are more than 10 million IS2 records, filters need to be applied to using a where clause. The largest data set of the IS2 table is related to Account Territories, whose count can exceed more than 10 million records.
In order to extract them, filtering is necessary for the Account Territory-related IS2 VQL, see the following article: How to Query Integration Status 2 Records Based on Assignment Types in Veeva Align?
The easiest way is to run separate queries by filtering on the country__aln records. Depending on the amount of data, more queries with less number of countries need to be run.
In the event the result set contains more than 10 million records, the Loader Extract job will stop with an error that the 10 million limit has been reached.
Be sure to include the CRM Record ID field in the result dataset.
The sample query is as follows where countries are selected consecutively in each query:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null and territory__alnr.country__aln contains ('the Align IDs of the country__aln') and account__alnr.country__aln contains ('the Align IDs of the country__aln')
Filter to extract all IS2 records which are relevant in case of duplicate CRM Record ID field values:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null and territory__alnr.country__aln contains ('the Align IDs of the country__aln') and account__alnr.country__aln contains ('the Align IDs of the country__aln') and crm_delete_datetime__aln = null
In the event further filtering is necessary and null CRM Record ID IS2 records are not relevant:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null and territory__alnr.country__aln contains ('the Align IDs of the country__aln') and account__alnr.country__aln contains ('the Align IDs of the country__aln') and crm_delete_datetime__aln = null and crm_record_id__aln != null
Afterward, the extracted CSV files need to be merged. Use the following article:
How to Merge Multiple CSV Files and Combine Them Into One Large CSV File?
- The header rows need to be removed before the merge process except in the first CSV file.
- A similar methodology can be applied to any object in Veeva Align, such as the Account Territory object.
- In the event, the Vault Loader extract limit is not set to 10 million in the Align instance, raise a Veeva Align Support Ticket to have it increased.
Related Documentation:
Align Help Documentation:
Knowledge Base:
- How to Query Integration Status 2 Records Based on Assignment Types in Veeva Align?
- What are the Record ID and Master Align ID of a Record in Veeva Align?
- How to Find Duplicate Field Values in a Veeva Align Dataset Containing More Records than Microsoft Excel is Capable of Opening?
- How to Open a Ticket with Veeva Product Support?
External Article: