Business Intelligence, Analytics and Decision Support

Introduction and Context

Modern organisations generate data through transactional systems, websites, mobile applications, sensors, enterprise resource planning (ERP) systems, customer relationship management (CRM) platforms and social channels. Data alone does not improve performance. Value is created when data is transformed into reliable information, interpreted as evidence and used to make timely decisions.

Business intelligence (BI) and analytics provide the methods and technologies for this transformation. BI traditionally focuses on reporting, monitoring and understanding organisational performance. Analytics extends this capability by explaining why events occurred, estimating what may happen next and recommending appropriate actions. Decision-support systems (DSS) combine data, analytical models and human judgement to support semi-structured and complex decisions.

For an advanced diploma IT professional, competence in this area requires more than producing attractive charts. It involves understanding data quality, dimensional modelling, statistical reasoning, metric design, visual communication, governance, security and the organisational context in which decisions are made.

Learning Objectives

After completing this chapter, you should be able to:

1. Explain the relationship between business intelligence, analytics and decision-support systems.
2. Distinguish descriptive, diagnostic, predictive and prescriptive analytics.
3. Prepare, integrate and validate data for analysis.
4. Design meaningful KPIs and dashboards for operational and managerial users.
5. Apply statistical and analytical models to practical decisions.
6. Interpret analytical results while recognising uncertainty and limitations.
7. Evaluate ethical, security, governance and implementation issues.

1. The BI and Analytics Value Chain

A useful way to understand BI is as a value chain:

Data sources → Data preparation → Storage and modelling → Analysis → Visualisation → Decision → Action → Feedback

Data sources may include sales transactions, inventory records, service tickets, finance systems and external economic data. Preparation includes extraction, cleaning, transformation and integration. Storage may use a data warehouse, data lake or lakehouse. Analytical work converts prepared data into measures, patterns, forecasts or recommendations. Visualisation communicates findings, while decision-makers apply them to operations and strategy. Outcomes should then be measured and fed back into the system.

Figure: The BI and analytics value chain, including feedback from decisions to future analysis.

BI, analytics and DSS compared

BI is not identical to artificial intelligence. AI may provide techniques such as classification, natural-language querying or optimisation, but a BI programme also requires trustworthy data, suitable metrics, governance and human interpretation.

2. Types of Analytics

Descriptive analytics

Descriptive analytics summarises historical or current data. Examples include monthly revenue, average response time, stock levels and website conversion rate. It answers “what happened?”

Common measures include:

Here, xᵢ is an observed value and n is the number of observations. The arithmetic mean is appropriate when values are numeric and a representative average is meaningful. It can be strongly affected by extreme values.

This formula applies when the old value is non-zero and the direction of change is meaningful. If the old value is negative or close to zero, interpretation requires care.

Other descriptive measures include the median, mode, range, variance and standard deviation. The population variance is:

where μ is the population mean and N is the population size. For a sample used to estimate a wider population, the sample variance commonly uses n − 1 in the denominator. Standard deviation is the positive square root of variance and has the same units as the original data.

Diagnostic analytics

Diagnostic analytics investigates causes and relationships. It may involve drill-down from total sales to region and product, comparison of groups, correlation analysis, root-cause analysis and controlled experiments. It answers “why did it happen?”

Correlation measures linear association, not causation. Pearson’s correlation coefficient is:

The value of r lies between −1 and +1. A value near +1 indicates strong positive linear association; a value near −1 indicates strong negative linear association. The measure assumes paired numeric observations and is sensitive to outliers and non-linear patterns. Correlation does not establish that changes in one variable cause changes in another.

Predictive analytics

Predictive analytics estimates future or unknown outcomes using historical data. Applications include demand forecasting, credit-risk scoring, customer churn prediction and preventive maintenance.

A simple linear regression model is:

where ŷ is the predicted response, x is a predictor, a is the intercept and b is the estimated slope. A common least-squares estimate of the slope is:

The model assumes that the relationship is adequately represented by a straight line, observations are appropriately measured, errors are reasonably independent and predictions are not being extrapolated far beyond the observed range. A high predictive score does not automatically imply a causal explanation.

For classification, a confusion matrix distinguishes true positives, true negatives, false positives and false negatives. Important measures include:

Precision answers: of the cases predicted positive, how many were actually positive? Recall answers: of the actual positive cases, how many were detected? The appropriate measure depends on the cost of false alerts and missed cases.

Prescriptive analytics

Prescriptive analytics recommends actions under constraints. It may use optimisation, simulation, rules or decision analysis. It answers “what should we do?”

A general linear optimisation model can be written as:

subject to:

Here, xⱼ are decision variables, cⱼ are objective coefficients, aᵢⱼ are resource coefficients, bᵢ are resource limits and Z is the objective value. The model is useful only when the objective, constraints and data reflect the real decision environment. An mathematically optimal solution can be operationally poor if the model omits staff availability, service quality or ethical constraints.

3. Data Preparation and Analytical Foundations

Poor data preparation is one of the most common causes of unreliable analytics. A disciplined workflow includes:

1. Define the analytical question. Specify the decision, population, time period and desired outcome.
2. Identify data sources and ownership. Record source systems, refresh frequency, definitions and access rights.
3. Profile the data. Examine data types, missing values, duplicates, ranges, outliers and distributions.
4. Clean and transform. Standardise dates, units, categories and identifiers; correct or document errors.
5. Integrate sources. Match keys carefully and resolve conflicting definitions.
6. Validate. Reconcile totals with trusted source systems and test business rules.
7. Document lineage. Record where data came from, what transformations occurred and who approved them.

Data quality has several dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness and fitness for purpose. A missing value is not automatically an error. For example, “not applicable” differs from “not recorded”. Replacing all missing numeric values with zero can introduce serious bias.

Data warehouse and dimensional modelling

A data warehouse is a subject-oriented, integrated and historical store designed for analysis. In dimensional modelling, a fact table records measurable events, while dimension tables describe the context of those events. A sales fact may contain date key, product key, store key, quantity and sales amount. Dimensions may contain product category, store region and calendar attributes.

The grain must be declared explicitly: for example, “one row per product per invoice line”. Mixing different grains in one fact table causes double counting. A star schema normally has a central fact table connected to denormalised dimensions. A snowflake schema further normalises dimensions and may reduce redundancy, but can make queries more complex.

A robust analytical dataset should also address slowly changing dimensions, late-arriving data, historical versioning and time zones. Measures should have clear aggregation rules. Additive measures, such as sales amount, can be summed across dimensions; semi-additive measures, such as account balance, should not normally be summed across time.

4. KPIs, Metrics and Dashboard Design

A metric is a quantified measure. A key performance indicator (KPI) is a metric selected because it indicates progress toward an important objective. Every KPI should have:

• a clear business purpose;
• a precise definition and calculation;
• an owner;
• a target or benchmark;
• a reporting frequency;
• a defined population and time window;
• a data source and quality rule; and
• an action or escalation associated with significant variation.

For example, a service-level KPI may be:

The denominator must exclude only cases justified by an approved business rule. Changing the denominator without disclosure can make performance appear better without improving service.

A dashboard should match its audience and decision horizon. An operational dashboard may show near-real-time exceptions, queue length and unresolved incidents. A managerial dashboard may show trends, targets, variance and business-unit comparison. A strategic dashboard may focus on long-term objectives, market position and financial sustainability.

Effective dashboards use a visual hierarchy: the most important information is prominent, trends are visible and exceptions are easy to identify. Labels should be explicit, units should be shown and colours should not be the only way of communicating meaning. Excessive gauges, three-dimensional charts, unexplained red-green coding and decorative graphics reduce clarity. A dashboard should answer a decision question rather than display every available measure.

Variance can be expressed as:

For a percentage variance, when the target is non-zero:

The sign must be interpreted in context. A negative cost variance may be favourable, while a negative revenue variance is usually unfavourable.

5. Decision-Support Models

A DSS generally contains four interacting components:

1. Data management: databases, metadata, data quality and access controls.
2. Model management: statistical, financial, optimisation or simulation models.
3. Knowledge or rule management: policies, thresholds and domain expertise.
4. User interface and communication: dashboards, forms, alerts, scenarios and explanations.

Decision support may be structured, semi-structured or unstructured. Reordering inventory according to a fixed rule is relatively structured. Choosing a new distribution centre is semi-structured because models assist but do not replace managerial judgement. Responding to an unprecedented crisis is largely unstructured.

A useful decision process is:

Frame the problem → identify alternatives → define criteria → analyse evidence → assess uncertainty → choose and implement → monitor outcomes.

Multi-criteria decision analysis (MCDA) is useful when alternatives must be evaluated against several criteria. A simple weighted-score model is:

where wᵢ is the weight of criterion i, rᵢⱼ is the standardised rating of alternative j on that criterion and weights normally sum to 1. Ratings must be placed on a comparable scale. The method makes preferences visible but does not eliminate judgement; results should be tested through sensitivity analysis.

Worked example: supplier selection

A retailer evaluates three suppliers using quality, cost and delivery reliability. The weights are 0.40, 0.35 and 0.25. Ratings are on a 1–10 scale.

For supplier A:

Score_A = (0.40 × 8) + (0.35 × 7) + (0.25 × 9) = 3.20 + 2.45 + 2.25 = 7.90

For supplier B:

Score_B = (0.40 × 9) + (0.35 × 8) + (0.25 × 7) = 3.60 + 2.80 + 1.75 = 8.15

For supplier C:

Score_C = (0.40 × 7) + (0.35 × 9) + (0.25 × 8) = 2.80 + 3.15 + 2.00 = 7.95

Supplier B has the highest weighted score. However, the decision should not be finalised without checking minimum quality requirements, contract risk, capacity and sensitivity to changes in the weights. If delivery reliability is strategically critical, increasing its weight could change the result.

6. Applied Case: NorthStar Homeware (Illustrative)

Background

NorthStar Homeware is a fictional regional retailer with 35 stores and an online channel. Management observes frequent stock-outs for popular products, while some slow-moving items occupy warehouse space. Sales, inventory and supplier data exist in separate systems.

Problem

The organisation wants to improve product availability without increasing total inventory. Existing reports show monthly sales but do not connect demand, lead time, promotion and store-level stock. Managers also disagree about the definition of “available”.

Analysis

The analytics team first defines the grain of an integrated dataset as one product–store–day record. It validates sales totals against the finance system, standardises product identifiers and distinguishes zero sales from missing records. The team creates descriptive measures for sales, stock-on-hand, stock-out days and gross margin. Diagnostic analysis reveals that stock-outs are concentrated in products with promotional campaigns and suppliers with variable lead times.

A forecast model estimates weekly demand using recent sales, seasonality and promotion indicators. Forecasts are evaluated on a holdout period rather than only on the training data. The team reports forecast error by product category because an aggregate accuracy figure hides poor performance for high-value items.

A replenishment decision model considers forecast demand, supplier lead time, available storage and a service-level target. It also includes a managerial rule that hazardous or restricted products require additional approval. The dashboard displays current stock, projected days of supply, expected stock-out risk, open purchase orders and recommended actions.

Approach and solution

NorthStar establishes a KPI catalogue. “Stock availability” is defined as the proportion of eligible store-product days on which at least one sellable unit is available, with exclusions documented. Data owners are assigned, refresh times are published and access is restricted according to job role. Store managers receive exception lists rather than a complex corporate dashboard.

Recommendations are presented with explanations: expected demand, lead-time assumption, current stock and constraints. Managers can override a recommendation, but the reason is recorded for later evaluation.

Outcome

In this illustrative case, the organisation reports fewer stock-out events during the pilot and a reduction in slow-moving inventory. The results are treated as provisional because the pilot period may coincide with seasonal changes. NorthStar therefore compares pilot stores with similar non-pilot stores and continues monitoring margin, customer satisfaction and supplier performance.

Learning points

The case demonstrates that analytics success depends on definitions, data integration, user adoption and feedback—not merely on selecting a sophisticated forecasting algorithm. A recommendation is valuable only when it is understandable, feasible and connected to an accountable action.

7. Evaluating Analytical Models

A model must be evaluated for both technical performance and business usefulness. Data should be separated into training and testing periods or samples when prediction is involved. For time-dependent data, random splitting can leak future information into the past; chronological validation is generally more appropriate.

Regression evaluation may use mean absolute error:

where yᵢ is the observed value and ŷᵢ is the prediction. MAE has the same units as the response and gives equal absolute weighting to errors. Root mean squared error is:

RMSE gives greater influence to large errors. Neither measure is universally superior; selection depends on the consequences of errors.

Evaluation should also consider calibration, fairness across groups, stability over time, interpretability, computational cost and the consequences of incorrect decisions. Monitoring after deployment is essential because data distributions, customer behaviour and business policies change.

8. Ethics, Governance, Security and Regulation

Analytics can expose personal, financial, health or behavioural information. Responsible practice includes purpose limitation, data minimisation, lawful processing, retention controls, access management, encryption, audit trails and secure disposal. Organisations must comply with applicable privacy and data-protection laws in their jurisdiction.

Important governance questions include:

• Who owns the data and approves its use?
• Can a person request access, correction or deletion where legally applicable?
• Is consent valid for the intended purpose?
• Are sensitive attributes being used directly or indirectly?
• Can an affected person understand and challenge an automated decision?
• Are model outputs being used outside their validated context?

Bias may enter through unrepresentative samples, historical discrimination, proxy variables, measurement error or unequal missingness. Removing a protected attribute does not guarantee fairness because other variables may act as proxies. Testing should examine performance and error rates across relevant groups, while respecting privacy and statistical reliability.

Security controls should follow least privilege, segregation of duties, strong authentication, logging and incident response. A dashboard can itself create risk if it exposes individual salaries, customer identities or commercially sensitive forecasts to unauthorised users.

9. Contemporary Developments and Best Practice

Cloud data platforms, self-service BI, streaming analytics, natural-language interfaces and machine learning are changing analytical practice. Generative AI can assist with query writing, summarisation and exploratory analysis, but generated content must be checked for factual accuracy, data leakage, unsupported conclusions and inappropriate access. AI should augment accountable decision-making rather than obscure responsibility.

Best practice includes:

1. Begin with a decision and measurable business outcome.
2. Establish a shared glossary for metrics and dimensions.
3. Make data lineage and assumptions visible.
4. Separate exploration from certified reporting.
5. Use appropriate validation and avoid leakage.
6. Present uncertainty, not only point estimates.
7. Design with accessibility and the user’s workflow in mind.
8. Monitor data quality, model drift and realised business outcomes.
9. Keep humans accountable for high-impact decisions.
10. Review models and dashboards periodically as conditions change.

Challenges and Limitations

BI programmes may fail because of fragmented systems, inconsistent definitions, poor data quality, insufficient skills, weak executive sponsorship or resistance to transparent measurement. Dashboards can encourage short-term optimisation when organisations focus only on visible KPIs. Predictive models can reproduce historical inequities, while prescriptive systems may produce infeasible recommendations. Data overload can also delay decisions rather than improve them.

Analytical results are conditional on their data, assumptions and context. A forecast is not a promise, a correlation is not causation and an optimal mathematical solution is not necessarily the best organisational decision. Professional judgement, stakeholder consultation and post-decision evaluation remain essential.

Chapter Summary

Business intelligence transforms organisational data into reports, measures and insight. Analytics extends BI through descriptive, diagnostic, predictive and prescriptive approaches. Reliable analysis begins with a well-framed decision, careful data preparation, explicit definitions and appropriate validation. Dimensional modelling, data lineage and quality controls support trustworthy reporting.

KPIs connect measurement to objectives, while dashboards communicate trends, targets, exceptions and required actions. Decision-support systems combine data, models, rules and human judgement. Regression, classification, forecasting, optimisation and multi-criteria scoring can support decisions, but their assumptions and limitations must be understood. Ethical, privacy, security and governance controls are essential, particularly when automated or AI-supported decisions affect individuals. The value of analytics is ultimately demonstrated through improved, monitored and accountable action.

Key Terms

Business intelligence: Technologies and practices for reporting, monitoring and analysing organisational performance.

Business analytics: Methods for examining data to explain events, predict outcomes and recommend actions.

Decision-support system: An interactive system combining data, models and interfaces to support decisions.

Descriptive analytics: Analysis that summarises what has happened or is happening.

Diagnostic analytics: Analysis that investigates causes, relationships and contributors.

Predictive analytics: Analysis that estimates future or unknown outcomes.

Prescriptive analytics: Analysis that recommends actions under objectives and constraints.

Key performance indicator: A metric selected to indicate progress toward an important objective.

Data warehouse: An integrated historical store designed primarily for analysis and reporting.

Data lake: A repository that can store large volumes of raw and processed data in varied formats.

Fact table: A dimensional-model table containing measurable business events.

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

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

Correlation: A statistical measure of association between variables, not proof of causation.

Regression: A modelling method for estimating relationships between a response and predictors.

Precision: The proportion of predicted positive cases that are actually positive.

Recall: The proportion of actual positive cases that are correctly detected.

Optimisation: The process of selecting the best feasible solution under a defined objective and constraints.

Model drift: Decline in model validity as data or relationships change over time.

Data governance: Policies, roles and controls that promote trustworthy and responsible data use.

Review Questions

Short Answer Questions

1. Distinguish business intelligence from business analytics.
2. What is the difference between a metric and a KPI?
3. Why must the grain of a fact table be declared?
4. Explain why correlation does not establish causation.
5. State two responsibilities of data governance.

Long Answer Questions

1. Explain the four types of analytics and give an organisational example of each.
2. Describe a complete workflow for preparing data for a management dashboard.
3. Discuss the design principles of an effective operational dashboard and compare them with those of a strategic dashboard.
4. Explain the components of a decision-support system and how they interact.
5. Critically evaluate the benefits and limitations of using AI in business analytics.

Analytical / Application Questions

1. A company’s actual monthly cost is 118,000 units of currency and its target is 125,000. Calculate the percentage variance and explain whether the result is favourable, taking account of the fact that this is a cost measure.
2. Design a KPI catalogue entry for delivery performance, including its definition, formula, data source, owner, frequency, target and escalation rule.
3. A retailer wants to reduce stock-outs without increasing inventory. Propose an analytics and decision-support solution, identifying data requirements, analytical methods, dashboard measures, constraints and governance risks.

Multiple Choice Questions with Answers

Question 1

Which question is primarily answered by descriptive analytics?

A. What happened?

B. What should we do under constraints?

C. Why did the event occur?

D. What will happen next?

Correct Answer: A. What happened?

Question 2

Which statement about correlation is correct?

A. It always proves that one variable causes another.

B. It measures association and does not by itself establish causation.

C. It can only have positive values.

D. It is unaffected by outliers.

Correct Answer: B. It measures association and does not by itself establish causation.

Question 3

A fact table in a star schema primarily contains:

A. Business measures and keys linking to dimensions

B. Only user passwords and access roles

C. Unstructured documents without identifiers

D. Dashboard colour definitions

Correct Answer: A. Business measures and keys linking to dimensions

Question 4

If actual revenue is 90,000 and target revenue is 100,000, the percentage variance is:

A. 10%

B. −10%

C. −90%

D. 90%

Correct Answer: B. −10%

Question 5

Recall is calculated as:

A. TP ÷ (TP + FP)

B. TN ÷ (TN + FN)

C. TP ÷ (TP + FN)

D. FP ÷ (TP + FP)

Correct Answer: C. TP ÷ (TP + FN)

Question 6

What is the principal purpose of a KPI?

A. To display every available data field

B. To indicate progress toward an important objective

C. To replace all managerial judgement

D. To guarantee that a target will be achieved

Correct Answer: B. To indicate progress toward an important objective

Question 7

In a weighted-score decision model, the weights should normally:

A. Be measured in currency units

B. Sum to 1 when expressed as proportions

C. All be negative

D. Be assigned after the preferred alternative is selected

Correct Answer: B. Sum to 1 when expressed as proportions

Question 8

Which practice is most appropriate when evaluating a time-dependent forecast?

A. Randomly mix future observations into the training period

B. Use chronological validation to reduce future-information leakage

C. Evaluate only on the training data

D. Delete all observations with seasonal patterns

Correct Answer: B. Use chronological validation to reduce future-information leakage

Question 9

Which measure is especially important for identifying duplicate records and repeated events?

A. Uniqueness

B. Colour contrast

C. Model intercept

D. Dashboard refresh speed only

Correct Answer: A. Uniqueness

Question 10

Why should a prescriptive analytics model be reviewed by domain experts?

A. Mathematical optimisation never produces a numerical result.

B. Experts can check whether objectives, constraints and recommendations reflect operational reality.

C. Domain experts eliminate the need for data quality controls.

D. Expert review guarantees that the model is unbiased.

Correct Answer: B. Experts can check whether objectives, constraints and recommendations reflect operational reality.