Data Warehousing, OLAP and Analytical Platforms

Introduction and Context

Operational database systems are designed to record current transactions efficiently: orders are placed, payments are received, products are shipped, and accounts are updated. Business analysis has different requirements. Analysts need to compare performance across months, regions, products, customer segments, and channels. They need historical, integrated, and consistently defined data. Running complex analytical queries directly on operational systems can also interfere with transaction processing.

A data warehouse is a subject-oriented, integrated, time-variant, and relatively stable collection of data designed to support analysis and decision making. Modern analytical platforms extend this idea through cloud warehouses, data lakes, lakehouses, columnar storage, streaming pipelines, and self-service business intelligence. For AI and ML practitioners, these platforms provide the historical and engineered data required for forecasting, recommendation, anomaly detection, and model monitoring.

This chapter explains how analytical data is modeled, moved, stored, queried, governed, and used. It emphasizes design choices rather than any one vendor product.

Learning Objectives

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

1. Distinguish operational processing from analytical processing.

2. Define grain, facts, dimensions, measures, and dimensional hierarchies.

3. Design star and snowflake schemas for business analysis.

4. Explain ETL, ELT, data marts, data lakes, lakehouses, and streaming architectures.

5. Describe OLAP operations and the role of columnar storage.

6. Evaluate data quality, governance, performance, security, and cost considerations.

7. Apply dimensional modeling to a realistic business case.

From Transaction Processing to Analytical Processing

Online transaction processing (OLTP) systems support many concurrent users performing short, atomic operations. They commonly use normalized relational schemas to reduce redundancy and protect integrity. Typical workloads include inserting an order, updating inventory, or recording a payment.

Online analytical processing (OLAP) supports complex queries over large historical datasets. Queries may aggregate millions or billions of rows and involve joins, filtering, grouping, and comparisons over time. Examples include:

• What was monthly revenue by region and product category?
• Which customer segments have declining retention?
• How did actual sales compare with budget?
• Which delivery routes produce the highest cost per shipment?

The distinction is not absolute. Modern systems increasingly support hybrid transactional and analytical processing, but workload isolation remains important. A separate analytical platform can improve performance, preserve historical records, and establish common definitions of business measures.

A warehouse is not merely a large database. Its value depends on integration and meaning. If one department defines “active customer” as a customer with a purchase in 30 days and another uses 90 days, dashboards may be technically correct but organizationally inconsistent.

Dimensional Modeling

Dimensional modeling organizes data around business processes. It normally contains fact tables, dimension tables, measures, keys, and hierarchies.

Grain: the Most Important Design Decision

The grain states exactly what one row in a fact table represents. Examples are:

• One row per sales order line.
• One row per product per store per day.
• One row per account per month.
• One row per shipment event.

The grain must be declared before selecting measures. A fact table at order-line grain may contain quantity and line amount, whereas a table at order grain may contain total order amount. Mixing these grains can cause double counting. For example, joining an order-level fact to an order-line fact without care may repeat order totals for every line.

Fact Tables

A fact table records measurable events or periodic states. It usually contains:

• Foreign keys to dimensions.
• Numeric measures such as quantity, revenue, discount, or cost.
• Degenerate dimensions such as an order number when no separate dimension is required.
• Optional audit and lineage attributes.

Measures may be additive, semi-additive, or non-additive. Revenue and quantity are generally additive across products, locations, and time. Account balance can be added across accounts but normally not across time; it is semi-additive. Ratios such as margin percentage should usually be recalculated from their component values rather than summed.

A useful derived measure is:

Revenue = Quantity × Unit Price

This relationship applies when quantity and price refer to the same grain and pricing convention. If discounts, taxes, returns, or currencies are involved, the model must state whether revenue is gross, net, before tax, after tax, or converted into a reporting currency.

Dimension Tables

Dimensions describe the context of facts. Common dimensions include Date, Product, Customer, Store, Employee, Supplier, and Promotion. A dimension may contain descriptive attributes used for filtering, grouping, and labeling.

A Date dimension can contain day, week, month, quarter, fiscal period, holiday indicator, and financial year. A Product dimension may contain SKU, product name, brand, category, and department. Dimensions commonly use surrogate keys rather than operational identifiers. A surrogate key is an warehouse-generated identifier that allows multiple historical versions of an entity to coexist.

Star Schema

A star schema has a central fact table directly connected to relatively denormalized dimensions. It is simple for analysts and generally requires fewer joins.

For a retail sales process:

• FactSales(date_key, product_key, store_key, customer_key, quantity, sales_amount, cost_amount)
• DimDate(date_key, calendar_date, month, quarter, year)
• DimProduct(product_key, sku, product_name, brand, category)
• DimStore(store_key, store_code, city, region)
• DimCustomer(customer_key, customer_id, segment, loyalty_tier)

The schema supports queries such as sales by year and category or profit by region and customer segment.

Snowflake Schema

A snowflake schema normalizes some dimensions into related tables. For example, DimProduct may link to DimBrand, DimCategory, and DimDepartment. Snowflaking can reduce repeated attribute storage and improve maintenance when hierarchies are shared, but it adds joins and may be less intuitive for users. The choice depends on platform performance, governance needs, data volume, and user skill.

Slowly Changing Dimensions

Dimension attributes change over time. A customer may move to a new segment, or a product may change category. Slowly changing dimension (SCD) techniques preserve or overwrite history.

• Type 1 overwrites the old value. It is appropriate when historical correction is not required.
• Type 2 creates a new dimension row for each meaningful version, commonly with effective_from, effective_to, and current_flag attributes. Facts are linked to the version valid at the event time.
• Type 3 stores limited previous values in additional columns and is useful for narrowly defined comparisons.

Type 2 preserves historical context but increases complexity and dimension size. The business must define which changes are historically significant.

OLAP and Analytical Operations

OLAP enables multidimensional analysis. A conceptual cube may have dimensions such as time, product, and geography, with measures such as sales and units. Physical systems do not always store literal cubes; they may use relational tables, columnar files, materialized views, or proprietary aggregates.

Important operations include:

• Roll-up: aggregate to a higher level, such as city to region or month to year.
• Drill-down: move to more detail, such as year to quarter to month.
• Slice: select one dimension value, such as sales for 2025.
• Dice: select ranges or sets across several dimensions.
• Pivot: change the orientation of dimensions in a report.
• Drill-through: move from an aggregate result to underlying records.

A cube must be interpreted carefully. Summing daily account balances across a month is usually invalid, whereas selecting the ending balance or calculating an average may be appropriate. Analytical semantics are as important as query syntax.

ETL, ELT, and Data Integration

ETL means extract, transform, and load. Data is extracted from sources, transformed in a processing layer, and loaded into the target warehouse. ELT loads relatively raw data first and performs transformations inside the analytical platform. Cloud-scale systems often favor ELT because storage is inexpensive and compute can be scaled independently.

A robust pipeline commonly includes:

1. Source discovery and metadata capture.

2. Incremental extraction using timestamps, change data capture, logs, or source-specific mechanisms.

3. Landing data in a raw or staging area.

4. Validation of schema, completeness, duplicates, formats, and referential relationships.

5. Standardization of dates, currencies, codes, and identifiers.

6. Transformation into dimensions and facts.

7. Loading with appropriate transaction and restart behavior.

8. Reconciliation against source totals.

9. Monitoring, alerting, lineage recording, and documentation.

Idempotence is important. An idempotent pipeline can be safely rerun without creating duplicate business results. This may require stable business keys, merge logic, load identifiers, and deduplication rules.

Batch pipelines process data periodically. Near-real-time and streaming pipelines process events continuously or in short intervals. Streaming introduces concerns about out-of-order events, late arrivals, duplicate delivery, replay, state management, and exactly-once or effectively-once outcomes. “Exactly once” is not a universal property; it depends on the complete source, processing, and storage design.

Data Marts and Analytical Architecture

A data mart is a subject- or department-focused analytical store, such as a finance, marketing, or supply-chain mart. A dependent mart is populated from an enterprise warehouse, promoting consistent definitions. An independent mart is built directly from departmental sources and can be faster to create but may produce conflicting metrics and duplicated logic.

A common layered architecture includes:

• Source systems: applications, files, sensors, APIs, and external feeds.
• Landing or raw layer: immutable or minimally altered source data.
• Integration layer: validated, standardized, and conformed data.
• Warehouse or lakehouse layer: governed analytical tables.
• Semantic layer: business metrics, dimensions, access policies, and reusable definitions.
• BI and data science tools: dashboards, notebooks, reports, and models.

The semantic layer is particularly important for self-service analytics. It can define measures such as net revenue, active customer, and contribution margin once, reducing inconsistent calculations across reports.

Columnar Storage and Query Performance

Traditional row storage is effective when a transaction accesses many columns of a small number of records. Analytical queries often scan a few columns across many records. Columnar storage stores values from the same column together, enabling projection of only required columns and often achieving strong compression because similar values are adjacent.

Performance also depends on partitioning, clustering, indexing where supported, file sizes, statistics, caching, materialized views, and query design. Partitioning by date may reduce the amount of data scanned when queries filter by time, but excessive partitions can increase metadata overhead. Clustering or sorting can improve locality for frequently filtered attributes.

Compression reduces storage and input/output, but it does not automatically solve poor joins or incorrect grain. Analysts should inspect query plans, avoid unnecessary columns, filter early when appropriate, and aggregate at suitable stages.

Data Lakes and Lakehouses

A data lake stores large volumes of structured, semi-structured, and unstructured data, often in object storage. It can hold raw files, logs, images, documents, and event streams. Its flexibility supports exploratory work and machine learning, but unmanaged lakes may become “data swamps” characterized by unclear ownership, poor metadata, duplicate data, and uncertain quality.

A lakehouse attempts to combine the flexibility and low-cost storage of a lake with warehouse capabilities such as transactions, schema management, governance, and reliable analytical tables. Open table formats and transaction layers can support versioning, updates, time travel, and concurrent readers and writers. These capabilities improve reliability but do not remove the need for data contracts, quality rules, and stewardship.

The choice among a warehouse, lake, and lakehouse should consider workload, governance, latency, data variety, team expertise, cost, and interoperability. These are architectural patterns rather than guarantees of quality.

Streaming Data and Modern Decision Systems

Streaming architectures treat business events as continuously produced records. Examples include clicks, payment events, machine readings, location updates, and application logs. A typical architecture uses producers, a durable event broker, stream-processing services, and one or more analytical sinks.

Streaming can support fraud detection, predictive maintenance, dynamic pricing, operational dashboards, and personalization. It may coexist with batch processing in a unified design. Event-time processing is generally preferable when the time an event occurred differs from the time it was received. Watermarks can help a system decide how long to wait for late events, but the choice involves a trade-off between latency and completeness.

For reliable streaming, teams should define event schemas, compatibility rules, unique event identifiers, retention policies, replay procedures, dead-letter handling, and monitoring for lag and missing events. Streaming data is not automatically more valuable than batch data; the required decision latency should determine the architecture.

Worked Design Example: Retail Sales Warehouse

Consider an illustrative retailer operating stores and an online channel. Management wants to analyze sales, promotions, inventory, and customer behavior.

Background and Problem

The retailer’s point-of-sale system, e-commerce platform, inventory application, and loyalty system use different product codes and customer identifiers. Reports disagree about revenue because one system includes tax and another excludes it. Managers need daily dashboards, while the data science team needs several years of historical transactions.

Analysis

The primary business process selected is sales. The grain is one row per sold order line, including returned lines represented with appropriate signs or return indicators. The fact table includes quantity, gross_amount, discount_amount, tax_amount, net_amount, and cost_amount. Net amount must be explicitly defined; for example, the organization may define it as gross amount minus discounts and excluding tax.

Dimensions include Date, Product, Store, Customer, Promotion, and Sales Channel. Product and customer dimensions use surrogate keys. A Type 2 strategy is selected for customer segment and product category because management wants historical reports to reflect the classifications valid when sales occurred.

Approach and Solution

Source data first lands in a raw object-storage area. Change data capture extracts new and changed records from operational systems. Standardization maps product codes to a conformed Product dimension, converts monetary values to a reporting currency using documented exchange-rate rules, and validates that every fact has valid dimension references.

A star-schema warehouse provides curated facts and dimensions. A semantic layer defines net sales, units sold, gross margin, and return rate. Daily batch processing is sufficient for executive reporting, while payment-fraud events are sent through a streaming path for low-latency detection. Reconciliation compares source order counts and monetary totals with warehouse results, with documented treatment of cancellations and returns.

Outcome

Managers can compare current performance with historical product and customer classifications. Analysts use consistent measures across dashboards. The data science team obtains reliable training data and can trace model features to governed sources. The organization does not eliminate all complexity: late returns, changing exchange rates, and customer identity resolution remain managed processes.

Learning Points

The most consequential decisions were grain, metric definitions, conformed dimensions, historical treatment, and reconciliation. Technology supported the design, but it did not substitute for business definitions and governance.

Governance, Security, Ethics, and Professional Practice

Analytical platforms often combine personally identifiable information, financial records, location data, and behavioral data. Governance should address:

• Data ownership, stewardship, classification, retention, and deletion.
• Role-based or attribute-based access control.
• Encryption in transit and at rest.
• Masking, tokenization, anonymization, and controlled environments.
• Consent, lawful purpose, data minimization, and individual rights where applicable.
• Audit logs, lineage, incident response, and regulatory reporting.

Aggregated data can still enable re-identification when combined with external sources. Access should follow least privilege, and sensitive attributes should not be exposed merely because they are technically available.

For AI and ML, historical warehouse data may encode bias or change due to policy shifts. Training datasets require temporal validation, leakage checks, documented feature definitions, and monitoring for drift. A model should not use information that would have been unavailable at prediction time. Governance should cover not only the storage platform but also derived features, labels, model outputs, and retention policies.

Challenges and Limitations

Analytical projects commonly fail because of unclear ownership, inconsistent definitions, poor source quality, excessive copying, or insufficient user adoption. Other limitations include:

• Historical changes can complicate reproducibility.
• Real-time processing increases operational and monitoring complexity.
• Cloud consumption costs may rise through inefficient scans and duplicated storage.
• Schema changes can break pipelines and reports.
• A warehouse may not be suitable for every unstructured or exploratory workload.
• A lakehouse may require specialized skills and disciplined governance.
• Self-service BI can multiply conflicting metrics without a semantic layer.

Best practice is to begin with prioritized business decisions, define measurable outcomes, establish data contracts, document grain and metric semantics, automate quality tests, and deliver incrementally. Architecture should be evaluated by reliability, usefulness, security, latency, maintainability, and total cost—not by fashion or product branding.

Contemporary Developments

Modern analytical platforms increasingly provide serverless scaling, separation of storage and compute, open table formats, metadata catalogs, vectorized execution, real-time analytics, and integrated ML services. Generative AI can assist with natural-language querying, catalog search, documentation, and pipeline development. However, generated SQL and explanations must be validated because language models can misunderstand metric definitions, permissions, joins, and data freshness.

Data products and data mesh approaches emphasize domain ownership, discoverability, interoperability, and platform support. They can improve organizational accountability but may also fragment standards if common governance and shared definitions are weak. The enduring principles remain clear grain, trustworthy data, transparent semantics, appropriate security, and evidence-based architectural choices.

Chapter Summary

Data warehouses and analytical platforms separate or optimize analytical workloads so organizations can study historical, integrated data for decision making. Dimensional modeling centers on an explicitly declared grain, fact tables containing events or states, dimension tables providing descriptive context, and measures whose aggregation behavior is understood. Star schemas favor simplicity, while snowflake schemas normalize dimensions at the cost of additional joins.

OLAP supports roll-up, drill-down, slice, dice, pivot, and drill-through analysis. ETL and ELT pipelines extract, validate, standardize, transform, and load data with attention to incremental processing, idempotence, lineage, and reconciliation. Data marts focus on business subjects; warehouses provide governed analytical integration. Data lakes accommodate varied data, while lakehouses combine flexible storage with stronger table and transaction management.

Columnar storage, partitioning, clustering, caching, and materialized views can improve analytical performance, but correct grain and semantic definitions remain fundamental. Streaming systems support low-latency decisions but require careful treatment of event time, duplicates, late arrivals, replay, and monitoring. Security, privacy, fairness, regulatory compliance, and model governance are essential when analytical data supports AI and ML.

Key Terms

Data warehouse: Governed, integrated, historical data store designed for analysis.

OLTP: Processing optimized for operational transactions.

OLAP: Processing optimized for multidimensional analytical queries.

Fact table: Table recording measurable business events or states.

Dimension table: Table describing the context of facts.

Grain: Exact meaning of one row in a fact table.

Measure: Numeric value analyzed or aggregated.

Star schema: Fact table directly connected to denormalized dimensions.

Snowflake schema: Dimensional schema with normalized dimension structures.

Surrogate key: Warehouse-generated identifier for a dimension record.

Slowly changing dimension: Method for handling changes in dimension attributes.

ETL: Extract, transform, and load integration process.

ELT: Extract, load, and transform integration process.

Data mart: Subject-focused analytical data store.

Columnar storage: Storage organized by columns rather than complete rows.

Data lake: Flexible storage for large volumes and varieties of data.

Lakehouse: Architecture combining lake flexibility with warehouse management features.

Streaming data: Continuously produced and processed event data.

Semantic layer: Governed definitions of business measures and analytical concepts.

Data lineage: Record of data origin, transformations, and movement.

Review Questions

Short Answer Questions

1. What is the purpose of declaring the grain of a fact table?

2. Distinguish additive, semi-additive, and non-additive measures.

3. What is the difference between ETL and ELT?

4. Name four common OLAP operations.

5. Why are surrogate keys useful in dimensional models?

Long Answer Questions

1. Compare OLTP and OLAP systems in terms of workload, schema design, users, and performance requirements.

2. Explain the design of a star schema and compare it with a snowflake schema.

3. Describe a reliable analytical data pipeline from source extraction through BI consumption.

4. Compare data warehouses, data lakes, and lakehouses, including suitable use cases and limitations.

5. Explain how governance, privacy, and security should be incorporated into an analytical platform.

Analytical / Application Questions

1. Design the grain, fact table, dimensions, and measures for a university course-enrollment analytics system. Explain how you would represent withdrawals and changing student programs.

2. A company reports that revenue doubled after joining an order fact table to an order-line table. Diagnose the likely modeling problem and propose a correction.

3. An IoT company needs equipment-failure alerts within one minute but also requires five years of historical analysis. Propose a batch-and-streaming architecture and identify its main reliability risks.

Multiple Choice Questions with Answers

Question 1

What does the grain of a fact table specify?

A. The compression method used for the table

B. What one row in the table represents

C. The number of dimensions in the schema

D. The physical location of the database

Correct Answer: B. What one row in the table represents

Question 2

Which measure is generally semi-additive?

A. Units sold

B. Sales revenue

C. Account balance

D. Order-line quantity

Correct Answer: C. Account balance

Question 3

Which schema usually provides direct, simple connections between a fact table and dimensions?

A. Star schema

B. Network schema

C. Hierarchical schema

D. Document schema

Correct Answer: A. Star schema

Question 4

What is the principal purpose of a Type 2 slowly changing dimension?

A. To delete all historical values

B. To preserve historical versions of dimension attributes

C. To compress fact tables

D. To prevent incremental loading

Correct Answer: B. To preserve historical versions of dimension attributes

Question 5

Which activity is characteristic of OLAP?

A. Recording a single payment transaction

B. Updating one inventory row

C. Rolling monthly sales up to annual sales

D. Enforcing a password policy

Correct Answer: C. Rolling monthly sales up to annual sales

Question 6

Why is columnar storage often effective for analytical queries?

A. It reads every column for every row

B. It allows queries to read only the needed columns

C. It eliminates the need for data governance

D. It guarantees that joins are unnecessary

Correct Answer: B. It allows queries to read only the needed columns

Question 7

Which statement best describes ELT?

A. Data is transformed before extraction

B. Data is extracted, transformed, and never stored

C. Data is loaded into the analytical platform before transformation

D. Data is encrypted but not integrated

Correct Answer: C. Data is loaded into the analytical platform before transformation

Question 8

Which feature is especially important for a reliable streaming pipeline?

A. Ignoring duplicate events

B. Defining event identifiers and replay procedures

C. Removing all timestamps

D. Using only monthly batch loads

Correct Answer: B. Defining event identifiers and replay procedures

Question 9

A retailer wants historical reports to reflect the product category valid at the time of each sale. Which design is most appropriate?

A. Overwrite every category change immediately

B. Use a Type 2 product dimension

C. Remove the product dimension

D. Store category only in the dashboard

Correct Answer: B. Use a Type 2 product dimension

Question 10

What is a major purpose of a semantic layer?

A. To define reusable and consistent business measures

B. To replace all source systems

C. To store only unstructured images

D. To disable access control

Correct Answer: A. To define reusable and consistent business measures