Skip to Content
author's profile photo Former Member
Former Member

SCD Type 1 in Data Services

Hi guys,

I was implementing today a scd type 1 in data services and I got some questions. My example is pretty simple:

1) I have datastore A and B. A is staging area database and b is datawarehouse database.

2) Table user from A has 3 fields (user_code, user_name, user_status - all are varchar) and Table B is the dw user dimension that has 4 fields (user_code, user_name, user_status (all are varchar) + user surrogate key field (integer))

3) Fired off a table_comparison transform between A and B, primary key is the user_code, user_name and user_status are the fields to be used to compare and to be updated

Now my questions, first i was a bit sceptical about key generation - I always let the database identity columns to do that work, so I set sky_user as identity - so I didn't fill Generated Key Column, but since I also had it coming from the output schema, I used a query transform to not let it map into the final dw user dimension table.

Strange enough inserts were working, but the fields I was expecting to be updated, because I changed user_status and user_name, are also generating new records as it was considering all the 3 fields to be the unique identifiers of a new row.

What might be causing this behavior?

Thank you

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Aug 18, 2016 at 02:47 PM

    You cannot have a Query transform following a Table_Comparison. So you've included a Map_Operation. That's where things go wrong. You've probably turned all updates into inserts again.

    Is your target MS SQL Server? Then, if you're on DS 4.2 SP6 or higher, set Identity Insert to Off in the target table options.

    In any other case, have two outgoing connections, each to a Map_Operation, from the Table_Comparison:

    1/. Discard all Row Types but Updates. Let data simply flow into the target table.

    2/. Discard all Row Types but Inserts, map Insert into Normal. Remove PK column in mapping of a Query transform. Let data flow into (a 2nd instance of your) target table.

    Add a comment
    10|10000 characters needed characters exceeded

    • I am sorry but your remarks don't make much sense. Please read again what I have written and try to understand.

      I have developed literally hundreds of data flows with this simple structure:

      In flow 1/ how do you avoid the update of the surrogate key field, since table_comparison transform will move forward all the fields from the table to be compared?

      Because it's SCD 1, there's a one-to-one relationship between natural and surrogate PK's. The table_comparison will pick up the right surrogate key, when a record with the source natural key already exists in the target.


      I think it's the following, on target table select under Update control section, turn Use input keys to Yes. Also the datastore table fields must reflect correctly that the field is a PK.

      Don't change any of the settings of the target table!

      I had however another problem, because they are in the same dataflow executing at the same time, table becomes locked and it fails. I noticed that in 2 different dataflows, running in serial it works well but it duplicates the table comparison operation so it performs worse.

      You must be doing something wrong. I have never ever experienced such an issue.

      Anyway if somehow this option turns out to be usable in a single dataflow, is it worth using this approach vs using Table comparison + Key Generation? They will never be fully pushdown into the database anyway (2 different datastores and table comparison is being used).

      Only code generated from Query transforms can be pushed down in a SQL Server environment. Full SQL pushdown isn't possible when any other transform type is used in a data flow.

      pastedImage_2.png (12.1 kB)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.