BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bkv1958
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

18 REPLIES 18
LinusH
Tourmaline | Level 20

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
bkv1958
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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

bkv1958
Calcite | Level 5

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.

Patrick
Opal | Level 21

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

APPereira
Calcite | Level 5

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.

bkv1958
Calcite | Level 5

Thank you, everyone,

Patrick
Opal | Level 21

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.

bkv1958
Calcite | Level 5

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

anna_holland
SAS Employee

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

bkv1958
Calcite | Level 5

Hi, please mark no. 2.

Thanks

JohnHeaton
Calcite | Level 5

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

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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