BI Requirements and Analytical Planning

Introduction

A Power BI report is only as useful as the business decision it supports. Many reporting projects fail not because the software is difficult to use, but because the team begins with data, charts, or visual design before clarifying the underlying business need. Analytical planning provides the bridge between an organisational question and a reliable, usable Power BI solution.

Business intelligence (BI) requirements describe what decision-makers need to know, why they need to know it, how information should be measured, which data is required, and how the completed report will be judged. Analytical planning turns these requirements into a structured specification for data modelling, calculations, visualisations, security, refresh, and deployment.

This chapter presents a practical workflow for translating stakeholder needs into measurable objectives, key performance indicators (KPIs), data requirements, report specifications, and acceptance criteria. The emphasis is on foundational professional practice suitable for Power BI projects in business, public-sector, and non-profit environments.

Learning Objectives

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

1. Distinguish business questions, analytical questions, KPIs, metrics, and targets.
2. Identify and manage stakeholders during requirements discovery.
3. Convert vague requests into measurable analytical objectives.
4. Define KPIs using clear numerators, denominators, time periods, units, targets, and ownership.
5. Specify data sources, grain, quality requirements, refresh needs, and security constraints.
6. Translate requirements into report-page and visual specifications.
7. Write testable acceptance criteria for a Power BI solution.
8. Recognise common planning failures, ethical risks, and limitations of BI requirements.

1. From Business Need to BI Requirement

A business need is a problem, opportunity, or decision that matters to an organisation. For example, a sales director may need to understand why revenue is below plan, while a service manager may need to identify cases that have exceeded their response target. These statements are valuable starting points, but they are not yet complete BI requirements.

A BI requirement should connect five elements:

• Decision: What action or choice will the user make?
• Question: What does the user need to know?
• Measure: How will performance or change be quantified?
• Evidence: Which data will support the measure?
• Acceptance: How will the team determine that the solution is fit for purpose?

A request such as “Create a sales dashboard” is therefore incomplete. A stronger requirement might be: “Regional sales managers need to identify, each Monday, products whose current-month revenue is at least 10% below the comparable budget, so that they can prioritise corrective action.” This version identifies users, timing, measure, comparison, threshold, and intended decision.

Business, analytical, and technical requirements

Requirements can be grouped into three related categories.

A business requirement explains why. An analytical requirement explains what must be analysed. A technical requirement explains how the solution must operate. Confusing these levels creates either vague dashboards or technically impressive reports that do not support decisions.

2. A Structured BI Workflow

A practical BI workflow is iterative rather than purely linear. Nevertheless, a defined sequence helps teams avoid omissions.

1. Identify the decision and stakeholders.
2. Discover and prioritise business questions.
3. Define objectives and success measures.
4. Specify KPIs, dimensions, targets, and business rules.
5. Assess data availability, quality, ownership, and security.
6. Design the report structure and user experience.
7. Define acceptance criteria and test cases.
8. Build, validate, review, and refine.
9. Document assumptions and establish ownership after deployment.

Figure: An iterative workflow for translating business decisions into a validated BI product.

The loop in the workflow is important. Stakeholders may discover during prototyping that a requested chart does not answer the real question. Data profiling may reveal that a proposed KPI cannot be calculated consistently. Acceptance testing may expose a misunderstanding of the business rule. Revisiting earlier stages is a sign of responsible analysis, not failure.

3. Stakeholder Discovery and Question Definition

Stakeholders include people who use, sponsor, provide, govern, or are affected by the BI solution. They may include executives, operational managers, analysts, data owners, information-security staff, finance specialists, and report consumers.

A stakeholder inventory should record:

• role and department;
• decisions supported;
• required level of detail;
• frequency and timing of use;
• preferred delivery channel;
• authority to approve definitions;
• data access restrictions; and
• potential concerns or conflicts.

Interviews, observation, workshops, document review, and examination of existing reports are useful discovery methods. Asking “What chart do you want?” often produces a list of visual preferences. Better questions include:

• What decision will this report change?
• What is difficult to determine today?
• Which comparisons matter: time, region, product, customer, or plan?
• What action follows an unfavourable result?
• How quickly must the information be available?
• Which definitions are disputed?
• What would make you distrust the report?

Turning vague requests into objectives

A useful objective identifies an audience, an action, a measure, a scope, and a time horizon. One practical pattern is:

Enable [user] to [make a decision or take an action] by monitoring [measure] across [scope] at [frequency], in order to [business outcome].

For example: “Enable branch managers to investigate service delays by monitoring average resolution time and the proportion of cases outside target by branch and case type each morning, in order to reduce overdue cases.”

Objectives should be prioritised. A simple classification is:

• Must have: essential to the decision or compliance obligation.
• Should have: materially improves usefulness but is not essential.
• Could have: desirable enhancement if time and data permit.
• Out of scope: explicitly excluded from the current release.

This prevents scope expansion and makes trade-offs visible.

4. Metrics, Measures, KPIs, and Targets

These terms are related but should not be treated as synonyms.

A measure is a quantified value, such as invoice amount. A metric is a defined measure used to assess a phenomenon, such as average invoice value. A KPI is a metric judged to be strategically important and monitored against a target or threshold. A target is the desired level, while a threshold defines a boundary that may trigger attention or action.

A KPI definition should include:

Common KPI calculations

For a count-based rate, the general form is:

The numerator must be a subset of the denominator when the measure is a proportion. The denominator must not be zero. The time period and inclusion rules must be identical for both components.

For example, if 920 of 1,000 eligible orders are delivered on time:

The result is dimensionless and expressed as a percentage. It should not be calculated by averaging percentages from groups unless the groups have equal denominators or a weighted method is deliberately used.

For a percentage change between a prior value and a current value:

This formula assumes that the prior value is non-zero and that the two values are comparable in definition, scope, and time basis. If prior revenue was $80,000 and current revenue was $92,000:

For an average, the arithmetic mean is:

However, an average can hide variation and may be distorted by extreme values. A service team may therefore need both average resolution time and the proportion exceeding the service-level target.

KPI design cautions

A KPI can be technically correct but behaviourally harmful. For example, measuring call-centre agents only by calls completed may encourage rushed conversations and lower customer satisfaction. A balanced design may include productivity, quality, customer outcome, and compliance measures.

Avoid vanity metrics: numbers that are easy to display but do not inform a decision. Also avoid excessive KPIs. A report with dozens of equally prominent indicators can create cognitive overload and obscure priorities.

5. Data Requirements and Data Profiling

A data requirement specifies what information is needed to calculate and interpret the measures. It should not simply list a system name. The analyst should identify the relevant entities, attributes, events, relationships, and history.

Important data requirements include:

• source system and responsible owner;
• business entity, such as order, invoice, employee, or case;
• grain, meaning what one row represents;
• required fields and data types;
• keys and relationships;
• date fields and time zone;
• historical coverage;
• refresh frequency and acceptable delay;
• quality rules;
• security classification and access restrictions; and
• retention or regulatory requirements.

Grain is foundational

Grain describes the level of detail represented by a record. A sales transaction table may have one row per invoice line, while an inventory snapshot may have one row per product, warehouse, and day. Combining these tables without understanding grain can duplicate values and produce incorrect totals.

Suppose an order header contains a shipping charge and an order-line table contains three product lines. Joining the header charge to all three lines may make the shipping charge appear three times. The requirements document should identify the grain and specify how measures are aggregated.

Data-quality dimensions

Data quality is multidimensional. Common dimensions are:

• Completeness: required values are present.
• Accuracy: values represent the real-world event correctly.
• Consistency: the same concept is represented uniformly across systems.
• Timeliness: data is available when required.
• Validity: values conform to allowed formats and ranges.
• Uniqueness: duplicate records are controlled.

A requirement should convert these ideas into tests. For example: “At least 99% of delivered orders must contain a valid delivery date” is testable, whereas “The data must be good” is not.

Data profiling should occur before finalising KPI definitions. Analysts should inspect null values, duplicates, unexpected categories, date ranges, outliers, and referential integrity. Profiling does not prove that data is accurate; it reveals conditions requiring investigation by data owners.

6. Report Specifications and User Experience

A report specification translates analytical requirements into a planned Power BI experience. It should define the purpose of each page rather than merely list visual types.

A useful page specification contains:

A report often benefits from a layered structure:

1. Overview page: status, trends, and exceptions.
2. Diagnostic page: breakdowns by relevant dimensions.
3. Detail page: records supporting investigation.
4. Definitions page: KPI descriptions, refresh time, and caveats.

Visual choice should follow the analytical task. A line chart supports trends; a bar chart supports category comparison; a matrix supports detailed cross-tabulation; a card supports a small number of headline values. A chart should not be selected merely because it looks attractive.

Titles should communicate the measure, population, and time basis. “Sales performance” is weaker than “Net sales by region, current financial year.” Tooltips, alt text, logical tab order, sufficient contrast, and restrained colour use improve accessibility. Colour should not be the only way to communicate status, because some users have colour-vision deficiencies and printed reports may lose colour distinctions.

7. Acceptance Criteria and Testing

Acceptance criteria state the conditions under which stakeholders will accept the solution. They should be observable and testable. A useful structure is:

Given [context], when [action or condition], then [expected result].

Examples include:

• Given the Finance workspace, when a user selects March 2025, then total revenue agrees with the approved finance extract within the agreed tolerance.
• Given a regional manager’s account, when the report is opened, then only the manager’s authorised regions are visible.
• Given a refresh failure, when the scheduled refresh does not complete, then the support owner receives an alert and the report displays the last successful refresh time.
• Given a filter for “Online,” when it is applied, then every page containing sales measures reflects the same channel filter.

Acceptance criteria should cover at least:

• numerical correctness;
• filter and interaction behaviour;
• definitions and exclusions;
• data freshness;
• performance;
• security and row-level access;
• accessibility;
• error handling; and
• documentation.

Reconciliation is essential. A Power BI total should be compared with a trusted source using the same scope and business rules. Differences may result from timing, currency conversion, cancelled transactions, rounding, or differing definitions. The purpose of reconciliation is not to force identical numbers blindly, but to explain and approve differences.

8. Illustrative Case: Northstar Retail Delivery Reporting

This is a fictional illustrative case designed for learning purposes.

Background

Northstar Retail operates stores and an online channel. Senior management believes delivery reliability has declined, but different departments report different percentages. The organisation asks for a Power BI delivery dashboard.

Problem

The initial request is “Show delivery performance by branch.” Discovery reveals several problems:

• Operations counts cancelled orders in the denominator, while Finance excludes them.
• The warehouse system records dispatch date, but the customer-service system records promised and actual delivery dates.
• Some branches use local time and others use UTC.
• Managers need to investigate individual late orders, while executives need a monthly trend.
• Regional managers must not see customer details outside their assigned regions.

Analysis

The BI team conducts interviews with logistics, customer service, Finance, data engineering, and regional managers. The principal objective becomes: “Enable logistics managers to identify and investigate delivery failures daily, while enabling executives to monitor the monthly on-time delivery trend.”

The agreed KPI is:

Cancelled orders, test orders, and orders without a valid promised date are excluded. The delivery date determines the reporting month. The target is 95%.

The team also defines a diagnostic metric: late-order count. This is a count, not a percentage, and is useful for prioritising workload. A small branch with a low rate may have fewer actual late orders than a large branch with a higher count.

Approach and solution

The specification contains three pages:

1. Executive overview: current on-time rate, target variance, monthly trend, and regional comparison.
2. Operations analysis: carrier, warehouse, product category, and reason-code breakdowns with exception highlighting.
3. Order investigation: filterable detail table with order identifier, region, promised date, actual date, delay days, and reason.

Data requirements identify order, promised-delivery, actual-delivery, branch, region, carrier, and reason-code fields. The model uses a dedicated date dimension and clearly documented relationships. A standard time zone is selected for refresh and reporting logic. Customer-identifying fields are hidden from users who do not need them.

Data-quality tests check for duplicate order identifiers, missing promised dates, invalid delivery sequences, and unknown branch codes. A reconciliation report compares monthly eligible-order counts and on-time totals with an approved operational extract.

Outcome

The pilot reveals that two apparently different KPIs had been using different denominators. Once the definition is standardised, the organisation accepts one official rate and documents the previous measures as historical, non-comparable values. Regional managers use the investigation page to identify carrier and warehouse problems. Executives use the overview page to monitor the target.

Learning points

The case demonstrates that dashboard development is not primarily a chart-selection exercise. Agreement about population, grain, dates, exclusions, ownership, and access is more important than visual decoration. It also shows why requirements discovery must involve multiple stakeholders: no single department possessed the complete definition or data pathway.

9. Governance, Ethics, and Responsible BI

BI requirements can create risks when they expose personal information, enable unfair comparisons, or encourage inappropriate surveillance. Planning should therefore address governance from the beginning.

Relevant controls include:

• data minimisation: include only fields necessary for the purpose;
• purpose limitation: do not reuse data for an incompatible purpose without appropriate approval;
• role-based access and row-level security;
• protection of sensitive personal, financial, or health information;
• documented definitions and ownership;
• retention and deletion rules;
• auditability of changes and refreshes; and
• transparent communication of limitations.

A KPI may appear neutral while embedding bias. For example, a complaint-rate comparison may disadvantage branches serving populations with greater barriers to service, or an employee productivity measure may ignore differences in task complexity. Analysts should ask who benefits, who may be harmed, which groups are missing, and whether a measure is being used beyond its validated purpose.

AI-assisted features can support natural-language exploration, anomaly detection, and summarisation, but they do not remove the need for approved definitions and human review. An AI-generated explanation may be incomplete or infer causation from correlation. Sensitive data should not be entered into unapproved tools. The source measures, assumptions, and limitations must remain visible to users.

10. Common Failures and Best Practices

Common failures

• Beginning with a preferred visual instead of a decision.
• Treating stakeholder requests as complete specifications.
• Defining KPIs without numerator and denominator rules.
• Ignoring grain and double-counting measures.
• Using inconsistent date fields or time zones.
• Building before profiling data.
• Hiding exclusions and assumptions.
• Creating too many pages or metrics.
• Accepting a report without formal reconciliation.
• Treating security as a deployment-stage problem.

Best practices

• Document every KPI in plain language and calculation logic.
• Use a requirements traceability matrix linking objectives to measures, data, visuals, and tests.
• Prototype with representative users before extensive development.
• Separate official measures from exploratory measures.
• Include a definitions and data-freshness section in the report.
• Test filters, totals, drill-through actions, refreshes, and security roles.
• Record unresolved questions and assign owners and due dates.
• Design for accessibility and different levels of analytical literacy.
• Review whether the report changes decisions, not merely whether it has been published.

Chapter Summary

BI requirements and analytical planning connect organisational decisions to a trustworthy Power BI solution. The process begins by identifying stakeholders and the decisions they must make, then converts vague requests into measurable objectives. Metrics and KPIs require explicit populations, numerators, denominators, time bases, targets, exclusions, and ownership. Data requirements must address grain, relationships, quality, refresh, security, and history. Report specifications should define the purpose of each page, appropriate visual tasks, interactions, accessibility, and detail navigation. Acceptance criteria make correctness, usability, performance, and governance testable. Finally, responsible BI requires attention to privacy, fairness, transparency, and the limitations of both data and AI-assisted features.

Key Terms

Business requirement: A statement of an organisational need, outcome, or decision.

Analytical requirement: A specification of what must be measured, compared, explained, or monitored.

Technical requirement: A condition relating to implementation, operation, security, or performance.

Stakeholder: A person or group that uses, sponsors, supplies, governs, or is affected by a BI solution.

Measure: A quantified value representing an observed business phenomenon.

Metric: A defined measure used to assess performance or condition.

KPI: A strategically important metric monitored against a target or threshold.

Target: A desired level of performance.

Threshold: A boundary that triggers attention, escalation, or action.

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

Data profiling: Examination of data structure, values, completeness, and quality patterns.

Completeness: The extent to which required data values are present.

Refresh frequency: How often source data is updated in the BI solution.

Report specification: A planned description of report pages, users, measures, filters, and interactions.

Acceptance criterion: A testable condition that must be satisfied for approval.

Reconciliation: Comparison of BI results with an approved reference source.

Row-level security: Restriction of data rows according to a user’s authorised scope.

Data minimisation: Collecting or displaying only data necessary for the stated purpose.

Requirements traceability: Linking objectives to KPIs, data, report elements, and tests.

Review Questions

Short Answer Questions

1. What distinguishes a business requirement from a technical requirement?
2. Why must a KPI definition specify its denominator?
3. What does data grain mean, and why is it important?
4. State four dimensions of data quality.
5. What is the purpose of acceptance criteria?

Long Answer Questions

1. Explain the workflow for translating a business decision into a validated Power BI report.
2. Discuss how stakeholder interviews can reveal hidden assumptions in a reporting request.
3. Explain the difference between a measure, metric, KPI, target, and threshold, using examples.
4. Describe the principal components of a data requirements specification.
5. Discuss governance and ethical risks that should be considered during BI planning.

Analytical / Application Questions

1. A manager requests a dashboard showing “customer satisfaction performance.” Develop a measurable objective, two possible KPIs, required dimensions, and three acceptance criteria.
2. A company reports an on-time rate of 80% for a small branch and 90% for a large branch. Explain why percentages alone may be insufficient for operational prioritisation and identify additional measures required.
3. A Power BI report shows total invoice value that is 15% higher than the finance system. Propose a structured investigation covering grain, filters, dates, exclusions, currency, duplicates, and reconciliation evidence.

Multiple Choice Questions with Answers

Question 1

Which statement best describes an analytical requirement?

A. The organisation must reduce delivery delays.

B. The report must refresh every morning at 07:00.

C. Show delivery rate by region and carrier to identify sources of delay.

D. Only regional managers may access regional data.

Correct Answer: C. Show delivery rate by region and carrier to identify sources of delay.

Question 2

If 920 of 1,000 eligible orders are delivered on time, what is the on-time delivery rate?

A. 9.2%

B. 92%

C. 98%

D. 920%

Correct Answer: B. 92%

Question 3

Which element is essential in a rate-based KPI definition?

A. A decorative colour scheme

B. The numerator and denominator

C. The number of report pages

D. The preferred chart type

Correct Answer: B. The numerator and denominator

Question 4

What does grain describe in a dataset?

A. The colour intensity of a visual

B. The refresh speed of a report

C. The level of detail represented by one record

D. The number of users in a workspace

Correct Answer: C. The level of detail represented by one record

Question 5

Which is an example of an acceptance criterion?

A. The dashboard should look modern.

B. Users should like the report.

C. When March is selected, revenue agrees with the approved extract within the agreed tolerance.

D. The report should contain several charts.

Correct Answer: C. When March is selected, revenue agrees with the approved extract within the agreed tolerance.

Question 6

Which data-quality dimension concerns the presence of required values?

A. Completeness

B. Uniqueness

C. Timeliness

D. Validity

Correct Answer: A. Completeness

Question 7

Why can averaging percentages from groups produce a misleading result?

A. Percentages cannot be displayed in Power BI.

B. The groups may have different denominators.

C. Percentages always measure currency.

D. Averages can only be used for dates.

Correct Answer: B. The groups may have different denominators.

Question 8

What is the primary purpose of a report overview page?

A. To display every available transaction

B. To provide status, trends, and important exceptions

C. To replace data-quality testing

D. To store source-system records

Correct Answer: B. To provide status, trends, and important exceptions

Question 9

Which practice best supports responsible BI?

A. Include all available personal data for future use.

B. Hide KPI definitions to simplify the report.

C. Apply data minimisation and role-based access.

D. Allow AI-generated explanations without review.

Correct Answer: C. Apply data minimisation and role-based access.

Question 10

What is requirements traceability?

A. Linking objectives to measures, data, report elements, and tests

B. Tracking the physical location of report users

C. Counting the number of visuals on a page

D. Comparing colours used by different reports

Correct Answer: A. Linking objectives to measures, data, report elements, and tests