Skip to main content
To homepage To homepage
Question

Slowly changing dimension

  • December 9, 2025
  • 1 reply
  • 15 views
A
Participant

Hi,

I have a question regarding history-enabled tables and slowly changing dimensions (SCDs). In my case, I have a specific store that recently changed its store name. In the transaction table, I want to keep the old store name for all transactions prior to 01.01.2023.

The best solution is probably to introduce a history table with a Slowly Changing Dimension on the store dimension, right?

Currently, my table is incremental, but once a week we perform a full load back to 2021 (a large amount of data). According to the article I read, I should add an additional join similar to the one shown in the picture below. However, in my experience, adding such lookups often makes the execution significantly slower.

What do you think? Do you have any suggestions for a better approach, or is this perhaps already the best solution?
I’m also wondering whether it would help to convert the transaction table (the fact table) to a proper history table instead of keeping it as an incremental table? The dimension table (stores) is a history table.

 

 

1 reply

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+8
  • rory.smith
  • TimeXtender Xpert
  • December 9, 2025

Hi,

store name is an attribute of a store - you want to track the change of attributes over time in the dimension table, not the fact table. In other words, if you make the attributes you want to track over time type 2 you can find the correct name for the moment of the transaction.

To be able to link the name at the time of the transaction, you will always need to look up the surrogate key (DW_Id in this case) to the fact table as your business key is not fine-grained enough due to type 2 introducing multiple records for the same business key.

Lookups are more expensive than no lookups obviously. The question is why you need to full load 5 years of data: old data should really be static (if business processes and line-of-business software is working properly). If you cannot avoid such large loads, then you need to improve your infrastructure to handle the load or accept that there can only be one name for a store (old or current).

I would not set a fact table up to be historical unless a pressing reason appears: type 1 history prevents deletions in the source from deleting records in the DWH, but this means you get more costly cleansing. If you make your facts type 2 historical that will definitely lead to slower reload times as fact tables tend to be large. Some people like doing this by default and some contexts require this for auditing reasons, but I would not choose to implement this without a reason.

 

Terms and ConditionsCookie settingsAccessibility statement