Unit 1: Data warehousing and BI
Data Mining for Business Decisions notes · PTU syllabus (MBA 941-18)
On this page
Unit summary
Before data can be mined it must be collected, integrated and stored for analysis. This unit covers strategic information needs, operational vs informational data stores, the definition, characteristics, role and structure of a data warehouse, an introduction to business intelligence, OLAP operations, data marts, dimensional modelling and the ETL process.
After this unit you can
- Explain strategic information needs and distinguish operational and informational systems
- Explain the characteristics, role and structure of a data warehouse
- Explain business intelligence, OLAP operations and data marts
- Explain dimensional modelling and the ETL process
PTU syllabus topics
- Strategic information needs
- operational vs informational data stores
- data warehouse definition/characteristics/role/structure
- introduction to business intelligence
- OLAP operations
- data marts
- dimensional modeling and ETL process
Roll-up
Summarise: city to region
Drill-down
More detail: year to month
Slice
Fix one dimension
Dice
Select a sub-cube
Pivot
Rotate the view
Topic 1
Strategic information needs
- Executives need integrated, consistent, historical and summarised information to spot trends, compare performance and plan strategy — not just day-to-day transaction records.
- Problems with operational data for strategy: scattered across systems, inconsistent codes, only current values, not designed for complex queries.
- Examples of strategic questions: Which customer segments are most profitable over five years? How did a price change affect regional sales? Which products are bought together?
Topic 2
Operational vs informational data stores
Purpose
Run day-to-day business transactions
Support analysis and decisions
Data
Current, detailed, constantly updated
Historical, summarised and detailed, read-mostly
Design
Normalised for fast inserts and updates
Denormalised (star schema) for fast queries
Users
Clerks, operational staff (many)
Managers and analysts (fewer)
Queries
Simple, predictable
Complex, ad hoc
Example
Bank's core banking system
Bank's customer profitability warehouse
- Operational data store (ODS): integrated, current-valued data from several operational systems, used for operational reporting — between OLTP and the warehouse.
Topic 3
Data warehouse: definition and characteristics
Bill Inmon: a data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process.
Subject-oriented
Organised around subjects — customer, product, sales — not applications
Integrated
Consistent names, codes and units from many sources
Time-variant
Holds historical snapshots with time stamps
Non-volatile
Loaded and read; not updated in place
- Role: single version of the truth, historical analysis, faster reporting, foundation for BI, data mining and AI.
Topic 4
Structure of a data warehouse
- 1Source systems
ERP, CRM, files, web logs, external data
- 2Staging area
ETL — extract, clean, transform
- 3Data warehouse
Integrated detailed and summary data plus metadata
- 4Data marts
Departmental subsets
- 5Access tools
Reports, OLAP, dashboards, data mining
- Metadata: "data about data" — source, meaning, transformations, refresh schedules.
- Approaches: Inmon (top-down) — enterprise warehouse first, then data marts; Kimball (bottom-up) — build dimensional data marts linked by conformed dimensions.
- Modern options: cloud data warehouses (Snowflake, Google BigQuery, Amazon Redshift, Azure Synapse) and data lakes or lakehouses for unstructured data.
Topic 5
Introduction to business intelligence
Business intelligence (BI) is the set of processes, technologies and tools that turn data into information and insight for business decisions.
- Decisions and action
- Insight
Analytics, data mining, forecasting
- Information
Reports, dashboards, OLAP
- Data
Warehouse, marts, sources
- BI tools: reporting (SSRS, Crystal), OLAP, dashboards (Power BI, Tableau, Qlik), data mining and predictive analytics, self-service BI.
Topic 6
OLAP operations
Online analytical processing (OLAP) lets users analyse multidimensional data (a cube) interactively.
Roll-up (drill-up)
Aggregate to a higher level — city to state
Drill-down
Go to more detail — year to quarter to month
Slice
Fix one dimension — sales for 2025 only
Dice
Select a sub-cube — 2025, North region, electronics
Pivot (rotate)
Swap rows and columns to view from another angle
- Types: MOLAP (multidimensional storage, fast), ROLAP (relational storage, scalable), HOLAP (hybrid).
Example
A sales manager starts with annual sales by region, drills down to months in the North region, then slices to show only the electronics category.
Topic 7
Data marts
- Data mart: a subset of the warehouse focused on one department or subject — sales, finance, HR.
- Dependent (fed from the enterprise warehouse) vs independent (built directly from sources — quicker but risks inconsistency).
- Benefits: faster, cheaper, tailored to users; risk: data silos if not built on conformed dimensions.
Topic 8
Dimensional modelling
- Fact table: numeric measures of a business process (sales amount, quantity) with foreign keys to dimensions.
- Dimension tables: descriptive context — date, product, customer, store.
Date dimension
Day, month, quarter, year, festival flag
Product dimension
SKU, brand, category
Store dimension
Store, city, state, format
Customer dimension
Age group, loyalty tier
- Snowflake schema: dimensions normalised into sub-tables — saves space but needs more joins. Fact constellation (galaxy): several fact tables sharing dimensions.
- Grain: the level of detail of one fact row — decide it first.
- Slowly changing dimensions: Type 1 (overwrite), Type 2 (add a new row with dates), Type 3 (add a column for the previous value).
Topic 9
The ETL process
- 1Extract
Pull data from source systems — full or incremental
- 2Transform
Clean, standardise codes, de-duplicate, derive fields, aggregate, apply business rules
- 3Load
Initial load, then periodic refresh into the warehouse
- ELT: load raw data first, transform inside the cloud warehouse — common with modern platforms.
- Tools: Informatica, Talend, SSIS, Azure Data Factory, dbt.
- Data quality checks: completeness, accuracy, consistency, timeliness, uniqueness.
Key terms
- OLTP
- Systems for processing day-to-day transactions
- Data warehouse
- Subject-oriented, integrated, time-variant, non-volatile data store
- OLAP
- Interactive multidimensional analysis
- Star schema
- Fact table surrounded by dimension tables
- ETL
- Extract, transform and load
Quick revision
- Strategic information needs; OLTP vs warehouse; ODS.
- Inmon's four characteristics; architecture; Inmon vs Kimball; cloud warehouses.
- BI: data → information → insight → action.
- OLAP: roll-up, drill-down, slice, dice, pivot; MOLAP, ROLAP, HOLAP.
- Data marts; star, snowflake, galaxy; grain; SCD types; ETL and ELT.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.Distinguish OLTP and data warehouse systems.
- Q2.State Inmon's definition of a data warehouse.
- Q3.What is metadata?
- Q4.Distinguish slice and dice.
- Q5.What is a data mart?
- Q6.Distinguish star and snowflake schemas.
Long-answer questions
- Q1.Explain the need for a data warehouse and compare operational and informational systems.
- Q2.Explain the characteristics and architecture of a data warehouse.
- Q3.Explain business intelligence and OLAP operations with examples.
- Q4.Explain dimensional modelling and the ETL process.
Stuck on this unit?
Message SBS on WhatsApp for help with Data Mining for Business Decisions, or to ask about studying MBA at Synetic.