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
Topic 1
Strategic information needs
Operational systems answer day-to-day questions ("Has this order shipped?"); strategic decisions need a different kind of information that brings data together across functions and over time.
- 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?
Example
A strategic question: a retail chain's CEO asks, "Which product categories have grown fastest in tier-2 cities over the last three years, and how does that compare with margin?" Answering it needs sales, product, store and finance data combined over three years — something billing systems alone cannot provide.
Exam tip
Link this to why data warehouses were created.
Topic 2
Operational vs informational data stores
Operational systems (OLTP) run the business; informational systems (data warehouses, OLAP) help analyse the business. Mixing the two slows down transactions and gives inconsistent reports.
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.
Example
Two systems, two jobs: a bank's core banking system records every deposit in real time (OLTP). Every night, the data is copied into a warehouse where analysts study five years of deposit trends by branch and customer segment (OLAP) without slowing down the live system.
Exam tip
Compare them in a table: purpose, users, data (current vs historical), queries (simple vs complex) and updates (frequent vs periodic loads).
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.
Example
The four characteristics in a telecom warehouse: data is organised around customers, calls and plans (subject-oriented); billing, CRM and network data use the same customer ID (integrated); it keeps five years of monthly records (time-variant); and loaded data is not changed by daily operations (non-volatile).
Exam tip
Learn Inmon's definition word for word — it is a common short question.
Topic 4
Structure of a data warehouse
A typical architecture has source systems, a staging area where ETL takes place, the central warehouse, data marts, metadata and front-end tools for reporting, OLAP and data mining.
- 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.
Example
Data flow in a retail warehouse: sales from point-of-sale systems, stock data from ERP and customer data from the loyalty app are extracted to a staging area, cleaned and loaded into the warehouse, and then fed to a sales data mart used by regional managers' dashboards.
Exam tip
Draw the architecture from sources to users.
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.
Example
BI in action: a pharmacy chain's dashboard shows daily sales by store and product. Managers notice that a cough syrup is selling out in northern stores each winter and adjust stock before demand peaks.
Exam tip
BI tools include reports, dashboards, OLAP and data mining; examples are Power BI and Tableau.
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 marts are dependent (built from the central warehouse) or independent (built directly from source systems). They are faster and cheaper to build but can create inconsistent data if built independently.
- 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.
Example
A marketing data mart: the marketing team needs only campaign, customer and sales data. A data mart with just these subjects lets them run reports quickly without searching the whole enterprise warehouse.
Exam tip
Compare Inmon's top-down approach (warehouse first) with Kimball's bottom-up approach (data marts first).
Topic 8
Dimensional modelling
Dimensional modelling organises data into fact tables (numeric measures such as sales amount and quantity) and dimension tables (descriptive context such as date, product, store and customer). The star schema has one fact table joined directly to dimensions; the snowflake schema normalises dimensions further.
The fact table holds measures (sales amount, quantity); dimension tables hold descriptive attributes
- 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).
Example
A sales star schema: the fact table records quantity and revenue for each sale, linked to Date, Product, Store and Customer dimensions. A manager can then ask, "Revenue from shampoo in Ludhiana stores in October" easily.
Exam tip
Draw a star schema — it is one of the most frequently asked diagrams.
Topic 9
The ETL process
ETL is often the most time-consuming part of building a warehouse, because source data is messy and inconsistent.
- 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.
Example
Transforming data: one source records gender as M/F, another as 1/2, and a third as Male/Female. During transformation, all are standardised to one code, duplicate customer records are merged, and missing pin codes are flagged before loading.
Exam tip
Write the three steps with two examples of transformation each.
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.
