Decision Stream: Advanced Issues 1

[Loukas]

Hi, Decision Stream experts
Please assist in any of the following problems:
1. I want to build a dimension where the primary key (business key) will be generated (Max[primary key] + 1) only when new rows are inserted,
2. I am looking for a way to have conditions on UPDATE/INSERT for example:
UPDATE MATERIAL WHEN MATERIAL_LIFE.CREATION_DATE > MATERIAL_SMART.CREATION_DATE

ELSE INSERT NEW RECORD.

I haven't find a way to solve these yet. Please advice...

 

[MattOh]

Hi Loukas,
I can answer your questions, but I want a little more clarity on what you want to do. Both of these questions sound a awful lot like Type 2 Slowly Changing Dimension support, which DecisionStream can handle with build in options. But just to confirm...

First, if you are creating a new dimension record and you want an automatically generated id that is incremented by 1 from the current maximum... that sounds like a surrogate key to me. Is this what you are trying to do?

Second, are you looking for this conditional update/insert on the same dimension table? If so, this sounds like Type 2 slowly changing dimension support, which DS automatically handles too.

Give us a little more information on what you are attempting to accomplish, from a higher level goal perspective. OK?

Matt

 

[Loukas]

Yes, you are right MattOh, I am trying to implement Type 2 Slowly Changing Dimensions but - as i think - in a very particular way.

1. I have a CUSTOMER table (target) which has a primary key (CUSTOMER_ID) and an alternative key CUSTOMER_CODE.
This table is populated from two other tables CUSTOMER_LF AND CUSTOMER_SM (located at the source database). When the CUSTOMER exists in CUSTOMER_LF & in CUSTOMER_SM then his/her details are updated. If a new CUSTOMER_CODE is created in CUSTOMER_LF table then (it means that a new customer has been created) in this case a new CUSTOMER_ID should be generated (maximum CUSTOMER_ID + 1) in CUSTOMER (target) table.

DS does not allow to define CUSTOMER_ID both as a business key and as a surrogate key.

2. I am looking for a conditional UPDATE/INSERT on a different dimension table:
This is for a MATERIAL (target) dimension table.
Again, there are two source tables: MATERIAL_LF & MATERIAL_SM. In case that a material has a creation date in MATERIAL_LF (source) which is > than the creation date this material has in MATERIAL_SM (source) it means that this material is an old material and it's details should be updated, otherwise it is a new material and it should be inserted with a new material_id.

The only way I can think of is to have the CREATION_DATE as a variable... Is this how/where i should define my conditions?

Thank you for your answers ....

 

[MattOh]

Let's take the Customer one first. I'll handle the second question in a separate post.

In Customer_LF and Customer_SM, is Customer_Code the key in your source system? I'm guessing Customer_ID doesn't even exist in your source system tables, is that right?

If so, you don't even need slowly changing dimension support on this dimension. Let me explain what a dimension build does. At the beginning of the build execution, it loads up all members (Customers in this case)into memory. You should have identified one column (e.g. Customer_Code) as the "business key" (i.e. how you find the customer in your source system).

Then it checks the incoming datastream from the source system and checks every incoming row against the members in memory by business key.

If the business key is in both the incoming data source and in memory, the member already exists and the build will, at most, update the existing row on the dimension table.

If the incoming business key does not exist in memory (a new Customer_Code in Customer_LF) then a new row will be inserted into the dimension table. That row will contain the new Customer_Code that was brought in.

If you also specify you want to include a surrogate key on the dimension table, DS will automatically increment the previously largest surrogate key value by 1 and write it out too. I think this is what you want for Customer_ID.

The real point here is, it doesn't sound like Customer_ID exists on your source system at all. If that is true, then it is NOT a business key. Business keys are what users need to find entities in the source systems. Business keys are ALSO what DS uses to determine if a dimensional member already exists in a dimension table in the data mart or whether it needs to add a new member.

I think you're trying to make Customer_ID be a business key when it can't.

Does that help or did I miss something?
Matt

 

[MattOh]

OK, now on the Materials tables. You are reading two data sources in, merging them(?) and delivering to one target dimension table? Only if the dates don't match, you want a new row written out to the table with a new Materials_ID?

This sounds similar to the first problem but with a twist. Again, I'm guessing that Materials_ID is not a business key. How do users find a materials record in the source system? Something like Materials_Code?

I see two ways you can go on this one depending on what you want to accomplish.

First, and easiest, in the template simply declare Material_Code as the business key, Create_Date as a type 2 column in the template, and Materials_ID as a surrogate key.

The second option is that you could create a composite key from Materials_Code and Create_Date and use that as the "business key", then every time DS sees a new combination of Materials_Code and Create_Date, it sees that as a new dimensional member. And, as in the example in the previous posting, if you want DS to create a new "Materials_ID" for you, declare it as a surrogate.

I hope that helps. This is fairly complicated to cover in e-mail.

Good luck,
Matt

 

[Loukas]

Thank you, MattOh! Your answers are really very helpful!
"I'm guessing Customer_ID doesn't even exist in your source system tables, is that right?" Well, this is not the case; in fact the source tables also have CUSTOMER_ID as primary key...

But I found your explanation of how DS works in every stage very helpful and I think this will solve the problem.

Thanks again.
(as a matter of fact I have one more question but I will post it in a new thread ... )