Business Requirements, Metrics and KPI Frameworks

Introduction

A business intelligence dashboard is valuable only when it supports a real decision. Power BI and Tableau can connect to data, calculate measures and produce attractive visualisations, but neither tool can determine what an organisation should measure. That responsibility belongs to the analyst, who must translate organisational goals into clear analytical requirements.

This chapter presents a practical framework for moving from a business problem to a usable dashboard. The process involves identifying stakeholders, clarifying decisions, formulating analytical questions, defining user stories, selecting dimensions and measures, designing key performance indicators (KPIs), setting targets and agreeing success criteria. It also considers balanced scorecards, KPI trees, leading and lagging indicators, benchmarks, governance and ethical risks.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish business problems, analytical questions, measures, dimensions and KPIs;
• convert stakeholder needs into precise user stories and dashboard requirements;
• define SMART metrics with suitable targets, time periods and ownership;
• construct KPI trees and balanced scorecard views;
• distinguish leading indicators from lagging indicators;
• calculate and interpret common business measures;
• specify requirements that can be implemented in Power BI or Tableau;
• evaluate data quality, governance, ethical risks and dashboard success.

From Business Problems to Analytical Questions

A business problem describes an undesirable condition or an opportunity. It is usually expressed in managerial language: “Customer retention is falling,” “branches are missing service-level commitments,” or “marketing expenditure is increasing without corresponding sales growth.” Such statements are not yet dashboard specifications.

A useful translation process has five stages:

1. Business objective: What organisational outcome matters?
2. Decision: What decision must a person make?
3. Analytical question: What evidence is needed to support that decision?
4. Metric and dimension: What will be measured, and how will it be segmented?
5. Action and success criterion: What action should follow, and how will improvement be judged?

For example:

An analytical question should identify a population, period, comparison or decision context. “How are sales?” is weak because it has no measure, time frame or comparison. “How did monthly net sales by region compare with the budget during the current quarter?” is substantially more useful.

Requirements Elicitation and User Stories

Requirements elicitation is the structured discovery of what users need from an information product. Important stakeholders may include executives, operational managers, analysts, data engineers, finance staff, compliance officers and dashboard consumers. Different stakeholders may use the same data for different decisions.

Useful elicitation techniques include interviews, observation, workshops, document analysis, prototype reviews and analysis of existing reports. The analyst should ask:

• What decision are you trying to make?
• How frequently is the decision made?
• What action follows a high or low result?
• Which definitions currently cause disagreement?
• What level of detail is required?
• Which filters, comparisons and alerts are necessary?
• What data is trusted, restricted or unavailable?

A user story expresses a requirement from the user’s perspective:

As a regional sales manager, I want to compare actual revenue with target by branch and product category each week, so that I can redirect sales activity before the month closes.

A good user story is specific enough to test. Acceptance criteria might state that the dashboard must display current-week revenue, target variance, branch ranking, product-category filtering and a last-refresh timestamp. Requirements should be prioritised. A common classification is:

• Must have: essential to the decision;
• Should have: important but not essential for the first release;
• Could have: useful enhancement;
• Won’t have now: deliberately deferred.

This prevents dashboards from becoming collections of every available field.

Measures, Dimensions and Grain

A measure is a quantitative value that can be aggregated or analysed, such as revenue, units sold, cost, response time or number of orders. A dimension describes the context in which a measure is examined, such as date, customer, product, branch, channel or salesperson.

A fact is an event or recorded business occurrence, such as an invoice line, website visit or support ticket. A dimension table supplies descriptive attributes used for filtering and grouping. In a dimensional model, facts and dimensions are related through keys.

The grain specifies what one row in a fact table represents. Examples include “one row per invoice line,” “one row per customer per month” and “one row per support case.” Grain must be established before measures are defined. If invoice data is joined incorrectly to a customer table containing multiple records per customer, revenue may be duplicated.

Measures may be:

• Additive: safely summed across all relevant dimensions, such as units sold;
• Semi-additive: summed across some dimensions but not time, such as account balance;
• Non-additive: not meaningfully summed, such as a percentage or average temperature.

A dashboard requirement should state the aggregation rule. “Average delivery time by region” is different from “total delivery time by region,” and an average of daily percentages may not equal the correct overall percentage.

Metric Design and Common Calculations

A metric is a defined method of quantifying an aspect of performance. A KPI is a metric judged to be strategically important and linked to a target, owner and action. Therefore, every KPI is a metric, but not every metric is a KPI.

Ratios and rates

A rate compares an event count with an appropriate population or exposure. The general form is:

The numerator must be a subset of the denominator, the period must be consistent, and the unit should be stated. For example, if 240 of 3,000 orders were returned:

A conversion rate might be:

The organisation must define “conversion” and “qualified opportunity”; otherwise users may compare incompatible results.

Growth and variance

Percentage growth from an earlier value to a later value is:

The previous value must not be zero. If revenue rises from $80,000 to $92,000:

Absolute variance from target is:

Percentage variance is:

For cost measures, a negative variance may be favourable, whereas for revenue it may be unfavourable. The dashboard should therefore label status according to business meaning, not merely mathematical sign.

Weighted averages

When groups differ in size, an overall average should generally be weighted. If group values are xᵢ and weights are wᵢ, with total weight greater than zero:

For example, a 90% satisfaction score from 100 responses and an 80% score from 900 responses produces:

A simple average of 90% and 80% would be 85%, which would misrepresent the combined population.

SMART Metrics and KPI Specifications

A strong KPI is Specific, Measurable, Achievable, Relevant and Time-bound. SMART is a design discipline rather than a guarantee that a target is strategically correct.

A KPI specification should include:

Targets should be based on strategy, capacity, historical performance, external benchmarks or negotiated service commitments. A target without a time horizon is incomplete. Thresholds should also be documented. For a “higher is better” KPI, green might mean achievement of at least 100% of target; for a “lower is better” KPI, the rule must be reversed.

KPI Trees and Causal Logic

A KPI tree decomposes a high-level outcome into drivers that managers can influence. For example, profit can be represented as:

Revenue may be decomposed as:

These relationships are useful only when definitions and units are compatible. A retail KPI tree might connect profit to revenue, price, volume, product mix, variable cost, fixed cost, conversion rate and average basket value.

A KPI tree helps distinguish outcome measures from drivers. It does not prove causation. A rise in advertising spend may coincide with higher sales, but seasonality, price changes or competitor activity may also contribute. Analysts should avoid presenting a visual hierarchy as causal evidence unless the relationship has been properly tested.

Leading and Lagging Indicators

A lagging indicator records an outcome after it occurs: quarterly profit, annual employee turnover or completed deliveries. It is useful for assessing results but may provide little time to intervene.

A leading indicator provides earlier information about conditions likely to influence an outcome: sales pipeline, training completion, defect rate, website abandonment or preventive-maintenance completion. Leading indicators are valuable for management action but may be noisy and may not reliably predict the final outcome.

A balanced performance system uses both. For example, a customer-service scorecard might include:

• lagging: customer retention and complaint resolution satisfaction;
• leading: first-response time, unresolved cases older than 48 hours and agent training completion.

The relationship should be reviewed over time rather than assumed. A leading indicator is useful when it is timely, controllable, consistently measured and empirically associated with the desired outcome.

Balanced Scorecards and Strategic Alignment

The balanced scorecard extends performance measurement beyond short-term financial results. Its traditional perspectives are:

1. Financial: revenue, margin, cash flow and return on investment;
2. Customer: satisfaction, retention, complaints and service quality;
3. Internal process: cycle time, quality, productivity and reliability;
4. Learning and growth: skills, innovation, engagement and capability.

The framework encourages a portfolio of measures rather than optimisation of one number. For example, reducing service cost while increasing complaints would indicate that financial improvement is damaging customer and process performance.

A scorecard should not become a long list of unrelated measures. Each KPI should connect to a strategic objective, have a clear owner and support a decision. Objectives can be displayed in a strategy map showing how learning and process capabilities are expected to influence customer outcomes and, ultimately, financial outcomes. These links are hypotheses to be monitored, not automatic proof of causality.

Benchmarks, Targets and Success Criteria

A target is a desired future level for an organisation. A benchmark is a comparison point. Benchmarks may be:

• internal: another branch, period or product;
• historical: previous performance after accounting for seasonality;
• external: industry or competitor reference data;
• standard-based: a contractual, regulatory or service-level requirement.

Comparisons must be like-for-like. A branch serving a different customer segment should not be judged solely against a high-volume urban branch. External benchmark definitions, sampling methods and periods should be documented.

Dashboard success criteria should cover both product and business outcomes. Examples include:

• 90% of intended managers access the dashboard weekly;
• refresh completes by 07:00 on business days;
• reconciliation with the approved finance report is within an agreed tolerance;
• managers report a reduction in manual reporting time;
• documented decisions use the dashboard’s definitions and evidence.

Adoption alone does not prove value. A dashboard can be frequently viewed yet fail to improve decisions.

Translating Requirements into Power BI and Tableau Designs

The requirements document should precede visual design. A practical specification includes a page purpose, audience, KPIs, filters, drill paths, data sources, refresh frequency, security rules and acceptance tests.

In Power BI, requirements may map to a star schema, relationships, DAX measures, calculated tables, row-level security and scheduled refresh. In Tableau, they may map to data sources, calculated fields, parameters, sets, hierarchies, actions and published workbooks. The tool changes implementation details, but not the underlying requirement discipline.

A measure should normally be calculated from the correct fact-table grain rather than copied as a fixed value. Time intelligence requires a reliable date dimension and a clearly defined calendar. Dashboard filters should have predictable scope, and tooltips should expose definitions where ambiguity is likely.

A useful dashboard layout often contains:

• a summary area for the most important KPIs;
• trend views showing direction and seasonality;
• breakdowns by meaningful dimensions;
• exception views identifying where action is required;
• definitions, refresh time and data-quality notices.

Visual emphasis should correspond to decision importance. Excessive colour, three-dimensional charts, unexplained gauges and decorative graphics reduce analytical clarity.

Applied Case: Illustrative Retail Service Dashboard

Background

The fictional company Northstar Retail operates 25 stores and an online channel. Senior managers believe that sales growth is slowing, but store managers argue that stock availability and delivery delays are the main causes. Existing reports use different definitions of “sales,” “on-time delivery” and “active customer.”

Problem

The organisation needs a weekly dashboard that helps regional managers identify underperforming stores, distinguish demand problems from fulfilment problems and act before the monthly review.

Analysis

Workshops identify three decisions:

1. Which stores require immediate intervention?
2. Are weak results caused by low traffic, poor conversion, stock-outs or delivery performance?
3. Which product categories and customer segments require attention?

The agreed grain is one row per order line for sales and one row per delivery for fulfilment. Approved measures include net sales, units, gross margin, conversion rate, stock-out rate, on-time delivery rate and repeat-customer rate.

Definitions are documented. Net sales exclude cancelled orders and include approved discounts. On-time delivery means delivery on or before the promised date. A stock-out is recorded when a requested product is unavailable at the time of order.

Approach and solution

The dashboard contains:

• an executive page with net sales, margin, conversion and on-time delivery;
• a regional page with store ranking and target variance;
• a driver page showing traffic, conversion, stock-outs and delivery performance;
• a product page with category and SKU-level exceptions;
• a definitions panel and data-refresh timestamp.

A KPI tree links net sales to traffic, conversion and average order value. A second branch connects customer experience to stock-outs and delivery reliability. Leading indicators include stock-out rate and unresolved delivery exceptions; lagging indicators include repeat-customer rate and monthly margin.

Acceptance testing requires that dashboard totals reconcile with the approved finance report, filters work across all relevant visuals, restricted salary fields are inaccessible and each KPI displays its unit, period and target.

Outcome

After a pilot, managers discover that two stores have acceptable traffic but low conversion associated with stock-outs in high-demand categories. Another region has strong conversion but declining repeat purchases after delivery delays. The organisation redirects inventory and reviews carrier performance rather than applying the same sales intervention to every store.

Learning points

The case demonstrates that a useful dashboard is not simply a collection of sales charts. It depends on agreed definitions, correct grain, driver analysis, target ownership, leading and lagging indicators, and a clear connection between evidence and action.

Challenges, Governance and Ethical Issues

Metric governance is essential because definitions can influence resource allocation, performance appraisal and customer treatment. A KPI catalogue should record definitions, owners, lineage, update frequency, revision history and access restrictions.

Important risks include:

• definition drift: changing a calculation without preserving historical comparability;
• double counting: joining tables at incompatible grains;
• denominator bias: calculating rates from an incomplete population;
• survivorship bias: analysing only customers or employees who remain;
• Goodhart’s law: when a measure becomes a target, people may optimise the measure rather than the underlying objective;
• privacy risk: exposing personally identifiable or sensitive information;
• algorithmic bias: using historical data that reflects unequal treatment;
• false precision: presenting uncertain estimates as exact facts;
• accessibility failure: using colour alone or designs that exclude users with disabilities.

Role-based access, row-level security, data minimisation, audit logs and documented retention policies reduce risk. Personally identifiable information should not be displayed when aggregated data is sufficient. Automated or AI-assisted insights should be reviewed by accountable staff, especially where decisions affect credit, employment, healthcare or access to services.

Contemporary Practice and Best Practices

Modern BI environments increasingly combine semantic models, governed metric layers, natural-language querying, anomaly detection and embedded analytics. AI can help identify unusual patterns or generate narrative summaries, but it does not replace metric governance. Generated explanations may be incomplete, statistically misleading or based on incorrect filters.

Best practice is to:

1. begin with decisions rather than visual types;
2. define grain, numerator, denominator and exclusions;
3. maintain one governed definition for each enterprise KPI;
4. combine outcome and driver measures;
5. show target, actual, variance, period and owner;
6. test calculations against independent totals;
7. document assumptions and data limitations;
8. design for accessibility and appropriate security;
9. pilot with users and observe real decisions;
10. review whether the dashboard changes outcomes, not merely whether it is viewed.

Chapter Summary

Business intelligence requirements begin with organisational objectives and decisions, not with software features. Analysts translate problems into analytical questions, user stories, measures, dimensions and dashboard specifications. Measures require precise definitions, appropriate grain and valid aggregation. Ratios, growth rates, variances and weighted averages must use consistent populations, periods and units.

SMART metrics make expectations explicit, while KPI specifications connect definitions to targets, owners, data sources and status rules. KPI trees decompose outcomes into drivers, and balanced scorecards protect against narrow optimisation by combining financial, customer, process and learning perspectives. Leading indicators support early intervention; lagging indicators assess achieved outcomes. Targets and benchmarks must be comparable and justified.

Power BI and Tableau implement requirements through models, measures, filters, security and visual interactions, but neither tool resolves ambiguous definitions or poor governance. Effective dashboards are accurate, actionable, accessible, secure and evaluated by the decisions and outcomes they improve.

Key Terms

Business problem: An undesirable condition or opportunity requiring managerial attention.

Analytical question: A precise question that identifies evidence needed for a decision.

User story: A requirement expressed from the perspective of a user and intended action.

Measure: A quantitative value calculated or recorded from data.

Dimension: A descriptive category used to filter, group or compare measures.

Fact: A recorded business event or occurrence.

Grain: The level of detail represented by one row in a dataset.

Additive measure: A measure that can be summed across relevant dimensions.

KPI: A strategically important metric linked to a target, owner and action.

SMART: Specific, Measurable, Achievable, Relevant and Time-bound.

KPI tree: A hierarchy decomposing an outcome into contributing drivers.

Leading indicator: A measure providing early information about likely future performance.

Lagging indicator: A measure recording an outcome after it has occurred.

Balanced scorecard: A performance framework covering financial, customer, process, and learning perspectives.

Target: A desired level of future performance.

Benchmark: A reference point used for comparison.

Variance: The difference between actual performance and a target or comparison value.

Metric governance: Policies and practices controlling definitions, ownership, quality and access.

Row-level security: Access control restricting which records a user can view.

Success criterion: A testable condition used to judge whether a dashboard or initiative has achieved its purpose.

Review Questions

Short Answer Questions

1. Distinguish a measure from a dimension and give one example of each.
2. Why must the grain of a fact table be established before defining measures?
3. What does SMART mean in the context of KPI design?
4. State one difference between a leading indicator and a lagging indicator.
5. Why might an organisation use a balanced scorecard rather than financial measures alone?

Long Answer Questions

1. Explain the process of translating a business problem into a dashboard requirement, using an example.
2. Discuss the importance of numerator, denominator and population definitions when designing rate metrics.
3. Explain how a KPI tree can support managerial action while still failing to prove causation.
4. Describe the four traditional perspectives of the balanced scorecard and their value in BI.
5. Design a KPI specification for a customer-service dashboard, including formula, target, owner, dimensions and limitations.

Analytical / Application Questions

1. A company records 1,200 orders, of which 96 are returned. Calculate the return rate and identify two definitions that should be confirmed before comparing it with another branch.
2. A region reports 92% on-time delivery from 50 deliveries and another reports 84% from 950 deliveries. Calculate the combined on-time delivery rate using a weighted average and explain why a simple average is inappropriate.
3. Store revenue fell by 4%, but traffic fell by 12% while conversion increased. Construct a short KPI-tree analysis identifying what additional measures would be needed before recommending an intervention.

Multiple Choice Questions with Answers

Question 1

Which item is a dimension in a retail dashboard?

A. Net sales

B. Gross margin

C. Product category

D. Units sold

Correct Answer: C. Product category

Question 2

A fact-table grain specifies:

A. The colour scheme of a dashboard

B. What one row represents

C. The target value of a KPI

D. The number of dashboard pages

Correct Answer: B. What one row represents

Question 3

If 240 of 3,000 orders are returned, what is the return rate?

A. 0.8%

B. 8%

C. 12.5%

D. 80%

Correct Answer: B. 8%

Question 4

Which is a lagging indicator?

A. Training completion

B. Sales pipeline value

C. Monthly customer retention

D. Preventive-maintenance completion

Correct Answer: C. Monthly customer retention

Question 5

Which balanced-scorecard perspective includes employee capability and innovation?

A. Financial

B. Customer

C. Internal process

D. Learning and growth

Correct Answer: D. Learning and growth

Question 6

What is the main purpose of a KPI tree?

A. To decorate a dashboard hierarchy

B. To decompose an outcome into contributing drivers

C. To replace data-quality testing

D. To restrict user access

Correct Answer: B. To decompose an outcome into contributing drivers

Question 7

Revenue rises from $80,000 to $92,000. What is the percentage growth?

A. 8%

B. 12%

C. 15%

D. 20%

Correct Answer: C. 15%

Question 8

Which requirement is most specific?

A. Show sales performance

B. Make the dashboard attractive

C. Compare weekly net sales with target by branch and category

D. Include all available data

Correct Answer: C. Compare weekly net sales with target by branch and category

Question 9

Why is a weighted average appropriate when combining service scores from groups of different sizes?

A. It gives every group identical influence

B. It reflects the number of observations in each group

C. It removes the need for a denominator

D. It converts every score into a target

Correct Answer: B. It reflects the number of observations in each group

Question 10

Which practice best reduces metric ambiguity across departments?

A. Allow each department to define the KPI independently

B. Remove the KPI owner from documentation

C. Maintain a governed KPI catalogue with definitions and lineage

D. Use colour instead of numerical values

Correct Answer: C. Maintain a governed KPI catalogue with definitions and lineage