Deleted Output vs. Expired SCD2 Output

Apr 11, 2010 at 3:53 PM

Hi Todd,

I'm running v1.5.   It seems that existing dimension business keys that do not appear in the source input gets assigned to the EXPIRED SCD2 output.  That works for me, but makes me wonder about the purpose of the DELETED output.

Would you explain the purpose of the DELETED output? 



Apr 11, 2010 at 7:49 PM

The Deleted output comes into play if and only if you're not tracking SCD2 attributes.  If you get rows out of one, you won't get rows out of the other.  I included two outputs, because obviously the SCD2 Expired output is necessary, but I also wanted to allow dimensions that didn't have type 2 attributes to be able to track "deletions".  Since the dimension wasn't tracking type 2 attributes, it didn't make much sense to route those rows to an "SCD2 Expired" output, so I made a "Deleted" one...

Apr 11, 2010 at 9:00 PM

OK, I now understand the value of including a DELETED output for SCD 1's.  Thanks, Tim

Apr 13, 2010 at 1:07 PM

Hi Todd, great job!

Sorry for my poor English. I'm running v 1.5 (Jan 24) either and have some issue with DELETED output. I've surrogate key, business key and several SCD1 attributes exactly as you wrote above. I've 3 housekeeping fields in my existing dimension table. When I mark housekeeping fields as 'not used' I've no rows in DELETED output. When I mark housekeeping fields as SCD2 Current Record, SCD2 Effective Date and SCD2 Expiry Date accordingly, and have no SCD2 attribute, I see the red cross near Kimball Method SCD component with error message 'Some SCD 2 Housekeeping columns are specified, but no columns are identified as tracking SCD2 changes.'

The only way I managed to get rows in DELETED output was to 'switch on' housekeeping fields and have at least one SCD2 attribude. Could you clarify the right way of using the DELETED output without tracking SCD2 attributes?

Thanks in advance,




Apr 14, 2010 at 5:04 PM

There's no reason for you to have those housekeeping fields if you're not tracking SCD 2 attributes - that's why the component is complaining.

If you mark those columns as "not used", it should send rows to the deleted output - but only if there are rows in your existing dimension flow that are not in your source system flow.  Is that the case?  Can you describe a simple example?

Apr 15, 2010 at 10:08 AM

Hi Todd!

Many thanks for your reply. I tried various ways to get rows in DELETED output definitely. Actually I use you component in the same way as tcash wrote you before just working with smaller fact tables. It's really extremly fast component!

I've two databases named Source and Destination and one table in each database. In Source database

CREATE TABLE [DimensionSrc1](
    [ID] [bigint] NOT NULL,
    [Field1_varchar] [varchar](20) NULL,
    [Field2_money] [money] NULL,
    [Field3_forkey_bigint] [bigint] NULL,
    [ID] ASC

and in Destination database

CREATE TABLE [DimensionDW1](
    [DimentionKey] [bigint] NOT NULL,
    [AlternateKey] [bigint] NULL,
    [DateEffective] [datetime] NULL,
    [DateExpires] [datetime] NULL,
    [Field1_varchar] [varchar](20) NULL,
    [Field2_money] [money] NULL,
    [Field3_forkey_bigint] [bigint] NULL,
    [IsCurrent] [bit] NULL

To fill tables with sample data you can use the following scripts

INSERT INTO [DimensionSrc1]
VALUES    (200001,'somevalue1', 10000, 100001),
        (200002,'somevalue2', 20000, 100002),
        (200003,'somevalue3', 30000, 100003),
        (200004,'somevalue4', 40000, 100004),
        (200005,'somevalue5', 50000, 100005),
        (200007,'somevalue7', 70000, 100007),
        (200008,'somevalue8', 80000, 100008),
        (200009,'somevalue9', 90000, 100009),
        (200010,'somevalue10', 100000, 100010)
INSERT INTO [DimensionDW1](
VALUES    (1,200008,'somevalue8',80000,100008),

As you can see there are 9 rows in source dimension table and 10 rows in destination dimension table. As you wrote above I marked housekeeping fields as "not used". After execution the package I have all 10 rows in UNCHANGED output and no rows in DELETED one.




Apr 20, 2010 at 10:28 PM

I am having the same issue as Alex. I have 6 rows in my dimension table and 5 in my source table. I am not getting any rows in deleted or expired scd2. Has this been resolved yet?


Apr 22, 2010 at 2:14 PM
DTape85 wrote:

Has this been resolved yet?

Hi, Dan!

As I wrote above:

"The only way I managed to get rows in DELETED output was to 'switch on' housekeeping fields and have at least one SCD2 attribute." I don't have any real SCD2 attribute in my existing dimension.  To resolve the issue I added dummy SCD2 attribute for my Existing dimension input by adding Derived Column component with DT_UI1 data type and NULL(DT_UI1) expression for it's value. Then I added the similar attribute for the Source input. After all I treated this dummy attribute in Kimball Slow changing dimension component as SCD2 attribute.


Apr 30, 2010 at 6:57 AM

Sorry guys - I haven't had the time to troubleshoot this issue.  Can't say when I might either... but I'll convert this to a work item so that I don't forget to check it... eventually... :)

Apr 30, 2010 at 7:04 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.