Data Integration and Business Intelligence Foundations

Introduction

Business intelligence (BI) converts operational data into information that supports monitoring, analysis, planning, and decision-making. In most organisations, useful information is not stored in one place. Sales transactions may exist in an enterprise resource planning system, customer interactions in a customer relationship management platform, budgets in spreadsheets, and web activity in cloud services. Data integration is the discipline of bringing these different sources together in a controlled, reliable, and meaningful way.

This chapter establishes the foundation for later work with Power Query, Power BI, and Microsoft Fabric. It explains how data moves from operational systems to analytical environments, why data must be transformed before analysis, and how Power Query supports repeatable preparation without requiring extensive software development.

Learning Objectives

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

1. Define data integration and explain its importance to business intelligence.
2. Distinguish structured, semi-structured, and unstructured data.
3. Explain ETL, ELT, data pipelines, dataflows, and analytical workloads.
4. Describe the role of Power Query in Power BI and Microsoft Fabric.
5. Identify issues involving data quality, governance, security, and responsible use.
6. Select an appropriate integration approach for a practical business scenario.

1. Business Intelligence as an Information System

Business intelligence is not simply a collection of dashboards. It is an organisational capability involving data, technology, processes, people, and decision-making. A BI environment normally performs four broad activities:

• Data acquisition: obtaining data from operational and external sources.
• Data preparation: correcting, standardising, filtering, combining, and restructuring data.
• Data modelling and analysis: representing business entities and relationships so that measures can be calculated consistently.
• Communication and action: presenting findings through reports, dashboards, alerts, and planning processes.

Operational systems are designed primarily to record transactions efficiently. Analytical systems are designed to examine patterns across transactions. These purposes differ. A point-of-sale system must record an individual sale quickly and accurately; a BI system may need to compare five years of sales by product, region, channel, and customer segment.

An analytical workload commonly involves aggregation, filtering, joining, historical comparison, trend analysis, and repeated queries by many users. The design that is effective for transaction processing is not automatically effective for analysis. Data integration creates a bridge between these environments.

2. The Meaning and Scope of Data Integration

Data integration is the controlled combination of data from multiple sources so that it can be accessed and used as a coherent information resource. Integration may involve physical movement of data, logical access through connectors, or both.

A complete integration process addresses more than copying files. It must consider:

• Meaning: whether fields have consistent business definitions.
• Structure: whether columns, data types, and relationships are compatible.
• Timing: whether data is refreshed hourly, daily, in real time, or on demand.
• Quality: whether values are complete, accurate, valid, and consistent.
• Security: whether users are allowed to access particular records.
• Lineage: whether the origin and transformations of data can be traced.
• Reliability: whether failures are detected and recovery is possible.

For example, two systems may both contain a field called Revenue. In one system it may mean the invoice total before tax; in another it may mean cash received after discounts. Combining the columns without clarifying their definitions produces an integrated dataset that is technically joined but analytically misleading.

Integration patterns

Common integration patterns include:

These patterns are not mutually exclusive. A modern organisation may use federation for immediate operational analysis, replication for a warehouse, and orchestration to refresh a semantic model.

3. Data Types and Data Sources

Structured data

Structured data follows a defined schema. It is commonly organised into tables containing rows and columns. Relational databases, accounting tables, and well-designed spreadsheets are examples. Each column normally has a known meaning and data type, such as integer, decimal, date, or text.

Structured data supports predictable querying and relationships. For example, a sales table may contain OrderID, CustomerID, OrderDate, ProductID, Quantity, and NetAmount. However, structured does not mean correct. A table may have a clear schema while containing duplicate orders, invalid dates, or inconsistent customer identifiers.

Semi-structured data

Semi-structured data does not conform to one rigid table design but contains tags, keys, metadata, or hierarchical relationships. JSON, XML, application programming interface (API) responses, and many log files are common examples. One record may contain nested objects or arrays, and different records may contain different attributes.

Power Query can expand nested records, convert lists into rows, promote headers, and reshape semi-structured data into tables suitable for modelling. The analyst must still decide how nested information should be interpreted. Expanding an array of order lines, for instance, may create multiple rows per order and therefore alter the grain of the data.

Unstructured data

Unstructured data has no consistent tabular schema. Examples include email text, documents, images, audio, and video. Such data can be valuable, but its integration generally requires additional techniques such as document extraction, optical character recognition, natural-language processing, or computer vision. Power Query is primarily a structured and semi-structured data preparation tool; it is not a complete unstructured-data processing platform.

Common source categories

BI projects may use:

• relational databases and data warehouses;
• Excel workbooks and delimited text files;
• SharePoint and cloud storage;
• REST APIs and web services;
• enterprise applications such as finance, sales, and human resources systems;
• event streams, logs, and IoT platforms.

The source category influences authentication, refresh behaviour, privacy requirements, query performance, and available transformations.

4. ETL and ELT

ETL

ETL means extract, transform, load. Data is extracted from source systems, transformed in a processing layer, and then loaded into a target analytical repository.

Figure: The principal difference between ETL and ELT is where transformation occurs.

ETL is useful when the target must receive only validated and shaped data, when source data requires substantial cleansing, or when the target has limited transformation capability. It can also reduce the volume of data loaded into the target.

ELT

ELT means extract, load, transform. Raw or lightly processed data is loaded into a capable cloud or analytical platform, where transformations occur. ELT benefits from scalable storage and compute and can preserve raw data for reprocessing. It also supports a layered architecture in which raw, refined, and curated data are maintained separately.

ETL and ELT are architectural patterns rather than competing moral choices. Many modern solutions use a hybrid approach: basic filtering and type conversion may occur before loading, while complex transformations occur inside the analytical platform.

A transformation can be represented conceptually as:

Here, D_source is the source dataset, T is a documented sequence of transformations, and D_target is the resulting dataset. The notation is conceptual rather than a performance formula. It assumes that transformation rules are defined, repeatable, and applied consistently.

5. Data Pipelines, Dataflows, and Orchestration

A data pipeline is an automated sequence that moves and processes data. A pipeline may include extraction, validation, transformation, loading, testing, notification, and recovery activities.

A pipeline generally has:

1. a trigger, such as a schedule or arrival of a file;
2. source connections and authentication;
3. transformation or movement activities;
4. a destination;
5. monitoring and logging;
6. failure handling and retry rules.

A dataflow is a reusable data-preparation process, often focused on extracting and transforming data independently of a particular report. In the Microsoft ecosystem, Power Query-based dataflows can centralise preparation so that multiple reports or models use consistent logic.

Orchestration coordinates activities and their dependencies. For example, a refresh may first ingest sales data, then validate it, then refresh a model, and finally notify an owner. A pipeline is therefore not merely a query; it is an operational process with timing, dependencies, and control requirements.

6. Power Query: Purpose and Architecture

Power Query is Microsoft’s data connection and transformation technology. It provides connectors for numerous sources and a graphical interface for defining repeatable transformation steps. Power Query is available in products and services including Excel, Power BI, and elements of Microsoft Fabric, although capabilities and deployment contexts may differ.

A typical Power Query process is:

1. Connect: select a source and authenticate.
2. Inspect: examine tables, columns, errors, types, and sample values.
3. Shape: remove unnecessary columns, filter rows, split fields, rename columns, and change data types.
4. Combine: append tables with similar structures or merge tables using matching keys.
5. Validate: check row counts, nulls, duplicates, data types, and business rules.
6. Load: send the result to a worksheet, semantic model, dataflow, or other supported destination.
7. Refresh: rerun the defined steps when new source data becomes available.

Power Query records transformations as an ordered query. Its underlying language, M, is functional and case-sensitive. Users can work primarily through the interface, while advanced users can inspect or edit M expressions. The graphical interface does not eliminate the need for conceptual understanding: a sequence of apparently simple steps can change grain, remove important records, or produce incorrect joins.

Query folding

Query folding occurs when Power Query translates transformations into operations performed by the source system, such as a SQL database. Folding can improve performance because the source filters or aggregates data before transmission. Not all sources or transformations support folding, and folding may stop after a particular step.

Good practice is to filter early, select only required columns, preserve folding where practical, and avoid loading unnecessary data. These are performance guidelines, not absolute rules; business correctness always takes priority.

Append and merge

• Append places rows from one table below rows from another. It is appropriate when tables have comparable column meanings, such as monthly files with the same schema.
• Merge joins columns from one table to another using one or more matching fields. It is appropriate when a transaction table must be enriched with customer or product attributes.

A merge requires attention to key uniqueness. If the lookup table contains several rows for one key, a one-to-many match may duplicate transaction rows and inflate measures. The analyst should confirm the intended relationship and grain before expanding merged columns.

7. Data Grain, Quality, and Semantics

The grain of a table is the level represented by one row. A sales fact table may have one row per invoice line, while a budget table may have one row per department and month. Combining datasets with different grains without careful modelling is a frequent cause of incorrect results.

Important data-quality dimensions include:

Data preparation should distinguish between a missing value, a zero, and an unknown value. A missing sales amount is not automatically zero. Replacing nulls without understanding their meaning can produce materially false reports.

Semantic consistency is equally important. A business glossary should define terms such as customer, active account, gross sales, margin, and reporting date. Technical integration without semantic governance creates multiple versions of the truth.

8. Power BI and Microsoft Fabric Ecosystem

Power BI is a platform for connecting to data, modelling it, creating reports, and distributing analytical content. Power Query commonly performs ingestion and transformation, while the semantic model represents relationships, measures, and business logic. Report visuals communicate the results to users.

Microsoft Fabric is an integrated analytics environment that brings together workloads such as data integration, engineering, warehousing, data science, real-time analytics, and BI. In this ecosystem, Power Query may be used in desktop development, dataflows, and other preparation experiences. Fabric also provides storage and processing options that support lake-oriented and warehouse-oriented architectures.

The roles should be distinguished:

• Power Query: connect to and transform data.
• Semantic model: represent business entities, relationships, and analytical measures.
• Power BI report: present interactive analysis.
• Pipeline/orchestration tools: schedule and coordinate movement and processing.
• Fabric storage and analytical services: retain and process data at organisational scale.

These components cooperate but are not interchangeable. A report should not become the only location for critical cleansing logic when multiple departments require the same prepared data.

9. Analytical Workloads and Architecture Choices

Analytical workloads can be classified by latency, complexity, data volume, and user expectations.

• Batch analytics processes data periodically, such as a nightly sales report.
• Near-real-time analytics refreshes frequently enough for operational supervision.
• Historical analytics examines trends over months or years.
• Self-service analytics allows users to explore governed datasets.
• Operational analytics supports immediate actions, such as inventory replenishment.

An appropriate design balances freshness, cost, performance, complexity, and governance. Real-time processing is not automatically superior. If decisions are made weekly, a reliable daily refresh may be more valuable than an expensive streaming architecture.

A common layered design is:

1. Raw layer: preserves source data with minimal alteration.
2. Refined layer: applies standardisation, validation, and cleansing.
3. Curated layer: presents business-ready tables and measures.
4. Consumption layer: supplies semantic models, reports, and analytical applications.

The layered approach improves traceability and reusability, but it introduces storage and management overhead. The design should match organisational needs rather than follow a fashionable pattern.

10. Applied Case: Regional Retail Integration

The following is an illustrative case.

Background

Northstar Retail operates stores in three regions. Each region exports weekly sales data in a separate CSV file. Product information is maintained in an Excel workbook, while store details are held in a cloud database. Managers require a Power BI report showing sales, units, gross margin, and performance by region and product category.

Problem

The files use different column names and date formats. One region records product codes as text with leading zeroes, while another treats them as numbers. Product descriptions contain spelling variations. Several sales files include blank rows and repeated headers. A direct combination would produce unmatched products and inconsistent totals.

Analysis

The required grain is one row per sales line. Product and store tables should function as descriptive lookup data. The integration team identifies product code and store code as business keys, confirms the definition of gross margin, and agrees that cancelled transactions must be excluded.

Approach and solution

Power Query is used to:

1. connect to the folder containing regional files;
2. apply one transformation function to each file;
3. remove repeated headers and blank rows;
4. standardise column names and data types;
5. preserve product codes as text;
6. append regional tables;
7. filter cancelled transactions;
8. merge valid product and store attributes;
9. identify unmatched keys as data-quality exceptions;
10. load a clean sales table and supporting dimensions into Power BI.

The transformation steps are documented, and a refresh test compares row counts and total sales against approved regional control totals. Exceptions are not silently deleted; they are placed in an issue table for investigation.

Outcome

Managers receive one consistent report, and future weekly files are incorporated through refresh rather than manual copy-and-paste. The organisation also gains a repeatable process for identifying invalid product codes and missing store references.

Learning points

The case demonstrates that integration is a combination of technology and business agreement. Power Query simplifies repeatability, but it cannot determine whether gross margin is defined correctly or whether a product code is valid. Those decisions require domain knowledge, governance, and testing.

11. Governance, Security, and Ethical Responsibilities

Integrated data can reveal sensitive information about customers, employees, suppliers, and transactions. Responsible BI therefore requires:

• least-privilege access;
• secure credential management;
• protection of personal and confidential data;
• retention and deletion rules;
• audit trails and lineage;
• appropriate data classification;
• row-level or object-level restrictions where necessary;
• documented ownership and stewardship.

Privacy laws and sector regulations vary by jurisdiction, so organisations must consult applicable legal and compliance requirements. Analysts should avoid copying personal data merely because a connector makes it technically possible.

Data quality also has an ethical dimension. A report that systematically excludes a region, misclassifies customers, or uses outdated records may produce unfair decisions. Automated transformations should be explainable, tested, and subject to human review when consequences are significant.

Artificial intelligence can assist with anomaly detection, natural-language querying, and transformation suggestions. However, AI-generated transformations or interpretations must be validated. AI does not remove responsibility for source provenance, privacy, accuracy, or human accountability.

12. Best Practices and Limitations

Effective Power Query and integration practice includes:

• define business requirements and table grain before transforming;
• use stable keys rather than descriptions for joins;
• set data types explicitly;
• remove unnecessary columns early;
• use parameters for environments, dates, and file paths where appropriate;
• name queries and steps clearly;
• separate staging queries from final output queries;
• document assumptions and exceptions;
• test refreshes with changed and missing source files;
• monitor row counts, nulls, duplicates, and unmatched keys;
• preserve privacy and credentials through approved connections;
• reuse governed transformations rather than recreating them in every report.

Power Query also has limitations. Very large or highly complex workloads may require dedicated engineering, warehouse, lakehouse, or streaming technologies. Some transformations may not fold to the source and can be slow. A desktop query may work for an individual analyst but lack the monitoring, deployment, and governance required for enterprise production. Power Query is therefore an important component of an integration architecture, not a replacement for every data platform capability.

Chapter Summary

Data integration combines data from diverse sources into a coherent and governed resource for analysis. Business intelligence depends on the distinction between operational workloads, which record transactions, and analytical workloads, which examine patterns and support decisions. Structured data uses defined tables; semi-structured data uses flexible keys, tags, or hierarchies; and unstructured data requires additional processing techniques.

ETL transforms data before loading it, whereas ELT loads data before transforming it in the target platform. Data pipelines automate movement and processing, while dataflows provide reusable preparation logic. Power Query connects to many sources, records repeatable transformations, supports appending and merging, and may use query folding to improve performance. Its effectiveness depends on correct data types, keys, grain, validation, and business definitions.

Within Power BI and Microsoft Fabric, Power Query works alongside semantic models, reports, storage, and orchestration services. A professional solution also addresses privacy, security, lineage, governance, monitoring, and ethical responsibility. The central principle is that reliable BI is not created by visualisation alone: it begins with well-designed, well-understood, and well-governed data integration.

Key Terms

Data integration: Combining data from multiple sources into a coherent resource.

Business intelligence: Processes and technologies that turn data into information for decision-making.

ETL: Extract, transform, and load; transformation occurs before loading into the target.

ELT: Extract, load, and transform; transformation occurs after loading into the target.

Data pipeline: An automated sequence that moves, processes, validates, and loads data.

Dataflow: A reusable process for connecting to and transforming data.

Structured data: Data organised according to a defined schema, usually in tables.

Semi-structured data: Data with flexible or hierarchical organisation using keys, tags, or metadata.

Unstructured data: Data without a consistent tabular schema, such as text, images, or audio.

Analytical workload: A workload involving queries, aggregation, comparison, and interpretation.

Operational workload: A workload focused on recording and processing current transactions.

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

Query folding: Translation of transformations into operations executed by the source system.

Append: Combining tables by placing rows from one table after rows from another.

Merge: Joining tables using matching key fields.

Semantic model: A governed representation of entities, relationships, measures, and business meaning.

Data lineage: A record of where data originated and how it was transformed.

Orchestration: Coordination of data activities, dependencies, schedules, and responses.

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

Power Query: Microsoft technology for connecting to, shaping, and transforming data.

Review Questions

Short Answer Questions

1. What is data integration, and why is it necessary for business intelligence?
2. State one difference between structured and semi-structured data.
3. What is the principal difference between ETL and ELT?
4. Explain the difference between appending and merging tables in Power Query.
5. What is data grain, and why does it matter?

Long Answer Questions

1. Explain the stages of a typical data pipeline and discuss the role of monitoring and failure handling.
2. Compare ETL and ELT, including situations in which each approach may be appropriate.
3. Describe how Power Query contributes to a Power BI solution from connection through refresh.
4. Discuss the relationship between operational workloads and analytical workloads.
5. Explain why data governance, privacy, and lineage are necessary in an integrated BI environment.

Analytical / Application Questions

1. A company receives monthly sales spreadsheets from five branches. The files use different date formats and one branch includes cancelled orders. Design a Power Query preparation process and identify at least four validation checks.
2. A merge between a sales table and a product table increases total sales unexpectedly. Analyse the likely causes and explain how you would diagnose and correct the problem.
3. An organisation wants real-time analytics, but managers review performance only once each morning. Evaluate whether a daily batch pipeline may be more appropriate than a real-time architecture, and justify your conclusion.

Multiple Choice Questions with Answers

Question 1

Which statement best defines data integration?

A. Creating charts from a single spreadsheet

B. Combining data from multiple sources into a coherent and usable resource

C. Deleting all duplicate records from a database

D. Storing only unstructured files in cloud storage

Correct Answer: B. Combining data from multiple sources into a coherent and usable resource

Question 2

In an ETL process, when does transformation occur?

A. After data is loaded into the analytical store

B. Before data is extracted from the source

C. Between extraction and loading

D. Only after a report is published

Correct Answer: C. Between extraction and loading

Question 3

Which example is semi-structured data?

A. A relational table with fixed columns

B. A JSON document containing nested keys and arrays

C. A printed photograph

D. A handwritten invoice with no digital representation

Correct Answer: B. A JSON document containing nested keys and arrays

Question 4

What does appending tables in Power Query do?

A. Adds rows from one table below rows from another

B. Adds descriptive columns using a matching key

C. Deletes rows with null values

D. Converts a table into a chart

Correct Answer: A. Adds rows from one table below rows from another

Question 5

What is query folding?

A. Encrypting a query before transmission

B. Converting a query into a dashboard

C. Having the source system perform supported transformations

D. Combining two unrelated data models

Correct Answer: C. Having the source system perform supported transformations

Question 6

Why can a non-unique lookup key cause inflated totals after a merge?

A. It changes all text values to dates

B. It can create multiple matches for one transaction row

C. It automatically removes valid transactions

D. It prevents any columns from loading

Correct Answer: B. It can create multiple matches for one transaction row

Question 7

Which item is primarily the role of a semantic model?

A. Defining relationships, measures, and business meaning for analysis

B. Physically scanning paper documents

C. Replacing every source-system transaction

D. Scheduling a computer to restart

Correct Answer: A. Defining relationships, measures, and business meaning for analysis

Question 8

Which practice best supports reliable data integration?

A. Treating missing values as zero in every dataset

B. Joining tables using descriptive names only

C. Documenting assumptions and testing row counts and exceptions

D. Removing all records that fail a join without review

Correct Answer: C. Documenting assumptions and testing row counts and exceptions

Question 9

Which statement about Power Query is most accurate?

A. It is only a visualisation tool

B. It connects to sources and applies repeatable data transformations

C. It guarantees that business definitions are correct

D. It replaces the need for data governance

Correct Answer: B. It connects to sources and applies repeatable data transformations

Question 10

Why might a daily batch process be preferable to real-time processing?

A. Real-time processing is always impossible

B. Daily decisions may not justify the additional cost and complexity of real-time systems

C. Batch processing cannot retain historical data

D. Real-time systems cannot connect to databases

Correct Answer: B. Daily decisions may not justify the additional cost and complexity of real-time systems