Factors: Dimension or Fact?
3 posters
Page 1 of 1
Factors: Dimension or Fact?
I'm wondering where to put factors (e.g., probabilities) in my data model: Fact table or dimension table? I don't want to sum over them, but I want to multiply some of the facts from my fact table by these factors later on and maybe sum over the result. Which is the better option and why? Thanks!
Guest- Guest
Re: Factors: Dimension or Fact?
I'm wondering... If you put the probabilities in the fact table, wouldn't they essentially be digenerate dimensions? And if you several columns of probabilities, couldn't they be kept in a junk or mini-dimension?
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Factors: Dimension or Fact?
Put them in a lookup table if they're not needed for reporting.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Factors: Dimension or Fact?
Factors are dimensional attributes, not facts. By definition, measures represent magnitudes relating to a business event or state. Dimensions represent context for the measures. A factor is context. It qualifies the measures in fact tables.
Where to put a factor depends on the nature of the factor. It boils down to simple relational concepts. If it is solely dependent on a single dimension, it belongs as an attribute in the dimension table. If the factor depends on a combination of dimensions, it is usually stored as a degenerate dimension value in the fact table itself.
Where to put a factor depends on the nature of the factor. It boils down to simple relational concepts. If it is solely dependent on a single dimension, it belongs as an attribute in the dimension table. If the factor depends on a combination of dimensions, it is usually stored as a degenerate dimension value in the fact table itself.
Re: Factors: Dimension or Fact?
Thanks for your replies; that answers my question. In most cases, we already have a dimension table with the same grain as the fact table, so we don't even have to create a new dimension for the factors.
Guest- Guest
Re: Factors: Dimension or Fact?
starlight wrote:Thanks for your replies; that answers my question. In most cases, we already have a dimension table with the same grain as the fact table, so we don't even have to create a new dimension for the factors.
Uh oh, that's a red flag in your design. One to one relationships with the fact table should be avoided if at all possible.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Factors: Dimension or Fact?
Thanks for the hint. The grain is different in a technical way since we are using pivoted fact tables with different facts in different rows. So a separate dimension seemed to make more sense than a degenerate dimension as part of the fact table.
Guest- Guest
Similar topics
» Dimension Design with intermediate tables between fact and dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|