facts on dimension tables

[SteveCSC]

I have a client with many fact columns on their dimension tables. It is my suggestion to remove them - anyone have comments?

 

[kishleo]

can you tell it clearly? you mean more than one value comming from one dimention into the fact??? ô¿ô Kishore

 

[CanadianTechie]

Steve,

The answer to your question really depends on the client requirements and specific characteristics of the data warehouse/mart or "silo" of data that you are looking at. On the surface, it may seem like an easy answer, since Dimension tables are generally used as the entry points (i.e. defining your slice) into the fact tables as you try and answer specific business questions. If there are multiple dimensions in this DW then yes, facts should be isolated in a fact table and pulled out of the dimension tables, but if on the other hand specific facts are only "relevant" for one dimension, there might be a good reason for some of those decisions. If you start getting into non-additive facts, the water gets murkier...

But your "gut instinct" is a valid one -- without analysing the client's requirements and DW schema in more detail, I can't really comment much further.

Pedro Cardoso
A.K.A CanadianTechie

 

[SteveCSC]

Thank you. Yes the specific facts do apply only to that dimension, and I think I will address them on a case-by-case basis.