Data Sources, Warehousing and BI Architecture

Introduction

Business intelligence (BI) depends on more than dashboards. A trustworthy report requires suitable data, an appropriate storage design, controlled transformation processes, and an architecture that balances performance, cost, security, and flexibility. Power BI is usually the presentation and analytical layer of a wider information environment. It can connect to operational databases, files, cloud services, enterprise warehouses, semantic models, and real-time sources. The quality of the resulting insight therefore depends substantially on decisions made before a visual is created.

This chapter distinguishes operational and analytical systems, explains ETL and ELT, examines warehouses, marts, lakes and lakehouses, and compares common dimensional and enterprise architectures. It concludes by explaining Power BI storage and connectivity modes: Import, DirectQuery, live connection, and composite models.

Learning Objectives

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

• distinguish operational systems from analytical systems;
• explain ETL, ELT, data integration and data quality controls;
• compare data warehouses, data marts, data lakes and lakehouses;
• describe star and snowflake schemas and select between them;
• interpret the layers of an enterprise BI architecture;
• compare Power BI Import, DirectQuery, live connection and composite modes;
• recommend an architecture for a realistic organisational scenario; and
• identify governance, security, performance and ethical risks in BI design.

1. Operational and Analytical Systems

Operational systems

Operational systems support the routine activities of an organisation. Examples include point-of-sale applications, customer relationship management (CRM), enterprise resource planning (ERP), payroll, inventory and banking transaction systems. They are often called online transaction processing (OLTP) systems.

OLTP systems are designed for many concurrent users performing short, controlled transactions. A sales transaction may insert an order, update inventory, record payment, and create an invoice. The database normally prioritises consistency and rapid transaction completion. Tables are commonly normalised so that a customer, product, or supplier is stored once and updated consistently.

Typical characteristics include:

• current, detailed records;
• frequent inserts and updates;
• predefined business transactions;
• strict integrity and concurrency controls;
• limited historical analysis; and
• workloads focused on individual records or small transaction sets.

Operational data is not automatically suitable for reporting. Column names may be technical, codes may require interpretation, historical values may be overwritten, and joins across several systems may be slow or ambiguous. Directly querying a busy operational database can also affect the performance of the organisation's core processes.

Analytical systems

Analytical systems support investigation, comparison, planning and decision-making. They are commonly called online analytical processing (OLAP) systems. Instead of asking, “What is the status of this order?”, an analytical user may ask, “How did regional profit change by product category over the last three years?”

Analytical systems typically provide:

• integrated data from several sources;
• historical and time-variant records;
• structures optimised for aggregation and filtering;
• consistent definitions of measures and dimensions; and
• controlled access for reporting and analysis.

The distinction is one of design purpose rather than a strict technology boundary. A modern cloud platform may support both operational and analytical workloads, but workload isolation, modelling and governance remain important.

2. Data Integration: ETL and ELT

Data integration combines data from different systems so that it can be used consistently. The two widely used patterns are ETL and ELT.

ETL

ETL means extract, transform and load. Data is extracted from source systems, transformed in an integration engine or staging environment, and then loaded into a warehouse or mart. Transformations may include changing data types, standardising currencies, removing duplicates, validating codes, joining sources, and calculating derived attributes.

ETL is useful when:

• the target warehouse should receive only validated data;
• transformation logic is complex or centralised;
• the target platform has limited processing capacity; or
• sensitive fields must be masked before loading.

ELT

ELT means extract, load and transform. Data is first loaded into a scalable target platform, such as a cloud warehouse or lakehouse, and transformed there. ELT uses the storage and processing power of the target environment and is well suited to large volumes and changing analytical requirements.

ELT does not mean that data is loaded without controls. Basic validation, access control, metadata capture and protection of sensitive data may occur before or during loading. A common pattern is to retain a raw or landing layer and create curated layers through repeatable transformations.

Integration stages

A practical pipeline often contains the following stages:

1. Source and extraction: identify systems, owners, refresh requirements and extraction methods.
2. Landing or staging: receive source data, preserve source identifiers and record ingestion time.
3. Validation: check schema, completeness, data types, keys, ranges and duplicates.
4. Transformation: standardise, enrich, conform and calculate fields.
5. Storage: load warehouse tables, lake files, lakehouse tables or semantic models.
6. Publication: expose governed datasets and reports.
7. Monitoring: record successes, failures, row counts, latency and data-quality results.

A useful pipeline is repeatable and observable. It should be possible to identify when data arrived, which source version was used, what transformations occurred, and whether an error was corrected.

Data quality

Data quality is multidimensional. Important dimensions include accuracy, completeness, consistency, timeliness, validity, uniqueness and fitness for purpose. A report can be technically correct yet decisionally misleading if, for example, sales are complete but returns are omitted.

A simple completeness rate can be expressed as:

The numerator counts populated values and the denominator counts values that should exist. The measure applies only when the required fields and evaluation population are clearly defined. For example, if 9,700 of 10,000 required customer email values are present, completeness is 97%. Completeness does not prove that the email addresses are accurate.

3. Analytical Storage Platforms

Data warehouse

A data warehouse is a managed repository designed for integrated, historical analysis. It normally contains structured, curated data with defined business rules. A warehouse may receive data from ERP, CRM, finance, web and external sources. It often separates ingestion, integration, presentation and semantic layers.

Important warehouse capabilities include historical preservation, subject-area integration, metadata, controlled refresh, auditing, security and query optimisation. A warehouse may use relational tables, columnar storage, partitioning and materialised summaries. It is not merely a large database; its value lies in the controlled analytical model and consistent interpretation of data.

Data mart

A data mart is an analytical store focused on a department, process or subject, such as sales, finance or human resources. A dependent mart is derived from an enterprise warehouse, whereas an independent mart is built directly from source systems.

Marts can deliver focused performance and a simpler user experience. However, many independent marts can create “silos”: different departments may define revenue, customer or fiscal year differently. Conformed dimensions and shared semantic definitions reduce this risk.

Data lake

A data lake stores large quantities of data in its original or near-original form. It can contain structured tables, semi-structured JSON, logs, documents, images and sensor data. Its flexibility supports data science, exploration and future use cases that were not known when data was collected.

The flexibility of a lake creates risks. Without cataloguing, ownership, quality rules and lifecycle management, it can become a “data swamp”. Raw data should therefore be accompanied by metadata, access policies, lineage, retention rules and clear zones such as raw, validated and curated.

Lakehouse

A lakehouse combines the low-cost, flexible storage associated with a lake with warehouse-like management and analytical reliability. It commonly uses open or broadly accessible file formats, table metadata, schema enforcement, versioning and transactional behaviour. In practice, products differ, so “lakehouse” should be evaluated by capabilities rather than marketing terminology.

A lakehouse is attractive when an organisation wants one platform for BI, engineering, machine learning and large-scale data processing. It still requires modelling, governance and semantic design. A lakehouse does not automatically make definitions consistent or reports trustworthy.

4. Dimensional Modelling

Dimensional modelling organises data for understandable and efficient analysis. It separates measurable business events from descriptive context.

Facts and dimensions

A fact table records events or measurable states, such as sales lines, shipments or account balances. A dimension table describes the context of facts, such as date, customer, product, store and employee.

The grain must be declared before a fact table is designed. Grain means the exact meaning of one row. For example, “one row per invoiced product line” is a precise grain; “sales information” is not. Measures, keys and relationships must be compatible with that grain.

Additive measures can be summed across all relevant dimensions, such as quantity sold. Semi-additive measures can be summed across some dimensions but not time, such as an end-of-day account balance. Non-additive measures, such as ratios and percentages, should generally be recalculated from their components rather than summed.

A basic revenue measure is:

Here, i identifies a transaction line, quantity is the number of units, and unit price is the monetary price per unit. The formula assumes that each line has a valid quantity and price, and that currency, discounts, taxes and returns have been handled according to the stated business definition.

Star schema

A star schema has a central fact table connected directly to denormalised dimensions. It is easy for analysts to understand, usually requires fewer joins, and is highly suitable for Power BI semantic models. Dimension tables may contain repeated descriptive values, but this often improves usability and query simplicity.

Snowflake schema

A snowflake schema normalises one or more dimensions into related tables. For example, Product may connect to Subcategory, which connects to Category. Snowflaking can reduce duplication and support centrally maintained hierarchies, but it introduces more relationships and can make self-service analysis less intuitive.

Neither design is universally superior. A star schema is often preferred for a presentation layer; snowflaking may be justified by very large dimensions, complex hierarchies, shared master data or maintenance requirements. The selected design should preserve clear grain, reliable relationships and usable performance.

5. Enterprise BI Architecture

An enterprise BI architecture is a set of layers and responsibilities rather than a single product. A common logical architecture contains:

1. Source layer: operational databases, SaaS applications, files, APIs, IoT devices and external providers.
2. Ingestion layer: batch, streaming or change-data-capture processes.
3. Storage and integration layer: staging, lake, warehouse or lakehouse structures.
4. Transformation layer: cleansing, conformance, historisation and business rules.
5. Semantic layer: reusable dimensions, measures, relationships, hierarchies, calculation logic and security.
6. Consumption layer: Power BI reports, dashboards, Excel, embedded applications and analytic tools.
7. Governance layer: identity, catalogues, lineage, quality, privacy, auditing, deployment and lifecycle management.

A centralised enterprise architecture promotes common definitions and governance. A decentralised or self-service architecture gives departments agility and local control. A governed self-service model attempts to combine both: central teams provide certified data products and standards, while business users create local reports within controlled boundaries.

Architecture should be evaluated against data volume, latency, concurrency, source stability, regulatory obligations, skills, cost and expected analytical complexity. “Cloud” or “real time” is not itself an architecture decision; the design must explain how data moves, where definitions are maintained, and how failures are detected.

6. Power BI Connectivity and Storage Modes

Power BI can work with data in several ways. The choice affects refresh, performance, modelling capability, source load, security and user experience.

Import mode

In Import mode, data is copied into the Power BI model and compressed in memory and storage. Reports generally respond quickly because queries run against the imported model. Scheduled or triggered refreshes are required to obtain new source data.

Import is often appropriate when:

• the dataset fits within capacity and governance limits;
• fast interactive analysis is important;
• source systems should not receive every report query; and
• refresh latency is acceptable.

Import does not necessarily mean data is static. It can be refreshed frequently, but it remains a snapshot between refresh operations. Historical refresh strategy, incremental refresh and gateway or cloud-source configuration may be important.

DirectQuery

In DirectQuery mode, Power BI retains metadata and sends queries to the source when users interact with the report. This can provide fresher data and avoid importing a large dataset, but performance depends on source design, network latency, concurrency and query folding or translation.

DirectQuery is suitable when data volume or freshness makes import impractical, or when source-level security and central control are required. It requires disciplined source modelling, indexing or equivalent optimisation, limited visual complexity and careful testing under realistic concurrency. A fast result in development does not guarantee acceptable performance for hundreds of users.

Live connection

A live connection connects a report to an existing Analysis Services or Power BI semantic model. The report consumes centrally defined measures, relationships and security rather than importing its own copy of the model. This supports a single version of important business logic and reduces duplicated datasets.

The trade-off is reduced local modelling freedom compared with a fully independent model. A live-connected report is primarily a presentation layer over the governed semantic model.

Composite models

A composite model combines storage modes or sources within one model. For example, historical sales may be imported while current inventory is queried through DirectQuery. Composite designs can also support local modelling over a remote semantic model, subject to platform capabilities and restrictions.

Composite models are powerful but can introduce ambiguous relationships, cross-source query limitations, inconsistent latency and complex security behaviour. Designers should minimise unnecessary cross-source relationships, document latency expectations, and test interactions involving aggregations and detail tables.

Mode selection is a business decision. Ask how current data must be, how many users will query it, whether the source can handle the load, whether regulations restrict copies, and whether a certified semantic model already exists. Avoid choosing DirectQuery merely because the source is large; a properly designed warehouse and incremental Import model may provide a better user experience.

7. Applied Case: Northstar Retail Group

Background

Northstar Retail Group operates eighty stores and an online channel. Its sales transactions are held in a point-of-sale database, customer information in a CRM system, product information in an ERP system, and advertising data in cloud platforms. Regional managers report different revenue figures because one spreadsheet includes returns while another excludes them.

Problem

The organisation needs daily sales and margin reporting, near-current inventory visibility, and governed definitions of revenue, customer and product. Directly connecting Power BI to the point-of-sale database has slowed checkout during busy periods. The finance department also requires an auditable history of corrections.

Analysis

The point-of-sale and ERP databases are operational systems, not ideal enterprise reporting sources. The inconsistent spreadsheets represent independent marts without conformed dimensions. Inventory has a stronger freshness requirement than monthly financial analysis, while advertising data may arrive with different keys and time zones.

Approach and solution

Northstar establishes a cloud warehouse or lakehouse integration platform. Source extracts are landed with ingestion timestamps and retained according to policy. Validated pipelines standardise product identifiers, store codes, currencies and time zones. A dimensional presentation model is created with:

• a Sales fact at the grain of one order line;
• a daily Inventory snapshot fact at the grain of one product-store-day;
• Date, Product, Store, Customer and Promotion dimensions; and
• a governed definition of net revenue that specifies treatment of discounts, taxes and returns.

The main sales model uses Import mode with incremental refresh because managers need rapid analysis and daily data is sufficient. A separate inventory table uses DirectQuery or a frequent refresh strategy because stock availability is operationally important. Certified measures are published through a governed semantic model, and regional access is implemented through role-based security. Data-quality checks reconcile source totals with loaded totals and alert data owners when thresholds are exceeded.

Outcome

Managers receive consistent sales and margin figures, while checkout performance is protected from reporting queries. Inventory users see a clearly documented freshness indicator. Finance can trace corrections through pipeline logs and historical records. The design is more complex than a spreadsheet, but it makes definitions, ownership and limitations visible.

Learning points

The case illustrates that architecture begins with business requirements, not a Power BI visual. Grain and definitions must be agreed before measures are published. Different subjects may require different latency and storage modes. Finally, a certified semantic model reduces repeated logic and helps self-service users work safely.

8. Governance, Security and Ethical Considerations

BI platforms can expose personal, financial, employment and commercially sensitive information. Governance must therefore be designed into the architecture. Important controls include least-privilege access, encryption, identity management, row-level security, column masking where appropriate, audit logs, retention schedules and approved data-sharing procedures.

Organisations should identify data owners and stewards, document lawful or legitimate purposes for personal data, and respect applicable privacy and records-management requirements. A dashboard should not reveal an individual's sensitive status merely because a filter allows a small group to be isolated. Aggregation thresholds and suppression rules may be necessary.

Lineage and cataloguing help users understand where a value originated and which transformations affected it. Certified datasets should have owners, descriptions, refresh schedules, quality indicators and known limitations. Deployment pipelines and separate development, test and production environments reduce accidental changes.

AI-assisted data preparation and natural-language analysis can increase productivity, but they do not remove accountability. Users must validate generated formulas, protect confidential prompts and data, assess bias, and avoid presenting probabilistic output as fact. Automated anomaly detection should support human review rather than silently changing financial or operational records.

9. Best Practices and Common Failure Modes

Effective BI architecture generally follows these practices:

• define the business process and fact-table grain first;
• separate operational workloads from analytical workloads where appropriate;
• use conformed dimensions and shared definitions;
• retain source and ingestion metadata;
• design for incremental, recoverable and observable pipelines;
• prefer a clear star schema for the user-facing analytical model;
• choose storage mode according to freshness, performance, scale and governance;
• test with realistic data volumes and concurrent users;
• publish certified semantic models and document measures; and
• monitor refresh failures, latency, quality and cost.

Common failures include building reports directly on unstable spreadsheets, using many unrelated tables, mixing grains in one fact table, duplicating measures across departments, selecting DirectQuery without source optimisation, and assuming a lake automatically provides governance. Another failure is treating a dashboard as the entire BI solution while ignoring ownership, definitions and change management.

Chapter Summary

Operational systems run day-to-day transactions, whereas analytical systems integrate and structure historical data for investigation and decision-making. ETL transforms data before loading; ELT loads data into a scalable platform and transforms it there. Warehouses provide governed structured analysis, marts focus on subject areas, lakes provide flexible storage, and lakehouses combine flexible storage with stronger analytical management.

Dimensional modelling depends on a clearly declared grain, appropriate facts and dimensions, and carefully defined measures. Star schemas usually maximise analytical usability; snowflake schemas may be justified by normalisation or complex master-data requirements. Enterprise BI architecture spans sources, ingestion, storage, transformation, semantic modelling, consumption and governance.

Power BI Import provides fast local analysis but requires refresh. DirectQuery queries the source and can improve freshness but depends on source and network performance. Live connection promotes a central semantic model, while composite models combine approaches at the cost of additional complexity. The appropriate design is determined by business requirements, not by technology preference alone.

Key Terms

Operational system: A system that records and supports routine business transactions.

Analytical system: A system designed for analysis, comparison, planning and decision-making.

OLTP: Online transaction processing, focused on concurrent operational transactions.

OLAP: Online analytical processing, focused on multidimensional analysis.

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

ELT: Extract, load and transform; transformation occurs in the target platform.

Data warehouse: A governed, integrated repository for historical analytical data.

Data mart: An analytical store focused on a department or subject area.

Data lake: Flexible storage for structured, semi-structured and unstructured data.

Lakehouse: A platform combining lake flexibility with warehouse-like analytical management.

Fact table: A table recording measurable business events or states.

Dimension table: A table providing descriptive context for facts.

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

Star schema: A central fact table connected directly to dimensions.

Snowflake schema: A dimensional design in which dimensions are further normalised.

Semantic model: Governed relationships, measures, business logic and security exposed for analysis.

Import mode: A Power BI mode that stores a copy of data in the model.

DirectQuery: A mode that sends analytical queries to the source at interaction time.

Live connection: A connection to an existing governed semantic model.

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

Review Questions

Short Answer Questions

1. What is the principal difference between an operational system and an analytical system?
2. Define ETL and ELT.
3. Why must the grain of a fact table be declared before measures are designed?
4. State one advantage and one limitation of a data lake.
5. What is the main difference between Import and DirectQuery in Power BI?

Long Answer Questions

1. Explain the role of a data warehouse, data mart, data lake and lakehouse in an enterprise BI environment.
2. Compare star and snowflake schemas, including their effects on usability, maintenance and query complexity.
3. Describe the layers of a governed enterprise BI architecture.
4. Explain the circumstances in which Import, DirectQuery and live connection may each be appropriate.
5. Discuss why data quality, lineage, security and ownership are essential parts of BI architecture.

Analytical / Application Questions

1. A retailer requires fast analysis of five years of sales, but current inventory must be visible within five minutes. Recommend a Power BI architecture and justify the storage modes.
2. Two departments report different profit figures because one includes returned goods and the other does not. Diagnose the architectural and governance problem and propose corrective actions.
3. An organisation plans to connect 400 users directly to an operational ERP database through DirectQuery. Evaluate the risks and design a more suitable alternative.

Multiple Choice Questions with Answers

Question 1

Which system is primarily designed to support frequent inserts and updates for day-to-day business transactions?

A. OLTP system

B. Data lake

C. OLAP system

D. Semantic model

Correct Answer: A. OLTP system

Question 2

In an ETL process, where do the main transformations occur?

A. Only after users open a report

B. Before data is loaded into the target

C. Only inside the operational source

D. After the target has been archived

Correct Answer: B. Before data is loaded into the target

Question 3

What does the grain of a fact table describe?

A. The database server capacity

B. The number of dimensions in a model

C. The exact meaning of one row

D. The refresh frequency of a report

Correct Answer: C. The exact meaning of one row

Question 4

Which platform is most associated with flexible storage of structured, semi-structured and unstructured data?

A. Data mart

B. Data lake

C. OLTP database

D. Star schema

Correct Answer: B. Data lake

Question 5

Which schema normally places a fact table at the centre with dimensions directly connected to it?

A. Snowflake schema

B. Network schema

C. Star schema

D. Document schema

Correct Answer: C. Star schema

Question 6

What is a principal advantage of Power BI Import mode?

A. Queries always execute on the operational source

B. It generally provides fast interactive analysis from the model

C. It eliminates the need for all refresh operations

D. It requires no storage of data in the model

Correct Answer: B. It generally provides fast interactive analysis from the model

Question 7

What is a major consideration when using DirectQuery?

A. The source and network must handle report queries

B. The model cannot display any measures

C. Data can never be refreshed

D. It always produces faster results than Import

Correct Answer: A. The source and network must handle report queries

Question 8

What does a live connection primarily allow a report to consume?

A. An existing governed semantic model

B. Only unstructured files

C. A new copy of every operational table

D. A disconnected spreadsheet

Correct Answer: A. An existing governed semantic model

Question 9

Which measure is generally additive across products and stores, assuming consistent transaction definitions?

A. Quantity sold

B. Percentage margin

C. Average unit price

D. End-of-day account balance

Correct Answer: A. Quantity sold

Question 10

Why can independent data marts create organisational problems?

A. They prevent all data storage

B. They may use inconsistent definitions and dimensions

C. They always require live connections

D. They cannot contain historical data

Correct Answer: B. They may use inconsistent definitions and dimensions