topic Re: New columns dont show up in SCD Type 2 -> Detect Changes in SAS Data Management

New columns dont show up in SCD Type 2 -> Detect Changes

dirks — Fri, 09 Sep 2016 08:10:07 GMT

I am using Data Integration Studio 4.9. I am using a SCD Type 2 transformation and the source table has new columns, but I can't see them in the "Detect Changes" tab of the transformation.

Do I need to create them manually in the target table first?

Re: New columns dont show up in SCD Type 2 -> Detect Changes

RahulG — Fri, 09 Sep 2016 08:12:30 GMT

Answer is yes.

Ensure your key column list is correct.

Re: New columns dont show up in SCD Type 2 -> Detect Changes

dirks — Fri, 09 Sep 2016 08:15:18 GMT

Thanks!

Is there a fast and easy way to do it or do I need to write a PROC SQL statement by hand? I have to add a lot of new columns and writing them manually would be very tedious.

Re: New columns dont show up in SCD Type 2 -> Detect Changes

RahulG — Fri, 09 Sep 2016 08:24:54 GMT

If you want to add new columns in Table metadata.

Go to Table properties ---> Columns Tab ( you will see all columns present in table)

In column Tab, there are two option, 1. Add new columns. 2. Import columns.

Use these two options to add column in Table metadata. It will speed your work.

Re: New columns dont show up in SCD Type 2 -> Detect Changes

RahulG — Fri, 09 Sep 2016 08:32:11 GMT

First two options highlighted in yellow should help you.

Re: New columns dont show up in SCD Type 2 -> Detect Changes

dirks — Fri, 09 Sep 2016 08:48:02 GMT

I somehow doesnt work.

This is what I am doing:

1. Open Target Table

2. Columns -> Import Columns

3. Select Source Table -> Right Arrow to add all columns

4. Get a warning because of existing columns.

5. Click OK

6. Properties of SCD Type 2 Transformation -> Detect Changes -> Put all columns from left to right

7. Run SCD Type 2 Transformation

Now I get a warning for every new column I just added:

WARNING: The variable ACCOUNTID in the DROP, KEEP, or RENAME list has never been referenced.

Did I miss anything?

Re: New columns dont show up in SCD Type 2 -> Detect Changes

RahulG — Fri, 09 Sep 2016 08:52:58 GMT

Have you listed right set of column in business key tab.

I am guessing ACCOUNTID should be listed in business key

Re: New columns dont show up in SCD Type 2 -> Detect Changes

dirks — Fri, 09 Sep 2016 09:08:59 GMT

The business key is SERVICEID. ACCOUNTID was just an example. I get a warning for every new column.

The imported columns are gone now aswell. They dont show up anymore in the "Detect Changes" tab.

Re: New columns dont show up in SCD Type 2 -> Detect Changes

Patrick — Sat, 10 Sep 2016 02:57:03 GMT

There are 2 parts to this question.

1. On metadata level:

- propagate and map the new columns from source to target.

- Once done you will have these columns available for change tracking.

2. On physical table level:

- if you can drop the physical table. The DIS job will then re-create the table based on your table metadata definition with all the new columns as soon as you run it again.

- if you can't drop the table: The SCD2 loader doesn't add new columns "automatically" to the physical table so you will need to write a once off job with a SLQ alter table statement and add all these columns scripted. For all new columns which then are under type 2 change tracking a new version will be added to the target table as soon as you load new data (because the new columns will be NULL/missing initially).