Should the informational columns be on the fact or dimension

[vanamali]

I have some textual, numeric information only columns on a policy. Should I put it in a policy fact or a policy dimension

[BoxesAndLines]

Are they columns you add or sum? Put those in the fact. Everything else goes into dimensions.

[vanamali]

They are not additive.

They are just for information purpose on the reports. Users will not even search based on those columns.

[cjtravis]

Information that adds context and is not additive (as mentioned earlier) belongs on the dimension(s).

[vanamali]

Thank you.

Is it same if the contextual information is about the transaction (e.g. Invoice Type for Invoice Transaction')

[cjtravis]

Yes, those are common attributes that describe the transaction, which belong on a dimension. I recommend you look into Degenerate and Junk dimensions to help you if you feel you have attributes that help describe a fact, but may not belong on a standard dimension.

[vanamali]

Cool! Thank you very much for the answer and the tip.

[simmo2013]

I would agree to use a degenerate dimension if they attribute is going to be something like order# or PO#, if you use Junk or any other dimension then you have to maintain a dimension table the size of the fact table!

[ngalemmo]

That is generally not the case. If you set up junk dimensions correctly, they are usually orders of magnitude smaller than the fact table. High cardinality values, such as order #, belong as degenerate dimensions, as you have stated.

The only issue is free-form descriptive text. Such data should be in its own junk dimension, with one row for each unique string. I tend not to worry about the cardinality of such a dimension as its use would be minimal and on a very small subset of facts, if used at all. If you have an application that requires frequent text searches, there are additional structures you would implement to support that more effectively. The other thing, if these descriptions are entered by an employee, they tend to fall into patterns. You often see the same descriptions repeated or left blank, so there are usually far fewer unique descriptions than the number of facts.

[Sponsored content]