Halfway between type 1 and type 2 1

[bootsminimus]

Consider the following:

A store dimension has a type 2 attribute of chain. A chain is higher in the store's hierarchy and has a number of related attributes that have been denormalised and held on the store dimension. It is possible that the store can move chains and for reporting purposes the trading data can be viewed in the chain where the store existed at the time the sale took place. This is as I understand it to be expected for a type 2 dimension. However if any other attributes pertaining to the chain should change then these attributes behave on the store dimension as a type 1 but only going back as far as the store is in the chain. Any earlier than that it takes on all the attributes of the previous chain.

How is this usually handled?

 

[MDXer]

So if a Type 1 change occurs on a member that has had a previous Type 2 change occur you only apply the type 1 changes to the most current record?

 

[RiverGuy]

It sounds to me like not just the current record, but the set of records which share the same chain attribute. What if the previous Chain had an attribute change? (for example, the old chain was Kentucky Friend Chicken, the name of which changed to KFC--modify this regardless if it's the current chain for each store)

I would think you could modify your update statement logic to specify the chain's business key for type 1 changes if you have the chain's business key in your dimension.

For example:

UPDATE dimStore
SET ChainAttribute = <SuppliedAttribute>
WHERE StoreBusinessKey = <SuppliedStoreBusinessKey>
AND ChainBusinessKey = <SuppliedChainBusinessKey>

 

[RiverGuy]

Actually, my reply doesn't make sense in a way. You'd only ever be bringing in the current chain for each store, so you wouldn't be able to change the previous records for the other chain.

In this case, you could disregard the Type 1 changes for Chain in your existing ETL process, and create another process to bring in just Chain data to update any Store records based on the Chain's business key.

 

[Hans63]

As I understand what bootsminimus is trying to say, the situation is a single dimension (including hierarchies) that has attributes that should be handled as SCD type 2 and some attributes that shouls be handled as SCD type 1 (but for a limited duration).
Please correct me if I am wrong.

In such cases I would probably use two ETL streams (at least partway) to handle the changed SCD type 1 attributes and to handle the changed attribues of SCD type 2.

 

[bootsminimus]

I think everyone has got the gist of the problem. I am interested to note that it appears to be an unusual problem. Has nobody had to solve this problem before?

For now the store dimension holds predominately type 1 attributes and the chain number is the only type 2 attribute and there is a chain code and chain name that must behave (as I described in previous post) halfway between a type 1 and type 2. As I understand it I need to find every instance in the dimension where I get a match on chain number (assuming that this number can never be reused - a bit dodgy but alright for now!) and update the code and name to their new values.

Am I on the right track? Is there something that I'm missing? Is there a fundamental flaw in the original request that nobody else has had this issue?

 

[Hans63]

I have encountered this issue before. (although not this special case of SCD type 2). We decided to adress all changes as SCD type 2 at that time.
This was due to the expected extra effort to create 2 types of ETL mappings.

 

[bootsminimus]

Thanks Hans63 for your response. We could try to address this by making all changes SCD type 2 but then any modifications to the chain code and chain name would cause a type 2 change and give the incorrect result. They need to behave as a SCD type 1 change but only affecting rows where the chain number is the same. This is the dilemma.

 

[Hans63]

Yes, treating everything as a SCD type2 shifts the proble to the reporting side, where you have to make adjustments for the additional records.

I think your approach sounds like a workeable solution. Probably would try it myself.

 

[johnherman]

Store chain should be a detail table to Store. The StoreChain table should have effective dates of when the store entered the chain and when it left (and anything else0. You can then track the history of the store across chains, as well as associate the store with the current chain by using a separate join or view which only retrieves the current chain to which a store is assigned.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

Another advantage to the solution I proposed above (Store-Chain as detail/bridge table to store), you can enter Store-chain records which have an effective date in the future, to see what the stores' measures look like in a future or proposed Chain structure.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[bootsminimus]

Thanks johnherman for your input. I must say that we have had to resolve an number of problems using a bridge type solution and I can see that it would work in this situation as well.
You are right about the advantage it gives in providing a proposed store/chain change that's expected in the future.
However, whenever forced to create a bridge I only like to do it as a last resort because it:
1. Adds complexity for the front end tools.
2. Degrades performance.

 

[johnherman]

To improve performance, you could have a periodic (nightly?) process which reads the Store-Chain table and updates the key for the row which is currently in effect into the base dimension. Remember that one of the tenets of DW is to trade CPU cycles from off hours along with disk space to improve performance at demand time(s).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[bootsminimus]

I must say I never saw the bridge table (store-chain) as having to be updated as I would expect the ETL process to add the latest keys to the bridge. However the introduction of another join to get chain information would degrade performance compared to the 1st prize of having the chain data demormalised onto the store dimension. Perhaps not too bad in isolation but it does imply that the solution has to be watched carefully as more and more similar issues are raised as requirements.

 

[johnherman]

I may not have been clear. The Store table would be updated with the key of the Store-Chain table record which is in effect. The Store-Chain table is both a bridge table and an event table, since it stores the lifecycle of a Store across Chains.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright