Property Rental Model

View previous topic View next topic Go down

Property Rental Model

Post  steerpike on Tue Feb 10, 2015 4:50 am

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

steerpike

Posts : 5
Join date : 2015-02-10

View user profile

Back to top Go down

Re: Property Rental Model

Post  ron.dunn on Wed Feb 11, 2015 5:31 am

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

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Property Rental Model

Post  steerpike on Wed Feb 11, 2015 5:36 am

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

Thanks

steerpike

Posts : 5
Join date : 2015-02-10

View user profile

Back to top Go down

Re: Property Rental Model

Post  ron.dunn on Wed Feb 11, 2015 5:41 am

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?

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Property Rental Model

Post  steerpike on Wed Feb 11, 2015 5:56 am

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.

steerpike

Posts : 5
Join date : 2015-02-10

View user profile

Back to top Go down

Re: Property Rental Model

Post  ngalemmo on Wed Feb 11, 2015 8:50 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Property Rental Model

Post  steerpike on Wed Feb 11, 2015 10:06 am

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.

steerpike

Posts : 5
Join date : 2015-02-10

View user profile

Back to top Go down

Re: Property Rental Model

Post  ngalemmo on Wed Feb 11, 2015 11:08 am

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Property Rental Model

Post  BoxesAndLines on Wed Feb 11, 2015 10:42 pm

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Property Rental Model

Post  nick_white on Thu Feb 12, 2015 3:44 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Property Rental Model

Post  steerpike on Thu Feb 12, 2015 5:13 am

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

steerpike

Posts : 5
Join date : 2015-02-10

View user profile

Back to top Go down

Re: Property Rental Model

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