Recently I found a dimension table which had both attributes and measures. The reason for putting the measures in there was because they had the same grain as the dimension. I was thinking: was there a good reason why they should separate the measures into their own fact table? Several things that popped into my mind were:
- Was any “date” part of the grain? If so there could be an argument to break half of it (the measure columns) into a fact table, as this means there would be 2 dim key columns in the fact table: the main dim key and the date key (not only 1).
- There could be an advantage for ETL: we could truncate-reload the measures without affecting the dimension.
- And vice versa, we could treat the attribute as, for example, SCD 2 attributes, without affecting the measures.
As usual I welcome comments and discussion at email@example.com. Vincent 16/3/11.