BI Architecture, Platforms and Technology

Introduction

Business intelligence (BI) is not simply the production of charts. It is an organisational capability that converts operational data into trusted information for analysis, decision-making and action. BI architecture describes the components, data flows, standards and controls that make this capability possible. It connects source systems, data storage, transformation processes, analytical models, visualisation tools, application interfaces and governance practices.

A well-designed architecture must reconcile competing requirements. Users want fast, flexible and understandable analysis; executives require consistent measures; data engineers require maintainability and reliability; security teams require control and auditability; and finance teams require accurate, reconcilable results. No single platform or storage technology satisfies all requirements. Technology selection must therefore begin with business needs rather than product features.

This chapter examines the major architectural patterns used in contemporary BI: data warehouses, data marts, data lakes, lakehouses, OLAP systems, semantic layers, cloud BI, APIs and streaming systems. It also provides a framework for evaluating platforms according to workload, data characteristics, governance, cost, skills and organisational risk.

Learning objectives

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

1. Explain the purpose and relationships of major BI architecture components.
2. Distinguish data warehouses, data marts, data lakes and lakehouses.
3. Compare relational OLAP, multidimensional OLAP and modern analytical processing.
4. Explain the role of semantic layers and metrics governance.
5. Describe cloud BI, APIs and streaming architectures.
6. Evaluate BI platforms against functional, technical, financial and governance requirements.
7. Recommend an architecture for a realistic business scenario.

1. BI architecture as a socio-technical system

A BI architecture has both technical and organisational dimensions. Technically, it includes data sources, networks, storage, processing engines, models, interfaces and monitoring. Organisationally, it includes data ownership, definitions, policies, skills, decision rights and user behaviour. A technically advanced platform can still fail if users do not trust its figures or if responsibilities for data quality are unclear.

A useful logical flow is:

Sources → ingestion → storage → transformation → analytical model → semantic layer → reports, dashboards, applications and decisions

The flow is not always linear. Feedback from users may lead to changes in definitions, source extraction, data quality rules or business processes. Modern architectures also permit reverse movement: analytical outputs may be written back to operational systems or used by machine-learning applications.

Architectural layers

These layers are logical rather than necessarily separate products. A cloud service may provide storage, processing, modelling and governance in one integrated platform. Nevertheless, separating the concepts helps analysts reason about design and failure points.

2. Data warehouses and dimensional modelling

A data warehouse is a managed repository designed primarily for analysis and reporting. It integrates data from multiple sources, preserves historical information and presents data in structures optimised for analytical queries. Unlike an operational database, which is designed for frequent small transactions, a warehouse is designed for scans, joins, aggregations and comparative analysis.

Traditional warehouse programmes commonly use an extract–transform–load (ETL) process: data is extracted from sources, transformed before loading, and then stored in the warehouse. Modern programmes often use extract–load–transform (ELT), in which relatively raw data is loaded first and transformed using the warehouse’s processing capacity. ETL and ELT are patterns, not quality guarantees; both require validation, documentation and monitoring.

Facts, dimensions and the star schema

A dimensional model separates measurable business events from descriptive context.

• A fact table records events or observations, such as sales transactions, shipments or claims.
• A dimension table describes the context of those events, such as customer, product, date or store.
• A grain states exactly what one row in a fact table represents.

For example, the grain of a sales fact table might be “one row per product per order line.” Measures may include quantity, sales amount, discount and cost. Dimensions may include order date, customer, product, salesperson and location.

A star schema places a central fact table around relatively denormalised dimension tables. It is generally easy for BI users to understand and performs well for common analytical queries. A snowflake schema normalises some dimensions into additional related tables. Snowflaking can reduce duplication but increases join complexity and may be less intuitive for self-service analysis.

A fundamental design discipline is to declare grain before adding measures. If sales are recorded at order-line level but shipping cost is recorded at order level, joining them without care can duplicate shipping cost. This is a data-modelling error, not merely a visualisation problem.

Historical data and slowly changing dimensions

Operational systems often overwrite attributes. A customer’s segment, a product’s category or an employee’s department may change. Analytical systems must decide whether analysis should reflect the current value or the value that applied when an event occurred.

A common approach is a Type 2 slowly changing dimension. Instead of overwriting a record, the warehouse creates a new version with an effective start date, end date and current-record indicator. Historical facts can then be analysed against the dimension version valid at the time. This improves historical accuracy but increases design and maintenance complexity.

3. Data marts

A data mart is a subject-oriented analytical store serving a department, process or decision domain, such as finance, marketing or supply chain. A dependent data mart is populated from an enterprise warehouse and inherits its definitions. An independent data mart is built directly from operational sources or local files.

Data marts can deliver focused performance and usability. However, independent marts often create “multiple versions of the truth.” For example, marketing may define revenue as invoiced sales while finance defines it as recognised revenue. The existence of two technically valid calculations can still produce organisational confusion.

A governed architecture therefore treats data marts as controlled analytical products. Each mart should document its owner, grain, refresh schedule, definitions, source lineage, access rules and known limitations.

4. Data lakes and lakehouses

A data lake stores large volumes of data in relatively open formats, commonly including structured tables, semi-structured JSON or XML, documents, images, audio and machine logs. It often uses low-cost object storage and separates storage from processing. Data may be retained in raw form for future use, then transformed for specific analytical purposes.

A lake supports exploratory analytics, data science and diverse data types, but an ungoverned lake can become a “data swamp.” Typical problems include undocumented files, inconsistent schemas, duplicate extracts, unclear ownership, excessive permissions and unreliable freshness.

A lakehouse combines lake-style storage flexibility with warehouse-style management features. These may include transactional consistency, schema enforcement, table metadata, versioning, partition management, SQL access and governance. A lakehouse is an architectural pattern rather than a guarantee that all warehouse and lake capabilities are equally mature.

Comparing major storage patterns

The choice depends on requirements. A regulated financial reporting process may prioritise warehouse controls and reconciliation. A research team analysing clickstream, text and images may require lake capabilities. An organisation seeking one platform for SQL analytics and machine learning may consider a lakehouse, provided it can implement appropriate governance.

5. OLAP and analytical processing

Online analytical processing (OLAP) refers to technologies and design practices that support rapid, multidimensional analysis. Users may examine revenue by time, product, region and channel, then drill from year to quarter, month and day or pivot between dimensions.

OLAP approaches

• ROLAP (relational OLAP) stores data in relational tables and uses SQL or SQL-like queries. It benefits from relational scalability and flexibility but may require careful optimisation.
• MOLAP (multidimensional OLAP) stores precomputed data in specialised cubes. It can provide very fast responses for known dimensions and measures, although processing and storage may become burdensome as dimensions and detail increase.
• HOLAP (hybrid OLAP) combines aggregate data in a multidimensional structure with detailed records in relational storage.
• Modern cloud analytical engines may use columnar storage, distributed execution, caching, materialised views and vectorised processing without presenting themselves as traditional cubes.

OLAP performance depends on more than technology. Query design, data volume, cardinality, partitioning, indexing or clustering, caching, concurrency and data freshness all matter. A dashboard that sends hundreds of poorly designed queries can perform badly on a powerful platform.

A measure must also be semantically classified. Additive measures can be summed across all relevant dimensions, such as sales amount. Semi-additive measures can be summed across some dimensions but not time, such as account balance. Non-additive measures, such as percentages and ratios, should generally be recalculated from their components rather than summed.

For example, an overall conversion rate should be calculated as:

Here, “total conversions” and “total eligible visits” must use the same population and time period. Averaging daily conversion percentages can produce a different and often misleading result when daily visit volumes differ.

6. Semantic layers and metrics governance

A semantic layer is a governed abstraction between physical data structures and business users or consuming applications. It defines entities, relationships, dimensions, hierarchies, measures, filters and business rules in terms that users understand.

For example, a semantic layer may define:

• Net revenue = invoiced sales less approved discounts, returns and taxes excluded by policy.
• Active customer = a customer with at least one qualifying transaction in the previous 90 days.
• Gross margin = net revenue less cost of goods sold.

The value of a semantic layer is not just convenience. It reduces metric duplication, makes definitions reusable and improves consistency across dashboards, spreadsheets and embedded applications. It can also centralise row-level security and expose data lineage.

A semantic layer does not eliminate judgement. Organisations must still agree on definitions, ownership and exceptions. “Customer,” “order,” “profit” and “on-time delivery” may have different valid meanings in different contexts. The appropriate response is explicit documentation and contextual naming, not concealment of the disagreement.

A mature metrics governance process includes a metric owner, formal definition, formula or logic, grain, inclusions and exclusions, refresh expectation, data steward, quality tests and approval status. Certified metrics should be distinguished from experimental or user-created calculations.

7. Cloud BI architecture

Cloud BI uses internet-accessible infrastructure and managed services for data storage, processing, governance and visualisation. Common service models include:

• Infrastructure as a service (IaaS): the organisation manages virtual machines, networks and much of the software stack.
• Platform as a service (PaaS): the provider manages more infrastructure while the organisation develops data and analytical solutions.
• Software as a service (SaaS): users consume a complete application managed largely by the provider.

Cloud architectures offer elastic capacity, rapid provisioning, managed upgrades, global access and integration with data and machine-learning services. They also introduce dependence on connectivity, provider availability, pricing models and configuration quality.

Cloud cost is often usage-based. A simple conceptual estimate is:

This is not a universal pricing formula. Actual cost depends on provider-specific units, reservations, workload patterns, concurrency, region and contractual terms. Cost governance should therefore monitor consumption, query efficiency, idle resources, storage retention and user licences.

Important cloud design decisions include data residency, encryption, identity federation, private connectivity, backup, disaster recovery, service-level agreements and exit strategies. A platform should not be selected solely because it is “cloud” or because it offers the largest feature list.

8. APIs and embedded BI

An application programming interface (API) is a defined mechanism through which software systems request or exchange data or functionality. BI APIs may support report embedding, dataset refresh, metadata retrieval, query execution, user provisioning, alerts and export.

APIs enable BI to become part of business workflows. A sales application can display a customer’s purchasing trend; a logistics system can present late-delivery risk; a service portal can show performance against agreed targets. Embedded BI is valuable when insight must appear where work occurs rather than in a separate dashboard portal.

Good API design requires authentication, authorisation, input validation, rate limits, versioning, monitoring and error handling. Common integration styles include REST APIs using HTTP and JSON, GraphQL for flexible query structures, and event-driven interfaces for asynchronous communication. APIs should not expose unrestricted database access. A governed service should expose only necessary data and enforce the same security principles as interactive BI.

9. Streaming systems and real-time BI

Batch processing collects data over an interval and processes it together. Streaming processing handles events continuously or in small time windows. Streaming is appropriate when the value of information decreases rapidly with delay, such as fraud detection, equipment monitoring, dynamic pricing or operational alerting.

A typical streaming architecture contains event producers, a broker or event log, stream-processing components, serving stores and BI or operational consumers. Events should carry identifiers, timestamps, event types and sufficient context for interpretation.

Important concepts include:

• Event time: when the event actually occurred.
• Processing time: when the system processed the event.
• Ingestion time: when the platform received the event.
• Late-arriving event: an event received after the relevant time window.
• At-least-once delivery: an event may be delivered more than once, requiring deduplication or idempotent processing.
• Exactly-once semantics: the intended effect is applied once under defined system and failure assumptions; it is not a universal guarantee across every external system.

Streaming introduces operational complexity. Systems must manage out-of-order events, replay, schema evolution, back-pressure, state recovery and monitoring. “Real-time” should be defined quantitatively, such as a target maximum latency from event occurrence to dashboard availability. A real-time dashboard is unnecessary if a daily decision cycle is sufficient.

10. Common BI platform capabilities

A platform evaluation should examine capabilities as a coherent system rather than a checklist of marketing claims.

Data connectivity and preparation

The platform should connect to required databases, files, SaaS applications, APIs and event sources. It should support incremental extraction, change-data capture where required, data profiling, transformation, validation and error handling.

Modelling and analysis

Relevant capabilities include relational or dimensional modelling, calculated measures, hierarchies, time intelligence, parameterisation, write-back where appropriate, statistical functions and integration with notebooks or programming languages.

Visualisation and delivery

A professional platform commonly provides interactive dashboards, paginated reports, filters, drill-through, alerts, subscriptions, mobile access, accessibility features, export controls and embedded analytics. Visual sophistication is not a substitute for accurate data or useful decision support.

Collaboration and governance

Important capabilities include workspaces, version control or deployment pipelines, catalogues, lineage, certification, ownership metadata, audit logs and usage analytics.

Security

Security should include single sign-on, multifactor authentication, role-based access, row-level and column-level security, encryption, network controls, secrets management and segregation of duties. Security must be tested in the context of actual users and data combinations; merely configuring a role does not prove that information cannot be inferred indirectly.

Reliability and operations

Administrators should be able to monitor refreshes, query performance, capacity, failures, data quality, service health and cost. Recovery objectives should be explicit. Recovery point objective (RPO) states the maximum acceptable amount of data loss measured in time; recovery time objective (RTO) states the maximum acceptable time to restore service.

11. Evaluating technology choices

Technology selection should follow a requirements-led process.

Step 1: Define decisions and users

Identify who will decide, what decision will change, how frequently it occurs, and what consequence follows from an incorrect or late result. A board dashboard, a warehouse replenishment alert and a regulatory report have different requirements.

Step 2: Characterise data and workload

Document source systems, volume, velocity, variety, history, sensitivity, data quality, concurrency, query patterns and retention. Workload characteristics are more informative than vague labels such as “big data.”

Step 3: Establish non-functional requirements

Specify acceptable latency, availability, scalability, recovery, auditability, residency, interoperability and accessibility. Define measurable thresholds where possible.

Step 4: Compare alternatives using weighted criteria

A weighted scoring model can make assumptions explicit. If option score for criterion i is s_i and criterion weight is w_i, with weights summing to 1, the total score is:

Scores must use a defined scale, such as 1 to 5, and should be supported by evidence such as a proof of concept, reference architecture or supplier documentation. The model is a decision aid, not an objective truth. Sensitivity analysis should test whether a small change in weights reverses the recommendation.

Step 5: Test with representative workloads

A proof of concept should include realistic data volumes, security roles, refresh schedules, concurrency, failure scenarios and representative queries. A platform that performs well with a small sample may fail under production cardinality or user load.

Step 6: Consider total cost and organisational fit

Include licences, implementation, migration, training, support, data engineering, governance, network costs, storage, processing and exit costs. Consider whether the organisation can recruit or develop the required skills.

Illustrative case: Meridian Retail Group

Background

Meridian Retail Group operates 120 stores and an online channel. Store transactions are processed through a point-of-sale system, online orders through an e-commerce platform, customer interactions through a CRM system and inventory through an enterprise resource planning system. Executives currently receive weekly spreadsheets. Store managers want near-real-time stock visibility, while finance requires reconciled monthly revenue and margin reporting.

Problem

Different departments use inconsistent product and customer identifiers. Marketing reports online revenue including delivery charges, while finance excludes them. Inventory data arrives hourly from stores but daily from some suppliers. The organisation also wants to analyse clickstream and customer-service text in the future.

Analysis

Meridian has at least three workload classes:

1. Financial reporting: highly governed, reconciled, historical and repeatable.
2. Operational inventory: frequent refresh, low latency and store-level security.
3. Exploration and data science: diverse data types, uncertain future questions and potentially large volumes.

One technology should not be assumed to optimise all three classes equally.

Approach and solution

Meridian adopts a layered cloud architecture. Source data is ingested through batch pipelines and change-data capture where available. Raw extracts and clickstream data are retained in governed object storage. Curated transactional and finance data is stored in warehouse-compatible tables. A conformed product, store, customer and date model is created, with documented keys and historical treatment.

A certified semantic layer defines net revenue, gross margin, stock availability and comparable-store sales. Finance owns revenue definitions, operations owns inventory measures and a data council resolves cross-functional conflicts. Store managers receive an operational dashboard refreshed every 15 minutes where source capability permits; finance receives reconciled daily and monthly reports. Customer-service text is retained in the lake environment for controlled exploratory analysis rather than being mixed immediately into certified financial reporting.

Security uses single sign-on, role-based access and store-level row security. Monitoring measures refresh success, latency, data completeness, duplicate transactions and dashboard usage. A cost policy pauses non-production compute and applies retention rules to raw data.

Outcome

The architecture does not promise that every data product is real time. Instead, each workload receives a defined service level. Finance gains consistency and auditability, operations gains timely stock information, and analysts gain a governed environment for experimentation. The organisation also avoids forcing unstructured text into a rigid reporting warehouse before its analytical value and privacy implications are understood.

Learning points

1. Architecture should reflect decisions and latency requirements.
2. Shared dimensions and semantic definitions reduce disagreement.
3. A lake is not automatically governed simply because it is scalable.
4. Different workloads may justify different storage and processing patterns.
5. Security, cost and ownership must be designed from the beginning.

Challenges, ethics and governance

BI systems can reproduce organisational bias. If historical lending, hiring or service data reflects unequal treatment, a dashboard may make that pattern appear objective. Analysts should examine data provenance, missingness, selection effects and the consequences of classification rules.

Privacy requires data minimisation, purpose limitation, access control, retention management and appropriate consent or legal basis. Combining datasets can create sensitive inferences even when each source appears harmless. Personal data should not be copied into analytical environments without a defined purpose, appropriate protection and a retention schedule.

Governance should cover ownership, quality, lineage, access, definitions, retention, incident response and change management. Automated anomaly detection and AI-generated summaries can assist analysts, but outputs require validation. Generative AI may produce plausible but unsupported explanations, expose confidential data through poorly controlled prompts or amplify misleading metrics. Human review, approved data sources, logging and clear accountability remain necessary.

Other common limitations include vendor lock-in, rising consumption costs, network dependency, migration complexity, stale data, excessive dashboard proliferation and skills shortages. Architecture is therefore an ongoing management discipline, not a one-time procurement decision.

Best-practice checklist

• Begin with decisions, users and service-level requirements.
• Document the grain of every fact table.
• Establish conformed dimensions and certified measures.
• Separate raw, curated and presentation data where useful.
• Use incremental processing and data-quality tests.
• Treat security and privacy as design requirements.
• Measure freshness, availability, query performance and cost.
• Test with realistic workloads and failure conditions.
• Make ownership and definitions visible to users.
• Review architecture as volumes, regulations and business needs change.

Chapter Summary

BI architecture connects data sources, ingestion, storage, processing, modelling, semantic definitions, consumption and governance. Data warehouses provide structured, governed analytical storage; data marts focus on specific domains; data lakes retain diverse data flexibly; and lakehouses seek to combine lake flexibility with warehouse management features. OLAP supports multidimensional analysis through relational, multidimensional and hybrid approaches, while modern cloud engines use distributed and columnar techniques.

Semantic layers improve consistency by publishing governed business concepts and measures. Cloud BI offers elasticity and managed services but requires attention to cost, security, resilience and provider dependence. APIs enable embedded and workflow-based analytics. Streaming systems support low-latency decisions but require careful treatment of event time, delivery semantics, late data and operational recovery.

Technology choices should be evaluated against decisions, workload, data characteristics, latency, governance, security, total cost and organisational capability. The strongest architecture is not necessarily the newest or largest platform; it is the one that delivers trustworthy insight at an appropriate cost and risk level.

Key Terms

Business intelligence: Organisational capability for converting data into information and decisions.

Data warehouse: Governed analytical repository designed for integrated historical analysis.

Data mart: Analytical store focused on a department, subject or business process.

Data lake: Flexible repository for large volumes of structured and non-structured data.

Lakehouse: Architecture combining lake-style storage with warehouse-style management features.

Fact table: Table recording measurable business events at a declared grain.

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

Grain: Precise statement of what one fact-table row represents.

OLAP: Technology and practice supporting multidimensional analytical queries.

ROLAP: OLAP implemented primarily with relational tables and queries.

MOLAP: OLAP using specialised multidimensional structures or cubes.

Semantic layer: Governed abstraction that defines business entities, measures and relationships.

ETL: Extract, transform and load data-processing pattern.

ELT: Extract, load and transform data-processing pattern.

API: Defined software interface for exchanging data or functionality.

Streaming: Continuous or near-continuous processing of events.

Event time: Time at which an event occurred in the originating process.

Row-level security: Restriction that allows users to see only authorised rows.

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

Recovery time objective: Maximum acceptable time for restoring a service after disruption.

Review Questions

Short Answer Questions

1. What is the primary difference between an operational database and a data warehouse?
2. Why must the grain of a fact table be declared before measures are added?
3. What problem does a semantic layer help solve?
4. Distinguish event time from processing time.
5. State two risks associated with an ungoverned data lake.

Long Answer Questions

1. Compare a data warehouse, data mart, data lake and lakehouse in terms of purpose, schema, strengths and risks.
2. Explain how dimensional modelling supports business analysis, using facts, dimensions and slowly changing dimensions.
3. Discuss the advantages and limitations of cloud BI for a medium-sized organisation.
4. Explain the main capabilities that should be assessed when selecting a BI platform.
5. Describe how governance, privacy and security should be integrated into BI architecture.

Analytical / Application Questions

1. A hospital requires audited monthly finance reports, minute-by-minute intensive-care monitoring and research on clinical notes. Recommend an architectural pattern for each workload and justify your choices.
2. Two departments calculate customer retention differently. Design a process using a semantic layer and metrics governance to investigate and resolve the disagreement.
3. A streaming dashboard displays duplicate and late-arriving delivery events. Analyse possible causes and propose processing, data-quality and monitoring controls.

Multiple Choice Questions with Answers

Question 1

Which component primarily defines reusable business measures such as net revenue and active customer?

A. Semantic layer

B. Message broker

C. Raw object storage

D. Operational transaction log

Correct Answer: A. Semantic layer

Question 2

What does the grain of a fact table specify?

A. The database vendor used to store it

B. What one row in the table represents

C. The colour scheme of its dashboard

D. The number of dimensions in the entire warehouse

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

Question 3

Which architecture is most associated with storing diverse raw data for exploration and data science?

A. Data mart

B. Data lake

C. Paginated report

D. OLTP database

Correct Answer: B. Data lake

Question 4

Which measure is generally non-additive and should usually be recalculated from its components?

A. Sales amount

B. Units sold

C. Conversion rate

D. Shipping quantity

Correct Answer: C. Conversion rate

Question 5

What is the main purpose of an API in a BI environment?

A. To define a database table’s grain

B. To exchange data or functionality between software systems

C. To replace all data governance processes

D. To guarantee that every query runs in real time

Correct Answer: B. To exchange data or functionality between software systems

Question 6

Which statement best describes event time in a streaming system?

A. When the event occurred in the originating process

B. When an analyst opened a dashboard

C. When a query finished execution

D. When a platform licence was purchased

Correct Answer: A. When the event occurred in the originating process

Question 7

If three evaluation criteria have weights 0.5, 0.3 and 0.2, which condition is required for a weighted score to represent the stated model?

A. All option scores must be identical

B. The weights must sum to 1

C. The largest weight must be 1

D. The criteria must all measure cost

Correct Answer: B. The weights must sum to 1

Question 8

Which requirement is especially associated with audited financial reporting?

A. Unrestricted schema-on-read experimentation

B. Reconciliation, lineage and controlled definitions

C. Unlimited anonymous access

D. Elimination of historical data

Correct Answer: B. Reconciliation, lineage and controlled definitions

Question 9

What is a principal risk of independent data marts?

A. They cannot contain structured data

B. They always require streaming ingestion

C. They can create inconsistent versions of business metrics

D. They prohibit all departmental analysis

Correct Answer: C. They can create inconsistent versions of business metrics

Question 10

Which statement about a lakehouse is most accurate?

A. It is a guaranteed replacement for every database

B. It combines flexible lake-style storage with selected warehouse-style management capabilities

C. It can store only highly structured financial tables

D. It removes the need for security and governance

Correct Answer: B. It combines flexible lake-style storage with selected warehouse-style management capabilities