Business Requirements and Model Planning

Introduction

A Power BI solution is only as reliable as the business model beneath its reports. Attractive visuals cannot compensate for unclear business questions, inconsistent definitions, inappropriate granularity, or poor-quality source data. Data modelling therefore begins before tables are imported and before DAX measures are written. It begins with understanding how an organisation operates and how decision-makers need to analyse that operation.

Business requirements and model planning provide the bridge between organisational objectives and a usable analytical model. The analyst must translate statements such as “increase profitability,” “monitor customer retention,” or “identify delayed orders” into explicit processes, measurable outcomes, dimensions of analysis, data-quality rules, and reporting interactions.

This chapter presents a practical foundation for doing so. It focuses on the questions that should be answered before building a Power BI semantic model: What business process is being analysed? What does one row represent? Which events are facts? Which descriptive entities are dimensions? What definitions govern KPIs? Which data-quality problems could invalidate conclusions? How should the model support future analysis?

Learning Objectives

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

• elicit and document business questions, reporting requirements, and KPIs;
• identify business processes and define the grain of a fact table;
• distinguish facts, dimensions, measures, attributes, and hierarchies;
• map requirements to a dimensional model suitable for Power BI;
• specify data-quality, validation, security, and governance rules;
• identify analytical use cases and test whether a proposed model supports them;
• recognise common planning errors and recommend practical improvements.

1. From Business Questions to Analytical Requirements

A business requirement describes a decision, activity, control, or information need. A report requirement describes how that need should be presented. These are related but not identical. “Managers need a monthly sales report” is a presentation requirement. “Managers need to compare sales against target by region and product category in order to allocate sales resources” is a more useful analytical requirement because it identifies the decision, measures, dimensions, and intended action.

A well-formed requirement normally contains five elements:

1. Actor – who needs the information?
2. Decision or purpose – what will the information support?
3. Measure – what quantity or status is required?
4. Context – by which time periods, locations, products, customers, or organisational units?
5. Action or threshold – what response may follow from the result?

For example:

Regional sales managers need to review monthly net sales and gross margin by region, channel, and product category, compare results with budget, and investigate categories whose margin falls below 25%.

This requirement suggests at least two measures—net sales and gross margin—several dimensions, a budget comparison, and a threshold-based analytical use case.

1.1 Business questions and KPI definitions

A KPI is not merely a number displayed on a card. It is a formally defined indicator linked to a business objective. A KPI specification should state:

Where a rate or percentage is used, its definition must be explicit. A common margin formula is:

Here, net sales and cost of goods sold must use the same population, currency, and reporting period. The formula is applicable when net sales are non-zero. If net sales equal zero, the result should be treated according to an agreed business rule—often blank or “not applicable”—rather than an automatically generated zero.

A percentage should generally be calculated from its underlying components, not by averaging row-level percentages. For example, the correct overall margin is total gross profit divided by total net sales. An unweighted average of product margins can be misleading when products have very different sales volumes.

1.2 Requirement traceability

A requirement traceability matrix links business needs to model objects and validation tests. It reduces the risk that a report contains attractive but irrelevant metrics.

The matrix should be maintained as the model evolves. It is especially valuable when requirements change or several analysts contribute to the solution.

2. Identifying Business Processes

Dimensional modelling begins with business processes rather than departments or source systems. A process is a recurring organisational activity that produces measurable events or states. Examples include sales, purchasing, inventory movement, payments, support cases, deliveries, and employee absence.

A useful process inventory records:

• process name and owner;
• operational purpose;
• event that creates a record;
• lifecycle and status changes;
• source systems;
• relevant dates;
• measurable quantities;
• users and decisions supported;
• expected volume and refresh frequency.

A single source system may contain several processes. An order-management database, for example, may record order placement, shipment, invoicing, and payment. Treating all of these as one fact table can create ambiguous grain and incorrect calculations. The events should be considered separately unless the business process and grain are genuinely the same.

2.1 Selecting the business process boundary

The process boundary determines what the model can answer. A sales transaction model can answer questions about units sold and invoiced value. It cannot reliably answer inventory-on-hand questions unless inventory events or periodic snapshots are also modelled.

Questions for defining the boundary include:

• What event is being measured?
• When is the event considered to have occurred?
• Is the data transactional, periodic, or accumulating over time?
• Can the event be cancelled, corrected, or reversed?
• Which dates represent different stages of the process?

The choice of date is particularly important. An order may have order date, promised delivery date, shipment date, and invoice date. These dates answer different questions and should not be silently substituted for one another.

3. Grain: The Foundation of the Fact Table

The grain is the precise meaning of one row in a fact table. It should be written as a sentence before columns are selected. Examples include:

• one row per invoiced product line;
• one row per customer support case;
• one row per product and warehouse at the end of each day;
• one row per employee absence occurrence.

“Sales data” is not a grain. “One row per invoice line at the time the invoice is posted” is a grain.

Grain controls what can be counted, summed, related, and compared. If a table contains both invoice-line rows and invoice-header totals, summing both can double-count revenue. If a daily inventory snapshot is mistaken for a transaction table, summing stock across dates will produce an invalid result.

3.1 Types of fact tables

Transaction facts record individual events, such as sales lines or payments. They support detailed analysis and are usually additive across time, subject to business rules.

Periodic snapshot facts record the state of a process at regular intervals, such as daily inventory or month-end account balances. Snapshot values are often additive across products or locations but not across time. To report a month-end balance, the model should select the appropriate final snapshot rather than sum all daily balances.

Accumulating snapshot facts track a process with milestones, such as an order from placement through shipment and delivery. A row may be updated as dates become available. Measures such as cycle time and days late depend on clearly defined milestone dates.

Factless fact tables record that an event or relationship occurred without a numerical measure. Examples include student attendance, promotion eligibility, or a customer visit. Their rows can be counted, but the count must respect the grain.

3.2 Additivity

Facts are often classified by how they aggregate:

A measure’s aggregation behaviour is a modelling decision, not merely a visual formatting choice. For example, average selling price is usually calculated as total sales divided by total units, not as an average of stored row-level prices.

4. Dimensions, Facts, and Model Structure

A dimensional model separates measurable events from descriptive context. A fact table contains foreign keys to dimensions and numeric or additive observations. A dimension table describes the entities used to filter, group, and label facts.

Typical dimensions include Date, Product, Customer, Employee, Store, Region, and Scenario. A well-designed dimension normally has one row per business entity or entity version, a stable key, descriptive attributes, and a clear user-friendly label.

A simple sales model might contain:

• FactSales: invoice-line key, date key, product key, customer key, store key, quantity, net sales, cost;
• DimDate: date, month, quarter, year, fiscal period, weekday;
• DimProduct: product, brand, category, standard cost;
• DimCustomer: customer, segment, region, acquisition date;
• DimStore: store, city, region, manager;
• FactBudget: budget period, product or region key, budget amount.

The preferred structure is commonly a star schema: dimensions filter a central fact table through one-to-many relationships. This design is easier for users to understand and generally provides clearer DAX filter behaviour than a highly normalised network of tables.

Figure: A star schema in which dimensions provide descriptive filter context for a central sales fact table.

4.1 Keys and relationships

Relationships should reflect business meaning and cardinality. In a typical star schema, one dimension row relates to many fact rows. Dimension keys should be unique on the “one” side. Source-system identifiers may not be sufficient when entities change, merge, or appear in multiple systems; a warehouse or model key may be required.

A role-playing dimension is one dimension used for more than one business role. For example, a Date dimension may relate to Order Date, Ship Date, and Invoice Date. In Power BI, one relationship may be active while others are inactive, or separate role-specific date tables may be used. The choice should be documented because it affects DAX and user interpretation.

Avoid unnecessary many-to-many relationships. They can be valid—for example, when a customer belongs to multiple segments—but they often conceal duplicate keys or an unresolved bridge-table requirement. A bridge table can explicitly represent a many-to-many relationship and, where necessary, allocate facts according to an approved rule.

5. Mapping Requirements to a Model Plan

A model plan is a design document that specifies what will be built and why. At certificate level, it should be sufficiently detailed that another analyst can implement and test the model without repeating the entire discovery process.

A practical model-plan template includes:

5.1 Analytical use cases

Use cases convert requirements into user activities. Common Power BI use cases include:

• Monitoring: viewing current performance against a target;
• Comparison: comparing periods, regions, products, or customer groups;
• Diagnosis: drilling from an overall variance to its contributing causes;
• Exception management: locating late orders, unusual costs, or missing data;
• Planning: comparing actual results with budget or forecast;
• Exploration: filtering and discovering patterns not specified in advance.

For each use case, identify the required grain, dimensions, measure behaviour, and interaction. A dashboard that monitors daily stock requires a different fact design from a report that analyses monthly purchase spend.

6. Data-Quality and Governance Planning

Data quality is multidimensional. A model can be technically refreshed yet analytically wrong. Important dimensions of quality include:

• Completeness: required records or values are present;
• Uniqueness: keys do not duplicate unexpectedly;
• Validity: values conform to permitted formats and ranges;
• Consistency: the same entity and definition agree across systems;
• Timeliness: data is available within the required reporting window;
• Accuracy: values represent the real-world event sufficiently well.

Quality rules should be written as tests. Examples include:

• every sales row has a valid product key;
• product keys are unique in DimProduct;
• invoice-line quantity is not negative unless the row is a documented return;
• invoice dates are not after the refresh date without an approved explanation;
• the sum of posted sales reconciles to the finance control total within an agreed tolerance;
• every fact row maps to exactly one valid dimension member, including an explicit “Unknown” member where appropriate.

A reconciliation is not proof that a model is correct, but it is an essential control. Differences should be investigated rather than hidden by arbitrary adjustments.

6.1 Handling missing and late-arriving data

A missing dimension value can cause a fact row to disappear from analysis or appear under a blank category. A controlled “Unknown” or “Not provided” member preserves referential integrity while making the quality issue visible. It should not be confused with a genuine business category.

Late-arriving dimensions occur when a transaction arrives before its descriptive entity. The model may temporarily assign an inferred or unknown key and replace it when the full dimension record arrives. The procedure must be documented to avoid changing historical totals unexpectedly.

Returns, cancellations, and corrections require explicit rules. A return may be represented as a negative quantity and amount, as a separate event type, or through a reversal transaction. The approach should be consistent with the source system and KPI definitions.

6.2 Governance, privacy, and security

Business intelligence models often contain personal, financial, or commercially sensitive information. Planning should therefore include data minimisation, retention, access control, and auditability. Row-level security may restrict users to their region or department, but it must be tested with representative accounts. Security filters should not create misleading totals or reveal restricted values through aggregates.

Personal data should be included only when it has a legitimate analytical purpose. Names, contact details, and identifiers should be protected according to organisational policy and applicable privacy law. A model catalogue should identify data owners, definitions, refresh responsibilities, and approved users.

7. Worked Planning Example

Consider an illustrative retailer that wants to answer the following question: “Which products and regions are driving the monthly difference between actual sales and budget, and are low-margin products increasing?”

Step 1: Identify processes

The relevant processes are invoiced sales and approved budgeting. Inventory and orders are outside the initial scope because the question concerns recognised sales and budget variance.

Step 2: Define grain

The sales fact grain is one row per invoiced product line. The budget fact grain is one row per month, region, and product category. The two grains differ, which is acceptable, but the dimensions used to compare them must be compatible.

Step 3: Identify dimensions and facts

Sales facts include quantity, net sales, and cost. Budget facts include budget amount. Shared dimensions include Date, Region, and Product Category. Product-level analysis is possible for sales, but not for budget unless budget is supplied at product level. The report must not imply a product-level budget comparison that the source does not support.

Step 4: Define measures

The variance percentage requires a non-zero budget. The model should specify whether a negative budget is valid and how periods with no budget are displayed. Gross margin percentage is calculated from aggregated net sales and gross profit, not averaged from product percentages.

Step 5: Specify acceptance tests

The model is accepted only if monthly actual sales reconcile to the finance ledger, budget totals reconcile to the approved budget file, product and region filters produce expected subtotals, and a sample of low-margin products can be traced to source invoice lines.

This example demonstrates why model planning is not simply a list of tables. It is a set of explicit assumptions connecting business meaning, grain, calculations, and evidence.

8. Common Planning Errors

Starting with the report layout

Designing charts before defining the process often produces disconnected visuals and duplicated calculations. Begin with decisions and measures, then design the model and finally the interface.

Mixing grains

Combining order headers, order lines, payments, and shipments in one table can multiply rows and inflate totals. Separate processes or create carefully defined facts.

Treating every numeric column as a measure

A product code, postal code, or account number may be numeric but is not additive. Data type does not determine analytical meaning.

Averaging ratios

A simple average of percentages can misrepresent the overall result. Recalculate ratios from appropriate totals unless the business explicitly requires a weighted or unweighted average.

Using ambiguous dates

A report labelled “monthly sales” is unreliable if users do not know whether it uses order, invoice, or shipment date. Date meaning belongs in the requirements and model documentation.

Hiding quality problems

Replacing missing values with zero can change the meaning of a metric. Zero means none; blank may mean unknown or not supplied. The treatment must be agreed and visible.

Overusing bidirectional filtering

Bidirectional relationships may create ambiguous filter paths and unexpected totals. Prefer a clear dimension-to-fact filtering pattern unless a justified exception has been tested.

9. Contemporary Practice and the Role of AI

Modern Power BI projects increasingly use dataflows, semantic models, deployment pipelines, automated data-quality checks, and metadata catalogues. These capabilities improve repeatability but do not remove the need for business judgement.

AI-assisted tools can suggest measures, summarise data, generate documentation, or identify anomalies. Their output must be reviewed against approved KPI definitions, security rules, and source reconciliations. An AI-generated DAX expression may be syntactically valid yet use the wrong date, grain, or filter context. Human ownership of definitions and acceptance testing remains essential.

A mature planning practice treats the semantic model as a governed product. Its documentation should explain not only how tables connect, but also what the numbers mean, when they are valid, who owns them, and what limitations users must consider.

Chapter Summary

Business requirements and model planning translate organisational decisions into a reliable Power BI design. Requirements should identify users, decisions, measures, analytical context, and possible actions. KPIs require agreed definitions, units, time bases, aggregation rules, owners, and targets.

The business process must be identified before tables are designed. Every fact table needs a precise grain describing one row. Transaction, periodic snapshot, accumulating snapshot, and factless facts support different analytical questions. Facts record events or states, while dimensions provide descriptive context. Additivity must be considered because sales, balances, and ratios aggregate differently.

A star schema with clear keys and one-to-many dimension-to-fact relationships is a strong foundation for Power BI. A model plan should document processes, grain, dimensions, measures, relationships, dates, refresh, quality rules, security, and acceptance tests. Data quality includes completeness, uniqueness, validity, consistency, timeliness, and accuracy. Governance and privacy must be planned alongside technical design. Finally, analytical use cases and reconciliation tests confirm whether the model genuinely supports business decisions.

Key Terms

Business requirement: A documented organisational information or decision need.

KPI: A defined indicator used to assess performance against an objective or target.

Business process: A recurring activity that produces measurable events or states.

Grain: The precise meaning of one row in a fact table.

Fact table: A table containing measurable events or states and links to dimensions.

Dimension table: A table containing descriptive entities and attributes used for analysis.

Transaction fact: A fact table recording individual business events.

Periodic snapshot: A fact table recording process status at regular intervals.

Accumulating snapshot: A fact table tracking milestones in a process lifecycle.

Factless fact: A fact table recording an event or relationship without a numeric measure.

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

Semi-additive measure: A measure that can be summed across some dimensions but not across time.

Non-additive measure: A value, commonly a ratio, that should not be summed.

Star schema: A dimensional structure with a central fact table and surrounding dimensions.

Role-playing dimension: One dimension used for multiple business roles, such as different dates.

Cardinality: The relationship quantity between tables, such as one-to-many.

Data quality: The extent to which data is complete, valid, consistent, timely, and accurate.

Requirement traceability matrix: A document linking business requirements to model objects and tests.

Row-level security: Access control that restricts data rows visible to a user.

Review Questions

Short Answer Questions

1. What is the difference between a business requirement and a report layout requirement?
2. Define the grain of a fact table and explain why it must be documented.
3. Distinguish between a transaction fact and a periodic snapshot fact.
4. Why should overall margin generally be calculated from aggregated values rather than averaged row-level margins?
5. Name four dimensions of data quality.

Long Answer Questions

1. Explain how a business question can be translated into processes, facts, dimensions, measures, and acceptance tests.
2. Describe the structure and advantages of a star schema for Power BI.
3. Compare additive, semi-additive, and non-additive measures, providing an example of each.
4. Explain how ambiguous dates and mixed grain can produce misleading business intelligence results.
5. Discuss the governance, privacy, and security considerations that should be included in a model plan.

Analytical / Application Questions

1. A company combines order headers, order lines, payments, and shipments in one table. Diagnose the likely modelling problems and propose a fact-table design with appropriate grains.
2. A finance manager reports that the dashboard’s average product margin is 31%, while total gross profit divided by total net sales is 24%. Explain how both values could have been produced and identify which should normally be used for overall performance.
3. Design a model plan for a daily inventory report. Specify the business process, grain, facts, dimensions, semi-additive behaviour, quality rules, and at least two acceptance tests.

Multiple Choice Questions with Answers

Question 1

What is the most precise definition of fact-table grain?

A. The number of columns in a fact table

B. The precise meaning of one row in the fact table

C. The total number of dimensions in a model

D. The frequency at which a report is viewed

Correct Answer: B. The precise meaning of one row in the fact table

Question 2

Which example is a periodic snapshot fact?

A. One row per invoice line

B. One row per customer support case

C. Daily inventory level by product and warehouse

D. One row per payment transaction

Correct Answer: C. Daily inventory level by product and warehouse

Question 3

Which measure is usually semi-additive?

A. Units sold

B. Invoice sales amount

C. Inventory balance

D. Number of invoice lines

Correct Answer: C. Inventory balance

Question 4

In a star schema, dimensions primarily provide what function?

A. Descriptive filtering and grouping context

B. Only encrypted storage

C. Replacement for all fact measures

D. Random row generation

Correct Answer: A. Descriptive filtering and grouping context

Question 5

What is the main risk of mixing order-header and order-line rows in one fact table?

A. Improved referential integrity

B. Automatic elimination of duplicates

C. Double-counting and ambiguous grain

D. Faster KPI approval

Correct Answer: C. Double-counting and ambiguous grain

Question 6

Which rule is an example of a uniqueness test?

A. Every product key appears only once in the product dimension

B. Every report opens before 09:00

C. Every sales amount is displayed in blue

D. Every user receives administrator access

Correct Answer: A. Every product key appears only once in the product dimension

Question 7

If net sales are 80,000 and cost of goods sold is 50,000, what is gross margin percentage?

A. 30.0%

B. 37.5%

C. 62.5%

D. 160.0%

Correct Answer: B. 37.5%

Question 8

Why is an explicit KPI definition important?

A. It removes the need for source data

B. It ensures users interpret and calculate the indicator consistently

C. It guarantees that all data is accurate

D. It makes every measure additive

Correct Answer: B. It ensures users interpret and calculate the indicator consistently

Question 9

Which relationship pattern is generally preferred in a basic star schema?

A. Many-to-many relationships between every table

B. One-to-many relationships from dimensions to facts

C. Fact-to-fact relationships with no dimensions

D. Bidirectional relationships for all tables

Correct Answer: B. One-to-many relationships from dimensions to facts

Question 10

What should normally happen when a percentage denominator is zero?

A. The result should always be 100%

B. The result should always be negative

C. An agreed rule such as blank or not applicable should be used

D. The numerator should be discarded silently

Correct Answer: C. An agreed rule such as blank or not applicable should be used