Configuring Nitro Data Sync for Veeva CRM MyInsights

Provisioning the Nitro Data Sync for MyInsights

In order to visualize Nitro data in MyInsights, two core configuration steps are required:

  • Activating the Nitro data sync for Veeva CRM mobile platforms
  • Setting up and development of MyInsights reports for Nitro data

Activating the Nitro Data Sync for Veeva CRM MyInsights

  1. Submit a support ticket to activate Nitro data sync for a Veeva CRM org. Veeva sends a notification once activation is complete.
  2. Include the following in the support ticket:
  • Cluster Name
  • Instance Name
  • Veeva CRM Org ID
  1. Sign in and navigate to Setup > Develop > Custom Settings > VDS Settings to enable the sync in Veeva CRM.
  2. Create a setting for a profile in the VDS Settings.

  3. Enter a VDS Profile (Sync Profile ID) to enable Nitro data sync for the selected profile users. A Sync Profile value of 1 for the sales rep and a value of 2 for the FLM (First Line Manager) are populated by default.

  4. Navigate to the Veeva Mobile Object Configuration tab and activate the Full Sync VMOC for a device for the VDS Setting object.

Configuring Nitro for MyInsights

The metadata that defines the data to be synced to Veeva CRM MyInsights is deployed to the connector corresponding to the org. After the metadata is deployed, configure a Nitro Job to synchronize data from Nitro to the Nitro Presto cache layer. Once the data is synced to Nitro’s Presto cache, Veeva CRM mobile devices can begin to sync the data.

Creating the Metadata Package

Creating a metadata package involves creating special text files to define the structure of the data to sync to Veeva CRM mobile devices.

  1. Create an HTML(MyInsights) .yml file in the table directory of the package to sync down. The .yml file describes the raw data format of a table in the MyInsights layer as follows -
  • name - name of the object in the metadata. This must match the filename.
  • tableName - name of the physical table in the MyInsights layer
  • label - label of the table.
  • Schema - always set to DW_htmlSchema
  • primaryKey - column name of the primary key in this table
  • distStyle - always set to key
  • distKey - column name of the primary key. This column is used for the upsert logic of Nitro data sync.
  • partitionKey - column name of the partition key in this table. Nitro partitions the data set by this key in the cache server. If this table contains the crm_user_id__v column, crm_user_id__v should be the partition key to achieve a high query performance.
  • enabled - (Optional) set to false if this table is not being actively synced
  • columns :
  • name - name of the column in this table
  • label - label of the column. You can get this value in a MyInsights report via a method.
  • type - set to the column type. Supported column types are those supported in Amazon RedShift. (Use decimal instead of numeric since Nitro cache server can not recognize numeric data types.)
  • compression - reflects the compression used by Nitro to store the data. This should always be ZSTD.
  • notNull - Set to true or false to indicate if this column is required
  • enabled - set to false if this column is not being actively synced

    primaryKey and distKey must be set to the unique identifier used in the data. primaryKey determines the records to be synced during an incremental sync of the CRM client. distKey is used by the CRM client to process incremental data (upserts).

  1. Clear Veeva Cache to activate the defined metatdata for the Nitro data sync. This allows the sync server to get the latest metadata.

Considerations

The following columns are important for understanding the table structure for MyInsights:

  • crm_user_id__v - (Optional) If the records are filtered based on the user’s visibility, this column is required. Populate the value from the mapping tables. Filter this column to display records tied to a specific user.
  • effective_last_modified_date__v - MyInsights tables should contain this column with “__v” name space. This is a system column used to manage the incremental sync. This column stores the last modified date of each record in UTC format. (YYYY-MM-DDTHH - MI - SS.000Z)
  • effective_end_date__v - MyInsights table should contain this column with “__v” name space. This is a system column used to manage the active data. If this column is null, the record is active indefinitely. If this column has a value, the record is active until the value of the effective_end_date__v in UTC format.

Example

Copy 
name: fact_trx_trend_HTML__c
tableName:  fact_trx_tred__c
label: TRx Trend
schema:  DW_htmlSchema
primaryKey:  trx_trend_id__c
distStyle: key
distKey: trx_trend_id__c
partitionKey:crm_user_id__v
columns: 
- name:  trx_trend_id__c
label: TRx Trend Id
type: varchar(10)
compression: ZSTD
notNull: true
enabled:  true
- name:  date__c
label: Date
type:  date
compression: ZSTD
notNull:  true
enabled:  true
- name:   amount__c
label: Total Rx Amount
type:   decimal(8,2)
compression: ZSTD
notNull:   false
enabled:  true
- name: crm_user_id__v
label: CRM User Id
type: varchar(18)
compression: ZSTD
notNull:  true
enabled: true
- name:  effective_last_modified_date__v
label:  Effective Last Modified Date
type: varchar(24)
compression: ZSTD
notNull:  true
enabled:  true
- name: effective_end_date__v
label:  Effective End Date
type: varchar(24)
compression: ZSTD
notNull:  false
enabled:   true
  1. Create a .yml file in the syncProfiles directory of the package, for filtering sync data from the table based on the sync profile id:
  • name - name of the object in metadata. This must match the filename.
  • tableName - name of the table which the filter is populated to
  • profileId - Id of the profile. Map this id in the VDS Profile (Sync Profile ID) in Veeva CRM custom settings.
  • profileName - name of the profile
  • incrementalFilter - Set the where clause to pick up the incremental data based on the effective_last_modified_date__v. This filter is used for counting how many records are synced for the table and includes active and inactive records. If the count exceeds 20K,the sync logic automatically switches to use a full refresh.
  • activeIncrementalFilter - Set the where clause to pick up the incremental active data based on the effective_last_modified_date__v. This filter is used for upserting incremental active records to offline devices.
  • inactiveIncrementalFilter - Set the where clause to pick up the incremental inactive data based on the effective_last_modified_date__v. This filter is used for using incremental inactive records to delete the synced records in offline devices.
  • fullRefreshFilter - Set the where clause to refresh the table with the active data.

Considerations:

  • Each filter must begin and end with double quotes
  • Each filter must have support placeholders to dynamically replace to a value:
  • @@VOD_SF_USER_ID@@ - replaced with sync user’s SFDC id
  • @@LAST_MODIFIED_DATE@@ - replaced with max effective last modified date of each table in the offline device
  • @@VOD_MY_TERRITORY@@ - replaced with the sync user's territory id(s)
  • Each filter must have support Presto SQL functions. To compare the UTC format date time, use the from_iso8601_timestamp function. To compare the varchar data type fields, use the Cast function. If the field is null, use the Nullif function.
  • Nitro data sync uses the profile id to pick up the table and filter. For example, if the CRM profile, Primary Care Rep, is mapped to profile id, 1 (Rep), in VDS Profile (Sync Profile ID) in Veeva custom settings of, and a table in MyInsights layer does not have the sync profile populated with profile id =1, the table does not sync to the Veeva CRM platform for the CRM profile users. Only the tables with sync profiles with profile id =1 are synced to the Veeva CRM platform.

Example

Copy 
name: fact_trx_trend_HTML__c
tableName: fact_trx_trend__c
profileId: 1
profileName: Rep
incrementalFilter:   "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and from_iso8601_timestamp(effective_last_modified_date__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@)"
activeIncrementalFilter:   "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and from_iso8601_timestamp(effective_last_modified_date__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@) and NULLIF(effective_end_date__v, '') is Null"
inactiveIncrementalFilter:  "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and from_iso8601_timestamp(effective_last_modified_date__v) > from_iso8601_timestamp(@@LAST_MODIFIED_DATE@@) and NULLIF(effective_end_date__v, '') is not Null"
fullRefreshFilter: "Where crm_user_id__v = @@VOD_SF_USER_ID@@ and NULLIF(effective_end_date__v, '') is Null"
  1. Zip the tables and syncProfiles sub-directory after creating the.yml files. This zipped package is deployed to the connector.