SCD monitored column changes not detected because value can be NULL
4 posters
Page 1 of 1
SCD monitored column changes not detected because value can be NULL
We are creating SCD Type 2 values in dimension tables using the MS Merge statement, which uses a number of inequalities in the WHEN MATCHED clause to detect changes on particular columns between source and target, eg:
ds.value1 <> dt.value1
OR ds.date1 <> dt.date1
etc
The problem is that some monitored columns may have a Null value in the source data and change to non-Null, or may change from non-Null to Null. These never get detected by the above comparison code, so the trigger to Update or Insert is bypassed and no change results.
Is there a standard solution to this situation?
Thanks
Mark
ds.value1 <> dt.value1
OR ds.date1 <> dt.date1
etc
The problem is that some monitored columns may have a Null value in the source data and change to non-Null, or may change from non-Null to Null. These never get detected by the above comparison code, so the trigger to Update or Insert is bypassed and no change results.
Is there a standard solution to this situation?
Thanks
Mark
Mark Duffill- Posts : 2
Join date : 2014-09-02
Re: SCD monitored column changes not detected because value can be NULL
Can you not just extend the logic as follows:
ds.value1 <> dt.value1 OR (ds.value1 is null AND dt.value1 is not NULL) OR (ds.value1 is not null AND dt.value1 is NULL)
alternatively I think this syntax achieves the same thing (and is a bit shorter):
ds.value1 <> dt.value1 OR ISNULL(ds.value1, 'X') <> ISNULL(dt.value1, 'X')
(where 'X' is any value you want)
Regards
ds.value1 <> dt.value1 OR (ds.value1 is null AND dt.value1 is not NULL) OR (ds.value1 is not null AND dt.value1 is NULL)
alternatively I think this syntax achieves the same thing (and is a bit shorter):
ds.value1 <> dt.value1 OR ISNULL(ds.value1, 'X') <> ISNULL(dt.value1, 'X')
(where 'X' is any value you want)
Regards
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: SCD monitored column changes not detected because value can be NULL
Another method to detect change is to use a 32 bit CRC function to calculate a hash of the data you want to check.
When loading you concatenate the various values together and calculate an integer hash of the resulting string. Use NVL() functions on each value to convert nulls. You then compare the hash with the previous calculated value stored in the dimension.
When loading you concatenate the various values together and calculate an integer hash of the resulting string. Use NVL() functions on each value to convert nulls. You then compare the hash with the previous calculated value stored in the dimension.
Re: SCD monitored column changes not detected because value can be NULL
Thanks to both for suggestions
I wanted to avoid using the CRC method as we've already standardised on the method of column by column comparisons (as in this article http://www.kimballgroup.com/2008/11/design-tip-107-using-the-sql-merge-statement-for-slowly-changing-dimension-processing/)
Of Nick's suggestions I prefer the first one, although longer it covers all bases whereas the second needs to choose a value of 'X' that will never occur in the source date column (eg if source changes from NULL to 'X' then it would not be detected) - this would need the coder to consider both data type and data content to be sure of a suitable value.
Thanks
I wanted to avoid using the CRC method as we've already standardised on the method of column by column comparisons (as in this article http://www.kimballgroup.com/2008/11/design-tip-107-using-the-sql-merge-statement-for-slowly-changing-dimension-processing/)
Of Nick's suggestions I prefer the first one, although longer it covers all bases whereas the second needs to choose a value of 'X' that will never occur in the source date column (eg if source changes from NULL to 'X' then it would not be detected) - this would need the coder to consider both data type and data content to be sure of a suitable value.
Thanks
Mark Duffill- Posts : 2
Join date : 2014-09-02
Re: SCD monitored column changes not detected because value can be NULL
I like using MERGE statement to maintain SCD dimensions and have found Null in attribute invalidates the result of straight comparison (ds.value1 <> dt.value1). Nick's suggestion is the brute force method and will definitely work. However an elegant alternative is to leverage EXCEPT statement as follows:
WHEN MATCHED AND EXISTS
(SELECT ds.value1, ds.value2 ...
EXCEPT
SELECT dt.value1, dt.value2 ...)
WHEN MATCHED AND EXISTS
(SELECT ds.value1, ds.value2 ...
EXCEPT
SELECT dt.value1, dt.value2 ...)
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» the master detail tables facts
» Null value for dates
» Null Foreign Key
» Can a 'fact grain' attribute be null?
» NULL DATES IN FACTS
» Null value for dates
» Null Foreign Key
» Can a 'fact grain' attribute be null?
» NULL DATES IN FACTS
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|