Reduce the volume of the Cleansing Error tables

By default, the cleansing action logs a line of type Missing foreign key value in the CLS_XXX_Error tables if a Foreign Key has NULL value in a relationship between a Fact and an Entity or between 2 Entities.

Example: We don't know who buy the Product X (The Person is set to NULL in the Sale)

You have the same kind of line if a Foreign Key has a value that does not exist in the target object of the relationship.

Example: Product X was bought by Person Y, and Person Y didn't exist in the list of persons.

For more information about the cleansing actions, consult Take care of entries from Cleansing Error tables.

By default, the cleansing action logs an error if a Foreign Key has a NULL value in a relationship between a Fact and an Entity or between two Entities. However, you might want to treat a NULL Foreign Key as meaningful, indicating that the related record is unknown, but the data is still correct in the source.

And avoid the creation of all the concerned Cleansing Action Log lines in the CLS_XXX_Error tables to reduce their volume.

To avoid logging these as errors in the Cleansing Action error tables, apply the following updates to your Data Solution:

• Add a new singleton line in the concerned Entity for the N/A value (below is an example for the Product_Subcategory Entity):

-- Enable IDENTITY_INSERT in Product_Subcategory Entity
SET IDENTITY_INSERT [DW].[COR_EN_Product_Subcategory] ON
GO

--Add the singleton value in Product_Subcategory Entity
INSERT INTO [DW].[COR_EN_Product_Subcategory]
     ([BG_SourceSystem]
      ,[BG_LoadTimestamp]
      ,[BG_UpdateTimestamp]
      ,[Product_Subcategory_ID]
      ,[ProductSubcategoryID]
      ,[FK_Product_Category_ProductCategoryID]
      ,[Product_Category_Product_Category_ID]
      ,[Name])
VALUES (
      'N/A'
      ,CONVERT(DATETIME, '19000101', 112)
      ,CONVERT(DATETIME, '19000101', 112)
      ,-10
      ,-10
      ,0
      ,0
      ,'Not available');
GO

-- Disable IDENTITY_INSERT in Product_Subcategory Entity
SET IDENTITY_INSERT [DW].[COR_EN_Product_Subcategory] OFF
GO

• Add a term rule on the Foreign Key in the Entity in relation to use this N/A value (Product_Product in this example):
  • Edit the FK_Product_Subcategory_ProductSubcategoryID Term Mapping
  • Fill in the following Expression:

CASE
     WHEN s1.ProductSubcategoryID IS NULL
     THEN -10 --apply the N/A singleton value
     ELSE s1.ProductSubcategoryID
END

Now if you generate, deploy, and load your project again, no more Cleansing Action Log line will be created for a NULL Foreign Key in the relationship concerned by this update (relationship between a product and a Product_Subcategory), and you will be able to distinguish the "real" Unknown values (concerned by a Cleansing Action) and the N/A values.