One of our previous clients had a similar business problem, the business wanted to change the SCD type 1 to SCD type 2.
Well if you are trying to model the SCD in such a way that the business wants to see a comparative analysis of current items & history items in a line order then we may need SCD1 to do this kind of analysis.
It’s always a recommended approach to have a flag of validity for a SCD suppose if I have a Item dimension then I would also imbibe a Item_Valid field to do what if analysis for example if the Business wants to do a comparison of the current Year valid items against their previous Year items which may also contain discontinued items then we may need a type 1 SCD & also a Type 2 SCD based on the effective & start dates.
Now when creating a Business Model in a tool if we want to see an Item according to the validity we can use the Oracle Last function to aggregate on a dimension suppose we have an Item Id & a Item Effective date then we would model our business logic in such a way that the Item Effective date is aggregated as last using a Last function.
If possible try to model a flag field for historical validity too this gives more resilience to leverage the reports.
Also It’s pretty easy to model a SCD type 2 and change it to SCD type 1 but the vice versa is not simple
