Data Foundations, Quality and Governance

Introduction

Business intelligence is only as reliable as the data on which it is built. A Power BI dashboard may contain attractive visualisations, sophisticated calculations and rapid refresh schedules, yet still mislead decision-makers if its underlying data is incomplete, inaccurate, inconsistent, invalid, obsolete or poorly governed. Data foundations therefore precede dashboard design. Before selecting charts or writing measures, an analyst must understand what data represents, where it originates, how it is structured, who is responsible for it and what controls protect it.

This chapter introduces the practical foundations required for trustworthy business analytics. It examines common data types, sources and structures; explains the principal dimensions of data quality; and develops the governance practices needed to make data understandable, traceable, secure and reusable. The discussion is particularly relevant to Power BI because Power BI solutions commonly combine multiple sources, transform data through Power Query, relate tables through a semantic model and distribute insights to users with different permissions.

Learning Objectives

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

• distinguish common categorical, numerical, temporal and identifier data types;
• recognise structured, semi-structured and unstructured data sources;
• explain tables, records, fields, keys, relationships and dimensional structures;
• evaluate completeness, accuracy, consistency, validity and timeliness;
• apply practical data-quality measures and controls;
• explain data ownership, stewardship, metadata and lineage;
• identify important privacy, security and ethical obligations; and
• describe master data and its role in consistent reporting.

1. Data as a Foundation for Business Intelligence

Data is a recorded representation of events, objects, measurements or decisions. Information is data interpreted in context, while insight is an understanding that supports action. For example, the value 125 is data; “125 units were sold in the North region during March” is contextual information; and “North-region inventory should be replenished before the next promotion” is a possible insight.

A dashboard does not automatically convert poor data into good decisions. A useful way to express this principle is:

This is a conceptual relationship rather than a measured law. It indicates that a failure in any one factor can reduce decision value. High-quality data that is irrelevant to the decision is not useful, and an appropriate analysis can still be misunderstood by an inexperienced user.

Data should be examined at three levels:

1. Business meaning: What does the data represent? What business question does it answer?
2. Technical structure: In what format is it stored? How are records related?
3. Governance and control: Who owns it, how is it defined, and who may use it?

A Power BI report designer who focuses only on the technical level may create a functioning report that uses the wrong definition of revenue or duplicates customers across sources. Business interpretation and governance are therefore essential parts of analytics work.

2. Common Data Types

A data type describes the kind of value a field can contain and the operations that are meaningful for it. Correct typing is important in Power BI because data types influence sorting, aggregation, filtering, relationships and visual behaviour.

2.1 Categorical data

Categorical data places observations into groups.

• Nominal data has categories without an inherent order, such as product category, country or payment method.
• Ordinal data has ordered categories, although the distance between categories is not necessarily equal, such as low, medium and high risk.
• Binary data has two states, such as active/inactive or yes/no.

A category code such as 1 = Bronze, 2 = Silver and 3 = Gold should not automatically be treated as numerical data. The codes identify categories; calculating their average would generally have no meaningful interpretation.

2.2 Numerical data

Numerical data represents quantities.

• Discrete data consists of countable values, such as number of orders.
• Continuous data can take values within a range, such as weight, time or temperature.
• Measures are numerical fields that can be aggregated, such as sales amount and quantity.
• Identifiers may contain digits but are usually labels rather than quantities. Customer ID 1042 is not twice the customer represented by ID 521.

Numerical fields should also have a unit and business definition. “Revenue” might mean gross invoiced sales, net sales after discounts, or cash received. The field name alone is insufficient.

2.3 Temporal data

Temporal data includes dates, times, timestamps, months, quarters and financial periods. Analysts must distinguish:

• a date, such as 15 June 2025;
• a time, such as 14:30;
• a datetime, combining date and time; and
• a duration, such as 36 hours.

Time zones are especially important in international organisations. A transaction recorded at 23:30 UTC may belong to the following calendar day in another location. Reports should document whether timestamps are stored in local time or Coordinated Universal Time (UTC).

2.4 Text, geographic and media data

Text fields include descriptions, comments and addresses. Free text is difficult to aggregate because similar meanings may be written in different ways. Geographic data includes country, region, postal code, latitude and longitude. Power BI may interpret geographic categories incorrectly if, for example, a field named “Springfield” is not associated with a country or state. Images, audio and video are generally unstructured or semi-structured content and often require specialised tools beyond a standard relational model.

3. Data Sources and Structures

3.1 Common sources

Business intelligence commonly draws from:

• enterprise resource planning and customer relationship management systems;
• relational databases and data warehouses;
• spreadsheets and comma-separated value files;
• application programming interfaces (APIs);
• cloud storage and software-as-a-service platforms;
• web logs, sensors and Internet-of-Things devices; and
• surveys, manually entered forms and external market datasets.

Each source has different reliability, refresh frequency, ownership and structural characteristics. A spreadsheet may be easy to access but vulnerable to overwritten formulas, inconsistent column names and uncontrolled versions. A transactional database may be highly structured but contain operational codes that require specialist interpretation.

3.2 Structured, semi-structured and unstructured data

Structured data follows a defined schema. Relational tables with rows and columns are the standard example. Semi-structured data contains organisational markers but does not require every record to have identical fields; JSON and XML are common examples. Unstructured data lacks a consistent tabular structure, as in emails, documents, photographs and recordings.

A source’s structure affects the preparation process. Structured data can often be profiled with column-level tests. Semi-structured data may require expansion of nested records. Unstructured data may require natural-language processing, optical character recognition or classification before it becomes suitable for conventional reporting.

3.3 Relational foundations

A relational table contains records, also called rows, and attributes, also called columns or fields. A primary key uniquely identifies each row. A foreign key stores a value that refers to a key in another table. A relationship connects tables through matching keys.

For example, a Sales table might contain OrderID, DateKey, ProductKey, CustomerKey and Amount. ProductKey can connect it to a Product table, while CustomerKey connects it to a Customer table. The Sales table is a fact table because it records business events; Product and Customer are dimension tables because they describe the events.

A one-to-many relationship occurs when one product appears in the Product table but many sales rows refer to that product. A many-to-many relationship occurs when records on both sides may have multiple matches. Many-to-many structures require careful modelling because they can produce ambiguous filtering or double counting.

3.4 Grain and dimensional modelling

The grain of a table is the level represented by one row. A Sales table might have one row per invoice line, while a monthly summary table has one row per product, region and month. Mixing different grains without a clear design is a common source of incorrect totals.

A star schema usually places a fact table at the centre and connects it to descriptive dimensions such as Date, Product, Customer and Geography. This structure supports understandable filtering and efficient analysis. Dimension tables should generally contain one row per distinct business entity at the relevant level, while fact tables contain measurable events.

A date dimension is particularly important. It allows consistent year, quarter, month and weekday analysis and prevents different reports from applying different definitions of a reporting period.

4. Dimensions of Data Quality

Data quality is fitness for a defined purpose. A value can be acceptable for one use and unsuitable for another. For example, an address missing an apartment number may be adequate for regional sales analysis but inadequate for delivery.

4.1 Completeness

Completeness concerns whether required values and records are present. A field is incomplete when necessary values are missing; a dataset is incomplete when expected records have not arrived.

A simple completeness rate for a field is:

The denominator must be defined carefully. If blank values are legitimate—for example, a termination date for active employees—counting them as errors would be incorrect. Completeness rules should therefore be based on business requirements, not merely on the presence of a value.

4.2 Accuracy

Accuracy is the degree to which data represents the correct real-world value. A populated field may still be inaccurate: a customer’s phone number can be complete but belong to another person. Accuracy normally requires comparison with a trusted source, validation evidence or a controlled business process.

Accuracy is often harder to measure than completeness because the true value may not be directly observable. Sampling, reconciliations, exception reports and confirmation with process owners are common approaches.

4.3 Consistency

Consistency means that the same concept is represented in compatible ways across fields, systems and time. Examples of inconsistency include:

• “United Kingdom,” “UK” and “GB” used for the same country;
• different currencies presented as if they were comparable;
• a customer classified as active in one system and inactive in another; and
• totals in a dashboard that do not reconcile to the source ledger.

Consistency requires common definitions, reference tables, transformation rules and reconciliation procedures.

4.4 Validity

Validity concerns compliance with a permitted format, range, type or business rule. A date such as 31 February is invalid. An order quantity of −4 may be invalid for a sales transaction, although negative quantities could be valid for a return process. Validation must therefore reflect the meaning and process context.

Examples include:

• email address format checks;
• permitted-value checks for status codes;
• range checks for prices and quantities;
• referential-integrity checks ensuring that every ProductKey exists in the Product table; and
• cross-field rules, such as a delivery date not preceding an order date.

4.5 Timeliness and currency

Timeliness refers to whether data is available when needed. Currency refers to how up to date it is. A daily report may be timely for weekly planning but not for real-time fraud detection. A timestamp showing when data was extracted is therefore often as important as the business date.

A practical service-level measure is the proportion of scheduled refreshes completed within an agreed deadline. The target should reflect decision requirements rather than an arbitrary technical ambition.

4.6 Uniqueness and integrity

Uniqueness means that an entity or event is not represented more than once when duplication is not intended. Duplicate customers can inflate customer counts and duplicate transactions can inflate revenue. Integrity includes preservation of relationships and business rules as data moves between systems.

A quality score should not conceal serious failures by averaging dimensions. A report with 99% completeness but a missing high-value region may be unacceptable. Thresholds and criticality classifications are often more useful than a single overall score.

5. Profiling and Improving Data Quality

Data profiling is the systematic examination of data before modelling. A basic profile may include row counts, distinct-value counts, null counts, minimum and maximum values, frequency distributions, duplicate checks and relationship tests.

A practical workflow is:

1. define the business purpose and critical fields;
2. identify the source and expected refresh behaviour;
3. profile values and structural patterns;
4. classify exceptions by severity and likely cause;
5. correct data at the source where possible;
6. document unavoidable transformations; and
7. monitor quality continuously after publication.

Cleaning should not silently erase evidence. For example, replacing every blank with zero changes meaning: a blank quantity may indicate missing information, whereas zero indicates that no quantity occurred. Transformation steps should be recorded and, where appropriate, rejected records should be retained in an exception table.

A useful distinction is between error correction and business-rule transformation. Correcting “UKK” to “UK” may address an obvious coding error. Converting a transaction from local currency to a reporting currency is a transformation that must use a documented exchange-rate policy.

6. Data Governance: Roles and Accountability

Data governance is the framework of decision rights, responsibilities, policies and controls used to manage data as an organisational asset. It is not merely an IT activity. Business leaders must define meaning and acceptable use, while technical teams implement storage, access and quality controls.

Typical roles include:

• Data owner: accountable for a data domain, its definitions, risk decisions and access policy.
• Data steward: manages definitions, quality rules, issue resolution and day-to-day coordination.
• Data custodian: operates technical storage, backup, access and security controls.
• Data consumer: uses data according to approved purposes and reports problems.
• Privacy or security officer: advises on legal, regulatory and protective requirements.

Responsibilities should be explicit. If a dashboard shows an incorrect customer status, the organisation should know who can approve the definition, who can correct the source and who can change the report.

6.1 Policies and standards

A governance programme commonly includes standards for naming, definitions, classification, retention, access, quality, change management and incident handling. A data dictionary records agreed meanings. For example, “customer” might mean any account with a record, an account with at least one transaction, or an active paying account. Each definition produces different results.

Governance should be proportionate to risk. Financial reporting, health information and personal identifiers require stronger controls than low-risk publicly available data. Classification labels such as public, internal, confidential and restricted can guide handling requirements.

7. Metadata and Data Lineage

Metadata is data about data. Technical metadata may identify field names, data types, table locations and refresh schedules. Business metadata explains definitions, owners, approved uses and calculation rules. Operational metadata records refresh outcomes, record counts and quality exceptions.

A data catalogue brings metadata together so analysts can discover and understand approved datasets. A catalogue is valuable only when its content is maintained. An outdated description can be more harmful than no description because it creates false confidence.

Data lineage describes the path data follows from origin to consumption. In a Power BI environment, lineage may include a source system, a staging query, Power Query transformations, a model table, a DAX measure and a published visual. Lineage supports impact analysis: before changing a source field, an analyst can identify affected reports and measures.

Lineage also supports auditability. A decision-maker should be able to ask where a figure came from, which transformations were applied and when the data was last refreshed. Lineage is not the same as quality: knowing the path of an incorrect value does not make the value correct, but it makes investigation possible.

8. Privacy, Security and Ethical Use

Data governance must protect people as well as organisational interests. Personal data may include names, contact details, identifiers, location information, employment information and online identifiers. Sensitive data can create greater harm if disclosed or misused.

Important principles include:

• purpose limitation: collect and use data for specified legitimate purposes;
• data minimisation: use only what is necessary;
• access control: give users the least access required for their role;
• transparency: explain how data is collected and used;
• retention limitation: do not retain data indefinitely without justification; and
• security by design: build protection into systems and processes.

Legal requirements vary by jurisdiction, so organisations should follow applicable privacy and records-management law rather than assume that one framework applies everywhere. Power BI implementations may use workspace permissions, row-level security, sensitivity labels, secure gateways and controlled sharing. These tools reduce risk but do not replace governance decisions.

Anonymisation attempts to remove the possibility of identifying individuals, while pseudonymisation replaces direct identifiers with codes but may still permit re-identification when additional information is available. Aggregation can reduce disclosure risk, but small groups may still reveal individuals.

Ethical risks include discriminatory segmentation, unjustified surveillance, inappropriate profiling and the presentation of uncertain results as facts. Analysts should question whether a technically possible analysis is appropriate and whether affected people would reasonably expect the data to be used in that way.

9. Master Data and Reference Data

Master data describes core entities shared across processes, such as customers, products, suppliers, employees and locations. Reference data consists of controlled lists used to classify or interpret data, such as country codes, currencies, status values and industry codes.

Without master-data management, different systems may create separate records for the same customer or use different product descriptions. This leads to fragmented reporting. A master-data process establishes identifiers, ownership, matching rules, survivorship rules and change procedures.

A common approach is to create a “golden record”—the approved representation of an entity assembled from trusted attributes. This does not mean every source is discarded. Source-system identifiers may be retained in a cross-reference table so that transactions remain traceable.

Matching may use exact keys, such as a formally assigned customer number, or probabilistic evidence, such as similar names and addresses. Automated matching should be reviewed when decisions have material consequences. False matches combine two different entities; missed matches leave one entity fragmented. Both errors can distort analytics.

10. Applied Case: Regional Retail Performance Dashboard

The following is an illustrative case.

Background

A retail organisation builds a Power BI dashboard showing sales, margin, active customers and inventory by region. Data is drawn from an enterprise sales system, a product spreadsheet and a customer-service platform.

Problem

Managers report that dashboard sales exceed finance totals by 8%. Product categories differ between the sales and inventory views, and several customers appear twice. The dashboard also includes personal customer contact details that regional managers do not need.

Analysis

The analyst first establishes the grain of each source. Sales contains one row per invoice line, inventory contains one row per product and warehouse, and the customer-service file contains multiple interactions per customer. A direct merge of all three tables would multiply rows. Profiling reveals that:

• some invoice lines are duplicated after a failed file reload;
• returns are represented as negative sales in finance but excluded in the dashboard query;
• product categories are maintained manually in two different places;
• customer IDs are missing for some historical transactions; and
• the customer-service file includes names and phone numbers without a documented reporting need.

Approach and solution

The team defines net sales as invoiced sales less approved returns, excluding cancelled invoices. A controlled Date dimension and Product dimension are created. Sales remains at invoice-line grain, while customer interactions are kept in a separate fact table. A governed product reference table supplies the category definition. Duplicate invoice-line records are removed using a documented business key and source-load identifier. Unmatched historical customers are assigned to an “Unknown” member rather than being silently dropped.

The model includes only the customer attributes required for analysis. Contact details are excluded from the semantic model, and regional access is implemented through appropriate row-level security. Metadata records the sales definition, source systems, refresh time, owner and known limitations.

Outcome

After reconciliation, dashboard sales agree with finance within the approved tolerance. Product categories are consistent across reports, customer counts are no longer inflated by repeated service interactions, and unnecessary personal data is not distributed. The organisation also introduces a monthly quality review covering duplicate transactions, unmatched keys, refresh failures and category changes.

Learning points

The central lesson is that dashboard defects often originate in business definitions and data structures, not in visual design. Establishing grain, definitions, ownership and lineage before building visuals prevents many downstream errors. Separating fact tables also protects measures from accidental multiplication.

11. Contemporary Developments and Limitations

Cloud platforms increasingly provide automated catalogues, quality rules, lineage views and anomaly detection. Machine learning can identify unusual patterns, suggest data matches and classify text. Generative AI can assist with documentation and query development. However, automated recommendations are not automatically correct. They may reproduce historical bias, misclassify unusual but valid records or expose confidential information through inappropriate prompts.

Data quality tools also have limitations. A rule can detect a value outside an allowed range but may not know whether the business rule itself is appropriate. Metadata can become stale, lineage can be incomplete across system boundaries and master-data matching can create false confidence. Human ownership, review and accountability remain necessary.

Best practice is to combine prevention, detection and correction. Prevent invalid values at data-entry points; detect exceptions through profiling and monitoring; and correct root causes rather than repeatedly repairing reports. Governance should be integrated into delivery processes so that quality and privacy are considered before publication, not after an incident.

Chapter Summary

Reliable business intelligence begins with clear data foundations. Analysts must distinguish categories, quantities, identifiers and temporal values, and must understand whether sources are structured, semi-structured or unstructured. Relational concepts—including keys, relationships, grain, fact tables and dimensions—determine whether a Power BI model produces meaningful results.

Data quality is multidimensional. Completeness, accuracy, consistency, validity, timeliness, uniqueness and integrity should be assessed against a defined business purpose. Profiling, reconciliation, exception handling and monitoring are practical methods for controlling quality. Cleaning must preserve meaning and document transformations.

Data governance assigns accountability through owners, stewards, custodians and consumers. Metadata explains what data means and how it should be used, while lineage shows its movement from source to report. Privacy, access control, minimisation and ethical review protect individuals and organisations. Master data and reference data provide shared definitions for customers, products, locations and classifications. Together, these practices make dashboards more trustworthy, auditable and useful for decision-making.

Key Terms

Data type: The category of values a field can contain and the operations appropriate to it.

Categorical data: Data representing groups or labels rather than quantities.

Primary key: A field or combination of fields that uniquely identifies a record.

Foreign key: A field that refers to a key in another table.

Fact table: A table containing business events or measurable transactions.

Dimension table: A table containing descriptive attributes used to analyse facts.

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

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

Accuracy: The extent to which data reflects the correct real-world value.

Consistency: The extent to which data has compatible meanings and representations.

Validity: Compliance with permitted formats, ranges and business rules.

Timeliness: Availability of data when it is required for use.

Data profiling: Systematic examination of data values, structures and patterns.

Data governance: Policies, roles, rights and controls for managing data.

Data owner: The person accountable for a data domain and its approved use.

Metadata: Information that describes data, its meaning, structure or operation.

Data lineage: The documented path of data from origin through transformations to consumption.

Master data: Shared, relatively stable information about core business entities.

Reference data: Controlled values used to classify or interpret other data.

Row-level security: A control that restricts the rows visible to a user according to authorised rules.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Explain why an identifier containing digits should not automatically be treated as a numerical measure.
3. Define completeness and give one example of a completeness rule.
4. What is the purpose of metadata in a Power BI environment?
5. Distinguish master data from reference data.

Long Answer Questions

1. Explain the main dimensions of data quality and discuss why a single overall quality score may be misleading.
2. Describe how grain, fact tables and dimension tables contribute to a reliable Power BI semantic model.
3. Explain the roles of a data owner, data steward and data custodian.
4. Discuss how privacy, minimisation and access control should influence dashboard design.
5. Describe a practical workflow for profiling, correcting and monitoring data quality.

Analytical / Application Questions

1. A sales dashboard shows higher revenue than the finance ledger after a new returns file was introduced. Design an investigation plan that uses definitions, grain, reconciliation and lineage.
2. A company has three customer lists with different identifiers, names and addresses. Propose a master-data approach and explain the risks of false matches and missed matches.
3. A regional manager requests names, phone numbers and addresses for a dashboard that reports sales by territory. Evaluate whether the request should be approved and recommend an alternative design.

Multiple Choice Questions with Answers

Question 1

Which field is most likely to be an identifier rather than a numerical measure?

A. Customer ID

B. Sales amount

C. Quantity sold

D. Unit weight

Correct Answer: A. Customer ID

Question 2

What does the grain of a table describe?

A. The colour scheme used in a report

B. The level of detail represented by one row

C. The number of dashboards using the table

D. The storage size of the table

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

Question 3

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

A. Accuracy

B. Validity

C. Completeness

D. Timeliness

Correct Answer: C. Completeness

Question 4

A product category value does not appear in the approved category list. Which quality problem is most directly indicated?

A. Invalidity

B. Timeliness

C. Uniqueness

D. Currency conversion

Correct Answer: A. Invalidity

Question 5

Who is typically accountable for the definition and approved use of a data domain?

A. Data consumer

B. Data owner

C. Report viewer

D. Database user

Correct Answer: B. Data owner

Question 6

What is the principal purpose of data lineage?

A. To increase screen resolution

B. To show the path of data through sources and transformations

C. To replace all data-quality checks

D. To remove the need for metadata

Correct Answer: B. To show the path of data through sources and transformations

Question 7

Which modelling practice best reduces accidental multiplication of sales when analysing customer interactions?

A. Merge all tables into one wide table

B. Keep separate fact tables at their appropriate grain

C. Delete all interaction records

D. Use customer names as the only key

Correct Answer: B. Keep separate fact tables at their appropriate grain

Question 8

Which principle recommends collecting and using only data necessary for a stated purpose?

A. Data minimisation

B. Data duplication

C. Maximum retention

D. Unrestricted access

Correct Answer: A. Data minimisation

Question 9

Which statement about master data is correct?

A. It consists only of temporary dashboard filters

B. It describes shared core entities such as customers and products

C. It is always unstructured text

D. It cannot have an owner

Correct Answer: B. It describes shared core entities such as customers and products

Question 10

A blank termination date for an active employee is best treated as what?

A. Automatically invalid

B. Automatically zero

C. Potentially legitimate, depending on the business rule

D. A duplicate key

Correct Answer: C. Potentially legitimate, depending on the business rule