Hotel Data Warehouse project designed using SQL Server and Star Schema modeling techniques. Includes fact & dimension tables, relationships, constraints, and sample data for hotel analytics and reporting.
This project is a Hotel Data Warehouse designed using SQL Server and Star Schema modeling techniques.
The system stores and analyzes hotel operational data including:
- Guests
- Bookings
- Payments
- Hotel Services
- Employee Shifts
- Room Maintenance
- Customer Reviews
The project demonstrates Data Warehousing concepts, relational database design, and SQL development skills.
- SQL Server
- T-SQL
- Star Schema Modeling
- ERD Design
- Dim_Guests
- Dim_Dates
- Dim_Rooms
- Dim_RoomTypes
- Dim_Employees
- Dim_PaymentMethods
- Dim_Services
- Dim_Branches
- Dim_BookingChannels
- Fact_Bookings
- Fact_Payments
- Fact_HotelServices
- Fact_RoomMaintenance
- Fact_EmployeeShifts
- Fact_Reviews
- Primary Keys & Foreign Keys
- Identity Columns
- Star Schema Design
- Data Integrity Constraints
- Review Rating Validation
- Sample Data Insertion
- Business-Oriented Relationships
This warehouse can help answer questions such as:
- What is the total hotel revenue?
- Which room types are most booked?
- What are the highest-rated services?
- Which booking channels generate the most bookings?
- What is the average customer rating?
SELECT SUM(TotalAmount) AS TotalRevenue
FROM Fact_Bookings;SELECT AVG(Rating) AS AverageRating
FROM Fact_Reviews;SELECT
s.ServiceName,
SUM(h.Quantity) AS TotalUsage
FROM Fact_HotelServices h
JOIN Dim_Services s
ON h.ServiceID = s.ServiceID
GROUP BY s.ServiceName;-- Total Revenue
SELECT SUM(TotalAmount) AS TotalRevenue
FROM Fact_Bookings;
-- Average Rating
SELECT AVG(Rating) AS AvgRating
FROM Fact_Reviews;
-- Most Used Services
SELECT
s.ServiceName,
SUM(h.Quantity) AS TotalUsage
FROM Fact_HotelServices h
JOIN Dim_Services s
ON h.ServiceID = s.ServiceID
GROUP BY s.ServiceName;
---
Mohamed Ashraf Linkedin