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

Problem with scd2 loader in DIS 4.3

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Problem with scd2 loader in DIS 4.3

Hi. I hope some can give me som help on the:

I use the scd2 loader in DIS and it seems to work for me except on one thing.  Closed out rows get VALID_TO_DTTM equal to VALID_FROM_DTT on the same row. I expect it to be equal VALID_FROM_DTTM on the next row for the same bussiness key.

Whe the job runs, the target table is empty, and the input data set contains multiple records on the same bussiness key

Greetings,

Bjorn


Accepted Solutions
Solution
3 weeks ago
Respected Advisor
Posts: 3,901

Re: Problem with scd2 loader in DIS 4.3

What does guarantee that the SCD2 loader maintains the sort order of the source (also in future releases). I still believe that loading duplicate business keys is not something the SCD2 loader is designed for.

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Problem with scd2 loader in DIS 4.3

Seeing some sample input data and result might help.

But maybe "and the input data set contains multiple records on the same business key" is the key :smileyalert: to your problem. SCD2 loader doesn't (unfortunately) check changes between records in the input data.

Data never sleeps
Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

Yes, I have multiple records on the same business key in the source data set. Seems like I cnnot use the scd2 loader.

Super User
Posts: 5,260

Re: Problem with scd2 loader in DIS 4.3

First, the type 2 loader does not compare values between records in the source, just the first from the source and the current (if any) in the target. If you want that to happen, you need to build that logic on your own prior in the flow to the loader.

Again, some sample input and output data would help, and the settings on Change Tracking tab.

Data never sleeps
Respected Advisor
Posts: 3,901

Re: Problem with scd2 loader in DIS 4.3

The business key must make up a primary (composite) key in your source data. You should not have multiple records per business key in one load. You also shouldn't have 2 loads with the same value for "valid_from_dttm".

A type 2 table is intended to store history. But what's the history of 2 records with the same business key loaded at the same time? The SCD Type 2 loader still loads such records but you don't have control over the order in which they get loaded (in a single load) and which record with duplicate business key will be the last and therefore the current one.

"I expect it to be equal VALID_FROM_DTTM on the next row for the same bussiness key"  The way the loader works it's actually "VALID_FROM_DTTM minus 1 second".

Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

Patrick, I do have multiple records with the same bussiness key in one load.  I cannot see how to avoid that. I do  not use datetime() to set valid_from_dttm, but a datetime field in the input record. Actually when the record was created/modified in the source system.

Respected Advisor
Posts: 3,901

Re: Problem with scd2 loader in DIS 4.3

"I do  not use datetime() to set valid_from_dttm, but a datetime field in the input record. Actually when the record was created/modified in the source system."

That's not how the SCD Type 2 loader works. You need a constant value per load. If there are no intraday loads then I'm normally using a datetime value "beginning of the day" either derived from the date of the source data or based on datetime().

If you want to add the SCD Type 2 loader to add duplicates in a sequence based on another field like your source system load datetime then you need to split up your source data and call the SCD2 loader multiple times. I then would in each iteration pass a value to valid_from_dttm which is the value from the last iteration plus 1 second.

N/A
Posts: 1

Re: Problem with scd2 loader in DIS 4.3

From my understanding about your problem, you want to close out records with a singular load for more than one record with the same business key. I had the same issue and the way i solved

On SCD Type 2 Loader.

Tab "Change Tracking":

- on VALID_FROM_DTTM put DATETIME() function;

- on VALID_TO_DTTM put a date wich seems adequate for you, like '01JAN5999:00:00:00'DT;

Tab "Mappings":

- Map the column VALID_FROM_DTTM with the field from your input file.

Tab "Options/SCD":

- Allow Multiple Updates per Day - "YES"

Hope it is usefull.

APPereira.

Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

Thank you, everyone,

Respected Advisor
Posts: 3,901

Re: Problem with scd2 loader in DIS 4.3

So for us to learn something: How did you solve the problem.

Also: If your question is answered could you please mark the responses with were helpful and - if any - the correct answer - and mark the thread as answered.

Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

I tried to mark helpfull answers, but next time I look at the thread they are not marked.

Also I cannot figure out how to set my question to Answered

SAS Employee
Posts: 25

Re: Problem with scd2 loader in DIS 4.3

Hi bkv1958, Typically, you are only allowed to mark one answer as correct. Since there are a number of "helpful" answers, it may not let you mark one as correct. Please tell which answer was most helpful, and I will mark it as "correct" on your behalf. Thanks! Anna-Marie

Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

Hi, please mark no. 2.

Thanks

Occasional Contributor
Posts: 6

Re: Problem with scd2 loader in DIS 4.3

I would just add one additional step to the comments made by .  Before the SCDII loader in your extract transform ensure you are sorting the data under the order by tab so that the information is consistently sorted in the correct order to apply the changes to the SCD II.  This is important as many database engines do not guarantee the way data is read and presented in a non sorted data set.

If this is not done then your SCD II records may not be applied in the correct order causing the active record not to be the current record.

Cheers

John

Solution
3 weeks ago
Respected Advisor
Posts: 3,901

Re: Problem with scd2 loader in DIS 4.3

What does guarantee that the SCD2 loader maintains the sort order of the source (also in future releases). I still believe that loading duplicate business keys is not something the SCD2 loader is designed for.

☑ This topic is solved.

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

Discussion stats
  • 18 replies
  • 2041 views
  • 10 likes
  • 6 in conversation