A business dashboard people actually use is built backwards from the decisions it supports. Each tile answers a question someone acts on, every KPI has a written definition (formula, owner, source, grain, refresh, target and caveats), data passes quality checks before it is shown, and refresh frequency matches how fast the decision is made rather than how fast the technology allows.
Most unused dashboards fail for the same reasons: nobody agreed what the numbers mean, the numbers do not match finance's figures, or the screen shows everything and so helps with nothing. This guide covers how to avoid all three, whether you use a BI tool or a custom build.
Why should you start from decisions rather than charts?
Because a chart nobody acts on is decoration. Start by listing who makes which decisions, how often, and what they would do differently if a number moved. Only then pick the KPIs and the charts.
| Role | Decision | How often | What they need to see |
|---|---|---|---|
| Warehouse manager | Which orders to expedite or split today | Several times a day | Orders at risk of missing their promised date |
| Sales manager | Which accounts and reps need attention this week | Weekly | Sales against target, accounts with falling orders |
| Finance controller | Where margin is leaking | Monthly | Gross margin by category, branch and customer |
| Credit controller | Who to chase or put on hold | Daily | Overdue balances and days sales outstanding |
Ask each person: "If this number went up or down sharply, what would you do?" If there is no answer, the number is not a KPI for them. It may still belong in a report, but not on their dashboard.
How should you define a KPI?
Write a definition for every KPI before building anything, and publish them in a glossary next to the dashboard. Many disputes about "wrong numbers" are really two teams using one word for two calculations: sales may count revenue when an order is booked, finance when it is invoiced, and the bank when it is paid.
| Field | What to write | Example: gross margin % |
|---|---|---|
| Name | One name, used everywhere | Gross margin % |
| Business question | What decision it supports | Which categories and customers are profitable enough? |
| Formula | In words and as a calculation | Net sales minus cost of goods sold, divided by net sales |
| Inclusions and exclusions | What counts and what does not | Net of credit notes and returns; excludes inter-branch transfers |
| Owner | The person who rules on disputes | Finance controller |
| Source | System, tables or reports | ERP sales invoices and item costs |
| Grain | The lowest level of detail stored | Invoice line, rolled up by day, branch, category and customer |
| Refresh | How often it updates | Daily; final after month-end close |
| Target and thresholds | What good looks like | Set per category by finance |
| Caveats | Known limits | Landed cost updates when freight bills arrive, so recent margin can shift |
| Version | When the definition changed | Date and reason for each change |
Grain deserves extra attention. If you store only daily totals per branch, nobody can drill into a customer or an invoice later. Store the lowest practical level and aggregate upward.
Which data quality checks should run before a dashboard updates?
Run checks on every load and show the result. A dashboard that is silently wrong does more damage than one that admits a problem.
Data quality checklist:
- Freshness. Record when each source was last loaded and show "data as of" on every view.
- Completeness. Compare row counts and totals with the source, and check every branch or entity appears.
- Validity. Flag negative quantities, future dates and prices of zero.
- Uniqueness. Catch duplicate invoices or orders loaded twice.
- Mapping. Put sales for unknown products or customers into a visible "unmapped" bucket instead of dropping them.
- Reconciliation. For closed periods, dashboard revenue and margin must agree with finance's ledger. Agree who signs this off.
- Volume anomalies. Alert when a day's numbers are far outside the usual range; it is often a failed load, not a business event.
When a check fails, keep showing the last good data with a clear warning, and alert the KPI owner. Do not overwrite good numbers with a half-loaded day.
How often should a dashboard refresh?
Refresh as often as the fastest decision that uses the number, and no faster. Each step toward real time adds cost, complexity and load on source systems.
| Refresh | Good for | Cost and complexity | Example |
|---|---|---|---|
| Real time (seconds) | Operations where minutes matter | Highest: event streams or live queries, load on source systems, harder testing | Dispatch boards, production line stoppages |
| Near real time (minutes to hourly) | Intraday operations | Moderate: incremental loads or change capture | Order backlog, picking progress, stockouts |
| Daily | Most management KPIs | Low: overnight batch after the day closes | Sales against target, margin, overdue balances |
| Weekly or monthly | Finance, board and planning | Lowest: after period close, reconciled | Monthly margin, budget against actual |
Two practical cautions:
- Do not point live dashboards at the ERP's production database. Heavy queries can slow the system for people entering orders. Use a read replica or a separate reporting database.
- Tools set their own limits. Google's Looker Studio documentation explains that each data source has a data freshness setting and that available refresh rates vary by connector; its Google Ads and Google Analytics connectors refresh every 12 hours and that rate cannot be changed. In Power BI, imported data refreshes on a schedule, while DirectQuery sends queries to the source when the report is used.
Should you build a custom dashboard or use a BI tool?
Use a BI tool such as Power BI, Looker Studio or Metabase for internal analysis and management reporting. Build custom when the dashboard is part of a product or portal used by customers, when users need to act on what they see (approve, reorder, assign), or when role logic and design must match the rest of your application. Many organisations use both.
| Factor | BI tool | Custom-built dashboard |
|---|---|---|
| Time to first version | Fast, once data is modelled | Slower |
| Who changes it | Analysts | Developers |
| Ad hoc exploration | Strong | Limited unless built in |
| Showing data to external customers | Possible through embedding; check licensing for your user numbers | Built in |
| Row-level security | Supported, with tool-specific rules | You design and build it |
| Acting on the data | Limited | Full: buttons, workflows, write-back |
| Look and feel | Within the tool's options | Full control |
| Ongoing cost | Licences, which vary by vendor and user type | Development and maintenance |
The row-level security details differ by tool, so check them against your use case. Microsoft's documentation notes that Power BI row-level security applies to workspace users with the Viewer role, not to Admin, Member or Contributor roles, and that embedded reports need the user's identity passed in the embed token for per-user filtering. Metabase's documentation notes that its static embeds cannot use row and column security; restrictions there rely on locked parameters, while its full app and modular embedding can map identity provider attributes to permissions.
How should role-based views work?
Keep one set of KPI definitions and vary the scope by role. Everyone sees "gross margin %" calculated the same way; a branch manager sees their branch and a sales rep sees their own accounts.
- Executive: whole business, trends and comparison with target
- Branch or department manager: own unit, drill down to people and customers
- Individual: own accounts, tasks and pipeline
- Finance: everything, with reconciliation detail
- External customer: their own account only, through a portal
Enforce scope in the data layer, not by hiding tiles. If a user can change a filter in the address bar and see another branch, the security is not real.
How do you embed dashboards in a portal?
Pass the signed-in user's identity from the portal to the dashboard, enforce filtering on the server, and test with real accounts. Options range from embedding a BI tool's report to building native charts from your own API. Native charts suit simple, high-traffic customer views; embedded BI suits richer analysis for fewer users.
Check before committing: per-customer filtering in the embedded mode you plan to use, page load time, licensing at your expected number of viewers, and whether viewers can refresh data. Looker Studio's documentation, for example, notes that viewers cannot manually refresh an embedded report. For the wider portal design, see our customer portal development checklist.
Worked example: a sales and operations dashboard for a distributor
This example is illustrative. A distributor of packaged goods has three branches, an ERP for orders, stock and invoicing, a CRM for quotes, and a warehouse system for picking.
| KPI | Definition | Source | Grain | Refresh | Owner |
|---|---|---|---|---|---|
| Orders at risk | Open orders past their promised ship date, or with lines short of stock | ERP orders, warehouse stock | Order line | Every 15 minutes | Warehouse manager |
| Fill rate | Order lines shipped complete on their first shipment, divided by all order lines due to ship | ERP | Order line | Daily | Operations manager |
| Sales against target | Net invoiced sales divided by target | ERP invoices, targets held in ERP | Invoice line by rep, branch, day | Daily | Sales manager |
| Gross margin % | As defined in the template above | ERP | Invoice line | Daily, final at month end | Finance controller |
| Declining accounts | Customers whose orders over the last eight weeks are below the same weeks last year | ERP | Customer by week | Weekly | Sales manager |
| Days sales outstanding | Trade receivables divided by credit sales for the period, multiplied by days in the period | ERP | Customer | Daily | Credit controller |
| Open quotes | Quotes issued and not yet won or lost, by age | CRM | Quote | Daily | Sales manager |
Design choices that make it usable:
- Three views (operations, sales, finance) rather than one screen with every tile
- Every tile shows a comparison: against target, last week or the same period last year
- Each exception links to the record: an at-risk order opens the order, a declining account opens a call list
- "Data as of" shown on every view, with a warning when a check fails
- Products sold but not mapped to a category appear as "unmapped", so the category totals still reconcile
What was left out on purpose: website visits, total customer count and total orders all-time. They move without anyone needing to act.
If the data needed for these KPIs is spread across spreadsheets and disconnected systems, the dashboard is the easy part. Our ERP integration services connect the sources, and a custom ERP can bring orders, stock and invoicing into one place when the existing tools cannot.
Which dashboard anti-patterns should you avoid?
- Vanity metrics: numbers that only go up and prompt no action
- Too many tiles: if the main numbers need scrolling to see, split the view by role
- Undefined KPIs: two teams, one word, two calculations
- Numbers without comparison: a total with no target or prior period tells nobody whether it is good
- Real time everywhere: paying for second-by-second data that is reviewed weekly
- No "data as of" time: users cannot tell stale data from a slow day
- Colour as the only signal: red and green alone fail colour-blind users; add labels or icons
- No owner: without one, definitions drift and the dashboard is abandoned
- Dashboard as export tool: if people mainly download the data to a spreadsheet, the dashboard is not answering their question
When is a dashboard not the answer?
- The event is rare. An alert or exception email serves better than a screen someone must remember to check.
- The data is unreliable. Fix the integration and data ownership first; a dashboard will only spread the errors. Our guide to system integration approaches covers the options.
- The question is one-off. An analysis or report answers it without the cost of maintaining a dashboard.
- The process is broken. Measuring a broken process precisely does not fix it.
How Timeline Digital helps
We build dashboards inside the systems people already use, connected to the ERP, CRM and operational data behind them. Our business dashboard development service covers KPI definition, data pipelines, role-based views and embedding in portals. We start with a free pilot of 2 to 3 key modules before the full project, for example one role's view with its KPIs defined and reconciled, so you can check the numbers before scaling up.