Cleaning the SafariConnect data and answering the six business questions in SQL was one project. Turning those answers into something a CEO could actually look at during a board meeting was a completely different skill, and honestly the one I was less prepared for. This is the follow-up to my SQL cleanup post: how the same data ended up as a four-page Power BI dashboard, what it showed, and what presenting it actually taught me.
Power BI Only Saw What SQL Already Fixed
I didn't point Power BI at the raw bookings table. By the time the SQL project was done, I already had a handful of views built specifically to answer each business question, v_route_performance, v_driver_performance, v_monthly_revenue, v_cancellation_analysis, v_passenger_insights, and the base v_clean_trips view underneath all of them.
CREATE OR REPLACE VIEW v_route_performance AS
SELECT
route_code,
route_from || ' → ' || route_to AS route,
COUNT(*) AS total_bookings,
SUM(seats_booked) AS total_seats,
SUM(total_fare) AS total_revenue,
ROUND(AVG(fare_per_seat), 2) AS avg_fare,
ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_revenue DESC;
Connecting Power BI to those views instead of the raw table meant a lot of the aggregation logic (revenue by route, cancellation rate by route, driver rankings) was already sitting one layer below the dashboard, done once in SQL instead of being rebuilt as DAX measures for every visual. The dashboard's job wasn't to calculate the numbers, it was to display numbers that had already been calculated correctly.
I also added indexes on the columns I knew Power BI would be filtering and grouping by constantly, departure_date, route_code, driver_name, booking_status, since a dashboard that gets refreshed and filtered repeatedly is a very different access pattern from a one-off analytical query.
CREATE INDEX idx_bookings_depdate ON bookings (departure_date);
CREATE INDEX idx_bookings_route ON bookings (route_code);
CREATE INDEX idx_bookings_driver ON bookings (driver_name);
CREATE INDEX idx_bookings_status ON bookings (booking_status);
Not Every Reader Needed the Same Page
The report ended up as four pages, each built for a different reader in the room.
Executive Revenue Insights was the landing page, and it had to work for someone who might only look at it for thirty seconds. Six KPI cards sat across the top: Ksh223.97K total net revenue, 248 completed bookings, a 3.53 average trip rating, a 4.27 average driver rating, a 12.37% overall cancellation rate, and Ksh17.24K in total revenue lost. Below that, a revenue-by-route bar chart, a monthly revenue trend, a treemap of revenue by passenger city with Nairobi dominating everything else on the map, and a payment method breakdown showing M-Pesa well ahead of cash and card.
Driver Performance & Trip Insights was the operational page, filterable by route, vehicle type, passenger city, and driver name. It had a revenue-by-driver bar chart, a trips-by-driver chart, a seat class split (Economy at 79% against Business at 21%), and a day-by-hour booking heatmap that made the busiest and quietest periods immediately obvious just from the shading, no need to read a single number to see that Tuesday was busy and Sunday wasn't.
Insights was a plain-text page, deliberately not another chart. Just the key findings written out in full sentences, because sometimes the fastest way to communicate a finding is to just say it instead of asking someone to read it off a bar chart.
Recommendations closed out the report, translating each finding into an actual next step instead of leaving the board to draw its own conclusions.
Seeing It on a Screen Changed What Stood Out
A few findings only became obvious once they were sitting next to each other visually, in a way they hadn't been in a plain SQL result set.
- RT001 was the best route and one of the biggest problems at the same time. It brought in the most revenue on the network, KES 51,600, but it also lost the most to cancellations of any route, KES 5,169. Seeing the revenue chart and the cancellation chart on the same page made that connection obvious in a way that scrolling between two separate query results never would have.
- Driver rating and passenger rating told two different stories. Isaac Korir led on revenue, Hassan Abdi led on passenger satisfaction, and the platform's own driver ratings didn't reliably predict either one. Putting both metrics side by side on one page, instead of just reporting a single "top driver," was the only way to show that nuance honestly.
- The booking heatmap did more work than any number could. Tuesday's 49 bookings and Sunday's 10 look like a mild difference as two numbers in a sentence. On a shaded grid across the week, the contrast is immediate, and it turns into an obvious operational question: why are we running the same vehicle allocation on Sunday as we are on Tuesday?
- Nairobi wasn't just the top city, it was most of the map. The treemap made the scale of that dominance visual instead of just numeric, KES 110,410 sitting in one city versus everywhere else combined.
Charts, Then Findings, Then Actions
The Insights and Recommendations pages ended up being the most important pages in the whole report, and they're the two pages with the fewest charts on them.
Every chart on the first two pages answers a "what happened" question. The Insights page exists to answer "so what," written as plain findings instead of forcing the reader to interpret a bar chart themselves: RT001 is the top-performing route but also the most cancellation-prone, Nairobi generates the most revenue of any town, M-Pesa is the dominant payment method. The Recommendations page goes one step further and answers "now what": investigate the shared cause behind cancellations on RT005 and RT006, study Isaac Korir's approach for repeatable best practices, check whether Peter Ngugi's lower numbers are a driver issue or a route issue before responding to either.
That structure, chart pages, then a findings page, then a recommendations page, was deliberate. A director skimming for thirty seconds gets the KPI cards. Someone with five minutes gets the Insights page. Someone who actually has to decide something gets the Recommendations page. Not every reader needs the same depth, and building the report in layers meant I didn't have to guess which one any given person would want.
The Real Lesson Wasn't in the SQL
The SQL side of this project was about getting the numbers right. The BI side was about deciding what to do with numbers that were already right, and that turned out to be a genuinely different skill.
A correct number in the wrong format doesn't communicate anything. "12.37% cancellation rate" sitting quietly in a query result is not the same thing as a KPI card in red at the top of a dashboard next to "Ksh17.24K lost." Same number, completely different amount of attention it earns in a room.
I also learned that a dashboard isn't finished when the charts render correctly. It's finished when someone who never saw the SQL, never saw the messy CSV, and doesn't especially care how any of it works, can look at page one for thirty seconds and walk away knowing whether the business is doing well. Getting there took more thought about layout, hierarchy, and what to leave off the page than it took to write any of the underlying queries. Cleaning the data made the numbers trustworthy. Building the dashboard is what made them usable.
Top comments (0)