Dashboards with Power BI, Tableau and Looker Studio

Design a dashboard for a decision, model data for BI, choose charts, add filters, and manage refresh and access.

Est. 40 min Updated Suggest a fix
// Part 01 — Why dashboards?

A Dashboard Is an Interface for a Decision

A dashboard is useful when it helps a defined audience answer a recurring question and take an appropriate next step. A CEO may need a small set of trends and risk indicators; a category manager may need daily stock, order, and cancellation details. One dashboard rarely serves both well. Start with the decision, user, cadence, and action rather than with a list of charts.

Clarify what is monitored, what requires investigation, and who owns the response. A chart should have a named metric definition, relevant comparison, reporting period, and explanation of freshness. Show when data was last refreshed and provide a safe path to inspect detail. Avoid exposing customer-level information to audiences that only need aggregates.

Design each visual around a question and a response.
Audience questionUseful viewLikely action
Are orders on track today?Hourly completed orders vs a relevant baselineCheck operational coverage if a drop persists
Where are delays rising?p50 and p90 delivery time by service areaInvestigate affected routes or capacity
Did a category change over time?Weekly GMV and order count with annotationsAsk whether mix, promotions or tracking changed
💡 Note
A dashboard is not a data warehouse, metric dictionary, or substitute for investigation. Keep it focused on the decisions it is meant to support.
// Part 02 — Choose a tool

Select BI by Workflow, Governance and Cost

Power BI, Tableau, Looker Studio, and Metabase can all support reporting, but they serve different environments. Compare the team's existing identity and data stack, analyst skills, semantic modelling, sharing and access needs, refresh constraints, deployment workflow, and total operating cost. Product capabilities and plan limits change; verify current official documentation before procurement rather than relying on a remembered feature list.

These are decision prompts, not exhaustive product comparisons. Validate current details with vendors and your data platform.
ToolOften a fit when…Questions to verify
Power BIThe organisation already relies on Microsoft tools and managed BI workspacesLicensing, gateway and refresh needs, row-level security, deployment governance
TableauTeams value interactive visual exploration and an established Tableau ecosystemCreator / viewer access, server or cloud operations, published data-source governance
Looker StudioA lightweight shared report fits a Google-oriented workflowConnector behaviour, credentials, data freshness, sharing and access controls
MetabaseA team wants approachable SQL-backed exploration and dashboardsHosting and upgrades, permissions, model governance, query performance

A tool with many features can still be the wrong fit if report ownership is unclear or sensitive data can be shared too broadly. Prototype one representative workflow—from curated data to refresh, access review, and a business user's decision—before building a large catalogue of reports.

🎯 Pro Tip
Separate the BI tool from the data model. Well-defined warehouse tables and measures make it easier to change a visualisation product later without redefining every KPI.
// Part 03 — Data modelling

Use a Star Schema for Dashboard Queries

A star schema organises measurable events in a fact table linked to descriptive dimensions. A fact table has a declared grain such as one row per order line or one row per completed order. Dimensions describe entities such as customer, product, date, and location. Explicit grain prevents the most common reporting error: joining tables at different detail levels and multiplying counts or amounts.

For an e-commerce dashboard, a sales fact might contain one record per order line with quantity, gross item value, discount, and order identifiers. Product, date, customer segment, and fulfilment zone dimensions let a report group and filter those facts. Keep identifiers stable, define slowly changing attributes where history matters, and avoid joining two independent facts through an ambiguous many-to-many path without a deliberate model.

star_schema.sql
sql
-- Fact grain: one row per order line. Dimensions have one row per key at query time.
SELECT
  d.month_start,
  p.category_name,
  z.service_area,
  SUM(f.quantity * f.unit_price_rupees) AS gross_item_value_rupees,
  COUNT(DISTINCT f.order_id) AS orders
FROM mart.fact_order_line AS f
JOIN mart.dim_date AS d ON d.date_key = f.order_date_key
JOIN mart.dim_product AS p ON p.product_key = f.product_key
JOIN mart.dim_zone AS z ON z.zone_key = f.zone_key
WHERE d.month_start >= DATE '2026-07-01'
  AND d.month_start <  DATE '2026-10-01'
GROUP BY d.month_start, p.category_name, z.service_area
ORDER BY d.month_start, p.category_name, z.service_area;

The example sums gross item value before discounts and returns, so it is not automatically net GMV, recognised revenue, or profit. Put business treatment in the metric definition. Validate each dimension join is one-to-one at the selected key, or totals can multiply. Test aggregated results against a trusted source at the same grain.

// Part 04 — Dashboard structure

Put the Overview Before the Drill-Down

A practical layout starts with a clear title and reporting period, then a small number of headline metrics, a trend with a relevant comparison, a breakdown that helps diagnose movement, and a detail table for follow-up. Put filters where users expect them, show active filter state, and make reset behaviour obvious. Use consistent units, readable labels, and meaningful default periods; avoid forcing viewers to discover a hidden control to understand a chart.

Add context such as targets, prior comparable periods, or seasonality only when the comparison is meaningful. A target should have an owner and definition. A red badge without an agreed threshold creates alarm but not clarity. Include data freshness and a contact or owner for metric questions. For a live operations dashboard, distinguish late data from a genuine zero in activity.

dashboard_spec.md
text
Audience: City operations lead
Decision: Where should capacity be investigated today?
Refresh: Hourly; show last successful refresh time
Top row: completed orders, late-delivery rate, p90 delivery minutes
Trend: hourly p50 and p90 against comparable weekdays
Breakdown: service area by order volume and late rate
Detail: affected routes with minimum-count suppression
Filters: date, city, service area; visible active filter chips
Owner: Operations Analytics; metric definitions linked from each title
📌 Real World Example
If p90 delay rises in one area while order volume also doubles, a raw comparison may hide the demand change. Show both measures and provide enough context for an operator to ask the next question.
// Part 05 — Choose a chart

Match the Visual to the Comparison

A line chart is a natural choice for a time trend. A bar chart compares categories; sort it to make relative size clear. A scatter plot helps explore relationships between two numeric measures. A histogram shows a distribution; a box plot offers a compact view of centre and spread across groups. A table is often best when exact values and lookup matter more than pattern recognition.

Use a map only when location is central to the question and geographic encoding adds useful information. Avoid pie charts for many similar categories, 3D effects that distort comparisons, and dual axes that imply a relationship through arbitrary scaling. Keep colour meaning stable and do not rely on colour alone to distinguish states. Use direct labels and accessible contrast, especially for dense mobile screens.

Analytical taskGood starting chartCheck before publishing
Trend over timeLineConsistent time interval and clear treatment of missing dates
Compare categoriesSorted barReadable labels, units and a reasonable category count
Inspect distributionHistogram or box plotBin choices, sample size and outlier context
Show exact lookup valuesTableSort order, totals and visible filters
// Part 06 — Performance and access

Keep Dashboards Fast, Fresh and Safe

Dashboard performance depends on query design, data volume, concurrency, refresh strategy, and the BI engine. Filter early when appropriate, aggregate to the audience's needed grain, and avoid retrieving every row for a summary view. Use warehouse partitions and pre-aggregations where they fit the workload. Measure query duration and user experience rather than assuming a particular storage mode is always faster.

Establish refresh expectations and expose the last successful refresh. Handle failed refreshes visibly instead of showing stale data as current. Review who can view, edit, export, and share the report; use row-level controls when different audiences are permitted different records. Test those controls with representative roles, and avoid putting sensitive information into URLs, screenshots, or broad exports.

aggregate_before_bi.sql
sql
-- A daily aggregate can serve an overview more efficiently than scanning raw events each time.
SELECT
  event_date,
  city,
  COUNT(DISTINCT customer_id) AS active_customers,
  COUNT(DISTINCT order_id) AS completed_orders,
  SUM(order_value_rupees) AS gross_order_value_rupees
FROM mart.order_activity
WHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY
  AND order_status = 'completed'
GROUP BY event_date, city;

Aggregation is safe only when the metric is additive at the chosen dimensions. Distinct users across days are not additive; summing daily active users does not produce monthly active users. Preserve the correct grain or compute the distinct set over the full requested period.

// Part 07 — Validate and maintain

Treat a Dashboard as a Product

Before publishing, verify key totals against a trusted query, test edge dates and empty filters, inspect the mobile layout, and check access as a viewer. Ask a target user to complete the intended task without coaching. If they cannot explain the metric, comparison, or next action, improve the dashboard rather than adding another chart.

Keep a small inventory of dashboards with owner, audience, purpose, source, refresh schedule, and last review. Retire duplicates that lack active users. Version metric and model changes, announce material shifts, and track whether the dashboard is actually used for the decision it was built to support. Data freshness and definition links should remain visible after redesigns.

⚠️ Important
A dashboard can be technically correct and still mislead if a filter hides the denominator, a stale extract looks current, or a many-to-many join duplicates GMV. Validate the experience and the underlying calculation together.
// Part 08 — Pitfalls

Common BI and Dashboard Mistakes

Starting with a chart library rather than a user decision

Why: The result becomes a gallery of visuals without a clear next action.

Fix: Specify the audience, decision, cadence, and owner before choosing charts.

Joining facts at incompatible grains

Why: A one-to-many join can duplicate counts and monetary values.

Fix: Declare fact grain and validate key cardinality and totals after joins.

Summing non-additive metrics across dimensions

Why: Distinct users and rates do not behave like additive amounts.

Fix: Recompute from the correct underlying population at the requested grain.

Showing data freshness only in documentation

Why: Viewers may act on a stale extract as if it were current.

Fix: Display the last successful refresh and clearly mark delays or failures.

// Part 09 — Practice

Design an Operations Dashboard for a Marketplace

Practice

From star schema to a decision-ready BI page

  1. Choose Power BI, Tableau, Looker Studio, or Metabase for a hypothetical team and document the workflow assumptions you would validate.
  2. Define the grain and measures for order-line fact data plus date, product, customer, and service-area dimensions.
  3. Write a SQL query for weekly completed orders and GMV in ₹ by category; include the cancellation / refund policy in the specification.
  4. Sketch an operations dashboard with audience, decision, KPIs, trend, breakdown, filters, freshness, and owner.
  5. Test totals against source data, verify empty and filtered states, check viewer permissions, and ask a user to perform the target task.

Hint: Keep the BI model separate from chart layout. State each measure's grain and determine whether it is additive before aggregating.

🎯 Key Takeaways

  • ✓A dashboard should answer a recurring question for a specific audience and support an explicit action.
  • ✓Choose a BI product based on workflow, governance, access, refresh, operations, and current verified capabilities.
  • ✓A star schema makes dashboard queries clearer when fact grain and dimension relationships are explicit.
  • ✓Match chart type to the task, label comparisons and units, and keep filters and data freshness visible.
  • ✓Do not sum non-additive measures such as distinct users across dates; calculate them at the requested grain.
  • ✓Validate totals, performance, usability, freshness, and permissions before publishing and maintain dashboard ownership.

Next

What comes next

Model data for BI tools with star schemas and relationships, write measures in DAX, and keep a semantic model fast and trustworthy.

Lesson 13 · BI Data Models and Measures — Star Schemas and DAX →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...