topic Re: Problem with scd2 loader in DIS 4.3 in SAS Data Management

Problem with scd2 loader in DIS 4.3

bkv1958 — Fri, 01 Nov 2013 14:16:21 GMT

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

Re: Problem with scd2 loader in DIS 4.3

LinusH — Fri, 01 Nov 2013 16:37:11 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

Patrick — Fri, 01 Nov 2013 22:03:09 GMT

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

Re: Problem with scd2 loader in DIS 4.3

bkv1958 — Mon, 04 Nov 2013 13:18:14 GMT

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

Re: Problem with scd2 loader in DIS 4.3

bkv1958 — Mon, 04 Nov 2013 13:23:48 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

LinusH — Mon, 04 Nov 2013 13:43:55 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

Patrick — Mon, 04 Nov 2013 21:23:45 GMT

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

Re: Problem with scd2 loader in DIS 4.3

APPereira — Tue, 12 Nov 2013 11:26:29 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

bkv1958 — Tue, 12 Nov 2013 13:02:35 GMT

Thank you, everyone,

Re: Problem with scd2 loader in DIS 4.3

Patrick — Tue, 12 Nov 2013 23:01:19 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

JohnHeaton — Tue, 12 Nov 2013 23:42:27 GMT

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

Re: Problem with scd2 loader in DIS 4.3

Patrick — Wed, 13 Nov 2013 01:31:28 GMT

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.

Re: Problem with scd2 loader in DIS 4.3

JohnHeaton — Wed, 13 Nov 2013 02:14:53 GMT

The SCDII loader does support multiple business keys. The business key is designed to match records which are the same. The detect changes is designed to determine if the attributes have changed and if so create a new record for the same business key. When reading data from a database unless you use the order by syntax for SQL you are not guaranteed to retrieve the data in any particular order. It will depend where the database starts to read the data from the disk. So if you have multiple changes to data over a day, you need to specific an order by or sort to ensure the information is retrieve in the order the changes were applied.

Scenario

I have a customer C1, they have an address. We build a dimension to track customer address changes.

In a day they may be multiple updated to the customer information. The data you send to the data warehouse may have all the change records. In this scenario you business key which is the customer key in the source will be duplicated. The change columns of address will have different values. When extracting the data if you order the data on change date then you will get the chronological order records have changed.

Setting the Option Allow Multiple updates per day will allow the SCDII to process all the change records. In this way you will receive multiple records within a load which have been end dated and are not the current record. If this is not set then then the SCDII will only load the initial update and ignore the rest of the updates.

A lot of our data sets have multiple changes within a load period and thr SCDII supports it. We even use the SCDII transform for SCDI dimensions as if we ever wish to change we can reconfigure the transform without rewriting the Job.

Hope this helps

Cheers

John

Re: Problem with scd2 loader in DIS 4.3

Patrick — Wed, 13 Nov 2013 06:00:10 GMT

What you describe works BUT this is undocumented behaviour and you have therefore zero guarantee that it won't change in the future.

The option "Allow Multiple updates per day" is meant for multiple intraday loads using DATE instead of DATETIME values. With date values you can't really expire more than one record with the same business key more than once a day. This option is in my understanding mainly there to deal nicely with cases where there are multiple rows with the same business key in source. If you examine the code generated you will see that only the first row of these multiples will get used.

I also believe that there is an issue if you would run several loads with the same business key and valid_from with date values (not datetime).

I understand what you're doing and I can see in the generated code why this is working. My point is: It is nowhere mentioned in the documentation that the SCD 2 loader is designed for loading multiple rows with the same business key in one load. The loader is designed for dealing nicely with such cases but it doesn't give you any option to add additional sort columns to the business key.

What you will find in the generated code are things like "set work.etls_source; by <business key>;" What you would need to find in the code to be certain is "by <business key> <additional sort variables>;" - and for this you would need to be able to define these additional sort variables somewhere.

I've used DIS4.6 to generate the code and found even a Proc Sort - even though I selected "no" in the option for sorting the source data set.

For what you're doing: Make sure that option SORTEQUALS is set. SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Question is: Should you use an undocumented behaviour in a production implementation?

Re: Problem with scd2 loader in DIS 4.3

JohnHeaton — Wed, 13 Nov 2013 06:31:11 GMT

Patrick

Thanks for the information. That is interesting. I never really look at the code as I do not has a SAS programming background. It seems we are getting around this behaviour using the extract transform and extracting the data sorted by the business key and the datetime column. We are also not using a source column as an input to the start and end date of the record in the dimension. These WHO columns which are date are maintained and managed by DIS 4.5 in our case.

Then selecting create a view on the Extract physical properties. A PROC SQL statement is created which is ordered correctly ensuring the data into the SCD II transform is in the correct order.

create view work.W217J6G as

select

Agency_Resign_Date,

Business_Key,

Chnl_CD,

Chnl_LDesc,

from &SYSLAST

order by

Business_Key,

Agency_Resign_Date

;

The code seems stable. With regards to using undocumented features from release to release, I guess it depends on the feature and how it affects the process. We have isolated our SCD II Jobs in our ETL process to very simple jobs, i.e data is staged before the SCDII Job and then a wholesale replace of our dimension after the job. This way if the functionality changes it is very isolated and once fixed all functionality down stream will work.

In situations like this I do not see much different between undocumented (not specifically stated features) capabilities and potential new features you would want to adopt in a new release. Both will cause some rework and refinement of your ETL process.

Cheers John

Re: Problem with scd2 loader in DIS 4.3

Patrick — Wed, 13 Nov 2013 09:25:55 GMT

Hi John

I wouldn't "jump" that much if this would just be an undocumented feature in the sense of a parameter or switch which is not documented. In your case it's more about what code the SCD 2 transformation generates which allows for your approach to work.

I actually don't believe that the code generation will change in the near future in a way that your approach won't work anymore. So it's more of a principal question about which I'm having a very strong opinion:

If something is not documented you can't use it for a production implementation. If you're using it anyway then you can't expect the vendor to support you should it fail in the future, so you're adding risk to what you're implementing. You need to clearly identify this risk and to communicate it to the application owner. If you're not doing this and something fails then I would blame the application designer.

I totally understand the business reason for what you're doing and it's also much better from a performance perspective than splitting the source data set and loading it in several batches. Looking at the code generated I also believe it wouldn't be too hard to give us an option where we can define additional sort variables for loading duplicate business keys in one go. I'll create an idea for this. Vote for it if you agree.

Cheers

Patrick

Re: Problem with scd2 loader in DIS 4.3

bkv1958 — Tue, 25 Mar 2014 13:07:29 GMT

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

Re: Problem with scd2 loader in DIS 4.3

anna_holland — Tue, 25 Mar 2014 14:08:59 GMT

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

Re: Problem with scd2 loader in DIS 4.3

bkv1958 — Sat, 26 Apr 2014 17:07:58 GMT

Hi, please mark no. 2.

Thanks