I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.
My end goal is to be able to do a headcount of employees at the end of the month, therefore I designed my fact table as a monthly snapshot where each end-of-month I will populate my fact table (one row per employee per month).
My dimension table includes a DimEmployee table which is an SCD2 (any change made to an employee's information causes a new row to be introduced in the data source and I am keeping track of the most recent employee information via an is_current flag set to 'Y' or 'N').
My question is: do I need to update the dimensions daily and only insert whatever data I have at end-of-month into the fact table? Or do I update the dimensions end-of-month as well?
Thank you in advance!
Best Answer
You could go either in generally assuming you want to follow best practices of conformed dimensions you would want to update the customer daily so that other fact tables would have the updated information. what you don't want to do is have your previous snapshots updated to point to latest member as facts they are immutable and they should always point to the record as of the snapshot period