Business process identification
This is a Real estate agent company and we need a multi-dimensional model developed to better manage the customers’ data for both Homes for Sale & Houses for Rent. These two services are very similar but not exactly the same. The company needs the data on both services stored properly in the database.
Access of data
I worked as an intern in a real estate agent company so the data is accessible. However, they are all in paper work, which is really hard to be categorized and managed.
Dimensional model design
Fact:
- There are two fact tables in this model. One is for renting activities and the other one is for Sales. In each fact table, I have the foreign keys as composite primary key, which have the basic information for both parties and the house/apartment. For example, in the renting fact table, we have Tenant ID, Landlord ID, Location ID, and Type ID.
- There are numerical attributes for fact tables such as Monthly rent, final price. There is no textual fact.
Dimensions:
- The dimension tables for renting fact table are Tenant, Landlord, Renting house Type, Location, Agent, Department and Time. And the dimension tables for sales fact table are Buyer, Seller, Sales House Type, Location, Agent, Department and Time.
- Tables of Location, Time, and Agent and Department are the conformed dimensions because these dimensions are commonly used for both fact tables.
- And also Time table is the Role-playing dimension since it appears once in the renting fact table as “contract signed date” and “Deposit Date” twice in the sales fact table as “contract signed date” and “Inspection date”. Only one physical base Time table will be created and these three “time” will be the date views. And in the future if we need more time attributes in the fact table, we can create different date views from the physical base time table. The view’s attributes will be showed as FK from Time table.
- Currently we are having the situation that there are multiple tenants for each rental. If we allow arbitrary hierarchy we need to create an organization bridge table between the fact and dimension tables. The navigation bridge table contains one record for each pathway from a parent entity to each subsidiary beneath it. So the relationship between Fact and Bridge table and the one between Tenant and Bridge table are both many to one.
- When I was putting manager as an attribute in the Agent dimension, I realized that we need a bridge between the fact table and the agent table because a manager is also an agent. The bridge table contains one row for each pathway from a person to any person below him in the hierarchy, plus an additional row for his relationship to himself; in this case it is “# level from parent”. The manager key is also including in the department dimension.
- I attempted to make the House type and Location dimensions as one dimension. However, I found that this may become a rapidly changing monster dimension with continuously revised house type information. So breaking this dimension into two dimensions will manage the rapidly changing or large dimension.
- Each dimension has at least one hierarchy. For example, in the Agent dimension, there is Organization hierarchy (Department, Manger), profession hierarchy (years of experience, certified).
Attributes and keys:
- Each fact table has a composite primary key. For example, renting fact table has Tenant ID and Landlord ID as the composite primary key. And these primary keys are all surrogate key generated by the system.
- Primary key for all conformed dimensions are surrogate key as well since this is required.
- Use a semi-intelligent key or use the actual date (YYMMDD) for the time dimensions as a prefix in the surrogate key. For example, Contract signed date key should have actual date of contract signed as prefix.
- Also we have foreign keys in the fact tables which are the primary key from the dimensions. Since these dimensions are conformed dimensions, surrogate key is also required.
- Note that there are several attributes that are conformed facts in Renting and Sales fact tables. For example, parking type is one of the conformed facts.
Grain:
- The fundamental grain is the individual transaction. Therefore, the fact tables are measured at an instantaneous point in time, not over a time span. For example, the renting fact table describes a one-time rental of a house/apartment.
- The grain of time table with calendar information is on date only. However in the future if there is business need to have grain of hour band as a time stamp in the fact table, we can create an attribute that will carry “banded” hours and it will becomes a lower level dimension for the Date dimension in the current Time table.
