Here I am going to describe a scenario. In my knowledge we have some definition that can be changed according to scenario. So nothing is thumbs in rule but all are based on Scenario.
How we design Dimension and Fact
We are trying to put all the facts columns (Numerical Columns), where we can use the aggregate fruition into Fact Table and try to put all the description related columns into Dimension Table.
At least theoretically it is correct.
Life is not so Easy
Yes, Life is not so easy when designing a big enterprise. At least based on theoretical definition. Think as a situation of Product Table, where each product have price (Numerical columns) and price is changed according to time.
So where we are going put the information of columns Price. In Fact table as the Price is the numerical. Or in Dimension as Price are changes according to date. I mean to say that within the period of time the price is changed.
I am waiting for your suitable comments.
Hope you like it.
Posted by: MR. JOYDEEP DAS