SCD Wizard Issue

[RiverGuy]

I've got an SCD wizard set up for a dimension. It's only got a dozen or so attributes and I have them all set to Historical Attributes (other than the Business Key of course). The problem is, it's detecting every row that flows through as a historical change. I've double checked the component properties and my CurrentRowWhere is correct (using 2 date columns to denote time specific records), and IncomingRowChangeType is set to Detect. Any ideas on something else I can check?

 

[RiverGuy]

It's working now. The metadata must have been out of wack. I rebuilt the structure of my dimension table and rebuilt the SCD transformations.

 

[MDXer]

Be careful how large your dimensions are when using this task as performance will quickly become unreasonable. there are a few ways around this.

First with the wizard. Rather than allowing the OLE DB CMD task to fire for every typ1 update replace this with a destination to a staging table then run a SQL task in your COntrol flow after this data flow finishes.

If yuo have a large dimension then you are better wring a custom script task to do the SCD logic.

If you haven't profiled SQL while this SCD process runs you should. The results are very interesting. The comparison lookup is an uncached task so it executes queries for every record in the dataflow.

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