Version: 3.0 – 17/09/2024

General Information and Submission

• This is an individual assignment.

• Submission method: Submission is online through Moodle.

• Penalty for late submission: 5% deduction for each day.

• 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 maintain es, hosts,

listings, 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 dataAbasde tadblesWcan ebeCfouhnd aat tthepMoStawy acccooundt. Yeourcan, for example,

execute the following query:

select * from MStay.<table_name>;

The data definition of each table in MStay is as follows:

Tab	le Name	Attributes,Data Types and Key Constraints	Notes
REVIEW		Review_ID	Number	The table stores
			(PK)	review information
				of the related
		Review_Date	Date
				booking order.
		Review_Comment	Varchar2
		Booking_ID	Number
			(FK)

BOOKING

Booking_ID

Booking_Date

Number (PK)

Date

The table stores booking

information.

Booking_Stay_Start_Date Date

Booking_Duration Number

Booking_Cost Number

Booking_Num_Guests Number

Listing_ID

Number (FK)

(PK)

GUEST

httGpuesst_:ID//powcodeNru.mcbeor

The table stores all guest information.

m

Guest_Name Varchar2

LISTING

Listing_ID

Listing_Date Listing_Title

Number (PK)

Date

Varchar2

The table stores all listing information. Each listing has one property and one host information.

Listing_Price Number

Listing_Min_Nights Number

Listing_Max_Nights Number

Prop_ID

Number (FK)

Type_ID

Number (FK)

Host_ID Number

		(FK)
HOST	Host_ID	Number (PK)	The table stores all host information.
	Host_Name	Varchar2
	Host_Since	Date
	Host_Location	Varchar2
	Host_About	Varchar2
	Host_Listing_Count	Number
HOST_VERIFICA TION	Host_ID Channel_ID	Number (PF) Number	The table stores the verification information between host and
CHANNEL ht	Channel_ID tps://powcode Channel_Name	Number (PK) r.com Varchar2	The table stores the channel of verification for the hosts.
LISTING_TYPE A	dTydpe_IWD eChat p Type_Description	oNwumbcerod (PK) Varchar2	eThre table stores all listing types.
PROPERTY	Prop_ID Prop_Description Prop_Neighbourhood_Overv iew	Number (PK) Varchar2 Varchar2	The table stores all property information.
	Prop_Num_Beds	Number
	Prop_Num_Bedrooms	Number
	Prop_Num_Bathrooms	Number
	Prop_Num_Reviews	Number

	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 Amm_Description	Number (PK) Varchar2	The table stores all amenities information

A. Transforhmtatptiosn:/S/ptaogwe

coder.com

The first stage of this assignment is divided into TWO main tasks:

1. Design a datAa wdaredhouWse forethCe ahbovae tM-pStaoy dwatacbaose.der 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 (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 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]

   • 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 h

T

a) If you have done the data cleaning process, explain the strategies you used in this process (hyouttnepedsto:/sh/opw othewSQLctoo edxpelorre .thce oopemrational 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