SCD monitored column changes not detected because value can be NULL

View previous topic View next topic Go down

SCD monitored column changes not detected because value can be NULL

Post  Mark Duffill on Tue Sep 02, 2014 10:44 am

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

Mark Duffill

Posts : 2
Join date : 2014-09-02

View user profile

Back to top Go down

Re: SCD monitored column changes not detected because value can be NULL

Post  nick_white on Tue Sep 02, 2014 11:46 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: SCD monitored column changes not detected because value can be NULL

Post  ngalemmo on Tue Sep 02, 2014 1:45 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD monitored column changes not detected because value can be NULL

Post  Mark Duffill on Wed Sep 03, 2014 2:37 am

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

Mark Duffill

Posts : 2
Join date : 2014-09-02

View user profile

Back to top Go down

Re: SCD monitored column changes not detected because value can be NULL

Post  hang on Wed Sep 03, 2014 7:17 am

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 ...)

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: SCD monitored column changes not detected because value can be NULL

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum