I was hoping somebody would be able to offer advice in relation to tracking history in a fact table, in particular whether #2 below would be a viable option? I would have thought that #1 would be bad for performance given the volume of records created through history so maybe not best practice?. If anyone could suggest another viable approach that would be great.
Any feedback would be greatly appreciated!
Thanks in advance,
1) Implementing a SCD Type2 approach on the fact table with a FromDate, ToDate & IsActive flag using a the MERGE approach (In know this is common practice with dimensions)
2) Creating a separate Audit table and logging the actual output from the MERGE (i.e. $Action, deleted.col1, deleted.col2...etc) into the Audit table to track history (populating the FromDate & ToDate after using the Insert date of these records perhaps?). I was thinking this could be done for $Action IN (UPDATE,DELETE). This seems more straight forward to me but I could be wrong.