Data Modeling Real Estate Property?

[nba411]

If I have a relational database that holds real estate property information and I want to create a dimensional model to track occupancy by day and aggregate up to year, how would I model that? Properties are tracked across many states. Do I need to create a separate dimension for location and property or should I add the location information as an attribute of property?

For example, I was thinking:

Location_Dim:
State
City
Zip
Sub Division
Street

Property_dim:
Number of rooms
Sq Ft
Baths
property_type


Time:
Day
Week
Month
Year


Appreciate any help! Thanks.

[ngalemmo]

I would use two dimensions. It is more along the lines of how the business views its business.

[nba411]

Thanks for the prompt reply.

To follow up, if the users asks for property occupied by month by state and by amount of beds. Is it possible to create a fact that references two columns within one dimension. This is different than a multivalued dimension Kimball describes which is when a fact references two rows within a dimension. My thought is that in this case, I would need to have a seperate dimension for every factor ("by") the user needs the data divided in (time, location, and property detail).

[LAndrews]

I'm not sure I understand your question.

Each fact record should reference 1 row within the dimension. once that is established, the query can filter based on any number of attributes within the dimension.

When creating the fact record, the surrogate key lookup on the dimension is performed base on the natural/business key of the dimension ... this may be based on 1 or more columns.

[nba411]

My question is can I filter on two or more of the dimensions attributes at one time for a single fact? If a dimension has the following attributes:

Dim 1:
dim_1_key
Type
Location
color

and the fact looks like...

fact:
dim_1_key
some_qty

and I want to look at some_qty filtered by type and location. Can I filter on type and location if they are in the same dimension? Or am I incorrectly making the dimension by adding those two attributes in the same dimension? Should I be creating a separate dimension, for say, location in this case if my requirement is to filter on those two specific attributes?

Thanks all for the help. Trying to wrap my head around this dimensional thing...

[ngalemmo]

Yes, you can filter on as many attributes as you want. Just join the fact to whichever dimensions you need for the query.

[nba411]

Folks,

Thanks very much for the help. Appreciate you taking the time to help a newbie.

Until the next time...

[Sponsored content]