BI and DSS Architecture

Introduction

Business intelligence (BI) and decision support systems (DSS) convert operational data into information, insight and action. Their architecture describes the components involved, the way those components interact, and the controls needed to ensure that decisions are based on reliable, timely and appropriately interpreted evidence.

A modern BI and DSS ecosystem is not a single software product. It is a connected environment that may include transaction-processing systems, external data sources, integration pipelines, data warehouses, data lakes, semantic models, analytical engines, dashboards, reporting tools, applications and cloud services. People remain central to the ecosystem: executives, managers, analysts, operational employees, data engineers and governance specialists use different parts of the architecture for different purposes.

The quality of a decision depends on more than the volume of available data. It depends on whether the right data is collected, integrated, stored, defined consistently, analysed appropriately and communicated to the right person at the right time. Architecture therefore provides the structure through which data becomes decision value.

Learning Objectives

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

1. Explain the major components of a BI and DSS architecture.
2. Distinguish source systems, integration processes, storage platforms and analytical layers.
3. Describe batch, streaming and hybrid information flows.
4. Explain the purpose of semantic layers, analytical engines, dashboards and user interfaces.
5. Compare data warehouses, data lakes, lakehouses and cloud-based BI platforms.
6. Apply architectural principles to a practical organisational problem.
7. Identify issues involving governance, security, privacy, quality, ethics and responsible use.

1. The Architectural Perspective

An architecture is a structured representation of components, relationships, interfaces and flows. In BI and DSS, the architecture answers questions such as:

• Where is data created?
• How is it moved and transformed?
• Where is it stored?
• How are business definitions applied?
• How are calculations performed?
• How are results presented?
• How do users provide feedback or trigger action?
• How are security, quality and governance maintained?

A useful conceptual model contains six interacting layers:

1. Source layer: systems and environments in which data originates.
2. Integration layer: processes that extract, transport, cleanse, transform and combine data.
3. Storage layer: repositories that retain detailed, historical and curated data.
4. Semantic and analytical layer: business definitions, models, calculations and analytical services.
5. Presentation layer: reports, dashboards, alerts, applications and interfaces.
6. Governance and operations layer: security, metadata, quality, monitoring, compliance and administration across all other layers.

These layers are logical rather than necessarily physical. A cloud platform may provide several layers through one service, while a large organisation may implement each layer with different technologies.

Figure: A logical layered architecture for BI and decision support.

The upward arrows represent the common movement of prepared information toward users. In practice, feedback also travels downward: a user may submit a forecast adjustment, approve a plan or initiate a workflow that changes an operational system.

2. Source Systems and Data Origins

Source systems are the places where business events are recorded or external observations are acquired. Common internal sources include enterprise resource planning (ERP), customer relationship management (CRM), human-resource systems, point-of-sale systems, finance applications, supply-chain systems and spreadsheets. External sources include government datasets, market research, social media, partner feeds, weather data and public APIs. Sensors and application logs provide machine-generated data.

Source systems are generally designed to support operations rather than analysis. A sales transaction system must process orders quickly and preserve transaction integrity. It may therefore store data in a highly normalised structure distributed across many tables. An analyst, however, may need a historical view of sales by product, region, channel and customer segment. The analytical architecture must reorganise operational data without damaging the source system.

Important source-system characteristics include:

• Structure: relational tables, files, documents, messages or unstructured text.
• Velocity: the rate at which records arrive.
• Latency requirement: how quickly information must become available.
• Ownership: the department or external provider responsible for the data.
• Quality: completeness, accuracy, consistency, validity and timeliness.
• Change behaviour: whether schemas, codes or business processes change frequently.

A source register should record the system owner, data elements, refresh frequency, sensitivity classification, retention requirements and known quality limitations. Without this inventory, organisations often build reports using undocumented extracts and conflicting definitions.

3. Data Integration and Information Flows

Data integration makes information from different sources usable together. It commonly includes extraction, ingestion, validation, transformation, matching, enrichment and loading.

ETL and ELT

In traditional extract-transform-load (ETL), data is extracted from sources, transformed in an integration environment and loaded into a target warehouse. ETL can enforce strong controls before data reaches the warehouse.

In extract-load-transform (ELT), raw or lightly processed data is loaded into a scalable platform first and transformed there. ELT is common in cloud environments because storage and processing can scale independently. It also preserves raw data for later use, although it requires strong controls over transformation logic and access.

The choice is not ideological. ETL may be appropriate where target structures are tightly governed or processing capacity is limited. ELT may be suitable where an organisation needs flexibility, large-scale processing and multiple downstream uses.

Batch, streaming and hybrid flows

A batch flow moves data at scheduled intervals, such as hourly, nightly or weekly. It is appropriate for payroll, monthly financial reporting and many management reports. A streaming flow processes events continuously or with very low delay. It is useful for fraud detection, equipment monitoring, clickstream analysis and real-time operational alerts.

A hybrid architecture combines both. For example, current payment events may be streamed for fraud monitoring, while reconciled transactions are loaded in daily batches for financial reporting.

Integration operations

Typical integration operations include:

• Validation: checking data types, required fields and permitted values.
• Standardisation: converting dates, currencies, units and codes to common conventions.
• Deduplication: identifying repeated records.
• Entity resolution: determining that different records refer to the same customer, supplier or product.
• Joining: combining related records through keys or matching rules.
• Historical tracking: preserving changes in customer attributes or organisational structures.
• Reconciliation: comparing source totals with processed totals.
• Error handling: isolating rejected records, documenting reasons and supporting reprocessing.

Data integration is not merely a technical task. If two departments define “active customer” differently, combining their records does not automatically create a correct enterprise measure. Business agreement is required in addition to technical connectivity.

4. Storage Architectures

Operational databases

Operational databases support day-to-day transactions. They prioritise rapid inserts, updates and transaction consistency. They are usually not ideal for complex analytical queries across many years because such queries can compete with operational workloads.

Data warehouses

A data warehouse is a structured, subject-oriented repository of integrated and historical data designed primarily for analysis and reporting. Warehouse schemas often use fact tables and dimension tables. A sales fact table may contain quantity, revenue and cost, while dimensions describe product, customer, date and location.

A star schema has a central fact table connected directly to denormalised dimensions. It is generally easy for reporting tools and users to understand. A snowflake schema normalises dimensions into additional related tables, which can reduce duplication but may increase query complexity.

Data lakes

A data lake stores large volumes of raw, semi-structured and structured data. It supports exploratory analysis, data science and uses that were not known when the data was first collected. Its flexibility can become a weakness if ownership, cataloguing, quality and retention are neglected. An unmanaged lake can become a “data swamp”: data exists but cannot be trusted or located.

Lakehouses and data marts

A lakehouse seeks to combine the low-cost, flexible storage of a lake with the reliability, transaction support and management features associated with a warehouse. A data mart is a subject- or department-focused analytical store, such as a finance or marketing mart. Data marts can improve usability but may recreate silos if they are built independently without shared definitions.

Storage design should reflect business requirements, not fashion. Key decisions include retention, performance, cost, recovery, data granularity, historical change handling and access patterns.

5. Metadata and the Semantic Layer

Metadata is information about data. Technical metadata describes tables, columns, data types and pipelines. Business metadata explains meaning, ownership, permitted use and definitions. Operational metadata records refresh times, failures, lineage and processing status.

A data catalog helps users discover datasets and understand their context. Data lineage shows the path from source fields through transformations to reports or metrics. Lineage is valuable for impact analysis, troubleshooting, audit and regulatory response.

The semantic layer translates technical structures into business concepts. Instead of requiring every analyst to understand database joins, it may expose measures such as Revenue, Gross Margin and Customer Count, together with dimensions such as Month, Region and Product Category.

A semantic model should specify:

• the grain of each fact;
• relationships between entities;
• approved measures and calculation rules;
• filters and aggregation behaviour;
• time and currency conventions;
• security rules;
• ownership and certification status.

For example, a business may define gross margin as:

Revenue and Cost of Goods Sold must use the same period, currency, organisational scope and accounting basis. If Revenue is recorded at invoice date but cost is recorded at shipment date, the calculation may be misleading even though the arithmetic is correct.

A percentage margin is commonly calculated as:

This formula assumes Revenue is non-zero and that the numerator and denominator are measured on a comparable basis. A semantic layer centralises such logic, reducing the risk that different reports calculate the same term differently.

6. Analytical Engines and Decision Support

Analytical engines execute queries, aggregations, statistical models, optimisation routines and machine-learning workflows. They may include relational query engines, online analytical processing (OLAP), in-memory engines, notebooks, predictive-model services and optimisation solvers.

BI commonly supports three levels of analysis:

1. Descriptive analysis: What happened?
2. Diagnostic analysis: Why did it happen?
3. Predictive and prescriptive analysis: What may happen, and what action should be considered?

DSS capabilities can be classified by the nature of the decision. Structured decisions, such as replenishment according to established rules, can be highly automated. Semi-structured decisions, such as staffing or budget allocation, combine models with managerial judgement. Unstructured decisions, such as entering a new market, require interpretation, experience and consideration of qualitative evidence.

A DSS should not be judged solely by analytical sophistication. A complex model that users do not understand or cannot act upon may have less value than a transparent rule-based system. Model assumptions, uncertainty, data limitations and appropriate use must be communicated.

7. Dashboards, Reports and User Interfaces

The presentation layer delivers information to users. A report usually provides structured, often periodic detail. A dashboard provides a monitored view of selected measures, trends, exceptions and targets. An alert proactively notifies a user when a defined condition occurs. An embedded interface places analytics inside an operational application or workflow.

Effective interfaces match information to the decision. An executive dashboard may show a small number of strategic indicators and trends. A call-centre supervisor may need near-real-time queue status and staffing exceptions. An analyst may require filtering, drill-down, export and exploratory tools.

Good dashboard design includes:

• clear purpose and named audience;
• a limited number of meaningful measures;
• visible units, time periods and comparison bases;
• consistent definitions and labels;
• appropriate visual encodings;
• accessible colour choices and readable text;
• clear indication of data freshness;
• links to detail and explanations of exceptions;
• warnings against interpreting correlation as causation.

A dashboard is not a decision in itself. It should connect evidence to a decision process, accountability and action. Where possible, the interface should show the responsible owner, recommended next step and status of follow-up.

8. Cloud-Based BI Platforms

Cloud BI platforms provide hosted storage, integration, analytical processing and presentation services. Their benefits may include elastic capacity, rapid deployment, managed maintenance, collaboration and access from multiple locations. They can also support self-service analytics through shared semantic models and governed workspaces.

Cloud architecture introduces important design choices:

• Software as a service provides a complete application managed by a provider.
• Platform as a service provides managed development or data services.
• Infrastructure as a service provides configurable computing, storage and networking.
• Public cloud uses shared provider infrastructure with logical separation.
• Private cloud uses dedicated infrastructure or a private environment.
• Hybrid cloud combines cloud and on-premises resources.

Cloud adoption does not remove responsibility for governance. The organisation remains accountable for access, data classification, retention, contractual controls, regulatory obligations and appropriate configuration. Costs may also be difficult to predict when queries, storage and data movement scale unexpectedly.

A practical cloud design uses separate development, test and production environments; identity-based access; encryption; monitoring; automated deployment; backup and recovery; and documented exit or portability arrangements.

9. Governance, Security and Responsible Use

Governance establishes decision rights, policies, standards and accountability. It should cover data ownership, quality, definitions, access, retention, lineage, change management and issue resolution.

Security controls commonly include authentication, role-based or attribute-based authorisation, encryption in transit and at rest, network controls, audit logs and segregation of duties. Sensitive data may require masking, tokenisation or row- and column-level restrictions.

Privacy requires collecting and using personal data for legitimate, transparent purposes. Data minimisation, purpose limitation, retention control and appropriate consent or legal basis are important principles. An analyst should not access identifiable customer records merely because the platform makes them technically available.

Ethical risks include discriminatory segmentation, opaque scoring, excessive surveillance, misleading visualisation and automation without meaningful human review. A predictive model can reproduce historical bias if past decisions were biased. Governance should therefore include fairness assessment where relevant, explainability appropriate to the decision, monitoring for drift and a process for appeal or correction.

Data quality is multidimensional. Common dimensions are accuracy, completeness, consistency, validity, uniqueness and timeliness. A simple completeness rate can be expressed as:

This measure assumes that required fields and the population being assessed are clearly defined. A high completeness rate does not prove accuracy; a field may be filled but wrong. Quality measures should therefore be linked to business impact and monitored over time.

10. Designing a BI and DSS Architecture

A disciplined design process can follow these steps:

1. Clarify decisions and users. Identify who must decide what, when and with what consequences.
2. Define information requirements. Specify measures, dimensions, granularity, history, latency and acceptable uncertainty.
3. Inventory sources. Document systems, owners, quality, sensitivity and update patterns.
4. Select integration patterns. Choose batch, streaming or hybrid flows according to decision latency and reliability needs.
5. Design storage. Select warehouse, lake, lakehouse or marts based on data types, workloads, governance and cost.
6. Establish common semantics. Agree definitions, ownership, lineage and certified measures.
7. Build analytical services. Match query, statistical, predictive or optimisation methods to the decision.
8. Design the user experience. Provide role-appropriate dashboards, alerts, reports and workflows.
9. Implement controls. Add security, quality checks, monitoring, testing, backup and auditability.
10. Evaluate and improve. Measure adoption, decision speed, accuracy, data quality, cost and business outcomes.

A sound architecture is modular but not fragmented. Components should have clear interfaces and documented contracts. It should be possible to change a source system without rebuilding every report, while also preventing uncontrolled duplication of definitions.

Applied Case: NorthStar Retail’s Stock Availability Problem

This is an illustrative case.

Background

NorthStar Retail operates 85 stores and an online channel. Managers report that the organisation has enough inventory overall, yet customers frequently encounter out-of-stock products. Store managers use spreadsheets, the supply team uses an enterprise resource planning system, and the online team uses a separate platform.

Problem

The chief operating officer requests a DSS that identifies likely stock-outs, distinguishes supply problems from inaccurate inventory records and recommends replenishment priorities. The system must support daily planning but provide alerts for high-value products during the trading day.

Analysis

The architecture team inventories the sources: point-of-sale transactions, warehouse receipts, purchase orders, inventory adjustments, online orders, product master data and supplier lead times. It discovers that product codes differ between the online platform and the ERP system. Some stores also delay recording damaged goods.

A single dashboard built directly on the ERP system would not solve the problem. It would omit online demand, fail to reconcile product identities and treat inaccurate stock balances as facts.

Approach and solution

The team creates a governed product master and an entity-matching process. Daily historical transactions are loaded into a warehouse using a star schema. Near-real-time sales events are streamed into an operational monitoring store. Data-quality rules compare opening inventory, receipts, sales and adjustments, and exceptions are assigned to store managers.

A semantic layer defines available inventory, expected demand, supplier lead time and stock-out risk. A dashboard shows regional performance, while store users receive alerts for products whose projected availability falls below a defined threshold. The replenishment recommendation remains subject to planner approval because supplier constraints and promotional events may not be fully represented in the model.

Outcome

NorthStar obtains one governed view of product and inventory information. Managers can distinguish genuine low-stock conditions from data-recording problems. Planners receive prioritised exceptions rather than large undifferentiated spreadsheets. The organisation evaluates success through stock-out frequency, inventory accuracy, alert usefulness, response time and user adoption rather than dashboard page views alone.

Learning points

The case demonstrates that architecture is a business capability, not merely a collection of tools. Source ownership, entity resolution, data quality, semantic consistency, latency and human workflow all affect decision value. It also illustrates why automation should support, rather than conceal, managerial accountability.

Challenges and Limitations

BI and DSS projects commonly face several limitations:

• Conflicting definitions create different versions of revenue, customer or inventory.
• Poor source quality limits the value of sophisticated analytics.
• Latency trade-offs arise because faster data flows may cost more and require greater operational complexity.
• Integration fragility occurs when undocumented source changes break pipelines.
• Self-service sprawl can produce duplicated datasets and ungoverned reports.
• Performance and cost may deteriorate as data volumes and query demands increase.
• Model risk arises from inappropriate assumptions, data drift or overconfidence in predictions.
• Change resistance may prevent users from adopting a technically successful platform.
• Vendor dependence can make migration, interoperability and cost control difficult.
• Security exposure increases when sensitive data is copied into extracts or personal workspaces.

Architecture must therefore be reviewed as an evolving socio-technical system involving technology, processes, skills, incentives and organisational culture.

Contemporary Developments and Best Practices

Contemporary platforms increasingly combine batch and streaming data, governed self-service, natural-language interfaces, embedded analytics and machine-learning services. Generative AI may assist with query creation, explanation or report drafting, but its outputs require validation. It can misinterpret business definitions, expose sensitive information or produce plausible but unsupported conclusions if governance is weak.

Best practices include:

• begin with decisions and measurable outcomes rather than tools;
• maintain a shared business glossary and certified semantic models;
• treat metadata and lineage as operational assets;
• automate testing for schema changes, reconciliation and data quality;
• apply least-privilege access and monitor unusual use;
• separate raw, curated and presentation data;
• design for failure, recovery and reprocessing;
• provide transparent definitions, freshness indicators and caveats;
• involve business users throughout design and evaluation;
• monitor both technical performance and decision outcomes;
• review models, rules and access rights periodically.

Chapter Summary

BI and DSS architecture explains how data moves from operational and external sources to integrated storage, semantic models, analytical engines and decision interfaces. Source systems are designed primarily for operational work, whereas warehouses, lakes, lakehouses and marts support different analytical needs. Integration may use ETL, ELT, batch, streaming or hybrid patterns.

Metadata, lineage and semantic layers make data discoverable and give measures consistent business meaning. Analytical engines support descriptive, diagnostic, predictive and prescriptive work, while dashboards, reports, alerts and embedded applications connect analysis to users and action. Cloud platforms can improve scalability and collaboration but do not remove governance responsibilities.

Effective architecture aligns latency, granularity, quality, security, cost and usability with real decisions. Governance must address ownership, privacy, access, ethical use, model risk and accountability. The NorthStar case shows that a successful DSS depends on data quality, common identifiers, shared definitions and human workflows as much as on software.

Key Terms

Source system: A system or environment in which operational or external data originates.

Data integration: The process of combining data from multiple sources into usable information.

ETL: Extract, transform and load; a pattern in which data is transformed before loading into a target repository.

ELT: Extract, load and transform; a pattern in which data is loaded before transformation.

Data warehouse: A structured, integrated and historical repository designed primarily for analysis.

Data lake: A repository that stores large volumes of raw or lightly processed data in varied formats.

Lakehouse: An architecture combining flexible lake storage with managed analytical capabilities.

Data mart: A subject- or department-focused analytical repository.

Fact table: A table containing measurable business events or quantities.

Dimension: A descriptive context, such as date, product, customer or location.

Metadata: Information describing data, its meaning, structure, ownership or use.

Data lineage: The documented path of data from origin through transformations to outputs.

Semantic layer: A business-oriented layer containing common definitions, relationships and calculations.

OLAP: Online analytical processing for examining data across multiple dimensions.

Dashboard: An interface presenting selected measures, trends, exceptions or targets.

Data latency: The delay between data creation and its availability for use.

Data governance: Policies, roles, standards and controls that manage data as an organisational asset.

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

Decision support system: A system that combines data, models and interfaces to assist decision-making.

Data lakehouse: A managed analytical architecture intended to unite lake flexibility and warehouse governance.

Review Questions

Short Answer Questions

1. What are the major logical layers of a BI and DSS architecture?
2. How does a data warehouse differ from an operational database?
3. What is the purpose of a semantic layer?
4. When might streaming integration be preferred to batch integration?
5. Why are metadata and data lineage important?

Long Answer Questions

1. Explain the flow of information from source systems to a dashboard, including the roles of integration, storage and analytical services.
2. Compare ETL and ELT, and discuss circumstances in which each may be appropriate.
3. Compare data warehouses, data lakes, lakehouses and data marts in terms of purpose, strengths and limitations.
4. Explain how governance, privacy and security should be incorporated across a BI architecture.
5. Discuss why dashboard design must be linked to decisions, users and organisational workflows.

Analytical / Application Questions

1. A hospital wants real-time alerts for critical equipment but monthly reports for finance. Propose an integration and storage architecture, explaining where streaming and batch processing would be used.
2. Two departments calculate “customer retention” differently. Design a process using metadata, governance and a semantic layer to establish a reliable enterprise measure.
3. An organisation plans to copy all personal data into a cloud data lake for future analysis. Evaluate the proposal and recommend controls for privacy, security, retention, quality and responsible use.

Multiple Choice Questions with Answers

Question 1

Which layer primarily provides common business definitions such as Revenue and Gross Margin?

A. Source layer

B. Semantic layer

C. Network layer

D. Transaction layer

Correct Answer: B. Semantic layer

Question 2

What is the main purpose of a data warehouse?

A. To process point-of-sale transactions only

B. To replace all operational applications

C. To provide integrated historical data for analysis

D. To store only unstructured documents

Correct Answer: C. To provide integrated historical data for analysis

Question 3

Which integration pattern transforms data after it has been loaded into the target platform?

A. ETL

B. ELT

C. OLAP

D. ERP

Correct Answer: B. ELT

Question 4

Which situation is most suitable for streaming data?

A. Annual tax archiving

B. Monthly payroll preparation

C. Real-time fraud detection

D. Historical policy documentation

Correct Answer: C. Real-time fraud detection

Question 5

What does data lineage describe?

A. The visual colour scheme of a dashboard

B. The path of data from source through transformations to outputs

C. The physical location of every employee

D. The number of users logged into a system

Correct Answer: B. The path of data from source through transformations to outputs

Question 6

Which repository is generally designed for flexible storage of raw, varied-format data?

A. Data lake

B. Operational database

C. Data mart only

D. Semantic model

Correct Answer: A. Data lake

Question 7

A dashboard shows a gross margin percentage. Which condition is necessary for the standard calculation to be meaningful?

A. Revenue must be zero

B. Revenue and gross margin must use comparable scope and period

C. Cost of goods sold must be excluded from all records

D. The dashboard must use streaming data

Correct Answer: B. Revenue and gross margin must use comparable scope and period

Question 8

Which control best reflects the principle of least privilege?

A. Giving every analyst administrator access

B. Allowing users access only to data and actions required for their role

C. Publishing all raw data publicly

D. Removing audit logs

Correct Answer: B. Allowing users access only to data and actions required for their role

Question 9

Why can a high completeness rate fail to prove high data quality?

A. Completeness measures whether values exist, not necessarily whether they are accurate

B. Completeness is unrelated to required fields

C. Completeness can only be measured in a data lake

D. Completeness always measures timeliness instead

Correct Answer: A. Completeness measures whether values exist, not necessarily whether they are accurate

Question 10

In the NorthStar case, why was a governed product master important?

A. It eliminated the need for any source systems

B. It ensured that different product codes could be related consistently

C. It guaranteed that every forecast would be correct

D. It prevented managers from reviewing alerts

Correct Answer: B. It ensured that different product codes could be related consistently