top of page

Response to Case Study - Reponse 1

Nov 1, 2022
1 min read

The first thing I need to do is to create a view in my DKTravel database to join bookings and customer data into a form more appropriate for carrying out BI. This could be done within Tableau if the data was from outside a database, but as I have access to SQL Server I am combining the data here and will use that as the source for Tableau. Here is the view definition. I am splitting the dates into Month and Year components and also converting the Leadtime days into weeks.




CREATE VIEW v_BookingData_BI AS


SELECT C.PostCode,

C.Town,

YEAR(B.BookingDate) AS BookingDate_Year,

MONTH(B.BookingDate) AS BookingDate_Month,

YEAR(B.DepartDate) AS DepartDate_Year,

MONTH(B.DepartDate) AS DepartDate_Month,

YEAR(B.ReturnDate) AS ReturnDate_Year,

MONTH(B.ReturnDate) AS ReturnDate_Month,

B.LeadTime,

B.LeadTime/ 7 AS LeadTimeWeeks,

B.PassengerCount,

B.Resort,

B.BookingAmount AS Revenue,

B.ID

FROM tblCustomers C

INNER JOIN tblBookings B ON C.ID = B.CustomerID








For the purposes of my portfolio, I have installed a trial version of Tableau Desktop and have set up a Tableau Public workspace.




I have created 2 simple dashboard sheets in Tableau and published to Tableau Public showing Bookings Data on one and Revenue data on another.





 
 
 

Recent Posts

See All
Welcome to DK Travel

To demonstrate some of my skills, I have made up a ficticious travel agency called DK Travel and they are going to be asking me for...

 
 
 
Some Initial Setup

Before I start answering requests from DK Travel, I need to set up an initial customer database and due to GDPR cannot use real data. I...

 
 
 
Random Booking Data

As mentioned in the previous post, I can generate random data using T-SQL or Python. I have decided to do the work in Python and...

 
 
 

Comments


bottom of page