Static Facts on Dimension Table?

View previous topic View next topic Go down

Static Facts on Dimension Table?

Post  troy34 on 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?





troy34

Posts : 3
Join date : 2011-01-13

View user profile

Back to top Go down

Re: Static Facts on Dimension Table?

Post  LAndrews on 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

View user profile

Back to top Go down

Re: Static Facts on Dimension Table?

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

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

View user profile

Back to top Go down

Re: Static Facts on Dimension Table?

Post  troy34 on 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.

troy34

Posts : 3
Join date : 2011-01-13

View user profile

Back to top Go down

Re: Static Facts on Dimension Table?

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

View user profile

Back to top Go down

Re: Static Facts on Dimension Table?

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