Business Requirements and Source Assessment

Introduction

A Power BI solution is only as useful as the business decision it supports and the data on which it is built. Many reporting failures are not caused by Power BI or Power Query. They arise earlier, when a vague business request is accepted without clarification, when a key performance indicator (KPI) is defined inconsistently, or when a data source is selected because it is convenient rather than fit for purpose.

Business requirements and source assessment therefore form the foundation of effective business intelligence. The analyst must translate questions such as “Are sales growing?” or “Which customers are most profitable?” into precise data requirements. The analyst must then determine whether suitable data exists, who owns it, how frequently it is updated, how trustworthy it is, and whether it can be accessed legally and economically.

This chapter presents a practical framework for doing that work before significant data preparation begins. It focuses on certificate-level professional practice: understanding the business problem, defining measures, examining source characteristics, documenting risks, and making a reasoned recommendation.

Learning Objectives

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

• translate business questions into measurable information requirements;
• distinguish business requirements, data requirements, and technical requirements;
• define KPIs with clear numerators, denominators, populations, time periods, and ownership;
• assess data sources according to ownership, availability, quality, latency, granularity, reliability, cost, and fitness for purpose;
• identify common data-quality and governance risks;
• compare alternative sources and document source-selection decisions; and
• prepare a practical source assessment for a Power BI project.

1. From Business Questions to Data Needs

A business question describes a decision or uncertainty. A data requirement describes the evidence needed to answer that question. These are related but not identical.

For example, “How are we performing?” is too broad to guide data acquisition. A more useful question might be: “Did monthly online revenue meet the approved target in each region during the current financial year, and which product categories explain the variance?” This question identifies a subject, measures, comparison, time period, and analytical dimensions.

A useful translation chain is:

Business objective → decision question → KPI or analysis → required fields → source and refresh requirement

Consider the following example:

The final row is not merely a technical detail. If the source contains sales but no cost, gross margin cannot be calculated reliably. If it contains only monthly totals, transaction-level customer analysis is impossible.

1.1 Requirement types

Three requirement types should be separated.

Business requirements state what the organisation needs to achieve. For example, a sales manager needs early warning when regional performance falls below target.

Information or data requirements state what information is necessary to support that objective. For example, the organisation needs daily revenue by region, product, channel, and salesperson, together with an approved target.

Technical requirements state how the information should be delivered. Examples include a Power BI semantic model, a scheduled refresh at 06:00, row-level security, and a dashboard that loads within an acceptable time.

Confusing these levels creates weak solutions. “Build a dashboard” is a deliverable, not a business requirement. The underlying requirement may be to improve weekly sales intervention decisions.

1.2 Characteristics of a well-formed requirement

A useful requirement is:

• specific: it identifies the subject and intended decision;
• measurable: its result can be expressed using defined data;
• bounded: it identifies relevant populations, dates, and organisational scope;
• owned: a business person is accountable for confirming its meaning;
• testable: another person can determine whether it has been met; and
• prioritised: essential requirements are distinguished from desirable ones.

A requirement should also state the grain of analysis. “Customer sales” could mean one row per transaction, customer-month, customer-product, or customer-year. These alternatives produce different results and require different sources.

2. Defining KPIs Precisely

A KPI is a measure used to evaluate performance against an objective. A number becomes a credible KPI only when its meaning, calculation, scope, and ownership are agreed.

A KPI definition should normally include:

1. name and business purpose;
2. formal calculation or rule;
3. numerator and denominator, where applicable;
4. inclusion and exclusion criteria;
5. time basis and calendar definition;
6. dimensions by which it may be analysed;
7. target, threshold, or benchmark;
8. data source and refresh frequency;
9. responsible owner; and
10. known limitations.

For example, “delivery performance” may be defined as the percentage of completed deliveries made on or before the promised date. If cancelled orders are included in the denominator, the result may be misleading. If promised dates are overwritten after a delay, historical performance may be overstated.

2.1 Measures, dimensions, and attributes

A measure is generally numeric and can be aggregated, such as sales value, units, cost, or hours. A dimension provides a perspective for analysis, such as date, region, product, customer, or channel. An attribute is a descriptive property of an entity, such as product colour or customer segment.

The distinction is important because a measure may be valid at one grain but invalid at another. A monthly headcount snapshot should not automatically be summed across days. A customer’s credit limit should not be summed across transactions. Power BI can technically aggregate many columns, but technical possibility does not establish analytical validity.

2.2 Common KPI formulas

Revenue is often calculated as the sum of transaction values. If transaction value is quantity multiplied by unit price, then:

Here, qᵢ is the quantity in transaction line i, pᵢ is the applicable unit price, and the summation covers the defined transaction population. The formula assumes that prices and quantities use compatible units and that returns, discounts, tax, and currency conversion have been treated according to the business definition.

A margin percentage may be expressed as:

Revenue and cost of sales must refer to the same population, period, and currency. The formula is undefined when revenue is zero; a reporting design must specify whether such cases are shown as blank, zero, or excluded.

A percentage achievement against target is:

This formula assumes that actual and target are comparable—for example, both are measured in net revenue, use the same period, and cover the same organisational scope. A target of zero requires a separate business rule.

2.3 KPI ambiguity: a practical example

Suppose a manager requests “customer retention.” Possible definitions include:

• the percentage of customers who purchased in both consecutive periods;
• the percentage of customers at the beginning of a period who remain active at its end; or
• recurring revenue retained from an existing customer base.

These definitions have different denominators and data needs. The analyst should not choose one silently. The KPI owner must approve the definition, and the selected rule should be recorded in a KPI catalogue.

3. Understanding Data Grain and Scope

Grain is the level represented by one row or record. Examples include one sales line, one invoice, one customer, one product, one store-day, or one monthly budget record.

Grain determines what can be calculated. If a source contains one row per invoice, it may support invoice counts and invoice totals, but not necessarily product-level quantities if an invoice contains multiple products. Combining sources with different grains can also create duplication. Joining a customer table to a transaction table is usually appropriate when customer keys are unique in the customer table. Joining two transaction-level tables without a carefully defined relationship may multiply rows.

Before assessing a source, document:

• the row meaning;
• the unique identifier;
• the time represented by each row;
• whether records are current-state or historical;
• the valid dimensions; and
• how corrections, cancellations, and late-arriving records are handled.

Scope refers to the population covered. A source may include domestic sales but exclude exports, or completed orders but exclude refunds. A complete-looking table is not necessarily complete for the business question.

4. Source Assessment Framework

A source assessment evaluates whether a source is suitable for a defined analytical purpose. No source is universally good or bad; suitability depends on the requirement.

4.1 Ownership and accountability

Identify the system owner, data steward, technical custodian, and business approver where these roles differ. Ownership matters because someone must explain definitions, approve access, correct errors, and authorise changes.

Questions include:

• Who created and maintains the source?
• Who is accountable for the meaning of each important field?
• Is there a documented data dictionary?
• Who approves access?
• What happens when the source schema changes?

A spreadsheet maintained by one employee may be useful for a short-term analysis but present a continuity risk if the employee is unavailable. An enterprise system may have stronger controls but slower change processes.

4.2 Availability and accessibility

Availability includes whether the source exists, whether historical records are retained, and whether the BI team can access it. Accessibility includes authentication, permissions, connectivity, licensing, and technical format.

A source may be available in principle but inaccessible at the required frequency. For example, a report exported manually each month cannot support an hourly operational dashboard without a changed process.

Access should be tested using a small sample before design commitments are made. The test should verify connection, permissions, required tables or files, data types, historical coverage, and refresh behaviour.

4.3 Data quality

Data quality is multidimensional. Common dimensions are:

Quality metrics must be interpreted in context. A missing optional telephone number may be acceptable, while a missing transaction date may make a sales record unusable. A duplicate customer name is not necessarily a duplicate customer; a customer identifier is normally more reliable for uniqueness testing.

A simple completeness rate can be calculated as:

The result is meaningful only when “required value” and “records assessed” are defined. The calculation should also state whether blank strings, invalid placeholders, and default values count as missing.

4.4 Latency and freshness

Latency is the delay between an event occurring and its availability for analysis. A point-of-sale source may be near real time, while a finance ledger may be finalised monthly.

The required latency depends on the decision. A workforce planning report may need weekly data; fraud monitoring may need minutes. Refreshing a model more frequently than the source changes creates the appearance of timeliness without providing newer information.

Distinguish:

• event time: when the business activity occurred;
• ingestion time: when data entered the analytical platform; and
• publication time: when the report became available.

These timestamps help identify late-arriving data and explain why a report may change after an event date.

4.5 Granularity and historical depth

A source must be detailed enough for the required analysis. A monthly summary cannot answer a question requiring daily or transaction-level detail. Conversely, a very detailed source may increase storage, processing time, and privacy exposure without improving the decision.

Historical depth is equally important. A source with two years of history may be unsuitable for a five-year trend analysis. Changes in product codes, organisational structures, or KPI definitions across time must also be considered.

4.6 Reliability and stability

Reliability concerns whether the source is consistently available and behaves predictably. Assess:

• failure frequency and recovery arrangements;
• stability of file names, folder locations, and schemas;
• use of controlled master data;
• audit trails and change logs;
• backup and retention arrangements; and
• whether historical values can be reproduced.

A manually overwritten spreadsheet may provide correct values today but poor reproducibility tomorrow. A reliable source supports repeatable refreshes and allows users to understand how a reported number was produced.

4.7 Cost and effort

Cost includes more than subscription price. Consider licensing, storage, integration development, maintenance, support, data cleansing, security controls, and staff time. A free source with extensive manual preparation may be more expensive over its life than a licensed, governed source.

A basic comparison can classify cost as low, medium, or high, but the assumptions should be documented. Cost should be evaluated against business value and risk, not in isolation.

4.8 Fitness for purpose

Fitness for purpose is the overall judgement: can the source support the defined requirement at an acceptable level of risk and effort?

A source is fit when it has sufficient scope, grain, quality, timeliness, reliability, and lawful accessibility for the intended decision. Fitness is not the same as perfection. A source with 98% completeness may be acceptable for a strategic trend but unacceptable for regulatory reporting if the missing records are systematically biased.

5. Comparing and Rating Sources

A source assessment matrix makes assumptions visible. A simple rating scale may use 1 for poor, 3 for acceptable, and 5 for strong performance. Ratings should not be treated as objective facts; they are structured professional judgements supported by evidence.

The table does not prove that the ERP is the correct source. It indicates that the analyst should examine the ERP first, while perhaps retaining the spreadsheet as a supplementary source for targets not stored in the ERP.

If weighted scoring is used, define weights explicitly. A weighted score can be represented as:

where S is the total score, wⱼ is the weight assigned to criterion j, and rⱼ is the rating for that criterion. If weights are expressed as proportions, they should sum to 1. Ratings must use a consistent scale. A high score does not override a mandatory failure such as unlawful access, missing essential fields, or unacceptable latency; such conditions should be recorded as disqualifying constraints.

6. The Role of Power Query in Source Assessment

Power Query is primarily a data connection and transformation environment, not a substitute for source governance. It can help profile data by showing column distribution, errors, empty values, distinct values, and data types. It can also standardise names, remove unwanted rows, split columns, merge reference data, and document repeatable transformations.

However, transformation can conceal rather than solve a source problem. Replacing missing values with zero may turn unknown revenue into falsely reported zero revenue. Removing duplicates without understanding their meaning may delete legitimate transactions. Converting all dates to local time may create errors across regions.

A disciplined process is:

1. preserve the raw or minimally altered source where governance permits;
2. profile before transforming;
3. record assumptions and business rules;
4. validate transformed results against control totals;
5. separate cleansing from interpretation; and
6. obtain approval for material changes to meaning.

Useful profiling questions include:

• What percentage of values is null or blank?
• What are the minimum and maximum dates?
• Are identifiers unique where expected?
• Are there unexpected categories or codes?
• Do totals reconcile to an approved source report?
• Does the row count change after each transformation?

7. Applied Case: Regional Sales Performance

The following is an illustrative case, created for learning purposes.

Background

Northstar Office Supplies wants a Power BI report for regional sales managers. Managers need to compare net sales with monthly targets, identify declining product categories, and monitor the contribution of online and branch channels.

Problem

The initial request is “Create a real-time sales dashboard.” During interviews, the analyst discovers that managers make decisions in a morning meeting and do not require second-by-second information. They need reliable figures by 08:00 each day, including returns posted overnight. The request is therefore refined to a daily refreshed performance report.

Analysis of requirements

The agreed requirements are:

• net sales by day, region, product category, and channel;
• monthly target by region and channel;
• units sold and return value;
• comparison with the equivalent prior period;
• sales through the previous business day; and
• a clear exception indicator for regions below 90% of target achievement.

The KPI owner confirms that net sales means invoiced sales less approved returns, excluding sales tax. Targets are maintained by the commercial planning team.

Source assessment

Three sources are examined:

1. the ERP contains invoice lines and returns at transaction grain, with a daily overnight load;
2. a planning spreadsheet contains monthly targets but has inconsistent region names; and
3. the CRM contains customer and opportunity data but does not represent completed invoiced sales.

The ERP is selected for actual sales because it has appropriate grain, ownership, and historical detail. The planning spreadsheet is accepted for targets subject to standardised region keys and a controlled submission process. The CRM is rejected as the primary sales source because opportunities are not completed revenue.

Approach and solution

The analyst profiles the ERP data, confirms that invoice numbers and line numbers form a practical transaction key, and checks that returns can be linked to original sales or identified independently. A reference table maps planning spreadsheet region names to approved region codes. Control totals are compared with the finance team’s daily report.

The Power BI model uses a date dimension, product dimension, region dimension, channel dimension, sales fact data, and target data. The model documentation states that sales are available through the previous business day and that late adjustments may alter prior dates.

Outcome

The report supports the morning meeting and reduces manual reconciliation. Managers can distinguish sales performance from target performance and investigate category changes at an appropriate level of detail. The project does not claim real-time capability, because that was not necessary for the decision.

Learning points

• Clarifying the decision reduced unnecessary technical complexity.
• KPI ownership prevented competing definitions of net sales.
• Source selection depended on grain and business meaning, not merely accessibility.
• A spreadsheet can be acceptable when its limitations and controls are explicit.
• Reconciliation and documentation are essential parts of a trustworthy report.

8. Governance, Ethics, and Risk

Source assessment must include lawful and ethical use. Relevant issues include privacy, confidentiality, access control, retention, purpose limitation, and protection of sensitive attributes. A source containing customer contact details may not be necessary for a regional performance report and should not be imported merely because it is available.

Apply least-privilege access: users should receive only the data and permissions required for their roles. Sensitive data should be minimised, masked, aggregated, or excluded where possible. Row-level security may restrict users to authorised regions, but it must be tested; a poorly designed relationship can expose data unintentionally.

Analysts should also consider bias and representativeness. Missing records may be concentrated in one region or customer group. A high overall completeness rate can therefore hide serious local problems. Source limitations should be communicated rather than concealed in polished visuals.

For regulated or high-impact decisions, retain evidence of definitions, approvals, transformations, refresh history, and exceptions. A report should be explainable: users should be able to understand what the numbers mean and how they were produced.

9. Contemporary Developments

Cloud applications, application programming interfaces, data platforms, and self-service analytics have increased the number of available sources. They have also increased the risk of uncontrolled duplication. Different teams may extract the same operational data, apply different filters, and publish contradictory KPIs.

AI-assisted tools can help suggest transformations, classify columns, or identify anomalies. They do not replace business ownership or validation. An AI-generated transformation may misinterpret a status code, remove meaningful outliers, or expose sensitive information if data is submitted to an unauthorised service. Human review, approved tools, and documented testing remain necessary.

Modern organisations increasingly use data catalogues, lineage tools, data contracts, and shared semantic models. These practices improve discoverability and consistency, but they still depend on clear definitions and accountable owners.

Best-Practice Checklist

Before connecting a source to a Power BI solution, confirm that:

• the business decision and intended users are documented;
• each KPI has an approved definition and owner;
• grain, scope, history, and exclusions are known;
• source ownership and access rights are clear;
• quality, latency, reliability, and cost have been assessed;
• sensitive data has a justified purpose and suitable controls;
• the source can be refreshed repeatably;
• transformations are documented and tested; and
• limitations and reconciliation results are visible to stakeholders.

Chapter Summary

Business intelligence begins with a business decision, not a visual or a data connection. Analysts translate objectives into precise questions, KPIs, fields, dimensions, grain, scope, and refresh requirements. KPI definitions must identify calculation rules, populations, time periods, exclusions, ownership, and limitations.

Source assessment examines ownership, availability, quality, latency, granularity, historical depth, reliability, cost, governance, and fitness for purpose. Data quality includes completeness, validity, accuracy, consistency, uniqueness, and timeliness. Power Query supports profiling and repeatable transformation, but it cannot compensate for an unsuitable source or an undefined KPI.

A structured comparison of sources, supported by evidence and explicit assumptions, produces better decisions than selecting the easiest file or system. Ethical and governance considerations—including privacy, least privilege, lineage, and explainability—are essential. The final aim is not simply to obtain data, but to obtain trustworthy evidence that is appropriate for the decision being supported.

Key Terms

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

Data requirement: The information needed to support a business requirement.

Technical requirement: A specification for how a solution should be implemented or delivered.

KPI: A defined measure used to evaluate performance against an objective.

Measure: A numeric value used for analysis, such as revenue or units.

Dimension: A perspective used to group or filter measures, such as date or region.

Attribute: A descriptive property of an entity.

Grain: The level of detail represented by one row or record.

Scope: The population, period, or organisational area covered by data.

Data owner: The person or function accountable for a data asset’s meaning and appropriate use.

Data steward: A role responsible for operational data quality and definitions.

Completeness: The extent to which required records or values are present.

Validity: The extent to which data conforms to defined rules or formats.

Accuracy: The extent to which data represents the real-world object or event.

Latency: The delay between an event and its availability for analysis.

Reliability: The consistency and dependability of a source and its delivery process.

Fitness for purpose: The extent to which a source is suitable for a defined analytical use.

Data lineage: The record of where data originated and how it changed.

Reconciliation: Comparison of analytical results with an approved control source.

Review Questions

Short Answer Questions

1. What is the difference between a business requirement and a data requirement?
2. Why must the grain of a source be documented before modelling?
3. Name four dimensions of data quality.
4. What is data latency, and why does it matter?
5. Why is a KPI owner required?

Long Answer Questions

1. Explain how an analyst can translate a broad business question into a set of fields and source requirements.
2. Discuss the main criteria that should be used to assess the fitness of a data source.
3. Explain why a technically accessible source may still be unsuitable for a Power BI report.
4. Describe how Power Query can support source profiling and validation without replacing governance.
5. Discuss the ethical and governance risks of importing data simply because it is available.

Analytical / Application Questions

1. A sales report uses an easily accessible spreadsheet containing monthly regional totals, while managers request daily product-level analysis. Assess whether the spreadsheet is fit for purpose and recommend an alternative or supplementary source.
2. A KPI called “customer retention” produces different results in two departments. Identify the likely causes and design a definition document that could resolve the disagreement.
3. Design a source assessment matrix for a service-operations dashboard. Select at least five criteria, assign suitable evidence requirements, and explain how a disqualifying privacy or latency problem should be treated.

Multiple Choice Questions with Answers

Question 1

Which sequence best represents the translation from a business need to a source requirement?

A. Visual design → colour scheme → data type → source

B. Business objective → decision question → KPI → required fields → source requirement

C. Source → transformation → dashboard → business objective

D. Database licence → report page → KPI → business objective

Correct Answer: B. Business objective → decision question → KPI → required fields → source requirement

Question 2

What does the grain of a dataset describe?

A. The level of detail represented by one row

B. The number of users who can access the dataset

C. The financial cost of storing the dataset

D. The colour scheme used in a report

Correct Answer: A. The level of detail represented by one row

Question 3

Which item is most essential in a KPI definition?

A. The preferred dashboard font

B. The name of the report author only

C. The calculation rule and inclusion criteria

D. The number of pages in the report

Correct Answer: C. The calculation rule and inclusion criteria

Question 4

Which data-quality dimension asks whether required values are present?

A. Completeness

B. Reliability

C. Latency

D. Uniqueness

Correct Answer: A. Completeness

Question 5

A source contains monthly totals, but the requirement is to analyse individual transactions. Which source characteristic is inadequate?

A. Ownership

B. Granularity

C. Currency

D. Visual formatting

Correct Answer: B. Granularity

Question 6

What is latency in a data context?

A. The number of columns in a table

B. The delay between an event and its availability for analysis

C. The number of duplicate records

D. The time required to design a report page

Correct Answer: B. The delay between an event and its availability for analysis

Question 7

Why should an analyst avoid replacing every missing numeric value with zero automatically?

A. Zero is never a valid number

B. It may convert unknown or missing information into a misleading business result

C. Power BI cannot display zero

D. It always increases data latency

Correct Answer: B. It may convert unknown or missing information into a misleading business result

Question 8

Which source is most appropriate as the primary source for completed invoiced sales in the illustrative case?

A. CRM opportunities

B. A product catalogue

C. ERP invoice and return transactions

D. An employee contact list

Correct Answer: C. ERP invoice and return transactions

Question 9

In the weighted score formula S = ∑ⱼ wⱼrⱼ, what does rⱼ represent?

A. The rating for criterion j

B. The total number of records

C. The refresh interval

D. The source owner

Correct Answer: A. The rating for criterion j

Question 10

Which principle limits users to only the data and permissions necessary for their role?

A. Maximum disclosure

B. Least privilege

C. Unrestricted self-service

D. Historical expansion

Correct Answer: B. Least privilege