Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Factors: Dimension or Fact?

3 posters

Go down

Factors: Dimension or Fact? Empty Factors: Dimension or Fact?

Post  Guest 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

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

Post  Jeff Smith 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

Back to top Go down

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

Post  BoxesAndLines Tue Jun 12, 2012 12:27 pm

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

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

Back to top Go down

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

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

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

http://aginity.com

Back to top Go down

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

Post  Guest 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

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

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

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

Back to top Go down

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

Post  Guest 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

Factors: Dimension or Fact? Empty Re: Factors: Dimension or Fact?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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