Synetic College Logo
Unit 2 of 4 · B.Sc IT Sem 5

Unit 2: Building a data warehouse

Data Warehousing & Mining notes · PTU syllabus (BSIT504/BSBC501)

3 min read10 topics10 exam questions
On this page
  1. Unit summary
  2. Building a data warehouse: considerations
  3. Data preprocessing
  4. Preprocessing: cleaning, integration, transformation and reduction
  5. Concept hierarchy
  6. The multidimensional data model
  7. Star, snowflake and fact-constellation schemas
  8. Data warehouse architecture and design
  9. OLAP three-tier architecture
  10. Cube computation
  11. Attribute-oriented induction
  12. Key terms
  13. Quick revision
  14. Important questions

Unit summary

Building a warehouse means designing schemas, preparing data and choosing an architecture. This unit covers design, technical and implementation considerations, data preprocessing — summarisation, cleaning and transformation — concept hierarchies, the multidimensional data model, star, snowflake and fact-constellation schemas, warehouse architecture and design, OLAP three-tier architecture, cube computation and attribute-oriented induction.

After this unit you can

  • Explain considerations in building a data warehouse
  • Preprocess data and use concept hierarchies
  • Design star, snowflake and fact-constellation schemas
  • Explain the three-tier architecture, cube computation and attribute-oriented induction

PTU syllabus topics

  • Design/technical/implementation considerations
  • data preprocessing (summarization, cleaning, transformation)
  • concept hierarchy
  • multidimensional data model
  • star/snowflake/fact-constellation schemas
  • data warehouse architecture and design
  • OLAP three-tier architecture
  • cube computation
  • attribute-oriented induction
ClassificationStar schema
Fact table: Sales

  • Time dimension

    Day, month, year

  • Product dimension

    Name, category, brand

  • Store dimension

    City, region

  • Customer dimension

    Age, segment

1

Topic 1

Building a data warehouse: considerations

ComparisonBuilding considerations
Questions to answer
Examples

Design considerations

What subjects, sources, granularity, history and metadata?

Daily sales at item level for five years

Technical considerations

Hardware, DBMS, storage, network capacity, parallelism, tools

Cloud warehouse, columnar storage

Implementation considerations

Access tools, data extraction and loading, cleaning, security, user training, phased rollout

Start with a sales data mart, then expand

  • Approaches: top-down (Inmon — enterprise warehouse first, then marts) and bottom-up (Kimball — marts first using conformed dimensions).
2

Topic 2

Data preprocessing

ProcessETL process
  1. 1Extract

    Pull data from ERP, CRM, spreadsheets, logs

  2. 2Clean

    Fix missing, noisy and inconsistent data

  3. 3Transform

    Standardise codes, convert units, aggregate, derive fields

  4. 4Load

    Initial full load, then periodic incremental refresh

3

Topic 3

Preprocessing: cleaning, integration, transformation and reduction

Data mining (knowledge discovery in databases, KDD) finds hidden, useful patterns in large data. Types: association, classification, clustering, prediction, outlier detection. Preprocessing prepares data: cleaning (missing values, noise), integration (combining sources), transformation (normalisation, aggregation) and reduction (fewer attributes or records). Attribute-oriented induction summarises data by generalising attribute values up a concept hierarchy (city → state → country) to produce concise descriptions.

Key termsPreprocessing techniques
Summarisation
Aggregate detailed data — daily to monthly totals
Cleaning: missing values
Ignore record, fill with mean or most probable value
Cleaning: noisy data
Binning, regression, clustering to smooth outliers
Transformation: normalisation
Min-max (scale to 0–1), z-score
Transformation: generalisation
Replace low-level values by higher concepts

Example

Min-max normalisation of income ₹73,600 when the range is ₹12,000–₹98,000: (73,600 − 12,000) ÷ (98,000 − 12,000) = 0.716.

4

Topic 4

Concept hierarchy

  • A concept hierarchy maps low-level values to higher-level concepts and drives roll-up and drill-down.
ClassificationConcept hierarchies
Hierarchies

  • Location

    Street → city → state → country

  • Time

    Day → month → quarter → year (or day → week)

  • Product

    Item → brand → category

  • Numeric (set-grouping)

    Age 0–17 young, 18–59 adult, 60+ senior

5

Topic 5

The multidimensional data model

  • Data is viewed as a data cube of dimensions (time, product, location — the perspectives of analysis) and facts or measures (sales amount, units sold — the numbers analysed).
  • An n-dimensional cube is a lattice of cuboids: the base cuboid holds the lowest level; the apex cuboid holds the grand total. With n dimensions and no hierarchies there are 2ⁿ cuboids.
6

Topic 6

Star, snowflake and fact-constellation schemas

ComparisonWarehouse schemas
Structure
Pros and cons

Star schema

One central fact table linked to denormalised dimension tables

Simple, fast joins; some redundancy

Snowflake schema

Dimension tables normalised into sub-tables (city → state)

Less redundancy, easier maintenance; more joins, slower

Fact constellation (galaxy)

Several fact tables sharing dimension tables

Models several business processes (sales and shipping); complex

Example

Star schema for sales: fact table SALES(time_key, item_key, branch_key, location_key, units_sold, rupees_sold) with dimension tables TIME, ITEM, BRANCH and LOCATION.

7

Topic 7

Data warehouse architecture and design

ProcessData warehouse architecture
  1. 1Data sources

    Operational databases, files, external data

  2. 2Staging area and ETL

    Extract, clean, transform

  3. 3Warehouse storage

    Enterprise warehouse, data marts, metadata repository

  4. 4OLAP servers

    MOLAP, ROLAP or HOLAP

  5. 5Front-end tools

    Reports, dashboards, mining

ProcessWarehouse design process
  1. 1Choose a business process to model
  2. 2Choose the grain — atomic level of the fact table
  3. 3Choose the dimensions
  4. 4Choose the measures
8

Topic 8

OLAP three-tier architecture

HierarchyThree-tier architecture
  1. Top tier: front-end clients

    Query, reporting, analysis and data mining tools — Power BI, Tableau, Excel

  2. Middle tier: OLAP server

    ROLAP or MOLAP engine presenting multidimensional views

  3. Bottom tier: warehouse database server

    Relational DBMS fed by ETL tools, with metadata repository

9

Topic 9

Cube computation

  • Cube computation precomputes aggregates (cuboids) so OLAP queries answer instantly.
ComparisonMaterialisation options
What is precomputed
Trade-off

No materialisation

Nothing — compute on demand

Slow queries, no extra storage

Full materialisation

Every cuboid

Fastest queries, huge storage

Partial materialisation

Selected cuboids, iceberg cubes (only cells above a threshold)

Balance of speed and space

  • Techniques: sorting, hashing and grouping to aggregate; computing higher cuboids from smaller computed ones; multiway array aggregation for MOLAP; the SQL CUBE operator (GROUP BY CUBE) computes all group-by combinations.
10

Topic 10

Attribute-oriented induction

ProcessAttribute-oriented induction
  1. 1Collect task-relevant data with a query
  2. 2Remove attributes with too many distinct values and no hierarchy
  3. 3Generalise remaining attributes up their concept hierarchies
  4. 4Merge identical generalised tuples and accumulate counts
  5. 5Present as a generalised relation, crosstab, chart or rules

Example

Graduate students' data generalised: birth place → country, GPA → "excellent" or "very good", age → "21–25"; result: 16 students "India, 21–25, excellent" — a concise description of the class.

Key terms

ETL
Extract, transform and load process
Concept hierarchy
Mapping of low-level values to higher concepts
Fact table
Table of measures with keys to dimensions
Star schema
Fact table surrounded by dimension tables
Iceberg cube
Cube storing only cells above a threshold

Quick revision

  • Design, technical and implementation considerations; top-down vs bottom-up.
  • ETL; cleaning, summarisation, normalisation; concept hierarchies.
  • Dimensions and measures; cuboids; 2ⁿ.
  • Star, snowflake, fact constellation; architecture and design steps.
  • Three-tier architecture; full, partial and no materialisation; attribute-oriented induction.

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.What is ETL?
  2. Q2.What is a concept hierarchy?
  3. Q3.Distinguish a fact and a dimension.
  4. Q4.Distinguish star and snowflake schemas.
  5. Q5.Name the three tiers of OLAP architecture.
  6. Q6.What is an iceberg cube?

Long-answer questions

  1. Q1.Explain the considerations in building a data warehouse.
  2. Q2.Explain data preprocessing techniques.
  3. Q3.Explain star, snowflake and fact-constellation schemas with diagrams.
  4. Q4.Explain the three-tier data warehouse architecture and cube computation.

Stuck on this unit?

Message SBS on WhatsApp for help with Data Warehousing & Mining, or to ask about studying B.Sc IT at Synetic.

WhatsApp us