Modeling store vacancy

Hello

I am trying to develop a dimensional model to represent store vacancy. There are multiples shopping malls, each comprised of multiple suites which have a total lettable square footage, and a leased square footage that is less than or equal to this. I want to measure the proportion of each suite/store/mall/region that is standing empty at a given time (semi-additive).

My confusion stems from my initial desire to store the square footage values in the 'store' dimension, seeing as both of these (total sqft and leased sqft) change over time. However, these values are effectively what I am trying to measure, and it would seem logical to store these as measures in the fact and not as properties in the dimension.

My worry is compounded by the fact that I may wish to use 'store' as a conformed dimension for other facts where the square footage values will be needed (i.e.: would need to form part of the dimension itself).

Which seems the best approach?

Thanks!

Hi min.emerg,

If I understand your post correctly, my recommendation would be having the total square footage and the current leased square footage as attributes of the SCD 2 Store dimension. Depending on how you want to measure the footage and what other measures you want to use, you can run a report against the dimension or create fact tables.

Thanks,
Alisher
www.streebo.com

Hi Alisher

Thanks for the response. Running a report from the dimension could work, but it would be difficult to do this without SQL/MDX. Because I need to know the vacancy every month, my feeling is that I'll need some sort of periodic snapshot fact table. Storing the vacancy in the dimension and running a report on it would be a bit more complicated because the vacancy measure at every month would not be pre-computed in the fact.

Yes, if running a report from the dimension would be an issue, a monthly snapshot fact table would help. But I would still have the total square footage and the current leased square footage as attributes of the conformed dimension for other facts. The dimension will have more detailed info about the changes in the leased square footage and there will be no need to join fact tables.

Thanks,
Alisher
www.streebo.com

If I store the total and leased square feet in the dimension, would the periodic snapshot fact table be a factless fact? The thing that I am measuring would reside in the dimension.

Thanks again.

Hi,

will there be a case where you may sell a space by combining more than one store ?

thanks

Hi hkandpal

It seems entirely possible at this stage.

Thanks.