I know that a fact table in dimensional data model consists of measures and flags for an event or transaction. I also understand that it may contain date of the event. I have to design on fact table where I need to include one field for snapshot month. this snapshot month will tell that a transaction record is pertaining to which month of ETL load (batch load frequency is monthly). My question is this snapshot month attribute at fact table belongs to which category? it is not measure, metric or flag. It is date of the event , so can I call it as event date. Or it is degenerated dimension from time table?
1 Answer
Time stamp fields belong to the "time dimension" category. This may become clearer by implementing what you described as dimension table EtlLoadMonth with one record per month, and the fact table referencing this dimension table. However, as long as EtlLoadMonth does not contain more than one column, one may decide to save this table and prefer "month" to be directly an attribute of the fact table.
In reality, I would not expect to get huge differences in both design approaches, neither in required space nor in resulting running time.
