SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

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

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?


Accepted Solutions
Solution
‎09-19-2016 03:43 AM
Respected Advisor
Posts: 4,173

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

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).

 

 

View solution in original post


All Replies
Super Contributor
Posts: 266

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

Answer is yes.

Ensure your key column list is correct.

Contributor
Posts: 46

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

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.

Super Contributor
Posts: 266

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

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.

Super Contributor
Posts: 266

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

First two options highlighted in yellow should help you.

DI_table_option.png

Contributor
Posts: 46

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

[ Edited ]

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?

 

 

 

Super Contributor
Posts: 266

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

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

 

I am guessing ACCOUNTID should be listed in business key

Contributor
Posts: 46

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

[ Edited ]

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.

Solution
‎09-19-2016 03:43 AM
Respected Advisor
Posts: 4,173

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

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).

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 555 views
  • 0 likes
  • 3 in conversation