Data Sources and BI Architecture

Introduction

Business intelligence (BI) begins before a report is designed. It begins with decisions about where data is stored, how it is accessed, how it is transformed, and how business meaning is applied. A visually attractive dashboard can still produce poor decisions if its underlying data is incomplete, stale, inconsistent, or incorrectly modelled.

This chapter introduces the principal data environments used in BI and explains how they are connected through an architecture. It focuses on structured and unstructured data, databases, files, cloud services, application programming interfaces (APIs), data warehouses, data lakes, lakehouses, semantic models, and Microsoft Power BI connectivity modes. The emphasis is practical: a BI professional should be able to select a suitable source, understand the consequences of an architectural choice, and design a trustworthy path from operational data to decision-making.

Learning Objectives

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

• distinguish structured, semi-structured, and unstructured data;
• compare databases, files, cloud services, and APIs as BI sources;
• explain the purposes of data warehouses, data lakes, and lakehouses;
• describe the role of a semantic model in Power BI;
• distinguish Import, DirectQuery, live connection, and composite models;
• evaluate architecture in terms of performance, freshness, governance, security, and cost;
• recommend an appropriate architecture for a business scenario.

1. Data as the Foundation of Business Intelligence

Data is a recorded representation of events, entities, measurements, or observations. Information is data interpreted in context. For example, “125” is data; “125 units sold in the North region during March” is more meaningful information. BI converts information into insight by combining data with analytical models, business definitions, and visual communication.

A typical BI flow contains five broad stages:

1. Source systems capture or store operational facts.
2. Ingestion moves data into an analytical environment.
3. Transformation and quality processing standardise, validate, and reshape data.
4. Modelling defines relationships, measures, hierarchies, and business rules.
5. Consumption presents analysis through reports, dashboards, alerts, or applications.

These stages are not necessarily separate products. Power BI can connect directly to many sources and perform transformations through Power Query. In larger organisations, Power BI may sit above an enterprise warehouse, lakehouse, or governed semantic model.

A useful architectural principle is to separate the concerns of storage, transformation, modelling, and presentation. This separation makes systems easier to test, govern, reuse, and change.

2. Types of Data

2.1 Structured data

Structured data follows a defined schema. It is commonly organised into tables containing rows and columns, with known data types and relationships. Examples include customer records, sales transactions, inventory balances, and general-ledger entries.

Structured data is suitable for relational queries because fields such as CustomerID, OrderDate, and Amount have predictable meanings. Relational database management systems (RDBMSs) enforce rules such as primary keys, foreign keys, data types, and constraints.

2.2 Semi-structured data

Semi-structured data does not normally fit a rigid table, but it contains organisational markers such as keys, tags, or nested objects. JSON, XML, web event records, and many API responses are common examples. Two records may contain different attributes, or one attribute may contain a nested collection.

Semi-structured data is flexible and useful for exchanging information between applications. However, it often requires parsing, type conversion, flattening, and validation before it is suitable for a star-schema model.

2.3 Unstructured data

Unstructured data lacks a fixed tabular schema. Examples include emails, contracts, photographs, audio recordings, videos, scanned documents, and social-media text. Unstructured data can contain valuable business evidence, but extracting meaning may require search, natural-language processing, optical character recognition, or machine-learning techniques.

Power BI can visualise some unstructured-data outputs, but it is usually not the principal repository or processing environment for large collections of documents or multimedia. A common pattern is to process unstructured content elsewhere and expose structured metadata, classifications, extracted entities, or measures to the BI layer.

2.4 Data quality dimensions

Data quality is multidimensional. Important dimensions include:

• Accuracy: whether values represent reality;
• Completeness: whether required records or fields are present;
• Consistency: whether the same concept has compatible values across systems;
• Timeliness: whether data is available at the required time;
• Validity: whether values conform to permitted formats and rules;
• Uniqueness: whether unwanted duplicates have been removed.

A dashboard cannot compensate for poor source quality. Data-quality checks should therefore be designed into ingestion and transformation processes rather than performed only after a report has failed.

3. Common BI Data Sources

3.1 Relational databases

Relational databases store data in tables and use relationships to connect entities. SQL Server, Azure SQL Database, Oracle, PostgreSQL, and MySQL are examples. They are often used by enterprise applications such as finance, sales, human resources, and inventory systems.

A relational design typically separates data into normalised tables to reduce duplication and protect transactional integrity. For operational processing, this is beneficial. For analytics, a highly normalised structure may require numerous joins and may be less intuitive for report authors. Analytical systems therefore often create dimensional models containing fact and dimension tables.

A fact table records measurable events, such as invoice lines or service calls. A dimension table describes the context of those events, such as product, customer, date, or region. A star schema generally has a central fact table connected to dimensions through one-to-many relationships.

The grain of a fact table is its precise level of detail. For example, “one row per sales invoice line” is a grain statement. Establishing grain before creating measures prevents double counting and ambiguous analysis.

3.2 Files and spreadsheets

CSV, Excel, text, XML, and JSON files are widely used by small and medium-sized organisations. Files are convenient because they are easy to create and share. They are also risky because they may contain duplicated versions, changed column names, manual errors, inconsistent date formats, hidden rows, or undocumented business rules.

A professional file-based process should define:

• the file owner and expected location;
• naming and folder conventions;
• column definitions and data types;
• refresh frequency;
• validation rules;
• treatment of missing and duplicate records;
• access permissions and retention requirements.

Power Query can combine files from a folder when their structures are sufficiently consistent. This is useful for monthly sales files, provided that schema changes are detected rather than silently accepted.

3.3 Cloud services and SaaS applications

Cloud services provide storage, computation, databases, and business applications over a network. Examples include cloud data warehouses, cloud storage, customer relationship management systems, enterprise resource planning systems, and online advertising platforms.

Cloud sources can scale more easily than local systems and may provide managed security, high availability, and built-in connectors. However, they introduce considerations such as subscription cost, network dependency, regional data residency, identity integration, API limits, and vendor-specific features.

3.4 APIs

An API is a defined interface through which one software system requests data or actions from another. A BI process may call an API to retrieve exchange rates, marketing campaigns, weather observations, service tickets, or application events.

API-based ingestion requires attention to authentication, pagination, rate limits, error handling, incremental retrieval, and schema change. A response may return only the first page of records, so a connector must follow continuation links or page parameters. Repeatedly downloading an entire history is inefficient and may exceed service limits. A better design stores the last successful extraction point and requests only new or changed records when the API supports it.

APIs may use API keys, OAuth 2.0, service principals, or other authentication methods. Credentials should be stored in secure credential management facilities and never embedded in reports, source code, or publicly shared files.

4. Analytical Storage Architectures

4.1 Data warehouse

A data warehouse is a governed analytical repository designed for reporting and analysis. It usually integrates data from multiple operational systems, applies consistent definitions, and stores historical information in a query-optimised structure.

Traditional warehouse processes often use extract, transform, and load (ETL): data is transformed before it is loaded into the target structure. Modern platforms also use extract, load, and transform (ELT), in which raw or lightly processed data is loaded first and transformations are performed within the analytical platform.

Warehouse strengths include reliable governance, consistent dimensions, controlled history, and predictable reporting performance. Limitations may include implementation time, modelling effort, and difficulty accommodating novel data types without additional design.

4.2 Data lake

A data lake stores large volumes of raw or lightly processed data in files or objects. It can hold structured, semi-structured, and unstructured data. A lake is valuable when an organisation wants to retain detailed source data for exploration, data science, audit, or future use.

The flexibility of a lake creates a governance challenge. Without cataloguing, naming standards, quality controls, access policies, and lifecycle management, a lake can become a “data swamp”: a storage environment in which data exists but cannot be trusted or located efficiently.

4.3 Lakehouse

A lakehouse combines aspects of a data lake and a data warehouse. It retains flexible, scalable storage while adding table formats, transaction support, metadata, and analytical performance features. The goal is to support BI, engineering, and data science on a shared foundation.

A lakehouse does not automatically solve data-quality or governance problems. It still requires ownership, documented schemas, access controls, testing, and carefully designed consumption layers.

4.4 Medallion-style organisation

Many modern platforms organise data into layers such as bronze, silver, and gold. Bronze commonly preserves ingested source data; silver applies cleansing, standardisation, and integration; gold presents curated business-ready datasets or models. The names are not universal standards, but the underlying principle is useful: preserve lineage while progressively increasing usability and trust.

5. Semantic Models and Business Meaning

A semantic model is the analytical layer that translates technical data structures into business concepts. It can contain tables, relationships, measures, calculated columns, hierarchies, descriptions, formatting rules, and security definitions.

In Power BI, a semantic model formerly referred to as a dataset. It is consumed by reports and can be reused by multiple reports. The model should express business logic centrally where possible. For example, a measure named Net Sales is preferable to allowing each report author to construct a different version of net sales.

A measure is evaluated in filter context. A simplified measure might be expressed in DAX as:

Here, Sales[SalesAmount] and Sales[DiscountAmount] are columns, and the subtraction is applied within the current filter context. The expression is not a universal accounting definition: organisations must specify whether returns, tax, freight, or currency conversion are included.

A strong semantic model should have:

• a clear grain for each fact table;
• unambiguous relationships;
• a dedicated date table where time analysis is required;
• consistent naming and descriptions;
• measures for reusable business logic;
• appropriate data types and formatting;
• tested security roles;
• documentation of definitions and exclusions.

The semantic model is therefore more than a collection of tables. It is a governed interpretation of organisational data.

6. Power BI Architecture

Power BI commonly involves several components:

• Power BI Desktop: authoring, transformation, modelling, DAX development, and report design;
• Power BI Service: cloud publishing, sharing, refresh, workspaces, apps, dashboards, and governance;
• On-premises data gateway: secure communication between the Power BI Service and supported local or network sources;
• Power BI semantic model: the reusable analytical model behind reports;
• Reports and dashboards: consumption interfaces, with reports generally containing interactive pages and dashboards presenting pinned tiles;
• Dataflows and other preparation tools: reusable cloud-based data preparation, where appropriate.

A simplified flow is:

Source systems → connector or gateway → Power Query transformations → semantic model → report → Power BI Service users

The actual architecture varies. A report may connect directly to a warehouse semantic model, or Power BI may import data from files and APIs. Security may be enforced at the source, in the semantic model through row-level security, or through a combination of controls.

Architecture should be judged against requirements rather than fashion. Important requirements include data latency, data volume, query concurrency, source workload, availability, governance, licensing, security, and the skills of the support team.

7. Power BI Connectivity Modes

7.1 Import mode

In Import mode, selected data is loaded into the Power BI semantic model. Queries generally run against the in-memory model rather than sending every visual interaction to the source.

Advantages include fast report interaction, rich modelling capability, and reduced dependence on source availability during viewing. Scheduled or triggered refresh is required to update the imported data. Refresh duration, model size, gateway capacity, and source extraction time become important operational concerns.

Import is often suitable when:

• data can be refreshed at an acceptable interval;
• interactive performance is important;
• the model fits available capacity limits;
• source systems should be protected from frequent analytical queries.

7.2 DirectQuery

In DirectQuery mode, Power BI retains metadata and sends queries to the source when users interact with visuals. Data is not imported into the model in the same way as Import mode.

DirectQuery can provide access to near-current source data and avoid importing a very large volume. However, performance depends on source design, network latency, query translation, concurrency, and source workload. Some modelling and transformation capabilities may be more restricted than in Import mode, and poorly designed reports can generate expensive queries.

DirectQuery is appropriate only when its business benefit justifies the technical cost. “Real time” should be defined precisely: a report may show current source values, but it is still affected by transaction commit times, caching, refresh behaviour, and network conditions.

7.3 Live connection

A live connection connects a report to an existing Analysis Services model or Power BI semantic model. The connected model supplies the tables, relationships, and measures. This supports central governance and a single version of business logic.

Live connection is useful where a specialist team manages a shared enterprise model. Report authors have less freedom to create an independent local model, but this restriction can prevent conflicting definitions.

7.4 Composite models

A composite model combines storage modes or sources, commonly Import and DirectQuery. It may allow imported dimensions to work with DirectQuery facts, or combine local modelling with a remote semantic model, subject to supported features and design constraints.

Composite models provide flexibility but increase complexity. Relationships across storage modes, aggregation behaviour, security propagation, query folding, and performance must be tested. A composite design should have a clear purpose rather than being used merely because multiple sources are available.

8. Choosing a Connectivity Approach

A practical decision begins with requirements rather than technology. Ask the following questions:

1. How fresh must the data be: monthly, daily, hourly, or close to transaction time?
2. How large is the relevant data, and how quickly will it grow?
3. Can the source support concurrent analytical queries?
4. Is a governed enterprise semantic model already available?
5. Are advanced transformations or local calculations required?
6. What security, privacy, and residency requirements apply?
7. What happens if the source, gateway, or network becomes unavailable?
8. What are the licensing, capacity, and operating costs?

For example, a monthly management report based on a stable sales warehouse is usually a strong Import candidate. A call-centre dashboard requiring current queue status may require DirectQuery or a specialised near-real-time design. A multinational organisation with common financial definitions may benefit from a centrally managed semantic model and live-connected reports.

9. Worked Application: Retail Sales and Inventory

The following is an illustrative case.

Background

Northstar Retail operates 80 stores and an online shop. Sales transactions are stored in a cloud relational database. Store managers submit weekly stock files, while the e-commerce platform exposes order and campaign data through an API. Executives want a Power BI dashboard showing revenue, gross margin, stock availability, and online campaign performance.

Problem

The company has three conflicting sales totals because finance, marketing, and store operations use different definitions. The weekly stock spreadsheets also contain inconsistent product codes. Directly querying the transaction database during business hours has begun to slow the operational application.

Analysis

The transaction database is structured and authoritative for completed sales, but it is an operational system and should not carry uncontrolled dashboard workloads. The stock files are flexible but require validation. The API contains semi-structured data and requires pagination and incremental extraction. The organisation therefore needs integration, common definitions, and a controlled reporting workload.

Approach and solution

1. Sales transactions are copied incrementally into a warehouse or curated lakehouse layer.
2. Product codes from stock files are matched to a governed product dimension; unmatched codes are reported for correction rather than silently discarded.
3. API responses are stored with extraction timestamps and transformed into campaign and channel tables.
4. A star-schema semantic model is created with Sales, Inventory Snapshot, Campaign Performance, Date, Product, Store, and Customer or Channel dimensions.
5. Measures such as Net Sales, Gross Margin, and Stock Availability are defined centrally. The business documents whether gross margin includes delivery costs, promotions, and returns.
6. The Power BI model uses Import mode with scheduled refreshes. Inventory may be refreshed more frequently than historical sales if operational requirements justify it.
7. Row-level security limits store managers to their own stores, while executives receive an organisation-wide view.
8. Data-quality indicators show refresh status, unmatched products, missing dates, and rejected API records.

Outcome

The company obtains one governed definition of each principal metric, reduces load on the operational database, and gives users visibility into data quality. Managers can distinguish a genuine stock problem from a delayed or rejected stock file. The solution does not attempt to make every visual real time; instead, it aligns refresh frequency with decision requirements.

Learning points

• Architecture should protect operational systems.
• A semantic model resolves business meaning, not merely technical connectivity.
• Flexible sources require stronger validation and documentation.
• Refresh frequency should be selected according to decisions, not marketing language.
• Security and data-quality monitoring are part of BI design, not optional additions.

10. Governance, Security, and Ethical Issues

BI architecture determines who can see data and how confidently it can be used. Governance should define data ownership, classification, retention, lineage, access approval, and change management.

Sensitive information may include personal identifiers, health information, payment details, employment records, or commercially confidential measures. Applying least privilege means users receive only the access necessary for their role. Row-level security can restrict records by region, department, or store, but it must be tested with realistic user identities. Security in a report does not justify exposing sensitive data unnecessarily in the underlying model.

Organisations should consider applicable privacy and data-protection laws, contractual restrictions, retention requirements, and cross-border transfer rules. Aggregation, masking, pseudonymisation, and removal of unnecessary attributes can reduce risk.

Data lineage helps answer where a metric came from, which transformations were applied, and which reports depend on it. Audit logs and refresh histories support accountability. When AI or automated text generation is used to interpret data, human oversight remains necessary because generated explanations may be incomplete, biased, or factually wrong.

11. Challenges and Best Practices

Common challenges include schema drift, duplicate records, slowly changing business definitions, gateway failures, API throttling, source-system downtime, inefficient transformations, and unclear ownership. These issues are architectural because they affect the reliability of the whole BI service.

Recommended practices include:

• define the grain of every fact table;
• prefer star schemas for understandable analytical models;
• perform transformations as close to the source or preparation layer as practical;
• preserve source data and transformation lineage where auditability matters;
• use incremental extraction and refresh for growing datasets;
• test query folding or source pushdown where applicable;
• avoid unnecessary columns and high-cardinality fields in imported models;
• centralise important business definitions in measures or governed models;
• monitor refresh duration, failures, capacity, and user experience;
• document assumptions, exclusions, currencies, time zones, and update schedules;
• test security with representative roles;
• establish owners for data, models, reports, and technical operations.

The best architecture is not necessarily the most elaborate. It is the simplest architecture that satisfies freshness, scale, performance, governance, security, and cost requirements reliably.

Contemporary Developments

Modern BI architectures increasingly combine cloud warehouses, lakehouses, reusable semantic models, real-time event pipelines, and governed self-service analytics. Metadata catalogues and automated lineage tools improve discoverability. Data products and domain-oriented ownership seek to make trusted datasets available to business teams without removing central standards.

AI-assisted development can help generate queries, summarise trends, or identify anomalies, but it does not remove the need for sound source design and semantic definitions. An AI system can produce a fluent answer from a flawed model. Consequently, trustworthy AI in BI depends on trustworthy data, transparent definitions, access control, and human review.

Chapter Summary

BI architecture connects source systems to decisions through ingestion, transformation, modelling, and presentation. Structured data is tabular and schema-based; semi-structured data uses flexible markers such as JSON keys; unstructured data includes documents, images, audio, and video. Relational databases, files, cloud services, and APIs each provide useful but different source characteristics.

Data warehouses offer governed analytical storage, data lakes provide flexible storage for many data types, and lakehouses combine lake flexibility with stronger table and analytical capabilities. A semantic model gives data business meaning through relationships, measures, hierarchies, security, and documented definitions.

Power BI Import mode generally provides fast interaction through an in-memory model, whereas DirectQuery sends queries to the source and can support more current data at the cost of source dependence and performance complexity. Live connection reuses an existing governed model, and composite models combine storage approaches but require careful testing. Architecture should be selected according to freshness, scale, performance, governance, security, cost, and organisational capability.

Key Terms

Structured data: Data organised according to a predefined schema, commonly in tables.

Semi-structured data: Data with organisational markers but without a rigid relational structure.

Unstructured data: Data without a fixed tabular schema, such as documents or images.

Database: An organised system for storing, managing, and retrieving data.

API: A defined software interface for requesting data or services.

Data warehouse: A governed repository designed primarily for analytical workloads.

Data lake: Flexible storage for large volumes of raw or processed data in many formats.

Lakehouse: An analytical architecture combining lake flexibility with warehouse-like management features.

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

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

Fact table: A table containing measurable business events at a defined grain.

Dimension table: A table describing the context of facts, such as product, date, or customer.

Semantic model: A governed analytical representation of data and business meaning.

Measure: A calculation evaluated in the current filter context, commonly written in DAX.

Import mode: A Power BI storage mode that loads data into the semantic model.

DirectQuery: A mode in which queries are sent to the source during analysis.

Live connection: A connection to an existing Analysis Services or Power BI semantic model.

Composite model: A model combining storage modes or multiple supported data sources.

Row-level security: Controls that restrict the rows visible to particular users or roles.

Data lineage: Documentation of data origins, transformations, dependencies, and destinations.

Review Questions

Short Answer Questions

1. What is the principal difference between structured and unstructured data?
2. Why is the grain of a fact table important?
3. State one strength and one risk of using spreadsheets as BI sources.
4. What is the purpose of a semantic model?
5. How does DirectQuery differ from Import mode?

Long Answer Questions

1. Compare a data warehouse, data lake, and lakehouse in terms of purpose, data structure, users, and governance.
2. Explain how APIs introduce technical and operational considerations into a BI architecture.
3. Describe the roles of Power BI Desktop, Power BI Service, a gateway, and a semantic model.
4. Discuss the advantages and limitations of Import, DirectQuery, live connection, and composite approaches.
5. Explain how data governance and security should influence the design of a Power BI solution.

Analytical / Application Questions

1. A company needs a dashboard for daily executive sales analysis, but its operational database slows whenever many users open reports. Recommend an architecture and justify your choice.
2. An organisation receives monthly Excel inventory files with changing column names and duplicate product codes. Design a data-quality and modelling approach for Power BI.
3. A call centre requires current queue status, while executives need five years of historical performance. Propose a connectivity and storage strategy that addresses both requirements.

Multiple Choice Questions with Answers

Question 1

Which type of data is most commonly represented by rows and columns with a defined schema?

A. Structured data

B. Unstructured data

C. Audio data

D. Free-form narrative data

Correct Answer: A. Structured data

Question 2

What is the principal purpose of a semantic model in Power BI?

A. To replace all source systems

B. To provide governed business meaning through relationships and calculations

C. To store only image files

D. To prevent all data refreshes

Correct Answer: B. To provide governed business meaning through relationships and calculations

Question 3

Which connectivity mode generally loads data into the Power BI model for interactive analysis?

A. DirectQuery

B. Live connection

C. Import

D. API polling only

Correct Answer: C. Import

Question 4

What is a major consideration when using DirectQuery?

A. The source cannot contain tables

B. Every visual interaction may depend on source and network performance

C. Data is permanently disconnected from the source

D. It eliminates the need for security

Correct Answer: B. Every visual interaction may depend on source and network performance

Question 5

Which repository is primarily designed for governed analytical reporting?

A. Data warehouse

B. Email inbox

C. Image archive

D. Temporary browser cache

Correct Answer: A. Data warehouse

Question 6

What does the grain of a fact table describe?

A. The colour of a report theme

B. The exact level of detail represented by each row

C. The number of dashboard pages

D. The database password policy

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

Question 7

Which issue is especially relevant when retrieving data from an API?

A. Pagination and rate limits

B. Screen brightness

C. Printer toner

D. Font licensing in a report

Correct Answer: A. Pagination and rate limits

Question 8

What is a common characteristic of a data lake?

A. It stores only highly curated relational tables

B. It can store raw data in multiple formats

C. It cannot store historical data

D. It is exclusively a Power BI visual

Correct Answer: B. It can store raw data in multiple formats

Question 9

Which approach best supports a centrally governed model reused by many reports?

A. Live connection to an existing semantic model

B. Separate unvalidated spreadsheets for every user

C. Manual screenshots of source systems

D. Deleting all shared measures

Correct Answer: A. Live connection to an existing semantic model

Question 10

Why might a composite model be used?

A. To combine supported storage modes or sources for a specific analytical requirement

B. To remove the need for data modelling

C. To guarantee unlimited performance

D. To convert every source into unstructured data

Correct Answer: A. To combine supported storage modes or sources for a specific analytical requirement