Data Loading in CRM
Loading data into the data model is similar to loading data into any other application. Parent objects must be loaded prior to child objects and lookup objects must be loaded prior to the objects that reference them. When loading data into the data model, the key object relationships and prerequisites are as follows:
Account is the parent of Address. Accounts are loaded first, then Address. Address must reference the account using one of two values - the External ID or the Account ID.
Accounts are related to other Accounts through Affiliations. All Accounts that have an affiliation to another Account must be loaded before loading the Affiliation object. Affiliations link two Accounts using a "To" Account ID, a "From" Account ID, and a Role.
The Veeva data model uses record types on Accounts and Addresses. There are two ways to assign the record type to an Account or Address when loading data:
- Allow the data loader to use the default record type. Each SFDC Profile has default record types for the Account and Address objects. If the RecordTypeID is not included as part of the load, newly loaded records will be created using the default.
- Provide RecordTypeID on the load. If a data load will include multiple object types such as Hospitals and Professionals, the record type must be specified in the load process to avoid being assigned the default record type of the profile.
Child Account records represent hierarchical relationships between accounts in the Account Hierarchy. They consist of a lookup to the Parent Account and the Child Account. You must make sure to data load Accounts first, and then map Child Accounts according to the hierarchy. Additionally, when loading Accounts, designate the Primary Parent of that account in the Primary_Parent_vod field. The Child Account records you will subsequently load will automatically identify the Primary Parent based on that field on Account.
Products may be related to another Product through the Parent Product ID. When loading a Product hierarchy, you must load the parent Products first followed by the child Products. Another option is to sort the input by with parent objects first, then load all of the Products sequentially with a load batch size of 1.
The Formulary Data load consists of 3 objects - Formulary Products, Benefit Designs, and Benefit Design Lines. Benefit Design is the parent of Benefit Design Line, and Benefit Design Lines reference Formulary Product using a lookup reference. Since Benefit Design Lines are related to the other two objects, they must be loaded last. Benefit Design Lines must contain two references: the External ID or SFDC ID on the Benefit Design object and External ID or SFDC ID on the Formulary Product object.
The Call Data load consists of 6 objects – Call (Call2_vod), Call Detail (Call2_Detail_vod), Call Key Messages (Call2_Key_Message_vod), Call Discussions (Call2_Discussion_vod), Sample (Call2_Sample_vod), and Call Expenses (Call2_Expense_vod). The Call object is the parent object of all of the other Call objects. The key consideration of the Call2 data model is that all Attendees to group calls are now treated as individual calls. Each attendee will have its own call header and child objects – Details, Messages, Discussions, Samples, and Expenses. The Attendee Call records are associated to the Parent Call record via the Parent_Call_vod field on the Call2_vod object, which acts as a Lookup to the Parent Call2_vod record.
Additionally, two fields have been added to the Call2_vod object to assist in the creation of Call2_Detail_vod records and Call2_Key_Message_vod records. As described in the above field definitions, the Add_Detail_vod field on the Call2_vod object will create child Call2_Detail_vod records for the Call2_vod parent record. Similarly, the Add_Key_Message_vod field on the Call2_vod object will create child Call2_Key_Message_vod records for the Call2_vod parent record.
The overall process for loading data should follow this path:
- Load the Call2_vod record for all Parent Call .
- Usage of the Add_Key_Message_vod field is optional
- Usage of the Add_Detail_vod field is optional
- Load all Call2_vod records with a status equal to “Saved_vod”
- The Address_vod field can store a textual representation of the Address for the Call
- If PDMA Samples will be loaded then the following must be taken into account:
If the creation of the Disbursement Sample Transaction is not desired, then:
- Set the No_Disbursement_vod field on the Call2_vod object = True.
If the creation of the Disbursement Sample Transaction is desired, then:
- Set the No_Disbursement_vod field on the Call2_vod object = False (this is the default value).
- The Parent_Address_Vod Lookup to an Address must be set for the Parent Call2_vod record
- The Parent Call Lookup will not be set for the Call2_vod record representing the Parent Call
- The Call2_vod object supports Call records associated to a Contact and Account. The appropriate Lookup must be populated based on who the Call is associated with.
- Keep in mind whether the Call_DateTime_vod or Call_Date_vod field is being leveraged
- Load the Products Detailed into the Detailed_Products_vod field. The format is the Products discussed in order of priority from left to right with two spaces between each Product.
If the Add_Detail_vod field is not being leveraged, then load Call2_Detail_vod records for the Parent Call record.
If loading these records, the Call2_Detail_vod object supports the Call2_Date_vod field only.
If the Add_Key_Message_vod field is not being leveraged, then load Call2_Key_Message_vod records for the Parent Call record.
The Call2_Key_Message_vod object supports association to a Contact or Account. The appropriate Lookup must be populated based on who the Call2_Key_Message_vod record is associated with.
If loading these records, the Call2_Key_Message_vod object supports the Call2_Date_vod field only.
- Load the Call2_Discussion_vod records for the Parent Call record.
The Call2_Discussion_vod object supports association to a Contact or Account. The appropriate Lookup must be populated based on who the Call2_Discussion_vod record is associated with.
If loading these records, the Call2_Discussion_vod object supports the Call2_Date_vod field only
- Repeat steps 1 through 4 for all Attendees of the Calls.
- Associate all records to the appropriate Account or Contact.
For each Call2_vod record, set the Parent_Call_vod field to the Call2_vod record created in Step 1.
- Load the Call2_Sample_vod records to the appropriate Call2_vod record based on which Account received the Samples.
If the creation of Disbursement Sample Transactions is desired for the Call2_Sample_vod fields, then verify that.
- Load the Call2_Expense_vod records to all appropriate Call2_vod records, where the Expense is associated to the Account or Contact that incurred the expense.
- Update the Status_vod field to Submitted_vod for all Call2_vod records.
If the No_Disbursement_vod field on the Call2_vod object was set to False and other required fields were properly loaded, then the Disbursement Sample Transaction records will be created upon update to the Submitted status.
Veeva CRM contains a limited number of APEX triggers that are used to maintain the consistency of the data model. Here is a list of important triggers and their basic function.
Account: The Account trigger ensures that accounts with executed call reports are not deleted. The Account trigger also cleans up any affiliations that are associated with an account when the account is deleted.
Contact: The Contact trigger ensures that contacts attending an executed call reports are not deleted. The Account trigger also cleans up any affiliations that are associated with a contact when the contact is deleted.
Affiliation: The affiliation triggers ensure that when a record of type (AccountA => AccountB) is entered, then a record of type (AccountB => AccountA) is automatically created.
Address triggers: Address triggers ensure that longitude and latitude are blanked if a component of the street address is changed and that there can only be one primary address at a time. Triggers also contain logic to properly populate sampling license information.
Benefit Design (and children) triggers: The Benefit Design triggers synchronize data between the Benefit Design and Benefit Design Line tables.
Call header triggers: Call header triggers ensure that submitted calls cannot be edited, and that children of calls are deleted if the call header is deleted. Call header triggers also maintain a salesforce.com event (which is viewable on the salesforce.com calendar) and keep that data in sync with the call.
Call Children triggers: Child objects of the call have delete/update triggers to insure that submitted calls cannot be modified.
Coaching Report: Transfers ownership of the coaching report to the employee being reviewed.
Event triggers: A trigger on event ensures that if an event related to a Veeva CRM call is edited or deleted, the corresponding call report is kept in sync.
Call consistency triggers: Delete triggers on a variety of objects such as product strategy, account plans, etc are used to insure these objects cannot be deleted if they are referenced by a call that is submitted or in progress.
Inventory Order Allocation and Inventory Orders: Prevents deletion of submitted orders, transfers ownership to the Order for User, prevents overlapping allocations and creates allocations automatically that result from reallocations.
Sample Transaction: Sample Transaction trigger perform function to ensure data integrity, store audit information and populate Sample Receipt transactions.
Sample Inventory (and children): Sample Inventory triggers perform function to prevent modification of submitted inventories
Sample Receipts: The trigger pm Sample Receipt updates the Sample Transaction table when a receipt is confirmed.
Sample Lots: Ensures that Sample Lots cannot be deleted for lots that are used within a Sample Transaction.
Other than loading objects in the incorrect order, the only known issue related to triggers on objects is caused by a salesforce.com enforced limit on queries within a trigger. For example, if a trigger on an object performs multiple validations against other objects, each validation may require a SOQL query. These SOQL queries are limited to 100 per invocation of the trigger. In cases where this limit is exceeded, the loading process will receive a "Too many SOQL queries" error. If this error is encountered, reduce the batch size in the load process to reduce the number of calls and rerun the load.
The following is a list of recommended batch sizes for each type of Data Load.
- Account Affiliation Records : Batch Size = 30
- Historical Call Records: Batch Size = 15
- Address Records: Batch Size = 50
- Child Account Records = 25
- Call Sample Records = 10
- DCR Field Type Records = 15
Call objectives must contain at least the following items:
- To Date
- From Date
For more information on requirements for data loading specific Call Objective types that allow recurrence, one-click completion, and setting CLM objectives, see Call Objective Types.