A retailer’s dashboard shows that sales fell last month. One analyst recommends more advertising, another suggests discounting, and a third notices that the reporting pipeline has not loaded the final weekend. Each response could look reasonable on a slide. Only the last begins by checking whether the reported change is real.
A business intelligence exercise can teach much more than how to create a chart. It can give someone practice moving from an uncertain business question to an answer whose definitions, evidence, and limitations another person can inspect. SQL, spreadsheets, and reporting tools are part of that work, alongside the judgement needed to decide what a number means.
Clean practice datasets remain useful for learning a specific technique. The next stage is to introduce complications deliberately, with an answer key that distinguishes a data problem from a genuine business finding. Learners should be able to explain both the result and the route used to establish it.
Give the Exercise a Decision and a Boundary
Consider an exercise in which a retail manager has a fixed promotional budget for the next month. The task is to investigate a reported revenue decline and recommend whether the available evidence supports changing the promotion plan. This is narrower and more useful than asking for an attractive sales dashboard.
Specify the reporting periods, business units, and available sources. Supply order lines, order headers, refunds, product information, and monthly targets. Include enough documentation to begin, while leaving realistic questions that the analyst must resolve with the exercise owner.
The assessment should reward clarification. Does revenue mean ordered value, paid value, recognised revenue, or net sales after refunds? Are taxes and delivery charges included? Which date assigns a refund to a reporting period? Different definitions can be legitimate, but they cannot be silently mixed in one comparison.
This is the same starting point as a practical data analysis strategy: decide what question the evidence needs to answer before choosing the output. A learner who identifies a missing definition has made progress even before writing a query.
Make the Grain Visible Before Adding Joins
The grain describes what one row represents. An order header may represent one order, while an order-line table contains several rows for the same order. A monthly target might represent one region and month. These tables can be useful together, but they cannot all be summed after an unrestricted join.
For example, suppose an order has two lines worth 60 and 40 dollars, and the order header records a five-dollar delivery charge. Joining the header to its two lines repeats the delivery charge. Summing both joined copies reports ten dollars of delivery even though the order charged five.
Ask the learner to identify the grain of every supplied table and demonstrate the expected cardinality of each join. Joining order lines directly to several refund records can multiply both sides. Aggregating each source to an appropriate common grain before joining may resolve that particular problem.
Microsoft’s star-schema guidance explains the different roles of fact and dimension tables in Power BI models. Use that distinction to organise the exercise, while still checking the actual relationships. Calling a model a star schema does not prove that its keys are unique or its measures aggregate correctly.
The learner should retain a reconciliation query or worksheet at a simple reference grain. A total that matches before adding customer segmentation and changes afterwards gives a useful signal: investigate the relationship or filtering behaviour before interpreting the new total as business movement.
Introduce Data Problems With Accountable Resolutions
Messy data is useful training when the problems have a purpose. Add one duplicated extract, a missing product category, a malformed date, and a refund arriving after the first reporting cut-off. Record these deliberately in the instructor’s answer key rather than allowing accidental errors to determine the lesson.
A duplicated order identifier is not automatically a duplicated sale. The source might contain order revisions, separate lines, or repeated exports. The exercise should require the analyst to establish which case applies and document the rule used to select or combine records.
Missing values need similarly explicit treatment. Dropping orders with an unknown category can make a category chart look complete while understating total sales. Keeping an unknown category and showing its contribution may be the more honest result. The appropriate choice depends on the question and should be explained.
Maintain the raw input and a record of transformations. When a category is corrected or a timestamp is interpreted, another analyst should be able to repeat the operation. This turns data cleaning into a reviewable part of the analysis rather than an undocumented collection of spreadsheet edits.
| Exercise complication | Question for the learner | Useful evidence |
|---|---|---|
| Repeated extract | Is this the same event or another revision? | Source keys and extraction history |
| Order and line join | Did the join multiply a header amount? | Counts and totals at both grains |
| Missing category | What is excluded from the breakdown? | Explicit unknown group and reconciliation |
| Late refund | Which reporting period should change? | Agreed date policy and cut-off |
| Several currencies | Are amounts comparable? | Currency scope and conversion policy |
Define Measures That Survive a Second Reader
A measure needs a numerator, denominator where relevant, inclusion rules, and a time basis. Average order value can mean net sales divided by qualifying orders, but the treatment of fully refunded orders changes the result. Write the definition beside the calculation so the reviewer can see that choice.
Use a small illustrative checkpoint. Suppose a period contains 100 qualifying orders, gross item sales of 10,000 dollars, and refunds of 800 dollars. Under a definition that subtracts those refunds and retains all 100 orders in the denominator, net sales are 9,200 dollars and average order value is 92 dollars. These are exercise assumptions, not universal accounting definitions.
Comparisons need the same discipline. A calendar month with fewer trading days can have lower total sales without a corresponding decline in sales per trading day. Showing both may help, but dividing by days does not automatically control for weekday mix, holidays, or promotional differences.
Targets also have a grain. A regional monthly target copied onto every transaction will be overstated if summed repeatedly. Ask learners to show how the target enters the model and how its aggregation behaves when users change filters.
Require a Report That Separates Observation From Explanation
After checking totals, the analyst can break the change down by product, region, channel, or customer group. Begin with the groups that contribute most to the measured difference. An unusually large percentage change in a tiny category may matter less to the decision than a modest decline in the largest category.
A useful report can state that sales declined mainly in one region and that the decline coincided with fewer completed orders. It cannot establish that a competitor’s campaign caused the decline unless the supplied evidence supports that explanation. Encourage hypotheses, but label them and identify what further evidence would test them.
Dashboard design should help the reader inspect the claim. Include the period, unit, currency, refresh time, and important filters. Use labels and chart scales that make comparisons readable, and keep a route to the supporting data. A concise chart can be effective without concealing exclusions or uncertainty.
The recommendation should follow the available evidence. If the final weekend is missing, the appropriate immediate action may be to repair and refresh the report before reallocating budget. If the decline remains after reconciliation, the analyst can recommend a focused investigation or experiment rather than presenting a speculative cause as established.
Add a Second Run to Test Operational Thinking
Once the first analysis is complete, supply another extract. Change a source column name, add a late record, or update a product classification. Ask whether the existing process still works and whether the changed result can be explained. This reveals assumptions hidden in a successful one-off demonstration.
A historical classification change deserves particular care. Reporting prior sales using today’s customer segment answers a different question from reporting the segment recorded at the time of sale. The exercise can ask learners to choose and justify the intended view rather than assuming one is always correct.
For performance practice, increase the dataset enough to expose repeated scans or expensive joins, then inspect the relevant query plan and refresh behaviour. Avoid teaching a blanket rule that every slow query needs another index. The problem might be an unintended many-to-many join or unnecessary data movement.
Operational realism also includes access. Give learners only the customer attributes needed for the task, and ask whether exported reports preserve the intended restrictions. A dashboard that produces correct totals can still reveal more detailed data than its audience should receive.
Assess the Reasoning Alongside the Artefact
The submission should contain the measure definitions, repeatable preparation steps, reconciliation evidence, and a short decision note, as well as the report. Assess whether another analyst can reproduce the result and explain what would change if the assumptions changed.
Include known-answer checks for arithmetic and seeded data problems, while allowing more than one defensible recommendation. A learner may correctly conclude that the evidence is insufficient to explain a decline. That is a stronger analytical result than inventing a cause to satisfy the expected ending.
This gives business intelligence practice a clear progression: learn the tools on bounded examples, then use those tools to investigate increasingly realistic decisions. The dashboard remains important, but it becomes the visible result of an analysis that can withstand questions about its data, definitions, and implications.
