Property Rental Model

[steerpike]

I am developing a dimensional model for a property rental business, and have an issue relating to how to best model the rental charges.

We have a DimProperty, that's straightforward, and for each property there is a rental charge. If it were very simple we could make the charge an attribute of DimProperty, and treat it as a Type 2 that can change over time.

However, the charge has many attributes itself, and can be made up of several components (eg Rent Charge, Service Charge, Heating Charge etc), each of which will change over time.

The main fact table will store the transactions, such as charges raised and payments received, and would include the DimProperty FK (amongst other keys).

So, it seems to me that we need a DimRentalCharge, but this does not join to the transaction fact table - rather it logically has a one to many join from DimProperty, which starts to look like a snowflake model, which we want to avoid.

Any thoughts on how best to model this?

Is it correct to treat the charge as a dimension? and is it a genuine case where an outrigger dimension cannot be avoided?

Thanks

[ron.dunn]

Does the list of components (ie, rent, service, heating) stay constant over time, or do you need to vary the list?

[steerpike]

That list can change over time also - a new type of charge could be created at any point

Thanks

[ron.dunn]

My first thought is that your transactions should be entered by component.

Your fact table might be something like:

... date_id, property_id, component_id, amount ...

What objections might there be to that approach?

[steerpike]

Thanks for your reply

Yes, that is how the fact table would be structured, and there's no problem with that.

The issue is that the user may want to ask a question not about the actual transactions raised, but about what charges were active at a point in time. Eg, show me the charges and charge types (and other attributes that relate to charge) that applied to these properties on 01-01-2014.

That's why I was thinking of a separate dimension table for the charges, but it would have a 1-many relationship with DimProperty.

[ngalemmo]

Charges does not have a relationship with properties. Charges has a relationship to the fact. The fact just so happens to also have a relationship to property. Why should that be an issue?

[steerpike]

Yes charges does have a relationship with the fact, but it does also have a relation ship with Property. Each property can have a number of different charge types and amounts, effective over different time periods, including in the future.

A legitimate question for example would be 'what income can I expect from these properties in the financial year 2015/16?'. The fact table would not have the answer as the charges populate the fact table only once they have been raised. The only way to find the answer is to use a charges table, which I've assumed is a dimension.

I think this is similar to a product datamart scenario, ie where each product could have a price that varyies price over time. I'm sure I have seen this described in one of the Kimble books (but I can't find it now of course!) with the recommendation that Product Price is created as a dimension.

[ngalemmo]

The possible charges a property may have is an operational issue for billing. If you are modeling contractual arrangements, it would be represented by a fact table (essentially a budget fact).

[BoxesAndLines]

I would store the rental prices in the property dimension. The fact that you have many components is irrelevant. They are all dependent on the property.

[nick_white]

Hi - if I have understood this correctly, you have what has actually been charged (the transactions) and what is effectively a "price list" of charges that could be applied to properties.
If you want to answer questions such as "what income can I expect from these properties in the financial year 2015/16?" using your price list of charges then these charges are measures and therefore these need to be in a "price list" fact table

Regards

[steerpike]

Thanks all for replies

Nick_white explains it well, it is like a price list, so I agree it should be a fact table.

It maybe could have been held as a Type 2 attribute in the dimension, but due to the number of changes, and the large number of other Type 2 attributes in the dimension, the number of rows per property would explode.

Thanks

[Sponsored content]