FIT3003 Business intelligence and data warehousing - Assignment 2: M-Stay Residential Service
FIT3003 Assignment 2 - S2 2024 (Weight = 40%) Due - Friday, 11 October 2024, 4:30 PM
Version: 3.0 – 17/09/2024
General Information and Submission
o This is an individual assignment.
o Submission method: Submission is online through Moodle.
o Penalty for late submission: 5% deduction for each day.
o Assignment FAQ: There is an Assignment Frequently Asked Questions page set up for
the Assignment 2 on EdStem Forum.
Problem Description
M-Stay is a residential service that offers homestay and rental services to Monash students
and staff around Melbourne. The company has an existing operational database that
maintains and stores all of the business transactions information (e.g. properties, hosts,
listings, booking, etc.) required for the management's daily operation. As the business
grows, M-Stay has decided to build a Data Warehouse to improve their analysis and work
efficiency. However, since the staff at M-Stay have limited Business Intelligence and Data
Warehouse knowledge, they have decided to hire you to design, develop and quickly generate BI reports from a Data Warehouse.
The operational database tables can be found at the MStay account. You can, for example, execute the following query:
select * from MStay.<table_name>;
The data definition of each table in MStay is as follows:
|
Table Name |
Attributes,Data Types and Key Constraints |
Notes |
|
|
REVIEW |
Review_ID |
Number (PK) |
The table stores review information of the related booking order. |
|
Review_Date |
Date |
||
|
Review_Comment |
Varchar2 |
||
|
Booking_ID |
Number (FK) |
||
Booking_ID
BOOKING The table stores booking
Booking_Date
Guest_ID Number
Number (PK)
Date
(FK)
information.
GUEST
LISTING
Guest_ID Number
Booking_Stay_Start_Date
Booking_Duration
Booking_Cost
Booking_Num_Guests
Listing_ID
Date
Number
Number
Number
Number (FK)
The table stores all (PK) guest information.
Guest_Name Varchar2
Listing_ID Number The table stores all (PK) listing information.
Listing_Date
Listing_Title
Listing_Price
Listing_Min_Nights
Listing_Max_Nights
Prop_ID
Type_ID
Host_ID
Varchar2
Number
Number
Number
Number (FK)
Number (FK)
Number
Each listing has one property and one host information.
Date
HOST
HOST_VERIFICA TION
The table stores the verification information between host and
Host_ID
Host_Name
Host_Since
Host_Location
Host_About
Host_Listing_Count
Host_ID
(FK)
Number (PK)
Varchar2
Date
Varchar2
Varchar2
Number
Number (PF)
The table stores all host information.
Channel_ID
Number
(PF)
channel.
CHANNEL Channel_ID Number The table stores the (PK) channel of verification for the Channel_Name Varchar2 hosts.
LISTING_TYPE Type_ID Number The table stores all
PROPERTY
Type_Description
Prop_ID
Prop_Description
Prop_Neighbourhood_Overv iew
Prop_Num_Beds
Prop_Num_Bedrooms
Prop_Num_Bathrooms
Prop_Num_Reviews
(PK) listing types.
Varchar2
Number (PK)
Varchar2
Varchar2
Number
Number
Number
Number
The table stores all property information.
|
Prop_Rating_Location |
Number |
||
|
Prop_Rating_Cleanliness |
Number |
||
|
Prop_Rating_Value |
Number |
||
|
Prop_Average_Rating |
Number |
||
|
PROPERTY_AME NITY |
Prop_ID |
Number (PF) |
The table links property and amenity tables |
|
Amm_ID |
Number (PF) |
||
|
AMENITY |
Amm_ID |
Number (PK) |
The table stores all amenities information |
|
Amm_Description |
Varchar2 |
A. Transformation Stage
The first stage of this assignment is divided into TWO main tasks:.
Design a data warehouse for the above M-Stay database.
You are required to create a data warehouse for the M-Stay database. The management is especially interested in the following indicators:
-
● Number of reviews
-
● Number of listings
-
● Average booking cost
-
● Listing type
-
● Listing time [Month, Year]
-
● Listing season
o (Spring: 9 to 11, Summer: 12 to 2, Autumn: 3 to 5 and Winter: 6 to 8)
● Listing maximum stay duration [short-term: less than 14 nights, medium-term: 14 to 30 nights, long-term: more than 30 nights]
(find appropriate fact measures that can
calculate the average booking cost)
The following is a list of dimension attributes that you should include in your data
warehouse:
-
● Listing price range [low: less than $100, medium: $100 to $200, high: more than $200]
-
● Channels
-
● Booking duration [short-term: less than 30 nights, medium-term: 30 to
90 nights, long-term: more than 90 nights]
-
● Review time [Month, Year]
-
● Booking cost range [low: less than $5000, medium: $5000 to $10000, high: more than $10000]
For the attribute, ensure that it meets the requirements of the range or group specified in your submission, if required in the specification.
- Preparation stage.
Before you start designing the data warehouse, you have to ensure that you have explored the operational database and have done sufficient data cleaning. Once you have done the data cleaning process, you are required to explain what strategies you have taken to explore and clean the data.
The outputs of this task for Report are:
a) If you have done the data cleaning process, explain the strategies you used in this
process (you need to show the SQL to explore the operational database and SQL
of the data cleaning, as well as the screenshot of data before and after data
cleaning).
- Designing the data warehouse by drawing star/snowflake schema.
Design task A:
The star schema for this data warehouse may contains multi-facts. You need to identify the fact measures, dimensions, and attributes of the star/snowflake schema. The following queries might help you to determine the fact measures and dimensions:
-
● How many long-term stay duration listings are listed on Facebook?
-
● How many listings are listed in June 2015?
-
● How many listings are there in summer for an “Entire home/apt” in a medium
price range?
-
● How much is the average booking cost in March 2013?
-
● How many bookings were there for “Private rooms” with a short-term stay
duration in 2015?
-
● How many high-cost bookings were made in April 2014?
-
● How many reviews were given in February 2016?
Note: the star schema you created in Design Task A as the highest level of aggregation
Design task B:
In this assignment, consider the star schema you created in Design Task A as the
highest level of aggregation. The M-Stay company manager wants to implement a
drill-down function to explore more detailed information. Your task is to suggest
several ways to increase the granularity of your fact tables from Design Task A. In
other words, the manager wants to decrease the aggregation level of the fact tables
you created in Design Task A.
The outputs of task A & B for Report are:
-
b) A star/snowflake schema diagrams for design task A (You can use Lucidchart to
draw the star schema).
-
c) List suggestion of increase the granularity of your fact tables for design task B.
2. Implement design task A star/snowflake schema using SQL.
You are required to implement the star/snowflake schema that you have drawn in
design task A. This implies that you need to create the fact and dimension tables in
SQL. The output is a series of SQL statements to perform this task. You will also need
Note:
ORA-01536: space quota exceeded for tablespace ‘TABLE_NAME’, please check your SQL code whether you have properly joined all tables. This issue was mainly caused when you did not do the table join properly as the number of records multiplied during the process.
The outputs of this task for Report are:
a) Screenshots of the table structure you created for Design Task A, including the dimension table and fact tables.
to show that this task has been carried out successfully.
● If your account is full, you will need to drop all of the tables that you have
previously created during the tutorials.
● If you have dropped all tables in your account and you still encounter the
A sample of screenshots of the table structure
B. Data Analytic Stage
Conduct a data analysis using the star schema you created in Design Task A by writing SQL queries to explore the data further. Present your findings in a clear and concise manner, demonstrating your understanding of the dataset and highlighting any noteworthy observations or patterns.
The outputs of this task for Report are
