Measure in Dimension or Fact Table

Hello

I'm modeling the gross lettable area (GLA) of suites. I am going to use a periodic snapshot to accomplish this, where each suite will be loaded into the fact table and linked to a month in the Calendar dimension every month.

My understanding is the GLA is an attribute of the suite, and as such should form part of the suite dimension. However, the user will want to aggregate this value. Should I store the GLA on the suite dimension (the periodic snapshot table will turn out to be a factless fact in this case I think), on the fact table, or both? The suite dimension will be slowly changing (of type 2) because the GLA can change from month to month.

Thanks.

Measures go into the fact table.

Hi, what causes the GLA to change? Can part of the lettable area be in use and not available causing the GLA for that point and time to different than a suite's full capacity?
If that's the case, why not do this:
I was thinking that I'd put CAPACITY_GLA in the suite dimension..
And have measures GLA_FREE and GLA_USED in the fact where GLA_FREE + GLA_USED = CAPACITY_GLA. this would allow you to know the % of full capacity at a given time.

My idea and $1 will buy you a sweet tea at McDonalds.. hope this was helpful
MDM

It's a measure in the Suite Fact Table where the grain of the table is the suite. You can aggregate the square footage to calculate the total square footage of a building or a type of suite, etc.

But if the fact table is not at the suite level, then it's an attribute. There are a couple of different ways to handle it. One, put it on the Suite dimension. Or it can be a deginerate dimension on a fact table. But a better solution might be to create a seperate dimension table that rolls up the square footage into ranges. Someone might want a report on the number of suites by a range of Sq Ft; LT 500, 500 tp 750, 750 - 100o, etc. Someone might want to know the frequency of suites being occupied by range.

Sq Ft seems like customer income. It might be good to know the total income of a store's customers. But you also might want to know the income of the customers buying certain types of products.

i just found this in the Kimball reader.. the ideas seem to apply for this post

http://www.kimballgroup.com/2007/12/11/design-tip-97-modeling-data-as-both-a-fact-and-dimension-attribute/

developerpete wrote:i just found this in the Kimball reader.. the ideas seem to apply for this post

http://www.kimballgroup.com/2007/12/11/design-tip-97-modeling-data-as-both-a-fact-and-dimension-attribute/

Awesome, thanks developerpete!