5 Real-World Business Intelligence Exercises to Build Your UK Portfolio

In my first BI role at a London firm, my manager didn’t ask me to ‘write a SQL query.’ She walked over to my desk and asked, “Why are our Q4 sales down in the North, and what can we do about it?” I was stuck. I knew how to run queries, but I didn’t know how to answer a real business question.

The internet is full of technical puzzles. Most “BI practice problems” are just that: puzzles. They ask you to JOIN Table A to Table B, and that’s it. They don’t teach you the single most important skill a Business Intelligence analyst needs: how to use data to provide a clear, actionable answer to a real business question.

This guide provides 5 end-to-end business intelligence exercises, framed as real-world requests. We’ll go from the initial stakeholder question, through the SQL query and data cleaning, to the final dashboard in Power BI or Tableau. We’ll even use relevant UK datasets. This is how you build a BI portfolio that gets you hired.

What Makes a Good BI Exercise? (And Why Most Fail)

A good exercise isn’t just a SQL puzzle, an ETL task, or a dashboard design. A great, real-world exercise is an end-to-end workflow, just like a mini-project.

It must contain these four elements:

1. The Business Question: The initial, often vague, request from a stakeholder (the “Why”).
2. The Data Hunt: The process of finding, understanding, and querying the data (the “How”).
3. The Transformation: The critical step of cleaning and modeling the data (the “Work”).
4. The Insight: The final visualization and, most importantly, the simple English answer (the “Value”).

Let’s work through five practical examples.

BI Exercise 1: The Sales Team’s “Urgent” KPI Dashboard

This is the classic, essential BI project for beginners.

Step 1: The Business Request

“The Head of Sales for our UK operation needs a one-page dashboard to track her team’s performance against monthly targets. She needs to see total revenue, units sold, and performance by salesperson, all filterable by region (London, North, Midlands).”

Step 2: The “Pro” Response (Requirements Gathering)

Your first step isn’t to open Power BI. It’s to ask clarifying questions. This shows senior-level thinking and is a key part of stakeholder management.

Your clarifying questions:

• “What is the exact calculation for ‘target’? Is it a monthly total or a YTD figure?”
• “When you say ‘revenue,’ do you mean gross revenue or net revenue after returns?”
• “How often does this data need to be refreshed? Daily? Weekly?”

Step 3: The Data, The Query & The DAX

Data: For this, you can use a mock “UK Sales” CSV. You can find similar datasets on [Kaggle] or create your own. You’ll need two tables: Sales_Fact (with Date, Salesperson_ID, Region, Revenue, Units_Sold) and Salesperson_Dim (with Salesperson_ID, Name).

SQL/ETL: The query is straightforward. You need to combine the tables.

SELECT
  s.Date,
  s.Region,
  s.Revenue,
  s.Units_Sold,
  d.Name
FROM
  Sales_Fact AS s
LEFT JOIN
  Salesperson_Dim AS d
  ON s.Salesperson_ID = d.Salesperson_ID;

DAX: In Power BI, you’ll need a few basic DAX practice exercises to create your Key Performance Indicators (KPIs).

• Total Revenue = SUM(Sales_Fact[Revenue])
• Total Units = SUM(Sales_Fact[Units_Sold])
• Target Variance = [Total Revenue] - [Total Target]

Step 4: The Visualization & The Insight (Power BI Portfolio Project)

Your dashboard should be simple and answer the questions instantly.

Pro-Tip: Dashboard Design Principles

Your stakeholder doesn’t care about your complex data model. They care about the number.

• Top-Left is Key: Put your main KPIs (Total Revenue, Total Units) as large “cards” in the top-left corner, as that’s where the eye goes first.
• Choose the Right Chart: Use a bar chart for ‘Sales by Rep.’ It’s far easier to compare values than a pie chart. Use a line chart for ‘Revenue over Time.’
• Use Slicers: Add slicers for Region and Date so the manager can self-serve.

BI Exercise 2: The Marketing Team’s Campaign Analysis

This exercise tests your ability to connect different data sources to measure performance.

Step 1: The Business Request

“The Marketing Manager wants to know which of our three Q3 email campaigns (‘Summer Sale’, ‘Back to School’, ‘Autumn Promo’) drove the most revenue for our e-commerce site. I need the report for our Friday meeting.”

Step 2: The “Pro” Response (Clarifying the ‘Why’)

This is a marketing analytics project. The key here is “attribution.” How do you prove the email caused the sale?

Your clarifying questions:

• “How are we tracking this? Are we using UTM parameters in the email links?”
• “What is our attribution model? Are we only counting sales where the email was the ‘last touch’ before purchase?”
• “Do you also want to see conversion rate (Clicks / Sales) or just total revenue?”

Step 3: The Data & The Query (SQL Projects for BI)

Data: You’ll have three tables: Campaigns (Campaign_ID, Name), Orders (Order_ID, Date, Revenue, UTM_Campaign_ID), and Email_Clicks (Click_ID, Date, UTM_Campaign_ID).

SQL: This is where you show your SQL expertise.

Common Mistake: Using the Wrong JOIN

A new analyst might use an INNER JOIN between the Campaigns table and the Orders table. This is a huge mistake.

Why? If the “Autumn Promo” had zero sales, it wouldn’t appear in the Orders table. Your INNER JOIN would make that campaign completely invisible, and you’d incorrectly report that it didn’t exist.

You must use a LEFT JOIN starting from your Campaigns table. This ensures every campaign is listed, even if its sales figures are £0.

SELECT
  c.Name AS Campaign_Name,
  COUNT(DISTINCT e.Click_ID) AS Total_Clicks,
  COUNT(DISTINCT o.Order_ID) AS Total_Orders,
  SUM(o.Revenue) AS Total_Revenue
FROM
  Campaigns AS c
LEFT JOIN
  Email_Clicks AS e
  ON c.Campaign_ID = e.UTM_Campaign_ID
LEFT JOIN
  Orders AS o
  ON c.Campaign_ID = o.UTM_Campaign_ID
WHERE
  c.Campaign_Name IN ('Summer Sale', 'Back to School', 'Autumn Promo')
GROUP BY
  c.Name;

Step 4: The Visualization & The Insight (Tableau Dashboard Example)

The insight is the story behind the numbers.

Don’t just say: “Summer Sale won with £50,000 in revenue.”

Provide a real insight: “While the ‘Summer Sale’ drove the most total revenue (£50,000), the ‘Back to School’ campaign had a 30% higher conversion rate. This suggests its targeted messaging was far more effective. We should test this messaging on a wider audience.”

BI Exercise 3: The HR Team’s UK Attrition Report

This is a perfect business analyst case study. It focuses less on complex queries and more on data cleaning and defining metrics.

Step 1: The Business Request

“Our London office seems to have a high staff turnover. The HR Director needs a report showing employee attrition rates for the last 12 months, broken down by UK office (London, Manchester, Bristol) and department.”

Step 2: The “Pro” Response (The ‘Soft Skills’ Exercise)

This request is sensitive. The most important part of this HR analytics project is agreeing on the metric’s definition.

Your clarifying questions:

• “How are we formally defining ‘attrition’? Is it only voluntary leavers (resignations)?”
• “Do we include internal transfers? What about retirements or redundancies?”
• “The rate is (Leavers / Average Headcount). How do we want to calculate Average Headcount? Is it (Start + End) / 2 for the period?”

Step 3: The Data & The Query (Data Cleaning Exercises)

Data: You’ll almost always get a single, messy Employees CSV file. This is a data cleaning exercise.

• The department column has “Sales”, “sales”, “Sales Dept.”, and “Marketing”.
• The leave_date column has NULL values for active staff.
• The office_location column has “London” and “London (Soho)”.

ETL/Cleaning: You must fix this before you can analyze. You can use SQL or a BI tool’s built-in cleaner.

• In Power Query: Use the “Trim,” “Clean,” and “Replace Values” features. Group “Sales,” “sales,” etc., into a single “Sales” category.
• In SQL: Use functions like UPPER(), TRIM(), and CASE statements.
  SQL

  SELECT
    Employee_ID,
    CASE
      WHEN UPPER(TRIM(Department)) IN ('SALES', 'SALES DEPT.') THEN 'Sales'
      WHEN UPPER(TRIM(Department)) = 'MARKETING' THEN 'Marketing'
      ELSE 'Other'
    END AS Clean_Department,
    Hire_Date,
    Leave_Date,
    CASE
      WHEN Leave_Date IS NOT NULL THEN 1
      ELSE 0
    END AS Is_Leaver
  FROM
    Employees_Messy;
  

Step 4: The Visualization & The Insight

Visualization: Use a line chart to show ‘Attrition Rate over Time’. Use a stacked bar chart to show ‘Leavers by Department and Office’.

The Insight: “The dashboard confirms the hypothesis. The overall attrition rate is 15%, but the London office’s rate is 25%. This is driven almost entirely by the Sales department, which saw 80% of its leavers exit in Q2 and Q3.”

BI Exercise 4: The “Blue-Sky” Data Exploration (Using UK Open Data)

This is a more advanced exercise that shows real experience. Sometimes, a manager will give you a “no-request” request.

The ‘No-Request’ Request

“We have all this open-source data. Is there anything interesting in there for our business? Have a look.”

Your Task: Find an Insight

This is your chance to show curiosity and business-driven-decision making. We’ll use our UK target location to our advantage.

1. Get the Data: Go to data.gov.uk. This is an authoritative source for UK government data.
2. Find a Dataset: Search for and download the “Road Safety Data – Accidents 2022” file.
3. Create a Context: Imagine you work for a UK-based car insurance company. Your goal is to find a pattern that could inform risk pricing.
4. Explore: Load this massive CSV into Power BI or Tableau. (This also tests your ability to handle large files).
5. Ask Questions:
   • Is there a correlation between time of day and accident severity?
   • Which car models are most common in accidents in London vs. Manchester?
   • Does “Speed Limit” have a strong correlation with accident severity?

This data visualization project is all about exploration. Finding one interesting, relevant pattern (e.g., “Accidents involving 20-25 year old drivers in urban areas are 50% more likely to occur between 10 PM and 2 AM”) is the entire goal.

Building Your BI Portfolio: How to Present These Exercises

A portfolio with just a few screenshots of dashboards is useless. You must show your thinking. Use the STAR method to tell the story for each project.

• Situation: “The sales team needed a way to track KPIs against regional targets.”
• Task: “My task was to build an automated Power BI dashboard that clearly showed revenue, units sold, and salesperson performance, filterable by date and UK region.”
• Action: “I used SQL to JOIN the Sales_Fact and Salesperson_Dim tables. Then, in Power BI, I created three core DAX measures to calculate Total Revenue, Total Units, and Target Variance. I built a simple, one-page report with slicers for region and date.”
• Result: “The final dashboard provided an at-a-glance view of performance, enabling the sales manager to instantly identify that the ‘North’ region was 15% behind target.”

Host your SQL code on GitHub. Publish your interactive dashboards using Tableau Public or Power BI’s “Publish to web” feature. Then, write a simple blog post or project summary explaining your STAR story. This is exactly what recruiters want to see.

Conclusion: From “Exercises” to “Experience”

The best business intelligence exercises are not technical tests. They are end-to-end business problems.

Practicing the full workflow—from the vague stakeholder request, through the data cleaning and SQL, to the final, actionable insight—is what separates a beginner from a professional. The tools like Power BI, Tableau, and SQL are just the ‘how.’ Your real value as a BI professional is in understanding the ‘why.’

Always ask ‘why’ you are building something. When you do, you stop being a dashboard-builder and become a trusted partner in business-driven-decision making.

FAQs

How do I practice business intelligence skills?

Don’t just practice isolated skills like SQL or DAX. Practice the full, end-to-end project workflow.

1. Start with a business question.
2. Find a free dataset (from Kaggle or data.gov.uk).
3. Write a SQL query to extract the data.
4. Clean and model the data in Power BI or Tableau.
5. Build a dashboard that answers the initial question.
6. Write up your findings using the STAR method.

What are good BI projects for beginners?

A sales performance dashboard is the best project for beginners. Our Exercise 1 in this guide is a perfect starting point. It teaches you the fundamentals: calculating KPIs, using date tables, and building simple, effective bar and line charts.

What are the 5 basic tasks of business intelligence?

The five core tasks of any BI process are:

1. Data Collection: Gathering raw data from databases, files, and APIs.
2. Data Preparation: Cleaning, transforming, and modeling the data (ETL).
3. Data Analysis: Using queries (like SQL) and statistical tools to find patterns.
4. Data Visualization: Creating dashboards and reports (in Power BI, Tableau).
5. Decision Support: Presenting the final insights to stakeholders to help them make a decision.

Can I learn Power BI on my own?

Yes, absolutely. The best place to start is the official [Microsoft Learn for Power BI]. It provides free, authoritative documentation and tutorials. Once you understand the basics, the best way to master the tool is to move on to your own projects, like the ones in this guide.

What SQL skills are needed for BI?

For 90% of BI analyst jobs, you need a solid grasp of these core SQL skills:

• SELECT and WHERE (for filtering)
• GROUP BY and HAVING (for aggregation)
• JOINs (especially INNER and LEFT JOINs)
• CASE statements (for basic data cleaning)

More advanced roles will also look for window functions like RANK() and ROW_NUMBER().

How can I build a BI portfolio with no experience?

You don’t need a job to get experience. Use the business intelligence exercises in this guide. Find a public dataset you are passionate about (e.g., UK football stats, London property prices) and build a project around it. Document your entire process (the question, the query, the cleaning steps, the final insight) on GitHub or a simple blog. This is the experience recruiters want to see.

What is a BI case study?

A BI case study is a real-world business problem, just like our exercises. It’s a story, not just a dashboard.

• Bad example: “I built a sales dashboard.”
• Good example: “The business had a problem with high staff turnover (Situation). I was tasked with finding the cause (Task). I analyzed HR data and discovered the problem was isolated to one team in one office (Action), allowing HR to take targeted action (Result).”

ukblogspot.co.uk

About the Author

Mohammed Saad

Administrator

Hello, I'm Mohammed Saad is the founder of UKblogspot.co.uk. I'm a digital marketer and content writer with 2 years of experience, focused on creating helpful, well-researched content on diverse topics for a UK audience.

Visit Website View All Posts