Data Mining and Warehousing (AD-603 (A)) Unit 1 Notes

How unit 1 is examined

Covers what a data warehouse is, its three-tier architecture, preprocessing, schemas, data marts and metadata; the three-tier architecture, warehouse versus operational database, preprocessing, data marts and metadata carry the marks.

Data Warehousing: Introduction, Delivery Process, Data warehouse Architecture

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">High weight</span>

Definition. <mark>A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data that supports management's decision making (Inmon).</mark>

Key points.

Subject-oriented means the data is organised around major subjects such as customer, product and sales, not around day-to-day applications.
Integrated means data from many sources is cleaned and made consistent in naming, units and encoding before it is stored.
Time-variant means the warehouse holds historical data (typically 5-10 years), so every record carries a time element.
Non-volatile means data is loaded and read but not updated or deleted in place, so only load and read operations exist.
The delivery process runs in stages: IT strategy, business case, education and prototyping, business requirements, technical blueprint, build the version, history load, ad hoc query, automation, and extending the scope.
Architecture is three-tier: the bottom tier is the warehouse database server fed by ETL (extract, clean, transform, load) from operational databases and external sources.
The middle tier is the OLAP server, either ROLAP (relational back end) or MOLAP (multidimensional array), which presents multidimensional views of the data.
The top tier holds front-end client tools for query, reporting, analysis and data mining.

Diagram.

<figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u1-01" viewBox="0 0 534.2 80" width="534.2" height="80" role="img" aria-label="Three-tier architecture. Src = operational databases and external sources; ETL = extract, clean, transform, load; DW = warehouse database with data marts and metadata (bottom tier); OLAP = ROLAP/MOLAP server (middle tier); Cli = query, reporting, analysis and mining tools (top tier)"><style>#dsfig-u1-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u1-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u1-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u1-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u1-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u1-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u1-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u1-01 .t{fill:#16181D;font-weight:500}#dsfig-u1-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u1-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u1-01 .dot{fill:#16181D}#dsfig-u1-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u1-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u1-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u1-01 .ah{fill:#454C5A}#dsfig-u1-01 .ah.hi{fill:#2340B8}#dsfig-u1-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u1-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u1-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u1-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u1-01 .e{stroke:#B1B7C3}html.dark #dsfig-u1-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u1-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u1-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u1-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u1-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u1-01 .t{fill:#E6E8ED}html.dark #dsfig-u1-01 .t.inv{fill:#0F1115}html.dark #dsfig-u1-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u1-01 .dot{fill:#E6E8ED}html.dark #dsfig-u1-01 .ann{fill:#8FA3FF}html.dark #dsfig-u1-01 .lbl{fill:#858D9C}html.dark #dsfig-u1-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u1-01 .ah{fill:#B1B7C3}html.dark #dsfig-u1-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u1-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u1-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u1-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah1" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh1" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M59,40 L130.8,40" marker-end="url(#ah1)"/><path class="e" d="M170.8,40 L242.6,40" marker-end="url(#ah1)"/><path class="e" d="M282.6,40 L347.4,40" marker-end="url(#ah1)"/><path class="e" d="M401.4,40 L466.2,40" marker-end="url(#ah1)"/><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Src</text><circle class="n" cx="151.8" cy="40" r="18"/><text class="t" x="151.8" y="40" dy=".35em" text-anchor="middle">ETL</text><circle class="n" cx="263.6" cy="40" r="18"/><text class="t" x="263.6" y="40" dy=".35em" text-anchor="middle">DW</text><rect class="n" x="350.4" y="25" width="50" height="30" rx="15"/><text class="t" x="375.4" y="40" dy=".35em" text-anchor="middle">OLAP</text><circle class="n" cx="487.2" cy="40" r="18"/><text class="t" x="487.2" y="40" dy=".35em" text-anchor="middle">Cli</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Three-tier architecture. Src = operational databases and external sources; ETL = extract, clean, transform, load; DW = warehouse database with data marts and metadata (bottom tier); OLAP = ROLAP/MOLAP server (middle tier); Cli = query, reporting, analysis and mining tools (top tier)</figcaption></figure>

Comparison (operational database vs data warehouse).

Basis	Operational database (OLTP)	Data warehouse (OLAP)
Orientation	Transaction and application oriented	Subject oriented, analysis oriented
Users	Clerks, DBAs, many concurrent users	Managers, analysts, fewer users
Data	Current, detailed, isolated	Historical, summarised, integrated
Time span	Current, days to months	5-10 years of history
Operations	Short read-write transactions	Mostly complex read-only queries
Design	Entity-relationship, normalised	Star or snowflake, denormalised
Example	Bank deposit, ticket booking	Sales trend analysis over years

Warehouse and mining. A warehouse is not a prerequisite for data mining, because mining can run on any data set, but it helps greatly. It supplies clean, integrated, consolidated data, saves preprocessing effort, and gives multidimensional summaries to drill into. It enables OLAM (OLAP mining), where mining runs interactively on cubes with roll-up and drill-down. Example: mining sales patterns per region and quarter from a retail warehouse.

Answer frame. For the architecture question, open with the definition and the three tiers; draw the diagram above with full names; describe bottom, middle, then top tier and the ETL flow; close by saying it separates storage, processing and presentation. For the comparison, open with one line defining OLTP and warehouse, draw the table, close with an example. For the mining question, answer "not a prerequisite, but very helpful", give the ways, and close with OLAM.

Pitfall: Writing only two tiers or naming the tiers without saying what each holds loses marks.

Asked: [7 marks] (May 2023, Jun 2025) Describe 3-tier architecture of data warehouse with a neat sketch. Asked: [7 marks] (May 2024) Compare and contrast operational database systems with data warehouse. Asked: [7 marks] (May 2024) Is the data warehouse prerequisite for data mining? Does the data warehouse help data mining? If so, in what ways?

Data Pre-processing: Data cleaning, Data Integration and transformation, Data reduction

Definition. <mark>Data preprocessing is the set of techniques that converts raw, noisy, incomplete and inconsistent data into clean, consistent data suitable for warehousing and mining.</mark>

Key points.

Need: real data is incomplete, noisy and inconsistent, and poor-quality data gives poor mining results (garbage in, garbage out).
Data cleaning fills missing values (ignore the tuple, use a global constant, the attribute mean or the most probable value), smooths noise (binning, regression, clustering) and removes outliers and inconsistencies.
Data integration merges data from several sources, resolving schema conflicts, redundancy (detected by correlation analysis) and differing value representations.
Data transformation converts data into suitable forms through smoothing, aggregation, generalisation, normalisation (min-max, z-score, decimal scaling) and attribute construction.
Data reduction gives a smaller representation that yields nearly the same analytical results, using data cube aggregation, attribute subset selection, dimensionality reduction, numerosity reduction and discretisation.
Dimensionality reduction cuts the number of attributes: PCA projects data onto a few principal components that capture the most variance, and wavelet transform keeps only the strongest coefficients. Attribute subset selection instead drops irrelevant or redundant attributes.
Min-max normalisation is $v' = \dfrac{v-\min}{\max-\min}$; for example, income 73,600 in the range 12,000-98,000 maps to 0.716.

Answer frame. Open with the definition and the need; list the four steps cleaning, integration, transformation, reduction in that order with two techniques each; for the dimensionality reduction question give PCA, wavelet and attribute subset selection with their benefits (less storage, faster mining); close with the min-max example.

Asked: [7 marks] (May 2024, Jun 2025) Explain in detail about data preprocessing. What are the steps involved in data preprocessing? Explain about dimensionality reduction technique.

Data warehouse Design: schema and partitioning

Definition. A warehouse schema is the logical layout of fact and dimension tables; partitioning splits a large table into smaller parts.

Key points.

A star schema has one central fact table joined directly to denormalised dimension tables, so queries need few joins.
A snowflake schema normalises the dimension tables into sub-tables, which saves space but needs more joins.
A fact constellation (galaxy) schema has several fact tables sharing dimension tables.
Partitioning improves manageability and performance; horizontal partitioning splits by rows (usually by time), vertical by columns.

Data warehouse Implementation, Data Marts, Meta Data

Definition. <mark>A data mart is a subset of the warehouse focused on one department or subject, and metadata is data about the data in the warehouse.</mark>

Key points.

Implementation covers building the warehouse database, running ETL, indexing and materialising views for speed, and choosing ROLAP or MOLAP.
Data marts are of three types: dependent (built from the warehouse), independent (built directly from sources) and hybrid.
A data mart is important because it is smaller and faster, so queries for one department (sales, finance) respond quickly.
Data marts give departments focused access and control, cost less and are quicker to build than an enterprise warehouse.
In architecture the marts sit between the warehouse and client tools, serving OLAP and decision support locally.
Metadata describes the warehouse: operational metadata (source data, lineage, history), extraction and transformation metadata (ETL rules, load schedules), and end-user metadata (business names and definitions of data).
Metadata speeds retrieval by mapping business terms to tables and locating summaries, guides ETL through the mapping rules, and helps analysis by explaining what each measure means.
It also holds the schema, dimension hierarchies and access rules, so it is the map for data management.

Answer frame. For data marts, open with the definition, give the three types, then benefits (performance, access, cost, focus), and close with their place in OLAP and decision support. For metadata, open with "data about data", list the three types, then retrieval, transformation and analysis roles, and close with schema management.

Asked: [7 marks] (May 2024) What is the importance of data marts in data warehouse? Asked: [7 marks] (Jun 2025) Discuss metadata in the context of data warehousing and its role in data management; explain how it supports retrieval, transformation and analysis.

Example of a Multidimensional Data model

Definition. A multidimensional model views data as a cube, where dimensions (time, item, location) index cells that hold measures such as sales amount.

Key points.

A sales cube with dimensions time, item and location stores the measure dollars_sold in each cell.
Each dimension has a concept hierarchy, for example day, month, quarter, year.
A base cuboid holds the lowest level of detail, and the apex cuboid holds the total; together the cuboids form a lattice.
It is implemented as a star schema with a fact table for measures and one table per dimension.

Introduction to Pattern Warehousing

Definition. A pattern warehouse stores the patterns discovered by data mining (rules, clusters, models), not the raw data.

Key points.

Mined patterns such as association rules and clusters are saved with their measures like support and confidence.
Storing patterns lets users retrieve, compare and reuse them without re-running expensive mining.
It is queried like a data warehouse, so patterns can be tracked over time and validated.

Last-minute revision

Data warehouse: subject-oriented, integrated, time-variant, non-volatile (Inmon).
Three tiers: warehouse database server, OLAP server (ROLAP/MOLAP), front-end client tools.
ETL means extract, clean, transform, load.
OLTP is current, detailed, transactional; the warehouse is historical, summarised, analytical.
A warehouse is helpful but not a prerequisite for mining; it enables OLAM.
Preprocessing steps: cleaning, integration, transformation, reduction.
Min-max: $v' = (v-\min)/(\max-\min)$; PCA and wavelets reduce dimensions.
Schemas: star, snowflake, fact constellation.
Data mart types: dependent, independent, hybrid.
Metadata types: operational, extraction and transformation, end-user.

Memory hooks

SITN: Subject, Integrated, Time-variant, Non-volatile.
Bottom, Middle, Top: Store, Serve, Show.
Preprocessing CITR: Clean, Integrate, Transform, Reduce.
Star has one hub; snowflake has flakes off the dimensions.

Coverage checklist

Data Warehousing: Introduction, Delivery Process, Data warehouse Architecture: 3-tier architecture, operational database vs warehouse, warehouse and mining.
Data Pre-processing: Data cleaning, Data Integration and transformation, Data reduction: data preprocessing steps, dimensionality reduction.
Data warehouse Design: Data warehouse schema, Partitioning strategy: not asked recently.
Data warehouse Implementation, Data Marts, Meta Data: importance of data marts, metadata role.
Example of a Multidimensional Data model: not asked recently.
Introduction to Pattern Warehousing: not asked recently.

Account

Data Mining and Warehousing (AD-603 (A)) - Unit 1 Short Notes

How unit 1 is examined

Data Warehousing: Introduction, Delivery Process, Data warehouse Architecture

Data Pre-processing: Data cleaning, Data Integration and transformation, Data reduction

Data warehouse Design: schema and partitioning

Data warehouse Implementation, Data Marts, Meta Data

Example of a Multidimensional Data model

Introduction to Pattern Warehousing

Last-minute revision

Memory hooks

Coverage checklist