I am currently modelling Visual Data Lineage at the column level. I am using ER/Studio export to excel to document the ETL specifications for the programmers.
I would like to know what would be best way to document the Slowly Changing Dimensions Type 1 and Type 2. Here's a couple of things I'd like to document:
- Dimension Business Key (can be composite key)- Slowly Changing Dimension Type (1 or 2)- How to set the CURRENT_IND, EFF_DATE and EXPIRY_DATE columns when a new Dimension entry is inserted- How to set the CURRENT_IND, EFF_DATE and EXPIRY_DATE columns when a Type 2 happens (Expire current record, insert a new version)
I am mainly using the Visual Data Lineage.
You might want to consider using a combination of Data Movement Rules and attachments. Data Movement Rules may be best for documenting the slowly changing types to note how your columns are being set.