Fact table structure

[daredevil]

I am pretty knew to the matter and need some guidance to the below.
I have been of the view that facts shouldn't contain any descriptive information. But, pretty recently i switched companies and found that we have a scenario where facts in the area had some information like product type, some item codes in addition to item ids, a field called description. I would assume that this would be incorrect modelling. Could some one confirm.

Can facts have record created dates at the end?

[daredevil]

If possible please let me know the drawbacks for the same

[meb97me]

Assuming your fact table is capturing events that involve products it would be usual to have the product information such as product type, items codes etc as you mentioned all stored in seperate fields in the product dimension table and the fact table only store the product surrogate key which would point to the appropriate row in said product dimension table

drawbacks would be that incosistant/incomaptible information ie being held in the the same field so you wouldn't be able to report on it in your cube ie you couldn't filter your cube on just products of a certain type if this inforamtion is just being stored in a general description field along with other information.

HTH

[ngalemmo]

In a dimensional design it is, plain and simple, fundimentally wrong.

The purpose of a dimension is to provide context for facts. And of all dimensions, the most critical ones for providing context are the conforming dimensions, and Product is most definiately one of those. If I have multiple fact tables that have product as a dimension, I want to be absolutely sure that every fact table has all the information about that product associated to it. The only way to do that in a dimensional model is to place all attributes about the product in the dimension table.

There is also performance issues. A fact table is usually orders of magnitude larger that a dimension table. You want a fact table that is as thin as possible so queries that process large number of rows can execute efficiently. Bulking up a fact table with descriptions and other attributes just slows the whole thing down.

[daredevil]

Thanks. That helps. We would consider flags such as Y/N as dimensional information too right. Or can such be in facts?
Also, do we have row created dates/timestamps in facts? Thanks again.

[ngalemmo]

Flags, in general, go into dimensions, but there are exceptions... If you are implementing an accumulating snapshot fact table, it is common to include a current flag. However, that flag applies to the fact itself and has nothing to do with a dimension.

As far as insert and update dates go, sure... they go on all the tables. Again, it is because the context of those values relate to the row itself.

The other exeception are degenerate dimensions. These are situations where the entire dimension is the value itself and has no other attributes associated with it. It is commonly used for document numbers, such as order number or check number, where attributes relating to those documents are retained in other dimensions (with FKs off the fact).

[Sponsored content]