Static Facts on Dimension Table?
3 posters
Page 1 of 1
Static Facts on Dimension Table?
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?
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
Re: Static Facts on Dimension Table?
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
Re: Static Facts on Dimension Table?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Static Facts on Dimension Table?
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
Re: Static Facts on Dimension Table?
I think it should be on the dimension. It is a very slowly changing dimension.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» Static facts in dimensions?
» Measures in Dimension?
» Multiple Facts or Single Facts and Status Table?
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» Can a dimension table directly link to another dimension table?
» Measures in Dimension?
» Multiple Facts or Single Facts and Status Table?
» Pros and cons of consolidated dimension table Vs. many dimension table ?
» Can a dimension table directly link to another dimension table?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum