Business Intelligence

Business Intelligence (BI) is the set of technologies, tools and processes used to collect, integrate, analyse and present business data so that organisations can make better decisions.

1. Faster, fact-based decisions, because managers get timely and accurate information instead of relying on guesses.
2. Better insight into trends, customers and performance, which helps identify opportunities and problems early.

A data warehouse is a central repository that is subject-oriented, integrated, time-variant and non-volatile. It stores historical data from multiple sources to support reporting, analysis and decision making.

ETL stands for Extract, Transform, Load: data is extracted from source systems, transformed (cleaned and converted) and then loaded into the data warehouse.

Data mining is the process of discovering useful patterns, relationships and knowledge from large datasets using statistical and machine learning techniques.

Classification: Decision Tree (others: Naive Bayes, k-NN). Clustering: K-Means.

Keep it simple and clear: choose a chart type that suits the data and message, avoid clutter and misleading scales, and label axes and titles so the audience understands it quickly.

Microsoft Power BI (others: Tableau, QlikView).

OLAP (Online Analytical Processing) is used for fast multidimensional analysis of large data, letting users slice, dice, drill down and roll up data (for example sales by product, region and time) to support decision making and reporting.

Metadata is "data about data". In BI it describes the source, structure, meaning, transformation rules, and lineage of data in the warehouse (for example table definitions, column meanings and ETL mappings), helping users and tools understand and manage the data.

ETL (Extract, Transform, Load) is the process that feeds a BI system with clean, consistent data.

• Extract: pulls data from heterogeneous sources such as ERP, CRM, spreadsheets, logs and web APIs.
• Transform: cleans the data (removes duplicates, handles missing values), standardises formats and codes, applies business rules, joins and aggregates, and assigns surrogate keys.
• Load: writes the prepared data into the data warehouse or data marts, either as a full load or as incremental loads on a schedule.

Role: ETL integrates scattered data into one trusted, historical store, so reports, dashboards and OLAP queries are accurate and fast. Without ETL, BI results would be inconsistent and unreliable.

A star schema is the simplest dimensional model for a data warehouse. A central fact table is directly joined to several denormalised dimension tables, so the diagram looks like a star.

Main components

• Fact table: holds numeric measures (e.g. sales amount, quantity) and foreign keys to every dimension. It is large and grows quickly.
• Dimension tables: hold descriptive attributes used to filter and group, such as Time, Product, Customer and Store. Each has a primary key (usually a surrogate key).
• Keys: the fact table's foreign keys together define its grain.

Advantages: simple queries with few joins, fast aggregation and easy for business users to understand. Disadvantage: dimension redundancy because of denormalisation.

Data preprocessing is the set of steps that prepare raw data for mining. Raw data is usually incomplete, noisy and inconsistent.

Main steps

• Data cleaning: fill or remove missing values, smooth noise, remove outliers and duplicates.
• Data integration: merge data from several sources and resolve conflicts.
• Data transformation: normalisation, aggregation and discretisation.
• Data reduction: attribute selection, sampling and dimensionality reduction.

Importance: the quality of mining results depends on the quality of the input ("garbage in, garbage out"). Preprocessing improves accuracy, reduces processing time and cost, and makes the data suitable for the chosen algorithm.

Supervised learning uses labelled data, where each training example has a known output. The model learns a mapping from inputs to outputs and predicts labels for new data. Tasks: classification and regression. Examples: decision trees, Naive Bayes, linear regression (e.g. spam detection).

Unsupervised learning uses unlabelled data. The model finds hidden structure or patterns without a target. Tasks: clustering, association rules and dimensionality reduction. Examples: k-means, Apriori (e.g. customer segmentation).

In short, supervised learning learns from known answers to predict, while unsupervised learning discovers patterns without answers.

1. Know the audience and purpose: show only the KPIs relevant to the users' decisions, and keep the dashboard focused on one goal.
2. Keep it simple and uncluttered: place the most important information at the top-left, use whitespace, limit the number of visuals and use consistent, limited colours.
3. Choose the right visualisation: use line charts for trends, bars for comparisons and tables for exact values. Avoid 3D or decorative effects, and add clear labels, titles and filters or drill-down for interactivity.

• Data quality and integration: data is scattered across silos, inconsistent, duplicated or incomplete, which makes integration difficult.
• High cost and ROI uncertainty: licences, infrastructure and skilled staff are expensive, and benefits are hard to measure.
• Lack of clear business objectives and executive support: projects fail when requirements and KPIs are not defined.
• Skill shortage and user adoption: a shortage of BI and data skills, and resistance to change from users.
• Security, privacy and governance: protecting sensitive data and defining ownership and standards.
• Scalability and tool selection: handling growing data volumes and choosing tools that fit existing systems.

A typical data warehouse uses a multi-layer (three-tier) architecture.

1. Data source layer: operational databases (OLTP), ERP/CRM, flat files and external data. These supply the raw data.

2. Staging and ETL layer: data is extracted into a staging area, then cleaned, transformed and integrated, and loaded into the warehouse on a schedule.

3. Data storage layer: the central data warehouse holds integrated, subject-oriented, time-variant and non-volatile data, organised in star or snowflake schemas. Data marts hold departmental subsets. A metadata repository describes sources, mappings and definitions.

4. OLAP / analytics layer: an OLAP server (ROLAP, MOLAP or HOLAP) provides multidimensional views and fast aggregation.

5. Presentation (front-end) layer: reporting, dashboards, query tools and data mining tools used by business users.

Flow: sources, then ETL, then warehouse and marts, then OLAP, then reports and dashboards. Management and security services (monitoring, backup, access control) support all layers.

Both are supervised classification methods, but they work differently.

Decision tree: builds a tree of attribute tests (chosen using information gain, gain ratio or Gini index). Each leaf gives a class label.

Naive Bayes: a probabilistic classifier based on Bayes' theorem. It assumes attributes are conditionally independent given the class:

$P(C\mid X)\propto P(C)\prod_i P(x_i\mid C)$

It predicts the class with the highest posterior probability.

Decision tree strengths: easy to understand and visualise, handles numeric and categorical data, needs little preparation. Weaknesses: prone to overfitting (needs pruning), unstable with small data changes, biased toward attributes with many values.

Naive Bayes strengths: simple, fast, scales to high dimensions, works well with small training sets and text classification. Weaknesses: the independence assumption is often unrealistic, and zero-frequency problems need smoothing (Laplace).

Association rule mining finds interesting relationships between items in large transaction databases, e.g. market basket analysis. A rule has the form $A \Rightarrow B$, where $A$ and $B$ are disjoint itemsets.

Support: the fraction of transactions containing both $A$ and $B$.

$\text{support}(A\Rightarrow B)=\frac{\text{transactions containing }A\cup B}{\text{total transactions}}$

Confidence: how often $B$ appears in transactions that contain $A$.

$\text{confidence}(A\Rightarrow B)=\frac{\text{support}(A\cup B)}{\text{support}(A)}$

Rules are accepted when they meet minimum support and minimum confidence thresholds. Algorithms such as Apriori first find frequent itemsets, then generate rules from them. Lift is often used to check that the rule is more than chance.

Example: suppose there are 100 transactions. 20 contain bread, and 15 of those also contain butter. For the rule $\{\text{bread}\}\Rightarrow\{\text{butter}\}$:

$\text{support}=\frac{15}{100}=15\%,\qquad \text{confidence}=\frac{15}{20}=75\%$

So 75% of customers who buy bread also buy butter, which can guide shelf placement and promotions.

Purpose: give managers a one-screen view of monthly sales performance, with the most important figures first and details lower down.

Layout and justification

1. Top row, KPI cards (read first, top-left):

   • Total revenue (vs target and vs last month)
   • Number of orders
   • Average order value (AOV)
   • Conversion rate
   • Gross margin %
   • Return/cancellation rate
   • New vs repeat customers

   Cards give an instant health check, with arrows or colours showing variance against target.

2. Middle left, line chart of daily/weekly revenue for the month against the previous month or last year: shows trend and seasonality, and spikes from campaigns.

3. Middle right, gauge or bullet chart of revenue vs monthly target: shows progress to goal at a glance.

4. Lower left, bar chart of sales by product category / top 10 products: supports comparison and inventory and promotion decisions.

5. Lower middle, map or bar chart by region/city: reveals geographic performance and where to focus delivery or marketing.

6. Lower right, pie/donut or stacked bar of sales by channel (organic, ads, email, social), plus a funnel of visits to cart to purchase: shows which channels and stages need improvement.

7. Bottom, detail table of top customers or orders, with filters (date, category, region, channel) and drill-down.

Design notes: consistent colours (e.g. green for good, red for below target), minimal clutter, and interactive filters. Summary information at the top, then trends, then breakdowns, then details, follows the way decision makers read and lets them move from "what happened" to "why".

Goal: improve sales forecasting and inventory planning by giving planners a single, trusted, timely view of sales, stock and demand drivers.

1. Data sources

• Internal: POS transactions, ERP inventory and purchasing, CRM/loyalty data, e-commerce logs, supplier lead times.
• External: holidays and promotions calendar, weather, competitor prices, local economic data.

Chosen because forecasting needs history of sales together with stock levels and demand drivers.

2. ETL strategy

• Extract nightly (incremental, using change data capture for large tables) and use near-real-time feeds for POS if needed.
• Stage the raw data, then clean (duplicates, missing values, outliers, returns), standardise product, store and date codes, and apply business rules.
• Load into the warehouse with surrogate keys and slowly changing dimension handling (Type 2 for price/product changes).
• Add logging, error handling and data quality checks.

Why: ETL gives consistent, integrated data, and incremental loads keep cost and load time low.

3. Data modelling

• A central data warehouse with star schemas: Sales fact (quantity, revenue, discount, cost) and Inventory snapshot fact (stock on hand, reorder level).
• Dimensions: Date, Product, Store, Customer, Promotion, Supplier.
• Data marts for sales, inventory and marketing.

Why: the star schema gives fast, simple queries and supports OLAP slicing by time, product and store. Forecasting features (lagged sales, seasonality) are built from this layer.

4. Analytics and BI tool choice

• A mainstream BI tool (e.g. Power BI, Tableau) for self-service reporting, chosen for cost, ease of use and integration with the existing database and Excel.
• Forecasting with time-series methods (moving average, exponential smoothing/ARIMA) or regression/machine learning in R/Python, with results written back to the warehouse.
• Inventory rules: safety stock, reorder points and economic order quantity driven by forecasts and lead times.

5. Visualisation and dashboards

• Executive dashboard: revenue, margin, forecast vs actual, top categories.
• Inventory dashboard: stock-outs, overstock, days of cover, reorder alerts.
• Store/category dashboards with drill-down and filters, and scheduled reports and alerts.

Why: different roles need different views, and alerts turn insight into timely action.

6. Governance

• Data owners and stewards, a data dictionary and metadata, and agreed KPI definitions.
• Data quality monitoring, role-based access, encryption and privacy compliance for customer data.
• Change management, training and a BI steering committee to prioritise requests.

Integration and business value

Sources flow through ETL into the warehouse; models and forecasts read from it; dashboards present the results to users; governance wraps all layers so numbers are trusted. Value: more accurate forecasts, fewer stock-outs and less overstock (lower holding cost), better promotions and faster decisions. Success is measured by forecast accuracy (e.g. MAPE), stock turnover and lost-sales reduction. A phased rollout starting with one product category lowers risk.

Implementation best practices

• Align with business and clinical goals: define clear objectives and KPIs (e.g. readmission rate, waiting time, bed occupancy) with executive sponsorship.
• Involve clinicians and users early: gather requirements from doctors, nurses, administrators and finance staff to ensure adoption.
• Strong data governance: assign data owners and stewards, standard definitions, a master patient index and metadata management.
• Privacy and security by design: role-based access, encryption, audit trails, de-identification and compliance with health-data laws (e.g. HIPAA-like rules).
• Data quality first: profile, clean and validate data before analysis.
• Iterative delivery: start small with high-value use cases, deliver quickly and improve with feedback.
• Scalable architecture, training and change management: a flexible warehouse or lakehouse, user training and support.

Potential challenges

Technical

• Integrating many systems (EHR/EMR, lab, radiology, billing, pharmacy) with different formats and standards (HL7, FHIR).
• Real-time data needs, performance and scalability.
• Legacy systems and unstructured data (clinical notes, images).

Organizational

• Resistance to change and limited time of clinicians.
• Lack of skilled BI/data staff and unclear ownership.
• High cost and difficulty showing ROI; competing priorities.

Data-related

• Missing, inconsistent or duplicate patient records and coding differences.
• Privacy, consent and ethical concerns.
• Lack of common definitions for metrics.

Phased implementation plan

This phased, governance-led approach reduces risk, builds user trust and delivers value early while protecting patient data.