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

Static Facts on Dimension Table?

3 posters

Go down

Static Facts on Dimension Table? Empty Static Facts on Dimension Table?

Post  Guest Thu Jan 13, 2011 6:24 pm

I have basic dimensional modeling question. I would like to know if anyone can cite issues with putting a static fact on a dimension table vs. putting it on a fact table.

The example I'll use is that of an 'asset' dimension table and a 'month-end' fact table, where there are several asset related values that are set and don't change when the asset is purchased; original appraised value, for example. The month-end fact table is keyed by month and holds balances, not transactions, and therefore is non-additive across time. It is also already very wide, so I'd like not to put extra columns on there if I don't need to. I could store the original appraised value on the dimension once, or I could add it to the fact table but would then have to load the same value for every monthly period. Any pros/cons of doing it one way over the other?





Guest
Guest


Back to top Go down

Static Facts on Dimension Table? Empty Re: Static Facts on Dimension Table?

Post  LAndrews Thu Jan 13, 2011 6:50 pm


Should be no problem placing that value on the dimension table. It's considered a descriptive attribute of the asset, no different that weight, width etc .

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

Static Facts on Dimension Table? Empty Re: Static Facts on Dimension Table?

Post  BoxesAndLines Fri Jan 14, 2011 12:24 pm

A downside is your building a 1-1 relationship with the fact table. Those joins usually perform badly. If the value is not frequently queried, you maybe OK.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Static Facts on Dimension Table? Empty Re: Static Facts on Dimension Table?

Post  Guest Fri Jan 14, 2011 12:33 pm

Don't follow. The one dimension row still has many fact rows, one for every period and combination of other dimensional keys on fact table. My question is more to the point of having a value on the dimension that might be used as a fact, say in a crosstab, etc. One possible downside I've thought of is that reporting tools and cubes tend to want to treat values from dimensions as attributes and if you want to display the dimensional metric in the fact part of the crosstab it doesn't quite work, or requires changing your reporting model to make the dimensional metric look like it is coming from the fact table.

Guest
Guest


Back to top Go down

Static Facts on Dimension Table? Empty Re: Static Facts on Dimension Table?

Post  Jeff Smith Fri Jan 14, 2011 2:25 pm

I think it should be on the dimension. It is a very slowly changing dimension.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Static Facts on Dimension Table? Empty Re: Static Facts on Dimension Table?

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