Portal

Background

If you have updates to an existing record in xDM but the update does not load every column/attribute, there is a risk that you may have null/blank values after performing the update.

Here's an example.

Day 1: Imagine you have these Customer records.

When you load an update, you may only have the following new information to update Mary-Ann Markes (she changed her last name).

Day 2: Data update

If you load this update to Mary-Ann's record via ETL, you may end up with null for the DateOfBirth and MemberID fields because you didn't provide those values in your update load. Check out this Community Question for a detailed example.

This is the likely result if you loaded the Day 2 data update.

Day 2: Result of data update after processing integration job

This article will show you different ways you can overcome these null values so that your update can retain the existing values and avoid the null problem.

Here are the ways to set up your integration or data model so you don't have null values but can take the existing values from. the records already in xDM:

1A. Use a SemQL enricher with a filter to get the existing value.

1B. Use a SemQL enricher with logic built into the Enricher Expression.

2. In your ETL/SQL job for loading the data, retrieve the existing missing values and load them into the SD/SA table.

Option 1A: Use a SemQL enricher with a filter

This solution comes from @Brent Van Allen .

Scenario: Let's say you have 1 column where the value is null whenever you update the record and you want to keep the existing value. For example, this use case of updating a specific attribute.

• Set up a SemQL enricher running in the pre-consolidation scope. The pre-consolidation enrichment process has access to the MasterRecord object.

• Set up a Filter to check if the incoming record has a null value for the DateOfBirth is null (or the attribute you are concerned about). For example, DateOfBirth is null and MasterRecord.DateOfBirth is not null. This means the enricher will only run when your record update is missing the DateOfBirth and the MasterRecord has an existing value. The enricher shouldn't run to overwrite a DateOfBirth value if you are submitting an updated value for this attribute.
• The Enricher Expressions takes the MasterRecord value to populate the update. MasterRecord.DateOfBirth

This is what your SemQL enricher would look like:

NOTE: Option 1A works well if you have only 1 attribute to populate because the filter is checking on that particular attribute. The limitation arises if you have multiple attributes that you need to update. Proceed to Option 1B if you are dealing with multiple attributes.

Option 1B: Use a SemQL filter with logic built into Enricher Expressions

While Option 1A is a clean solution for 1 attribute, the downside of Option 1A is that it has less flexibility if you're grabbing existing values for multiple attributes. You can use a variation of the SemQL enricher concept. Instead of putting the logic into the Filter, you can manage the logic in each Enricher Expression.

Here is the DateOfBirth Enricher Expression:

case
  when DateOfBirth is null and MasterRecord.DateOfBirth is not null then MasterRecord.DateOfBirth
  else DateOfBirth
 end 

Here is the MemberID Enricher Expression:

case
  when MemberID is null and MasterRecord.MemberID is not null then MasterRecord.MemberID 
  else MemberID
 end

While this makes your enricher more complicated, this is still less maintenance than dealing with multiple SemQL enrichers (you could consider multiple SemQL enrichers as Option 1C where you create a SemQL enricher like Option 1A for every attribute you want to grab existing values).

Option 2: Retrieve the values during ETL/SQL insert time

Instead of using a SemQL enricher, you can grab the missing values when you load the data updates to xDM.

Using SQL as an example, your insert statement can join to the MI table (or GD table if you are using basic entities) to get the existing values.

Here is a working example:

insert into semarchy_customer_b2c_mdm.sd_person (
  b_loadid /* Semarchy system fields */
  ,b_classname  /* Semarchy system fields */
  ,b_pubid  /* Semarchy system fields */
  ,b_sourceid
  ,first_name
  ,last_name
  ,date_of_birth 
  ,member_id 
  ,source_email 
 )
 select
  semarchy_repository.get_continuous_loadid('CONTINUOUS_LOAD_INTEGRATE_ALL') as b_loadid /* Call to get Load ID */
  ,stg.b_classname /* Entity name. Found in MDM Workbench UI. */
  ,stg.b_pubid
  ,stg.b_sourceid
  ,stg.first_name
  ,stg.last_name
  ,mi.date_of_birth
  ,mi.member_id
  ,mi.source_email
 from semarchy_stg.source_person stg
 inner join semarchy_customer_b2c_mdm.mi_person mi
 on mi.b_sourceid = stg.b_sourceid and mi.b_pubid = stg.b_pubid
 ;