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

Unit 2: Building a data warehouse

Data Warehousing and Data Mining notes · PTU syllabus (PGCA1941)

3 min read9 topics10 exam questions
On this page
  1. Unit summary
  2. Building a data warehouse: considerations
  3. Data preprocessing: summarisation, cleaning and transformation
  4. The ETL process
  5. The multidimensional data model
  6. Star, snowflake and fact-constellation schemas
  7. Data warehouse architecture and design
  8. OLAP three-tier architecture
  9. Cube computation
  10. Data marts
  11. Key terms
  12. Quick revision
  13. Important questions

Unit summary

Building a warehouse involves design choices, clean data and a suitable schema. This unit covers design, technical and implementation considerations, data preprocessing, ETL, the multidimensional model, star, snowflake and fact-constellation schemas, OLAP three-tier architecture, cube computation and data marts.

After this unit you can

  • State design, technical and implementation considerations
  • Explain preprocessing and the ETL process
  • Design star, snowflake and fact-constellation schemas
  • Explain three-tier OLAP architecture, cube computation and data marts

PTU syllabus topics

  • Design
  • technical and implementation considerations
  • data preprocessing (summarization, cleaning, transformation)
  • ETL process
  • multidimensional data model
  • star/snowflake/fact-constellation schemas
  • data warehouse architecture and design
  • OLAP three-tier architecture
  • cube computation
  • data marts
ProcessETL process
  1. 1Extract

    From source systems

  2. 2Clean

    Fix errors, remove duplicates

  3. 3Transform

    Standardise, aggregate

  4. 4Load

    Into the data warehouse

  5. 5Refresh

    On a schedule

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: summarisation, cleaning and transformation

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.

3

Topic 3

The ETL process

ProcessETL
  1. 1Extract

    Pull data from source systems — full or incremental (change data capture)

  2. 2Transform

    Clean, de-duplicate, standardise codes, convert units, derive and aggregate, apply surrogate keys

  3. 3Load

    Initial load, then periodic incremental loads; refresh indexes and aggregates

  • ELT variant loads raw data first and transforms inside a cloud warehouse.
  • Tools: Informatica, SSIS, Talend, Pentaho, Apache NiFi, Azure Data Factory.
4

Topic 4

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

Topic 5

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.

6

Topic 6

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
7

Topic 7

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

8

Topic 8

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

Topic 9

Data marts

ComparisonData marts
Dependent
Independent

Source

Fed from the enterprise warehouse

Fed directly from operational sources

Consistency

Consistent with the enterprise view

Risk of conflicting numbers ("data silos")

Speed to build

Needs the warehouse first

Quick, cheap start

  • Top-down (Inmon): build the enterprise warehouse, then dependent marts. Bottom-up (Kimball): build conformed-dimension marts that together form the warehouse.

Key terms

ETL
Extract, transform and load
Fact table
Table of numeric measures with foreign keys to dimensions
Snowflake schema
Star schema with normalised dimensions
Data cube
Multidimensional view of measures
Dependent data mart
Mart fed from the enterprise warehouse

Quick revision

  • Design, technical and implementation considerations.
  • Cleaning, integration, transformation, reduction; ETL and ELT.
  • Multidimensional model; star, snowflake, fact constellation.
  • Three-tier OLAP; cube computation; dependent and independent marts; Inmon vs Kimball.

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 data cleaning?
  3. Q3.Distinguish star and snowflake schemas.
  4. Q4.What is a fact constellation?
  5. Q5.How many cuboids does a 3-D cube have?
  6. Q6.Distinguish dependent and independent data marts.

Long-answer questions

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

Stuck on this unit?

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

WhatsApp us