Unit 1 of 4 · MBA Sem 3

Unit 1: Data warehousing and BI

Data Mining for Business Decisions notes · PTU syllabus (MBA 941-18)

5 min read9 topics10 exam questions
On this page
  1. Unit summary
  2. Strategic information needs
  3. Operational vs informational data stores
  4. Data warehouse: definition and characteristics
  5. Structure of a data warehouse
  6. Introduction to business intelligence
  7. OLAP operations
  8. Data marts
  9. Dimensional modelling
  10. The ETL process
  11. Key terms
  12. Quick revision
  13. Important questions

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
1

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.

2

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.

ComparisonOLTP vs data warehouse
Operational systems (OLTP)
Informational systems (data warehouse, OLAP)

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).

3

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.

FrameworkCharacteristics of a data warehouse
  • 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.

4

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.

ProcessData warehouse architecture
  1. 1Source systems

    ERP, CRM, files, web logs, external data

  2. 2Staging area

    ETL — extract, clean, transform

  3. 3Data warehouse

    Integrated detailed and summary data plus metadata

  4. 4Data marts

    Departmental subsets

  5. 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.

5

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.

HierarchyBI value chain
  1. Decisions and action
  2. Insight

    Analytics, data mining, forecasting

  3. Information

    Reports, dashboards, OLAP

  4. 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.

6

Topic 6

OLAP operations

Online analytical processing (OLAP) lets users analyse multidimensional data (a cube) interactively.

ClassificationOLAP operations
OLAP cube
  • 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.

7

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).

8

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.

GraphStar schema: a fact table surrounded by dimensions
Sales (fact)TimeProductCustomerStore

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.
ClassificationStar schema for retail sales
Sales fact (amount, quantity, discount)
  • 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.

9

Topic 9

The ETL process

ETL is often the most time-consuming part of building a warehouse, because source data is messy and inconsistent.

ProcessETL
  1. 1Extract

    Pull data from source systems — full or incremental

  2. 2Transform

    Clean, standardise codes, de-duplicate, derive fields, aggregate, apply business rules

  3. 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

  1. Q1.Distinguish OLTP and data warehouse systems.
  2. Q2.State Inmon's definition of a data warehouse.
  3. Q3.What is metadata?
  4. Q4.Distinguish slice and dice.
  5. Q5.What is a data mart?
  6. Q6.Distinguish star and snowflake schemas.

Long-answer questions

  1. Q1.Explain the need for a data warehouse and compare operational and informational systems.
  2. Q2.Explain the characteristics and architecture of a data warehouse.
  3. Q3.Explain business intelligence and OLAP operations with examples.
  4. 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.

Planning your MBA in Ludhiana? See the MBA at SBS →

WhatsApp us