Configuring Nitro Data Sync for Vault CRM X-Pages

Provisioning the Nitro Data Sync for X-Pages

In order to visualize Nitro data in X-Pages, two core configuration steps are required:

  • Activating the Nitro data sync for Vault CRM mobile platforms
  • Setting up and development of X-Pages reports for Nitro data

Activating the Nitro Data Sync for Vault CRM X-Pages

  1. Submit a support ticket to activate Nitro data sync for Vault CRM. Include the following in the support ticket:
  • Cluster Name
  • Instance Name
  • Vault CRM Instance ID
  1. Log into Vault CRM once Veeva sends a notification once activation is complete.
  2. Navigate to Business Admin > Objects > vds_settings__v.
  3. Select Create.

  4. Enter a VDS Profile (Sync Profile ID) to enable Nitro data sync for the selected application 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.

  5. Select Save.

  6. Navigate to Business Admin > Objects > vmobile_object_configuration__v.

  7. Activate the Full Sync VMOC for a device for the VDS Setting object.

Configuring Nitro for X-Pages

The metadata that defines the data to be synced to Vault CRM X-Pages is deployed to the connector corresponding to the Vault CRM instance. 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, Vault CRM mobile devices can 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 Vault CRM mobile devices.

  1. Create an HTML(MyInsights).yml file in the table directory of the package to sync. The .yml file describes the raw data format of a table in the X-Pages layer as follows:
  • name - name of the object in the metadata. This must match the filename.
  • tableName - name of the physical table in the X-Pages 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_territory_id__v column, crm_territory_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 an X-Pages 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 X-Pages:

  • crm_territory_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 - X-Pages 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 - X-Pages 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_total_treatments_trend_HTML__c
tableName:  fact_total_treatments_tred__c
label: Total Treatment Trend
schema:  DW_htmlSchema
primaryKey:  trend_id__c
distStyle: key
distKey: trend_id__c
partitionKey:crm_territory_id__v
columns: 
- name:  trend_id__c
label: Total Treatment 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 Treatment Amount
type:   decimal(8,2)
compression: ZSTD
notNull:   false
enabled:  true
- name: crm_territory_id__v
label: CRM Territory 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 Vault CRM vds_settings__v.
  • 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:
  • @@USER_ID@@ - replaced with sync user’s id
  • @@LAST_MODIFIED_DATE@@ - replaced with max effective last modified date of each table in the offline device
  • @@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, Sales Rep, is mapped to profile id, 1 (Rep), in VDS Profile (Sync Profile ID) in Vault CRM vds_settings__v of, and a table in X-Pages layer does not have the sync profile populated with profile id =1, the table does not sync to the Vault CRM platform for the application profile users. Only the tables with sync profiles with profile id =1 are synced to the Vault 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.