What counts as a BI exercise?
A great BI exercise mirrors a real stakeholder ask: a concrete question, a messy dataset, and a shippable output (metric, chart, or dashboard) with a short takeaway. Each exercise below gives you an objective, steps, and an artifact you can publish.
How to use this guide
- Tools: Power BI or Tableau for dashboards; any SQL (PostgreSQL/MySQL); Excel/Sheets for validation; Python/R optional.
- Datasets: Use public data (retail orders, taxi trips, flight delays, inspections). Swap in your industry data when applicable.
- Delivery: Save a screenshot (or PDF) and a 3–5 sentence “Analyst Note” describing assumptions, insights, and next steps.
30 Business Intelligence Exercises
Beginner (1–10): Data basics, KPIs, and clear visuals
1) Data profiling & quality report
Level: Beginner • Time: 30–45m • Tools: Power Query / Tableau Prep • Dataset: Retail orders
Objective: Produce a profile (types, nulls, outliers) and list 3 fixes.
- Import CSV; inspect types, duplicates, and missing values.
- Summarize completeness & distinct counts per column.
- Fix types and obvious errors; document decisions.
Artifact: before/after screenshot + 1-page quality summary.
2) Clean & standardize dimensions
Level: Beginner • Time: 30–40m • Tools: Power Query • Dataset: Products & categories
Objective: Normalize category names and product IDs; export a clean DimProduct table.
3) Basic sales KPIs (Revenue, Orders, AOV)
Level: Beginner • Time: 30–45m • Tools: Power BI / Tableau • Dataset: Retail sales
-- Power BI (DAX)
Total Sales = SUM('Sales'[Amount])
Orders = DISTINCTCOUNT('Sales'[OrderID])
AOV = DIVIDE([Total Sales], [Orders])Artifact: KPI card trio + note on business impact.
4) Time intelligence: MoM/YoY with a Date table
Level: Beginner • Time: 40–60m • Tools: Power BI • Dataset: Sales + Date
Sales LY = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Rolling 3M Sales = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))Artifact: line chart with YoY % + commentary.
5) KPI dashboard (executive one-pager)
Level: Beginner • Time: 60–90m • Tools: Power BI/Tableau • Dataset: Sales & customers
Objective: Print-friendly A4/Letter view with 5 KPIs + sparklines.
6) Dimensional modeling: star schema sketch
Level: Beginner • Time: 40–60m • Tools: Whiteboard + BI model • Dataset: Sales & lookups
Output: FactSales → DimDate/DimProduct/DimCustomer/DimStore with surrogate keys.
7) Visual best practices (declutter & annotate)
Level: Beginner • Time: 30–45m • Tools: Any BI tool • Dataset: Any
Objective: Rebuild a cluttered chart into a clear, annotated alternative.
8) Data joins: INNER vs LEFT
Level: Beginner • Time: 30–40m • Tools: SQL • Dataset: Orders + customers
-- Customers with at least one order
SELECT c.customer_id, c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY 1,2;9) Drill-through journey
Level: Beginner • Time: 30–45m • Tools: Power BI/Tableau • Dataset: Sales
Objective: Region → store → product drill-through with persistent filters.
10) Cohort split (new vs returning)
Level: Beginner • Time: 30–45m • Tools: SQL/BI • Dataset: Orders
-- Mark a customer's first purchase date, then bucket orders
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders GROUP BY 1
)
SELECT o.*, CASE WHEN o.order_date = f.first_date THEN 'New' ELSE 'Returning' END AS cohort
FROM orders o JOIN firsts f USING (customer_id);Intermediate (11–22): Modeling, SQL windows, and alerting
11) Window functions: rank contributors
Level: Intermediate • Time: 40–60m • Tools: SQL • Dataset: Sales
SELECT product_id, SUM(amount) AS revenue,
RANK() OVER (ORDER BY SUM(amount) DESC) AS rev_rank
FROM sales GROUP BY 1;12) Retention with LAG()
Level: Intermediate • Time: 45–60m • Tools: SQL • Dataset: Monthly activity
WITH m AS (
SELECT customer_id, DATE_TRUNC('month', activity_date) AS mth
FROM events GROUP BY 1,2
),
r AS (
SELECT customer_id, mth,
LAG(mth) OVER (PARTITION BY customer_id ORDER BY mth) AS prev_mth
FROM m
)
SELECT mth, COUNT(*) FILTER (WHERE prev_mth IS NOT NULL) AS returning,
COUNT(*) AS active
FROM r GROUP BY 1 ORDER BY 1;13) Row-Level Security (territories)
Level: Intermediate • Time: 45–60m • Tools: Power BI • Dataset: Sales + user-region map
-- RLS filter expression on DimRegion
[Region] IN
CALCULATETABLE(
VALUES(RegionUsers[Region]),
RegionUsers[UserEmail] = USERPRINCIPALNAME()
)14) SCD Type 2 (product categories over time)
Level: Intermediate • Time: 60–90m • Tools: SQL/ETL • Dataset: Products
Objective: Maintain start/end dates and a current flag; join to fact by transaction date.
15) Pareto 80/20 analysis
Level: Intermediate • Time: 40–60m • Tools: DAX or SQL • Dataset: Sales
WITH p AS (
SELECT product_id, SUM(amount) AS revenue
FROM sales GROUP BY 1
),
r AS (
SELECT product_id, revenue,
revenue / SUM(revenue) OVER () AS pct,
SUM(revenue) OVER (ORDER BY revenue DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
SUM(revenue) OVER () AS cum_pct
FROM p
)
SELECT * FROM r;16) Anomaly detection (z-score)
Level: Intermediate • Time: 45–60m • Tools: SQL/BI • Dataset: Daily sales
WITH s AS (
SELECT d, SUM(amount) AS rev FROM sales GROUP BY d
),
z AS (
SELECT d, rev,
(rev - AVG(rev) OVER()) / NULLIF(STDDEV_POP(rev) OVER(),0) AS z
FROM s
)
SELECT * FROM z WHERE ABS(z) > 3;17) Forecast with ETS (baseline)
Level: Intermediate • Time: 45–60m • Tools: BI or Excel • Dataset: Monthly revenue
Output: Baseline forecast + error bands; list assumptions & limitations.
18) Marketing funnel dashboard
Level: Intermediate • Time: 60–90m • Tools: BI • Dataset: Sessions → leads → opps → wins
Metrics: conversion %, CPA, ROAS; annotate bottlenecks.
19) Inventory turns & reorder alerts
Level: Intermediate • Time: 45–60m • Tools: BI • Dataset: Stock & sales
Measure idea (DAX): Reorder Flag = IF([Days of Stock] <= [Lead Time Days], 1, 0)
20) Geographic benchmarking
Level: Intermediate • Time: 45–60m • Tools: BI + map • Dataset: Stores & regions
Objective: Heat map + table of under/over-performers vs national median.
21) What-if simulator (pricing)
Level: Intermediate • Time: 45–60m • Tools: BI • Dataset: Price/volume curve
Output: slider-driven price → margin → revenue chart; snapshot decision.
22) Data dictionary & lineage
Level: Intermediate • Time: 30–45m • Tools: Docs/BI • Dataset: Your model
Objective: Publish a mini catalog: tables, fields, sources, refresh cadence.
Advanced (23–30): End-to-end projects & ML-flavored insights
23) Churn early-warning dashboard
Level: Advanced • Time: 90–120m • Tools: SQL + BI • Dataset: Subscription events
Objective: Flag risky accounts by drop in usage vs baseline; show save actions.
24) Customer segmentation (K-means)
Level: Advanced • Time: 90–120m • Tools: Python/R + BI • Dataset: RFM features
Output: labeled segments + segment-specific actions and value.
25) Anomaly triage center
Level: Advanced • Time: 60–90m • Tools: SQL/BI • Dataset: Revenue/events
Objective: One page listing outliers with context; drill to source tables.
26) End-to-end sales pipeline (capstone)
Level: Advanced • Time: 120–180m • Tools: SQL + BI • Dataset: CRM & revenue
- Model tables (Leads → Opps → Wins); define stages & conversion logic.
- KPIs: win rate, cycle length, stage drop-off.
- Dashboard with filters (rep, region, segment); add decision notes.
27) Forecast vs actual with variance trees
Level: Advanced • Time: 90–120m • Tools: BI • Dataset: Plan & actuals
28) SLA/operations reliability
Level: Advanced • Time: 60–90m • Tools: SQL/BI • Dataset: Ticket/ops logs
29) Executive story deck
Level: Advanced • Time: 60–90m • Tools: BI + Slides • Dataset: Your best 3 exercises
Output: 6–8 slides: problem → evidence → decision; no raw charts without captions.
30) Personal data product (public)
Level: Advanced • Time: 90–180m • Tools: BI + lightweight DB • Dataset: Curated mash-up
Objective: Publish a public interactive dashboard or video walkthrough showcasing your strongest skills.
Industry scenarios (swap into any exercise)
- Retail/eCommerce: returns %, AOV, product affinity, promo lift, inventory turns.
- Fintech/SaaS: MRR/ARR, churn risk, expansion, cohort retention, CAC payback.
- Healthcare/Public: appointment no-shows, readmission rates, inspections/violations, wait times.
Portfolio rubric (self-assessment)
| Dimension | 1 | 3 | 5 |
|---|---|---|---|
| Business framing | Metric dump | Clear KPIs | Tied to decision |
| Data handling | Unreproducible | Documented steps | Reusable model |
| Visual clarity | Cluttered | Readable | Annotated & print-ready |
| Insight | Descriptive | Trends + variance | Clear recommendation |
| Credibility | No notes | Assumptions listed | Limitations + next step |
30/60/90 learning plan
Days 1–30
- Beginner (1–10): 1 per day or 5 per weekend; publish notes and screenshots.
- Set up a clean repo/folder scheme; create a data dictionary skeleton.
Days 31–60
- Intermediate (11–22): windows, RLS, SCD, variance, forecasting.
- Refactor first dashboards; add tests/validations on joins & measures.
Days 61–90
- Advanced (23–30): at least one capstone + one public demo/video.
- Apply for roles with tailored portfolios; include your Analyst Notes.
FAQs
Which tools should I learn first?
Start with one BI tool (Power BI or Tableau) and SQL. Use Excel/Sheets for validation; add Python/R later for segmentation or automation.
How much time per exercise?
Most tasks take 30–90 minutes; capstones 2–3 hours. Ship small but complete artifacts rather than “perfect.”
How do I present exercises in a portfolio?
Create a single landing page listing each exercise with a thumbnail, a 3–5 sentence Analyst Note, and a link to the interactive report or PDF.
Next steps
Want companion files (sample CSVs and measure templates)? Offer a download bundle and link those files within each exercise. Add a clear CTA: newsletter, course, or demo—whichever matches your goals.
{
“@context”: “https://schema.org”,
“@type”: “ItemList”,
“name”: “30 Business Intelligence Exercises”,
“itemListElement”: [
{“@type”:”ListItem”,”position”:1,”name”:”Data profiling & quality report”},
{“@type”:”ListItem”,”position”:2,”name”:”Clean & standardize dimensions”},
{“@type”:”ListItem”,”position”:3,”name”:”Basic sales KPIs”},
{“@type”:”ListItem”,”position”:4,”name”:”Time intelligence: MoM/YoY”},
{“@type”:”ListItem”,”position”:5,”name”:”KPI dashboard (executive one-pager)”},
{“@type”:”ListItem”,”position”:6,”name”:”Dimensional modeling: star schema”},
{“@type”:”ListItem”,”position”:7,”name”:”Visual best practices”},
{“@type”:”ListItem”,”position”:8,”name”:”Data joins: INNER vs LEFT”},
{“@type”:”ListItem”,”position”:9,”name”:”Drill-through journey”},
{“@type”:”ListItem”,”position”:10,”name”:”Cohort split (new vs returning)”},
{“@type”:”ListItem”,”position”:11,”name”:”Window functions: rank contributors”},
{“@type”:”ListItem”,”position”:12,”name”:”Retention with LAG()”},
{“@type”:”ListItem”,”position”:13,”name”:”Row-Level Security (territories)”},
{“@type”:”ListItem”,”position”:14,”name”:”SCD Type 2 (categories over time)”},
{“@type”:”ListItem”,”position”:15,”name”:”Pareto 80/20 analysis”},
{“@type”:”ListItem”,”position”:16,”name”:”Anomaly detection (z-score)”},
{“@type”:”ListItem”,”position”:17,”name”:”Forecast with ETS (baseline)”},
{“@type”:”ListItem”,”position”:18,”name”:”Marketing funnel dashboard”},
{“@type”:”ListItem”,”position”:19,”name”:”Inventory turns & reorder alerts”},
{“@type”:”ListItem”,”position”:20,”name”:”Geographic benchmarking”},
{“@type”:”ListItem”,”position”:21,”name”:”What-if simulator (pricing)”},
{“@type”:”ListItem”,”position”:22,”name”:”Data dictionary & lineage”},
{“@type”:”ListItem”,”position”:23,”name”:”Churn early-warning dashboard”},
{“@type”:”ListItem”,”position”:24,”name”:”Customer segmentation (K-means)”},
{“@type”:”ListItem”,”position”:25,”name”:”Anomaly triage center”},
{“@type”:”ListItem”,”position”:26,”name”:”End-to-end sales pipeline (capstone)”},
{“@type”:”ListItem”,”position”:27,”name”:”Forecast vs actual with variance”},
{“@type”:”ListItem”,”position”:28,”name”:”SLA/operations reliability”},
{“@type”:”ListItem”,”position”:29,”name”:”Executive story deck”},
{“@type”:”ListItem”,”position”:30,”name”:”Personal data product (public)”}
]
}
{
“@context”:”https://schema.org”,
“@type”:”FAQPage”,
“mainEntity”:[
{“@type”:”Question”,”name”:”Which tools should I learn first?”,
“acceptedAnswer”:{“@type”:”Answer”,”text”:”Start with one BI tool (Power BI or Tableau) and SQL. Use Excel or Google Sheets for validation; add Python/R later for segmentation or automation.”}},
{“@type”:”Question”,”name”:”How much time per exercise?”,
“acceptedAnswer”:{“@type”:”Answer”,”text”:”Most tasks take 30–90 minutes; capstones 2–3 hours. Focus on shipping complete, small artifacts with a brief written takeaway.”}},
{“@type”:”Question”,”name”:”How do I present exercises in a portfolio?”,
“acceptedAnswer”:{“@type”:”Answer”,”text”:”Create a single landing page listing each exercise with a thumbnail, a 3–5 sentence Analyst Note, and a link to the interactive report or PDF.”}}
]
}