Data Modeling Real Estate Property?

View previous topic View next topic Go down

Data Modeling Real Estate Property?

Post  nba411 on Thu Jun 27, 2013 3:20 am

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.

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  ngalemmo on Thu Jun 27, 2013 3:36 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  nba411 on Thu Jun 27, 2013 3:45 am

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

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  LAndrews on Thu Jun 27, 2013 11:28 am

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  nba411 on Thu Jun 27, 2013 11:44 am

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

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  ngalemmo on Thu Jun 27, 2013 12:47 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Data Modeling Real Estate Property?

Post  nba411 on Thu Jun 27, 2013 3:03 pm

Folks,

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

Until the next time...

nba411

Posts : 8
Join date : 2013-06-27

View user profile

Back to top Go down

Re: Data Modeling Real Estate Property?

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