Measure in Dimension or Fact Table

View previous topic View next topic Go down

Measure in Dimension or Fact Table

Post  min.emerg on Tue Nov 06, 2012 5:16 am

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.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Measure in Dimension or Fact Table

Post  BoxesAndLines on Wed Nov 07, 2012 10:11 am

Measures go into the fact table.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Measure in Dimension or Fact Table

Post  developerpete on Wed Nov 07, 2012 3:43 pm

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

developerpete

Posts : 5
Join date : 2012-11-05

View user profile

Back to top Go down

Re: Measure in Dimension or Fact Table

Post  Jeff Smith on Wed Nov 07, 2012 5:54 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Measure in Dimension or Fact Table

Post  developerpete on Thu Nov 08, 2012 2:22 pm

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

Posts : 5
Join date : 2012-11-05

View user profile

Back to top Go down

Re: Measure in Dimension or Fact Table

Post  min.emerg on Thu Nov 08, 2012 3:17 pm

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!

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Measure in Dimension or Fact 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