May 5, 2010 at 2:22 pm
I have a unique situation (at least to me) that is making me second guess my cube design plan. I'm working on a cube for a mall management company who leases retail space (suites) to tenants.
The most difficult issue is that the suites are constantly in flux, changing size to accommodate different tenants. For instance, today's suite #100 might be listed as 1000 sqft and suite #200 listed as 500 sqft (suites are adjacent for this example). Tomorrow the tenant in suite #100 might decide they need more space - so a wall comes down and suite #100 is now 1500 sqft and suite #200 becomes zero sqft. In other cases both #'s 100 and 200 might go to zero and a whole new #100A might get created at 1500 sqft. These changes are already tracked and visible in the business system software. My plan was to list the suites as a dimension in the cube, treating it as a type 2 SCD to record the sqft changes on the day they occurred (only records a change IF a change takes place).
The fact table would list actual MONTHLY rents charged per month per suite (suite as surrogate key to suite dimension). The only other dimension in my example will be a standard date dimension.
Let's assume the suite dimension contains the following records.
Key/Suite#/ChangeDate/Sqft
1/100/20100201/1000
2/100/20100516/1500
And the Rent Fact Table contains the following
SuiteKey/Rent$/YYMMRent
1/1000/201002
1/1000/201003
1/1000/201004
2/1250/201005
2/1500/201006
For illustration purposes, what I am trying to show is that the rent remained $10/sqft for the entire time (I'm calling this the effective rate), even though the size of the suite changed and so did the total rent. The pro-rated $1250 charge in May of 2010 represented a $10/sqft rate on the weighted average sqft for that month (1250). In June, since the tenant occupied the new 1500sqft space for the entire month, the rent was a full $1500.
When I browse the cube, displaying the date and suite dimensions against the rent fact table, I want to make sure not to distort the effecte sqft rate ($10/sqft in this example). I'd like to take the total rent and divide it by the listed sqft in the suite dim. If I do that however, I would divide the May rent by 1500, which would distort the $10/sqft rate (1250 /1500 = $8.33/sqft). Similarly a new lease/rent might start in the middle of the month for a new tenant, and likewise I would not want to take their prorated rent for the month and divide it by the non-prorated sqft value of the suite size.
Also, I need to make sure that if I slice the cube by calendar quarter, year, halfyear, etc, I show the sqft rate as $10/sqft as well.
I think I could simplify things and write some type of weighted sqft factor in the fact table or gross up the rent in the fact table in a separate column, but then I think I'm going to confuse the users as to which sqft is the true sqft. The one in the suite dimension or the column in the fact table?
Though for simplicity in my example I kept the rate/sqft at $10, in reality this could change in the real world, and could be higher or lower than the rent prior to the suite size change. Of course I would want to track the actual rate/sqft.
I keep generating ideas, but all of them seem kludgy to me. I would greatly appreciate some design tips to help overcome this!
Thanks!
May 6, 2010 at 9:09 am
Not sure if this will solve your problem, but you could try adding a few columns to your dim table holding the sq footage. By adding a delta and %usage columns, you could then add a new measure to divide the rent by.
using your example,
Key/Suite#/ChangeDate/Sqft/delta/pctUsed
1/100/20100201/1000/1000/1.00
2/100/20100516/1500/500/0.50
delta == new sqft - old sqft, e.g. 1500 - 1000
pctUsed == day of month / days in month, e.g. 16 / 31
so the new measure (either make it a true column in the table/view or add it as a calc in the data source view), would be like....
iif(sqft == (delta * pctUser), sqft, sqft - (delta * pctUsed))
you then use this new measure to divide the rent to get your per sqft rate.
so for the following types of events, you'd get:
new rental, first of month, new value would be sqft, so rent / sqft == true rate
new rental, 15th of month, new value would be sqft - (delta * pctused) == pro rated rate
increased rental, first of month, new value would be sqft, so rent / sqft == true rate
increased rental, 15th of month, new value would be sqft - (delta * pctused) == pro rated rate
The main issue I see with this is that it could decrease the rate result when people decrease their rented space. e.g. I drop from 1500 to 1000 for the last half of a month, my delta is -500, so 1500 - (-500 * 0.5) = 1750sqft. My rent would have remained at 1500 for the month (as I paid on the first), so my rate would be like $8.50. You could, of course, cater for this with an additional check (iif) where if the delta was negative, use the sqft. Or, this may not be a problem if they actually make pro-rated refunds for this type of event.
Another downside is that you now have a measure in your dimension table. You also need to add more logic to your SDCII logic, to capture the delta when inserting the new record.
HTH, let us know what you end up doing.
Steve.
May 12, 2010 at 11:24 am
Well I don't think you should be adding facts to your dimensions.
Isn't the issue here that your fact table is at the wrong granularity. Clients can change suite size at anytime, and your business system records the size per day, but your trying to hold a fact amount (effective rate) at the monthly level. If you held that at the daily level, then you should be able to calculate an achieved rate at a monthly level strightforwardly.
Whether you call them that, or whether you expose both of them is up to you, but in my experience (retail), if you choose your terms carefully, end users usually understand the difference between the two. (let the end users choose the terms)
In this case effective rate is a non-aggregatable fact. Acheived rate, can be aggregated over any time period or any other dimension hierarchy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply