Factors: Dimension or Fact?

View previous topic View next topic Go down

Factors: Dimension or Fact?

Post  Guest on Tue Jun 12, 2012 10:13 am

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


Back to top Go down

Re: Factors: Dimension or Fact?

Post  Jeff Smith on Tue Jun 12, 2012 10:40 am

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

View user profile

Back to top Go down

Re: Factors: Dimension or Fact?

Post  BoxesAndLines on Tue Jun 12, 2012 12:27 pm

Put them in a lookup table if they're not needed for reporting.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Factors: Dimension or Fact?

Post  ngalemmo on Tue Jun 12, 2012 8:43 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Factors: Dimension or Fact?

Post  Guest on Wed Jun 13, 2012 3:51 am

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


Back to top Go down

Re: Factors: Dimension or Fact?

Post  BoxesAndLines on Wed Jun 13, 2012 8:54 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Factors: Dimension or Fact?

Post  Guest on Fri Jun 15, 2012 8:28 am

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


Back to top Go down

Re: Factors: Dimension or Fact?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum