Comprehensive Business Intelligence Project: Hotel Data Analysis

This project focuses on analyzing hotel data using business intelligence techniques. It begins by identifying key business processes and their logical schemas, including tables for features, time, hotels, rooms, bookings, and checkouts. The project then identifies dimensions and measures for each process, culminating in the design of a single dimensional schema. UML class diagrams are used to visualize the database structure, followed by the creation of a data mart for efficient data access. The project includes OLAP queries to analyze room availability and income, demonstrating the practical application of business intelligence principles. SQL queries are used to analyze room reservations, availability, and income based on specific criteria. The project provides a comprehensive overview of data modeling, database design, and data analysis within a business context.

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

b) For each Business Processes identify the involved dimensions
The Identified business process with dimensions are listed in below.
For Features Table,
Attributes Data Types Key
Feature Id Integer Primary Key
Feature Description VARCHAR (45) -
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. There is no foreign key.
For Time Table,
Attributes Data Types Key
Time ID Integer Primary Key
Day VARCHAR (45) -
Month VARCHAR (45)
Holiday VARCHAR (45)
Year DATE
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. There is no foreign key.
For Hotel Table,
4

Attributes Data Types Key
Hotel Id Integer Primary Key
Hotel name VARCHAR (45) -
Category VARCHAR (45)
City VARCHAR (45)
State VARCHAR (45)
Region VARCHAR (45)
Room ID INT Foreign Key
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 Data Types Key
Room ID Integer Primary Key
5

Feature ID Integer Primary Key
Time Id Integer Primary Key
Hotel id Integer -
Bookings ID INT Foreign Key
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 Data Types Key
Bookings ID Integer Primary Key
Room ID Integer Primary Key
Feature ID Integer Primary Key
Time Id Integer Primary Key
Hotel id Integer -
Status VARCHAR (45) -
Expected Income VARCHAR (45) -
Features ID INT Foreign Key
Time ID INT Foreign Key
6

Checkouts ID INT Foreign Key
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 (McLaughlin, 2013),
Attributes Data Types Key
Room ID Integer Primary Key
Feature ID Integer Primary Key
Time Id Integer Primary Key
Hotel id Integer -
Payment Type VARCHAR (45) -
Income VARCHAR (45) -
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.
c) Business Process identify the main measures
Identified main measures are shown below.
Here, the main measures between the relationships is Hotel and Room table.
7

Here, the main measures between the relationships is Room and Bookings table (Surhone,
Timpledon & Marseken, 2010).
Here, the main measures between the relationships is Bookings and Features table.
8

Here, the main measures between the relationships is Bookings and Time table.
Here, the main measures between the relationships is Bookings and checkouts table.
9

d) Single Dimensional Schema
A database is contained at least one tables, and the connections among every one of the
tables in the database is on the whole called the database composition. In spite of the fact that
there are a wide range of pattern plans, databases that question recorded information for the
most part utilize a dimensional outline structure. Dimensional demonstrating in the
workbench is done at both the consistent and physical level. The dimensional demonstrating
ideas apply to both sensible and physical information models. Dimensional displaying adds
another layer to your information models, which work with numerous database director
sellers. The single dimensional schema for hotel database is illustrated as below.
For Bookings,
For Hotel table,
10

For Room Table,
For Time Table,
Features Table,
For checkouts Table,
11