The following dashboard for management reporting was published to Power BI Service:
The following report - Reservations Analysis - was created in Power BI Desktop:
The following is the data model within Power BI:
The following dashboard for management reporting was creating in QlikView. It enables the user to filter the data by various dimensions and it shows management summaries through charts as well as reservation listing for hotel management staff:
The following is the data model within QlikView:
Connecting to the database in SQL Server:
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HotelDb;Data Source=JECA-THINK;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=JECA-THINK;Use Encryption for Data=False;Tag with column collation when possible=False];
Loading SQL Server Database Tables:
LOAD Id as CountryId,
Code,
Name as CountryName;
SQL SELECT *
FROM HotelDb.dbo.Country;
STORE Country INTO [$(vQvdFiles)Country.qvd];
LOAD Id as CustomerId,
FirstName,
LastName,
Address as CustomerAddress,
City as CustomerCity,
PostCode as CustomerPostCode,
Phone as CustomerPhone,
DateOfBirth,
Email,
IsMale;
SQL SELECT *
FROM HotelDb.dbo.Customer;
STORE Customer INTO [$(vQvdFiles)Customer.qvd];
LOAD Id as HotelId,
Name as HotelName,
Address as HotelAddress,
PostCode as HotelPostCode,
City as HotelCity,
CountryId,
Phone as HotelPhone;
SQL SELECT *
FROM HotelDb.dbo.Hotel;
STORE Hotel INTO [$(vQvdFiles)Hotel.qvd];
LOAD Id as ReservationId,
ReservationDate,
YEAR(ReservationDate) as Year,
MONTH(ReservationDate) as MonthName,
num(MONTH(ReservationDate)) as Month,
dual('Q'&Ceil(MONTH(ReservationDate)/3),Ceil(MONTH(ReservationDate)/3)) as Quarter,
CustomerId,
RoomId,
NumberGuests,
CheckInDate,
CheckOutDate;
SQL SELECT *
FROM HotelDb.dbo.Reservation;
STORE Reservation INTO [$(vQvdFiles)Reservation.qvd];
LOAD Id as RoomId,
RoomNumber as Number,
RoomTypeId,
HotelId,
IsSmoking;
SQL SELECT *
FROM HotelDb.dbo.Room;
STORE Room INTO [$(vQvdFiles)Room.qvd];
LOAD Id as RoomTypeId,
Name as RoomTypeName,
Description,
RoomPrice as Price,
MaxGuests;
SQL SELECT *
FROM HotelDb.dbo.RoomType;
STORE RoomType INTO [$(vQvdFiles)RoomType.qvd];
Loading qvd Table Files:
LOAD CountryId,
Code,
CountryName
FROM
[$(vQvdFiles)Country.qvd]
(qvd);
LOAD CustomerId,
FirstName,
LastName,
CustomerAddress,
CustomerCity,
CustomerPostCode,
CustomerPhone,
DateOfBirth,
Email,
IsMale
FROM
[$(vQvdFiles)Customer.qvd]
(qvd);
LOAD HotelId,
HotelName,
HotelAddress,
HotelPostCode,
HotelCity,
CountryId,
HotelPhone
FROM
[$(vQvdFiles)Hotel.qvd]
(qvd);
LOAD ReservationId,
ReservationDate,
Year,
MonthName,
Month,
Quarter,
CustomerId,
RoomId,
NumberGuests,
CheckInDate,
CheckOutDate
FROM
[$(vQvdFiles)Reservation.qvd]
(qvd);
LOAD RoomId,
Number,
RoomTypeId,
HotelId,
IsSmoking
FROM
[$(vQvdFiles)Room.qvd]
(qvd);
LOAD RoomTypeId,
RoomTypeName,
Description,
Price,
MaxGuests
FROM
[$(vQvdFiles)RoomType.qvd]
(qvd);
The following dashboard for management reporting was created in Qlik Sense Desktop - Stories:
The following report was created in Qlik Sense Desktop:
The following is the data model within Qlik Sense:
The following table shows the entities and relationships for the databse system:
The following script was executed in SQL Server to create the database system:
CREATE TABLE Country
(
Id INT PRIMARY KEY IDENTITY (1,1),
Code NVARCHAR (10) NOT NULL,
Name NVARCHAR (50) NOT NULL
);
CREATE TABLE Hotel
(
Id INT PRIMARY KEY IDENTITY (1,1),
Name NVARCHAR (50) NOT NULL,
Address NVARCHAR (200) NOT NULL,
PostCode NVARCHAR (10) NOT NULL,
City NVARCHAR (50) NOT NULL,
CountryId INT FOREIGN KEY REFERENCES Country(Id) NOT NULL,
Phone NVARCHAR (10) NOT NULL
);
CREATE TABLE Customer
(
Id INT PRIMARY KEY IDENTITY (1,1),
FirstName NVARCHAR (50) NOT NULL,
LastName NVARCHAR (50) NOT NULL,
Address NVARCHAR (200) NOT NULL,
City NVARCHAR (50) NOT NULL,
PostCode NVARCHAR (10) NOT NULL,
DateOfBirth date NOT NULL,
Phone NVARCHAR (10) NOT NULL,
Email NVARCHAR (50) NOT NULL,
IsMale bit NOT NULL
);
CREATE TABLE RoomType
(
Id INT PRIMARY KEY IDENTITY (1,1),
Name NVARCHAR (50) NOT NULL,
Description NVARCHAR (250) NOT NULL,
RoomPrice money NOT NULL,
MaxGuests INT NOT NULL
);
CREATE TABLE Room
(
Id INT PRIMARY KEY IDENTITY (1,1),
RoomNumber INT NOT NULL,
HotelId INT FOREIGN KEY REFERENCES Hotel(Id) NOT NULL,
RoomTypeId INT FOREIGN KEY REFERENCES RoomType(Id) NOT NULL,--??
IsSmoking bit NOT NULL
);
CREATE TABLE Reservation
(
Id INT PRIMARY KEY IDENTITY (1,1),
ReservationDate date NOT NULL,
CustomerId INT FOREIGN KEY REFERENCES Customer(Id) NOT NULL,
RoomId INT FOREIGN KEY REFERENCES Room(Id) NOT NULL,
NumberGuests INT NOT NULL,
CheckInDate date NOT NULL,
CheckOutDate date NOT NULL
);
The following query provides details regarding all reservations that have been created:
SELECT RR.ReservationDate, C.FirstName, C.LastName, R.RoomNumber, RR.NumberGuests, RR.CheckInDate, RR.CheckOutDate, H.Name HotelName
FROM Reservation RR
LEFT JOIN Customer C ON RR.CustomerId = C.Id
LEFT JOIN Room R ON RR.RoomId = R.Id
LEFT JOIN Hotel H ON R.HotelId = H.Id;
The following query provides details from a business perspective, regarding Daily Reservations by Customer in the time range between 01.05.2018 – 20.5.2018, for Grand Hotel London:
SELECT RR.ReservationDate, C.FirstName, C.LastName, COUNT(*) TotalReservations
FROM Reservation RR
LEFT JOIN Customer C ON RR.CustomerId = C.Id
LEFT JOIN Room R ON RR.RoomId = R.Id
LEFT JOIN Hotel H ON R.HotelId = H.Id
WHERE RR.ReservationDate BETWEEN '2018-05-01' AND '2018-05-20'
AND H.Id = 7
GROUP BY RR.ReservationDate, C.FirstName, C.LastName
ORDER BY RR.ReservationDate ASC, C.FirstName ASC, C.LastName ASC;
Graduated with a Bachelor of Design in Architecture from the University of Sydney. Interested in Database Systems and Business Intelligence. Self-taught in SQL, SQL Server, and BI Tools (Power BI, QlikView). Developed a database system for a hotel chain, designing the database in SQL Server and implementing data visualizations in QlikView. Aspiring to work as a BI Developer because it would enable me to consolidate, analyze and visualize data to support clients in business decisions.
Email: j.cupac@gmail.com