Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SCD monitored column changes not detected because value can be NULL

4 posters

Go down

SCD monitored column changes not detected because value can be NULL Empty SCD monitored column changes not detected because value can be NULL

Post  Mark Duffill 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

Back to top Go down

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

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  Mark Duffill 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

Back to top Go down

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

Post  hang 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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