This is the first edition of my business intelligence toolkit. The same tools are being fitted to restaurant chains and retail.
The problem
A hotel group’s managers make the same decisions every day: what rate to charge tonight, how many rooms to oversell, whether a group booking is worth the rooms it takes, and how many housekeepers to schedule. The data to answer each question is already in the booking system and the time clock. It just isn’t in a shape anyone can use before the morning meeting.
I spent over a year working in hotel housekeeping in Niagara Falls, so I saw from the floor what happens when the schedule doesn’t match the arrivals. This project is the toolkit I wish that hotel had.
What I built
- A warehouse. A synthetic five-hotel group with about 640,000 bookings over four years, modelled as a star schema: fact tables for bookings and stays, dimensions for date, hotel, room type, channel and rate plan.
- Data quality first. Ten automated checks (duplicates, impossible dates, negative rates, orphan keys, occupancy above capacity and more) and unit tests run before any number reaches a dashboard.
- Revenue command centre. Occupancy, ADR and RevPAR with pace against last year, a back-tested demand forecast, lost demand on sold-out nights, net rates by channel after commission, and cancellations.
- Staff schedule builder. It turns the forecast workload into shifts with a mixed-integer program (PuLP), checks every shift against Ontario labour rules and exports to Excel.
- Free tools. A rate recommender, occupancy forecaster, overbooking calculator, group displacement quote, housekeeping board, labour productivity, night audit assistant, KPI dashboard, energy savings estimator and a report builder that takes any export.
What’s next
The tools currently read prepared files. I’m turning them into scheduled ETL pipelines that load an Azure SQL Database, so the dashboards refresh like they would in a real hotel group.
All hotels, guests, staff and figures are fictional samples.
Limitations
- All hotels, guests, staff and figures are fictional samples.
What's next
- Scheduled ETL into Azure SQL Database so the dashboards refresh like a real group's.
Run it on your own data
- In the browser (today)Free, no sign-up. What you enter stays on your device. Good for trying it and for small teams.
- On your files or databasePoint the same logic at your exports or a database view (PostgreSQL, SQL Server, Azure SQL or DuckDB), on your own machine or network.
- As a pipeline and warehouseA scheduled ETL job pulls from your systems (reservations, point of sale, inventory, payroll) into a star-schema warehouse, runs data-quality checks, and the tool or a Power BI report reads the result every morning.
How I'd set it up for you
I'd map your exports or database to the tool's model, package the job in a container that runs on your server or cloud account, add the checks your data needs, and hand over the code and a runbook. Your data stays on your systems. Get in touch if you'd like it fitted to your business.
Updates
No logged updates yet. New work on this project will appear here and in the activity log.