2

Recently, I came across the concept of Rapidly Changing Dimensions (Mini dimensions).

I understand the part where the fast-changing attributes are removed from the main dimension table and put into a junk dimension (with all possible combinations of the values in those attributes. This junk dimension will be connected to the parent dimension table by an intermediate "bridge-table" (mini dimension) which will contain the parent dimension key and the junk dimension surrogate key (along with start & end dates).

However, I failed to comprehend how it is implemented in real-life.

So, say if an RCD attribute changes, then is the record in the mini dimension (or parent dimension) is updated with the new SK from the junk dim? If yes, then how do we track the history in such a scenario, as we are destructively updating the same record existing in the mini dimension value?

Alternately, if a "new" record is created in the mini dimension (like SCD-2) containing the sk of the new junk dim record, then we are again having the same problem of the size of the mini dimension increasing with time. Also, does the fact hold the ik of only the parent dim or both the parent dim and the junk dim sks?

Can anyone please clarify with an example?

Assume there are 4 tables in the DW model:
1. PAT_DIM is the parent dimension
2. PAT_JNK_DIM is the junk dimension containing the RCD attributes
3. PAT_MINI_DIM is the mini-dim bridge table between 1 & 2 (above). 

PAT_DIM:  
--------  
pat_dim_sk, 
pat_id,
pat_dob,
blood_type

PAT_MINI_DIM:  
------------  
pat_id,
pat_rcd_sk,
start_date,
end_date

PAT_JNK_DIM:  
----------  
pat_rcd_sk,
pat_weight,
pat_bmi

Given the above example can anyone please help me understand how the Rapidly Changing Dimension (RCD) is modeled in the real-world scenario. How are the RCD tables inter-connected in the Data Warehouse.

marie20
  • 723
  • 11
  • 30
  • Personally I don't see a problem with putting attributes in the fact if it's appropriate. Don't get caught building a fact that virtually has a one-one to a dimension just because you think attributes should be in a dimension not a fact. Without giving it too much thought - why would the dimension not be joined directly to the fact like every other dimension? – Nick.Mc Aug 26 '19 at 22:41

2 Answers2

-1

Generally speaking Junk Dimension - consists of low cardinality flags and indicators in Dimensional Model.

In your case, you don't need a bridge table. The mini-dimension should contain the RCD and should be directly joined to the FACT Table.

DB Prasad
  • 785
  • 7
  • 9
  • so when the value of the RCD changes, the new RCD row surrogate key will be updated in the fact table row ? or will a new record be created in the fact table with the new surrogate key ? – marie20 Aug 28 '19 at 15:15
  • SCD Type 4 may be best suited for RCDs.But If you need to implement as SCD type 2 - then there are two options of implementing join between Fact and this RCD. – DB Prasad Sep 07 '19 at 12:24
-1

Depending on the requirement SCD Type 1 or Type 4 may be best suited for RCDs.

But if you need to implement it as SCD type 2 - then there are two options of joining Fact and this RCD and it also dependent on data model design.

Option 1 : If in the design you have DIM SK in FACT table, then you need to self the join the DIM to table to get the latest RCD where the Current Flag is true based on the natural key of DIM row. (Note there is no update to FACT table when RCD is changing).

Option 2: In your design you can create a new key (durable supernatural key /stable Key) for DIM and this key is carried to all the future version of the dim row. In the FACT table you keep the both the SK of DIM and Durable key of the DIM. So when you join can avoid the self join of Dim using the Durable Key and the Current Flag is true to get the latest DIM row. And if you need previous version of DIM row you can just join by the DIM SK.

DB Prasad
  • 785
  • 7
  • 9