[b]Issues with Decimals in the SCD Component Task[/b]

[Nassy]

Hi,

I was wondering if anyone could provide any input into a particlar problem I have been having. I have been doing some work for a client designing and populating their datawarehouse. Just recently I have encountered a problem that relating to the use of fields with a decimal data type in the Slowly Changing Dimension task.

Description of the problem

I have written a package that performs an incremental load of a dimension table DimDiscount. DimDiscount is a Type 2 changing dimension and I therefore I have used the Slowly Changing Dimension task to reflect this.

The problem is that DimDiscount has the following composite key:


StoreNo int
DiscountName nvarchar(500)
DiscountRate decimal(14,0)


When these three fields are set up as the Business Key in the Slowly Changing Dimension wizard the change capture does not work at all. However if
the DiscountRate in the dimension table is set to an integer then the change capture works!

At the moment the Discount Rates in the source table are all integers but that may not always be the case so it would be preferable to have Discount Rate as a decimal.

Conclusion

I can't get my head around this - I have been researching into whether or not there are any known issues with decimals but I can't find any... Does anyone know whether or not in theory you can have a field with a Decimal data type as a Business Key.

Any input on this would be greatly appreciated as it has been driving me up the wall!



Nassy

 

[MDXer]

This is where I use CRC columns If you carry a column in your dim table that is populated as binary_checksum(StoreNo,DiscountName,DiscountRate) Then in your data flow you create a calculated column that is the same checksum. in you SCD task you only need to compare the CRC value to pick up a change in any of the 3.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck