6BUIS001W: Business Intelligence

Business Intelligence

Table of Contents
Task – 1 Business Processes................................................................................................................1
a) Identify the main Business Processes and their logical schema............................................2
b) For each Business Processes identify the involved dimensions.............................................4
c) Business Process identify the main measures........................................................................7
d) Single Dimensional Schema..................................................................................................10
e) UML Class Diagrams............................................................................................................12
f) Data Mart...............................................................................................................................13
Task – 2 OLAP queries.....................................................................................................................14
a) In 2017, for each state and month, analyse the portion of rooms which are reserved, free,
and unavailable?............................................................................................................................14
b) In 2005, for each state and month, analyse the income of 4-star hotels and the cumulative
income of 4-star hotels?.................................................................................................................15
References..........................................................................................................................................17
1

Task – 1 Business Processes
a) Identify the main Business Processes and their logical schema
The Identified business process are listed in below.
For Features Table,
Attributes
Feature Id
Feature Description
The features table has two attributes like feature ID and feature description. This table
is used to provide and store the information about the hotel room booking features. Here, the
feature ID is the primary key.
For Time Table,
Attributes
Time ID
Day
Month
Holiday
Year
The time table has five attributes such as Time ID, day, month, holiday and year. This
table is used to provide and store the information about the hotel room booking timing
details. Here, the Time ID is the primary key.
For Hotel Table,
Attributes
Hotel Id
Hotel name
Category
City
State
Region
Room ID
The Hotel table has six attributes such as hotel ID, hotel name, category, city, state
and region. This table is used to store and provide the information about the hotel room
bookings. Here, the Hotel ID is the primary key and Room ID is the foreign key.
For Room Table,
Attributes
2

Room ID
Feature ID
Time Id
Hotel id
Bookings ID
The room table has five attributes such as Room ID, features ID, time ID, Hotel ID
and bookings ID. This table is used to store and provide the information about the hotel room
details based on hotel, features, and time and bookings tables. Here, the Room ID, features
ID, time ID and Hotel IDs are Primary key and bookings ID is foreign key.
For Bookings Table,
Attributes
Bookings ID
Room ID
Feature ID
Time Id
Hotel id
Status
Expected Income
Checkouts ID
The bookings table has seven attributes such as Room ID, feature ID, time ID, hotel
ID, status, expected income and Checkouts ID. This table is used to provide and store the
information about the room bookings details. Here, the Bookings ID, Room ID, features ID,
time ID and Hotel IDs are Primary key and checkouts ID is foreign key.
For Checkouts Table,
Attributes
Room ID
Feature ID
Time Id
Hotel id
Payment Type
Income
Bookings ID
The checkouts table has six attributes such as Room ID, feature ID, time ID, hotel ID,
payment type and income. This table is used to provide and store the information about the
room bookings details. Here, the Checkouts ID, Room ID, features ID, time ID and Hotel IDs
are Primary key and Bookings ID is foreign key.
3