Should the informational columns be on the fact or dimension
5 posters
Page 1 of 1
Should the informational columns be on the fact or dimension
I have some textual, numeric information only columns on a policy. Should I put it in a policy fact or a policy dimension
vanamali- Posts : 7
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
Are they columns you add or sum? Put those in the fact. Everything else goes into dimensions.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Should the informational columns be on the fact or dimension
They are not additive.
They are just for information purpose on the reports. Users will not even search based on those columns.
They are just for information purpose on the reports. Users will not even search based on those columns.
vanamali- Posts : 7
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
Information that adds context and is not additive (as mentioned earlier) belongs on the dimension(s).
cjtravis- Posts : 3
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
Thank you.
Is it same if the contextual information is about the transaction (e.g. Invoice Type for Invoice Transaction')
Is it same if the contextual information is about the transaction (e.g. Invoice Type for Invoice Transaction')
vanamali- Posts : 7
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
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.
cjtravis- Posts : 3
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
Cool! Thank you very much for the answer and the tip.
vanamali- Posts : 7
Join date : 2013-10-30
Re: Should the informational columns be on the fact or dimension
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!
simmo2013- Posts : 6
Join date : 2013-11-05
Re: Should the informational columns be on the fact or dimension
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.
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.
![-](https://2img.net/i/empty.gif)
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Number of Columns in Fact Tables vs. Dimension Tables
» Fact with large columns, 320.
» Too many columns in fact table
» SK and ID columns in a Fact table
» Number of Columns in Fact Tables vs. Dimension Tables
» Fact with large columns, 320.
» Too many columns in fact table
» SK and ID columns in a Fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|