How to Create a Business Intelligence Dashboard
A business intelligence dashboard is built in five steps: pick the decision the dashboard is supposed to support, connect the system that holds the data, model that data into a clean table or view, choose four to eight metrics that answer the decision, and lay them out so the most important number is top left. In practice: connect your warehouse or database to a BI tool (Power BI, Looker Studio, Tableau, Metabase), write a SQL query or point-and-click a dataset, drag the fields onto a canvas, filter to a time window, and share it with the people who need it. A first version of a real dashboard takes a competent analyst two to four hours. The reason projects take six weeks is almost never the dashboard, it is the data underneath.
The single decision that determines whether the dashboard gets used is step one. "A sales dashboard" is not a decision. "Which deals in this quarter's pipeline are at risk, so the VP knows where to spend Thursday" is a decision. Write that sentence before you open the tool. Every chart that does not help answer it gets cut. A dashboard with five charts that answer one question beats a dashboard with thirty charts that answer none, and the thirty-chart version is the one people stop opening after three weeks.
Step 1: Write the decision sentence
Format: who looks at this, how often, and what they do differently based on what they see.
- "The support lead checks this every morning and decides which tickets to escalate before standup."
- "The CFO checks this monthly and decides whether to adjust the hiring plan."
- "The growth PM checks this weekly and decides which acquisition channel gets next month's budget."
If you cannot finish the sentence with a concrete action, you are building a report, not a dashboard. Reports are fine. They just have different rules: they can be exhaustive, they can be exported, and nobody needs them to load in two seconds.
Step 2: Find where the numbers actually live
This is where timelines slip. List every metric in your decision sentence and write down the system of record for each one. A revenue dashboard usually needs Stripe or the billing table, the CRM for deal stage, and the product database for usage. Three systems means you need somewhere they can be joined.
Your options, in rough order of effort:
| Approach | When it works | What it costs you |
|---|---|---|
| Query one production database directly | Single source, small data, read replica available | Risk of slow queries hitting production; no joins across systems |
| Point the BI tool at each source separately | Each metric lives cleanly in one system | No cross-system charts; the join happens in someone's head |
| Sync everything into a warehouse (BigQuery, Snowflake, Postgres) with a pipeline tool | More than two sources, or you need history | Days to weeks of setup, plus ongoing pipeline maintenance |
| Spreadsheet export, refreshed by hand | One-off analysis, a board deck | Stale within a week, guaranteed |
Be honest about which one you are on. Most first dashboards should query a read replica of one database and stop there. Build the warehouse when you have proven the dashboard gets opened.
Step 3: Model the data before you visualize it
The mistake that produces the most rework is charting straight off raw tables. Raw tables carry test accounts, soft-deleted rows, duplicated records from a bad import, and timestamps in three timezones. If you chart them directly, every future chart inherits those problems and every future chart fixes them slightly differently. Then two dashboards show different revenue numbers and trust dies.
Instead build one view per subject area and chart only from views. A minimal revenue view:
create view v_revenue_daily as
select
date_trunc('day', c.created_at at time zone 'UTC') as day,
a.plan,
a.region,
sum(c.amount_cents) / 100.0 as revenue
from charges c
join accounts a on a.id = c.account_id
where c.status = 'succeeded'
and a.is_test = false
and a.deleted_at is null
group by 1, 2, 3;
Four decisions are now made once, in one place: what counts as revenue, what timezone a day is, which accounts are real, and what dimensions you can slice by. Encode business rules here, not in chart filters. A rule buried in a chart filter is invisible to the next person.
Step 4: Choose the metrics
Four to eight tiles. Each one should be a number a person can act on, paired with a comparison that gives it meaning. "Revenue: $412,000" tells you nothing. "Revenue: $412,000, up 6% on last month, 4% behind plan" tells you whether to worry.
Every metric needs three properties:
- A written definition. "Active user" means what, exactly? Logged in once in 28 days? Performed a core action? Put the definition in the dashboard, as subtitle text on the tile.
- A comparison. Prior period, same period last year, target, or benchmark. Numbers without a reference point generate meetings, not decisions.
- An owner. Somebody whose job the number reflects. Unowned metrics rot.
Watch for the vanity trap: cumulative totals that only go up, page view counts nobody can influence, and averages that hide the distribution. Median and p90 usually beat the mean for anything latency-shaped or spend-shaped.
Step 5: Lay it out
People read top left first. Put the number your decision sentence hangs on there, at the largest size on the page. Then supporting trend, then breakdowns, then the detail table at the bottom for the person who wants to check your work.
Practical rules that hold across every BI tool:
- One chart, one message. If you need two sentences to explain a chart, it is two charts.
- Line for time, bar for comparison between categories, table for anything a person will want to copy out. Pie charts work for two or three slices and fail past that.
- Truncated y-axes are for line charts only. On bars, start at zero or you have made a misleading picture.
- Colour means something or nothing. Use one accent for the series that matters and grey for context. Ten colours means the reader has to work the legend on every glance.
- Filters at the top, defaulted sensibly. Default to the window the decision actually operates on. A dashboard checked every morning defaults to yesterday, not last 12 months.
- Load time under three seconds. Pre-aggregate in the view or add an index. A slow dashboard is an unopened dashboard.
A worked example: churn risk in one afternoon
Decision sentence: "The account manager checks this Monday morning and decides which five accounts to call this week."
Sources: subscriptions and usage events in Postgres, support tickets in Zendesk exported nightly into the same database.
The view produces one row per account per week with: weekly active seats, change against the trailing four week average, days since last login, open tickets, days to renewal, and contract value.
The dashboard is five tiles. Top left: a table of accounts sorted by a risk score, contract value shown alongside so the AM prioritises by money at stake. Top right: count of accounts in each risk band, this week against last. Below: seat trend for the selected account, ticket volume for the selected account, and renewal calendar for the next 90 days.
Total charts: five. Total metrics: six. It answers exactly one question and an account manager can work from it in ninety seconds.
Where the simple version breaks
Two people disagree about a number. Almost always different definitions, not different data. Fix it by putting the definition on the tile and pointing both dashboards at the same view. If you have more than a handful of shared metrics, this is the point where a semantic layer earns its cost.
The dashboard is right but nobody changes anything. Usually the decision sentence was invented after the fact. Go back to step one and ask the intended user what they did last time they were surprised.
Refresh is too slow. Push aggregation upstream into materialized views or scheduled tables. BI tools are bad places to do heavy computation.
The answer isn't in any database. This is the real ceiling. The dashboard shows a customer's usage collapsed in week three, but the reason is a sentence in a support thread, a note in a CRM record, or a Slack message from the implementation engineer. BI tools connect to databases and modelled sources, so evidence that lives as text in a conversation sits outside what they can see, no matter how good the dashboard is.
When the numbers and the context live in different places
That last failure mode is the one dashboards structurally cannot close. You can see churn rising and still have no idea why, because the why is in Zendesk, Gmail and Slack rather than in a table.
Skopx connects to nearly 1,000 SaaS tools alongside direct database connections, so you can ask a question in chat and get an answer that pulls the Postgres number and the Slack thread that explains it, with citations to both. Describe the view you want in a sentence and it builds an internal console that reads live from those sources and can take an action behind a confirmed button click. It does not replace your BI stack for modelled reporting. It covers the part of the question your warehouse never had. See Internal Apps.
Skopx Team
The Skopx engineering and product team