Skip to content
CampusPrep
CS-703 (B) · Data Mining and Warehousing/Quick Revision Short Notes

Data Mining and Warehousing (CS-703 (B)) - Unit 1 Short Notes

How unit 1 is examined

Covers what a data warehouse is, its three-tier architecture, preprocessing (cleaning, integration, reduction), schemas, implementation, data marts and metadata; schemas and architecture carry the most marks, then cleaning, integration and metadata.

Introduction

<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">Not asked since 2022</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 process (W. H. Inmon).</mark>

Key points.

  1. Subject-oriented means data is organised around subjects such as customer, product and sales, not around day-to-day applications.
  2. Integrated means data from many sources is made consistent in naming, units and encoding before it is stored.
  3. Time-variant means the data carries a time element and holds history of 5-10 years, whereas an operational database holds only current values.
  4. Non-volatile means data is loaded and read but not updated or deleted in place.
  5. A warehouse serves OLAP (analysis, few complex queries), while an operational database serves OLTP (many short transactions).

Delivery Process

<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">Not asked since 2022</span>

Definition. The delivery process is the iterative lifecycle in which a warehouse is built and delivered in small, usable increments.

Key points.

  1. It starts with an IT strategy and a business case that justify the warehouse and its funding.
  2. Education and prototyping follow, so users understand the warehouse and refine their requirements.
  3. Business requirements and technical blueprint are then fixed, and the warehouse is built in versions.
  4. The history load, ad-hoc query and automation phases follow, and each new version is extended with requirement changes.

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>The three-tier data warehouse architecture has a bottom tier of warehouse database server, a middle tier of OLAP server and a top tier of front-end client tools.</mark>

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 499.8 217.6" width="499.8" height="217.6" role="img" aria-label="Src = operational sources, ETL = extract-clean-transform-load, DW = warehouse database and data marts (bottom tier), OLAP = OLAP server (middle tier), Cli = query, report, analysis and mining tools (top tier), Meta = metadata repository"><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 L122.2,40" marker-end="url(#ah1)"/><path class="e" d="M162.2,40 L225.4,40" marker-end="url(#ah1)"/><path class="e" d="M265.4,40 L321.6,40" marker-end="url(#ah1)"/><path class="e" d="M375.6,40 L431.8,40" marker-end="url(#ah1)"/><path class="e" d="M246.4,149.6 L246.4,61" marker-end="url(#ah1)" marker-start="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="143.2" cy="40" r="18"/><text class="t" x="143.2" y="40" dy=".35em" text-anchor="middle">ETL</text><circle class="n" cx="246.4" cy="40" r="18"/><text class="t" x="246.4" y="40" dy=".35em" text-anchor="middle">DW</text><rect class="n" x="324.6" y="25" width="50" height="30" rx="15"/><text class="t" x="349.6" y="40" dy=".35em" text-anchor="middle">OLAP</text><circle class="n" cx="452.8" cy="40" r="18"/><text class="t" x="452.8" y="40" dy=".35em" text-anchor="middle">Cli</text><rect class="n" x="221.4" y="162.6" width="50" height="30" rx="15"/><text class="t" x="246.4" y="177.6" dy=".35em" text-anchor="middle">Meta</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Src = operational sources, ETL = extract-clean-transform-load, DW = warehouse database and data marts (bottom tier), OLAP = OLAP server (middle tier), Cli = query, report, analysis and mining tools (top tier), Meta = metadata repository</figcaption></figure>

Key points.

  1. The bottom tier is a relational warehouse database server, fed from operational databases and external sources through back-end tools that extract, clean, transform, load and refresh data.
  2. Data reaches the bottom tier through gateways such as ODBC and JDBC, and this tier also holds the metadata repository and the data marts.
  3. Warehouse models are the enterprise warehouse (whole organisation), the data mart (one department) and the virtual warehouse (a set of views over operational data).
  4. The middle tier is the OLAP server, implemented as ROLAP (extended relational engine) or MOLAP (special multidimensional array engine).
  5. The OLAP server turns the multidimensional view requested by the user into operations on the data below.
  6. The top tier is the front end: query and reporting tools, analysis tools and data mining tools.
  7. Data flows upward, from sources through ETL into the warehouse, then through the OLAP server to the clients, while metadata describes every step.

Answer frame. Open with the definition of a data warehouse and the three tiers; draw the diagram above with full names in boxes; develop points 1-2 (bottom), 4-5 (middle), 6 (top); close with the data-flow line in point 7 and the three warehouse models.

Asked: [7 marks] (Dec 2020, Nov 2023, Dec 2024, Dec 2025) Explain with diagram a three-tier data warehouse architecture; also worded "define data warehousing and explain its architecture".

Data Preprocessing: Data cleaning

<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">Medium weight</span>

Definition. Data cleaning (cleansing) is the routine that fills in missing values, smooths noisy data, identifies outliers and resolves inconsistencies, because real-world data is incomplete, noisy and inconsistent and poor data gives poor mining results.

Key points.

  1. Missing values are handled by ignoring the tuple (poor when many attributes are missing), filling in manually (slow and unrealistic for large data), or using a global constant such as "Unknown".
  2. Missing values can also be filled with the attribute mean or median, the class-wise mean for tuples of the same class, or the most probable value found by regression, a decision tree or Bayesian inference, which is the best but costliest.
  3. Noisy data is a random error in a measured variable, and it is smoothed by binning, regression and clustering.
  4. Binning sorts the data, splits it into equal-depth bins, and smooths each value by bin means, bin medians or bin boundaries.
  5. Regression smooths data by fitting it to a function, and clustering groups similar values so that values outside all clusters are detected as outliers.
  6. Inconsistent data, such as different codes or units for the same item, is corrected by hand using external references, by knowledge-engineering tools and by data-discrepancy detection with metadata.

Example. Sorted prices 4, 8, 15, 21, 21, 24, 25, 28, 34 in three equal-depth bins: (4, 8, 15), (21, 21, 24), (25, 28, 34). By means: 9, 9, 9, 22, 22, 22, 29, 29, 29. By boundaries: 4, 4, 15, 21, 21, 24, 25, 25, 34.

Answer frame. Open with the definition and why data is dirty; develop missing values, then noisy data with the binning example, then inconsistency; for the Jun 2025 question give a short table of missing-value techniques (method, cost, quality); close with "clean data is the base of reliable analysis".

Asked: [7 marks] (Dec 2020, Dec 2024) Discuss the activities of data cleaning with the process associated with it; explain various methods of data cleaning. Asked: [7 marks] (Jun 2025) What is data cleaning? What are the different techniques for handling missing values? Pitfall: Bin smoothing is done on sorted data; replacing values without sorting first gives wrong bins.

Data Integration and transformation

<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">Medium weight</span>

Definition. Data integration combines data from multiple sources into one coherent store, and transformation converts it into forms suitable for the warehouse.

Key points.

  1. Schema integration and entity identification: the same attribute may be named differently (cust_id and customer_no), so metadata is used to match them.
  2. Redundancy: an attribute is redundant if it can be derived from another, and correlation analysis (chi-square for nominal, correlation coefficient for numeric) detects it.
  3. Data value conflicts arise because the same real-world value differs across sources in scale, units or representation (metric against British units).
  4. Sourcing tools identify and extract data from operational and external systems, and acquisition tools move and load it into the staging area.
  5. Clean-up tools remove errors, duplicates and inconsistencies using rules and lookup tables, so that the loaded data is accurate.
  6. Transformation tools apply smoothing, aggregation, generalisation, attribute construction and normalisation, and reformat data to the warehouse model.
  7. Min-max normalisation is $v' = \frac{v-\min}{\max-\min}(\text{new}_{max}-\text{new}_{min})+\text{new}_{min}$, and z-score is $v'=\frac{v-\bar{A}}{\sigma_A}$.
  8. Together these tools populate the warehouse with consistent, high-quality data that users can trust.

Answer frame. For the tools question, open with the definition of ETL, then take sourcing, acquisition, clean-up, transformation as four headed paragraphs (points 4-7) and close with point 8; for the issues question, list points 1-3 with one example each.

Asked: [7 marks] (Dec 2020) What are the issues to be considered during data integration? Asked: [7 marks] (Dec 2024, Jun 2025) Explain the role played by sourcing, acquisition, clean up and transformation tools in data warehousing.

Data reduction

<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">Not asked since 2022</span>

Definition. Data reduction produces a much smaller representation of the data that gives nearly the same analytical results.

Key points.

  1. Data cube aggregation, attribute subset selection and dimensionality reduction (PCA, wavelets) reduce the number of attributes or dimensions.
  2. Numerosity reduction replaces data by smaller forms: sampling, histograms, clustering and regression.
  3. Compression (lossy or lossless) shrinks the stored size of the data.
  4. Discretization replaces numeric values by intervals, and concept hierarchy generation replaces low-level values by higher-level concepts (city to state to country), which makes mining multi-level.

Asked: [7 marks] (Dec 2025) Describe the techniques used for data preprocessing in data warehousing. (Answer: need for quality, then cleaning, integration, transformation and reduction with discretization and concept hierarchies, as in the four sections around this one.)

Data warehouse Design: Datawarehouse schema

<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 schema is the logical arrangement of the fact table, which holds numeric measures, and the dimension tables, which hold descriptive attributes, in a multidimensional database.</mark>

Diagram. <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u1-02" viewBox="0 0 338 338" width="338" height="338" role="img" aria-label="Star schema. Sal = Sales fact table (keys plus units sold, dollars sold); Tim = Time, Itm = Item, Loc = Location, Cus = Customer dimensions, each one denormalized table"><style>#dsfig-u1-02 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u1-02 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u1-02 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u1-02 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u1-02 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u1-02 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u1-02 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u1-02 .t{fill:#16181D;font-weight:500}#dsfig-u1-02 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u1-02 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u1-02 .dot{fill:#16181D}#dsfig-u1-02 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u1-02 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u1-02 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u1-02 .ah{fill:#454C5A}#dsfig-u1-02 .ah.hi{fill:#2340B8}#dsfig-u1-02 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u1-02 .wl .t{font-size:12px;font-weight:700}#dsfig-u1-02 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u1-02 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u1-02 .e{stroke:#B1B7C3}html.dark #dsfig-u1-02 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u1-02 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u1-02 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u1-02 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u1-02 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u1-02 .t{fill:#E6E8ED}html.dark #dsfig-u1-02 .t.inv{fill:#0F1115}html.dark #dsfig-u1-02 .kd{stroke:#E6E8ED}html.dark #dsfig-u1-02 .dot{fill:#E6E8ED}html.dark #dsfig-u1-02 .ann{fill:#8FA3FF}html.dark #dsfig-u1-02 .lbl{fill:#858D9C}html.dark #dsfig-u1-02 .ptr{fill:#8FA3FF}html.dark #dsfig-u1-02 .ah{fill:#B1B7C3}html.dark #dsfig-u1-02 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u1-02 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u1-02 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u1-02 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah2" 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="ahh2" 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="M155.6,155.6 L53.4,53.4"/><path class="e" d="M182.4,155.6 L284.6,53.4"/><path class="e" d="M155.6,182.4 L53.4,284.6"/><path class="e" d="M182.4,182.4 L284.6,284.6"/><circle class="n" cx="169" cy="169" r="18"/><text class="t" x="169" y="169" dy=".35em" text-anchor="middle">Sal</text><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Tim</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">Itm</text><circle class="n" cx="40" cy="298" r="18"/><text class="t" x="40" y="298" dy=".35em" text-anchor="middle">Loc</text><circle class="n" cx="298" cy="298" r="18"/><text class="t" x="298" y="298" dy=".35em" text-anchor="middle">Cus</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Star schema. Sal = Sales fact table (keys plus units sold, dollars sold); Tim = Time, Itm = Item, Loc = Location, Cus = Customer dimensions, each one denormalized table</figcaption></figure> <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u1-03" viewBox="0 0 467 424" width="467" height="424" role="img" aria-label="Snowflake schema. Item is normalized into Supplier and Location into City, so hierarchies sit in separate tables"><style>#dsfig-u1-03 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u1-03 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u1-03 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u1-03 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u1-03 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u1-03 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u1-03 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u1-03 .t{fill:#16181D;font-weight:500}#dsfig-u1-03 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u1-03 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u1-03 .dot{fill:#16181D}#dsfig-u1-03 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u1-03 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u1-03 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u1-03 .ah{fill:#454C5A}#dsfig-u1-03 .ah.hi{fill:#2340B8}#dsfig-u1-03 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u1-03 .wl .t{font-size:12px;font-weight:700}#dsfig-u1-03 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u1-03 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u1-03 .e{stroke:#B1B7C3}html.dark #dsfig-u1-03 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u1-03 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u1-03 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u1-03 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u1-03 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u1-03 .t{fill:#E6E8ED}html.dark #dsfig-u1-03 .t.inv{fill:#0F1115}html.dark #dsfig-u1-03 .kd{stroke:#E6E8ED}html.dark #dsfig-u1-03 .dot{fill:#E6E8ED}html.dark #dsfig-u1-03 .ann{fill:#8FA3FF}html.dark #dsfig-u1-03 .lbl{fill:#858D9C}html.dark #dsfig-u1-03 .ptr{fill:#8FA3FF}html.dark #dsfig-u1-03 .ah{fill:#B1B7C3}html.dark #dsfig-u1-03 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u1-03 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u1-03 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u1-03 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah3" 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="ahh3" 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="M155.6,155.6 L53.4,53.4"/><path class="e" d="M182.4,155.6 L284.6,53.4"/><path class="e" d="M155.6,182.4 L53.4,284.6"/><path class="e" d="M182.4,182.4 L284.6,284.6"/><path class="e" d="M317,40 L408,40"/><path class="e" d="M55.8,308.5 L153.2,373.5"/><circle class="n" cx="169" cy="169" r="18"/><text class="t" x="169" y="169" dy=".35em" text-anchor="middle">Sal</text><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Tim</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">Itm</text><circle class="n" cx="40" cy="298" r="18"/><text class="t" x="40" y="298" dy=".35em" text-anchor="middle">Loc</text><circle class="n" cx="298" cy="298" r="18"/><text class="t" x="298" y="298" dy=".35em" text-anchor="middle">Cus</text><circle class="n" cx="427" cy="40" r="18"/><text class="t" x="427" y="40" dy=".35em" text-anchor="middle">Sup</text><circle class="n" cx="169" cy="384" r="18"/><text class="t" x="169" y="384" dy=".35em" text-anchor="middle">Cty</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Snowflake schema. Item is normalized into Supplier and Location into City, so hierarchies sit in separate tables</figcaption></figure> <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u1-04" viewBox="0 0 338 338" width="338" height="338" role="img" aria-label="Fact constellation. Sales and Shipping fact tables share the Time and Item dimensions"><style>#dsfig-u1-04 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u1-04 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u1-04 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u1-04 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u1-04 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u1-04 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u1-04 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u1-04 .t{fill:#16181D;font-weight:500}#dsfig-u1-04 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u1-04 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u1-04 .dot{fill:#16181D}#dsfig-u1-04 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u1-04 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u1-04 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u1-04 .ah{fill:#454C5A}#dsfig-u1-04 .ah.hi{fill:#2340B8}#dsfig-u1-04 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u1-04 .wl .t{font-size:12px;font-weight:700}#dsfig-u1-04 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u1-04 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u1-04 .e{stroke:#B1B7C3}html.dark #dsfig-u1-04 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u1-04 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u1-04 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u1-04 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u1-04 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u1-04 .t{fill:#E6E8ED}html.dark #dsfig-u1-04 .t.inv{fill:#0F1115}html.dark #dsfig-u1-04 .kd{stroke:#E6E8ED}html.dark #dsfig-u1-04 .dot{fill:#E6E8ED}html.dark #dsfig-u1-04 .ann{fill:#8FA3FF}html.dark #dsfig-u1-04 .lbl{fill:#858D9C}html.dark #dsfig-u1-04 .ptr{fill:#8FA3FF}html.dark #dsfig-u1-04 .ah{fill:#B1B7C3}html.dark #dsfig-u1-04 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u1-04 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u1-04 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u1-04 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah4" 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="ahh4" 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="M53.4,155.6 L155.6,53.4"/><path class="e" d="M53.4,182.4 L155.6,284.6"/><path class="e" d="M284.6,155.6 L182.4,53.4"/><path class="e" d="M284.6,182.4 L182.4,284.6"/><circle class="n" cx="40" cy="169" r="18"/><text class="t" x="40" y="169" dy=".35em" text-anchor="middle">Sal</text><circle class="n" cx="298" cy="169" r="18"/><text class="t" x="298" y="169" dy=".35em" text-anchor="middle">Shp</text><circle class="n" cx="169" cy="40" r="18"/><text class="t" x="169" y="40" dy=".35em" text-anchor="middle">Tim</text><circle class="n" cx="169" cy="298" r="18"/><text class="t" x="169" y="298" dy=".35em" text-anchor="middle">Itm</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Fact constellation. Sales and Shipping fact tables share the Time and Item dimensions</figcaption></figure>

Key points.

  1. A fact table stores the measures (units sold, dollars sold) and foreign keys to the dimensions, and a dimension table stores descriptive attributes and hierarchies such as day, month, year.
  2. The star schema has one central fact table joined directly to one denormalized table per dimension, so the picture looks like a star.
  3. In a star schema, redundancy is high, but queries are fast because they need few joins.
  4. The snowflake schema normalizes the dimension tables into further tables along their hierarchies (item into supplier).
  5. In a snowflake schema, redundancy and storage are lower and maintenance is easier, but queries need more joins and are slower.
  6. The fact constellation (galaxy) schema has multiple fact tables that share dimension tables, so it models several related business processes.
  7. Star suits a single data mart, and fact constellation suits the enterprise warehouse with many subject areas.
Basis Star Snowflake Fact constellation
Fact tables One One Several
Dimensions Denormalized Normalized Shared, denormalized or normalized
Redundancy High Low Depends on the design
Query speed Fastest, few joins Slower, many joins Complex, joins across facts
Typical use Data mart Storage-sensitive design Enterprise warehouse

Answer frame. Open with the fact and dimension definition; draw all three diagrams with full names and one sales example (Time, Item, Location, Customer); develop points 2-3, 4-5, 6 in that order; close with the comparison table and point 7.

Asked: [7 marks] (Nov 2023, Jun 2025, Dec 2024, Dec 2025) Discuss star schema, snowflake schema and fact constellation schema; also worded "schemas for multidimensional databases", "types of warehouse schema with example" and "schemas in multidimensional data model".

Partitioning strategy

<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">Not asked since 2022</span>

Definition. Partitioning divides a large fact table into smaller, separately managed pieces to improve performance and manageability.

Key points.

  1. Horizontal partitioning splits rows by time (month or year), by other attribute such as region, or by size, and it is the commonest form.
  2. Vertical partitioning splits columns, keeping frequently used columns apart from rarely used ones.
  3. Partitioning speeds queries because only the relevant partitions are scanned, and old partitions can be archived, backed up or dropped independently.
  4. The partition key should match the usual query filters, and partitions should be of similar size.

Data warehouse Implementation

<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">Low weight</span>

Definition. Implementation is the phased work of turning the warehouse design into a running system.

Key points.

  1. Data modelling comes first, giving the schema of facts and dimensions, and storage is then set up with partitioning and indexing.
  2. ETL extracts, cleans, transforms and loads data, and then refreshes it periodically.
  3. Efficient cube computation, bitmap and join indexes and view materialization make OLAP queries fast.
  4. OLAP servers and front-end tools are installed, followed by testing, deployment, user training and maintenance.
  5. Mining is coupled to a warehouse at four levels: no coupling (reads flat files), loose (queries the warehouse and stores results outside), semi-tight (some mining primitives such as sorting and aggregation run in the warehouse), and tight (mining is a warehouse function). For example, tight coupling lets a sales warehouse run classification of customers inside the OLAP server.
  6. In the Madhya Pradesh government case study, the warehouse's objective is to unify departmental data on citizens and services, using ETL from department systems with a star-type design, so that officials can monitor schemes and make decisions on dashboards and reports.

Asked: [7 marks] (Jun 2025) Describe in brief about data warehouse implementation. Asked: [7 marks] (Nov 2023) How can a data mining system be integrated with a data warehousing? Discuss with example. (Points 5.) Asked: [7 marks] (Jun 2025) Discuss in detail about the case study of Data warehouse for the Government of Madhya Pradesh. (Point 6.)

Data Marts

<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">Not asked since 2022</span>

Definition. A data mart is a subset of a warehouse focused on one subject or department, such as sales or finance.

Key points.

  1. A dependent data mart is fed from the central warehouse, so it stays consistent with enterprise data.
  2. An independent data mart is fed directly from sources, so it is quick and cheap to build but risks inconsistent, siloed data.
  3. Marts are smaller, cheaper and faster to build than an enterprise warehouse and give better performance for one group of users.

Meta Data

<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">Medium weight</span>

Definition. <mark>Metadata is data about data: it describes the structure, source, meaning and usage of the data in the warehouse.</mark>

Key points.

  1. Operational (back-room) metadata records source-system structures, field lengths and data-cleaning history of the operational data.
  2. Extraction and transformation metadata records the extraction frequency and methods, business rules and transformation logic used to load the warehouse.
  3. End-user (front-room) metadata gives business names, definitions, hierarchies and report definitions so that non-technical users can browse and query.
  4. The metadata repository holds all of it, including warehouse schema, views, dimensions, hierarchies, data-mart locations, the mapping from source to warehouse, and access and usage statistics.
  5. Metadata is used in ETL to map and transform data, in query processing to locate and interpret data, and in administration to manage security, refresh and archiving.
  6. Building blocks of a warehouse are source data, data staging, data storage, metadata, management and control, and information delivery (access tools).
  7. Challenges in metadata management include different formats and tools for each source, keeping metadata current as sources change, lack of a common standard, incomplete capture, and ownership and cost.

Answer frame. Open with the definition; list the three types (points 1-3); describe the repository (4) and the uses (5); for the Dec 2024 question start with the six building blocks (6), then importance, then challenges (7); close with "metadata is the map of the warehouse".

Asked: [7 marks] (Nov 2023, Jun 2025) Give detailed information about metadata in data warehousing. Asked: [7 marks] (Dec 2024) Enumerate the building blocks of a data warehouse. Explain the importance of metadata in a data warehouse environment. What are the challenges in metadata management?

Example of a Multidimensional Data model

<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">Not asked since 2022</span>

Definition. The multidimensional model views data as a data cube defined by dimensions (Time, Item, Location) and facts (measures such as dollars sold).

Key points.

  1. Each cell of the cube holds a measure for one combination of dimension values, for example sales of item "TV" in "Bhopal" in "Q1".
  2. With $n$ dimensions the cube set is a lattice of $2^n$ cuboids, from the base cuboid to the apex cuboid holding the grand total.
  3. Each dimension has a concept hierarchy (day, month, quarter, year) that enables roll-up and drill-down.

Introduction to Pattern Warehousing

<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">Not asked since 2022</span>

Definition. A pattern warehouse stores the patterns discovered by mining, such as rules, clusters and classifiers, rather than raw data.

Key points.

  1. Patterns are far smaller than the data, so they can be stored, queried and compared cheaply.
  2. Storing them lets analysts retrieve, reuse and maintain patterns without re-running the mining.
  3. A pattern management system supports pattern types, queries over patterns and checking whether patterns are still valid as data changes.

Last-minute revision

  • A data warehouse is subject-oriented, integrated, time-variant and non-volatile.
  • Three tiers: warehouse database server, OLAP server (ROLAP or MOLAP), front-end tools.
  • Warehouse models: enterprise warehouse, data mart, virtual warehouse.
  • Missing values: ignore, manual, global constant, mean or median, most probable value.
  • Noisy data: binning, regression, clustering; bin smoothing uses sorted equal-depth bins.
  • Bins 4, 8, 15 smooth to 9, 9, 9 by means and 4, 4, 15 by boundaries.
  • Integration issues: schema integration, entity identification, redundancy, value conflicts.
  • Min-max: $v'=\frac{v-\min}{\max-\min}(\text{new}_{max}-\text{new}_{min})+\text{new}_{min}$.
  • Star: one fact and denormalized dimensions; snowflake: normalized dimensions; constellation: many facts sharing dimensions.
  • Metadata types: operational, extraction and transformation, end-user.
  • $n$ dimensions give $2^n$ cuboids.
  • Coupling: none, loose, semi-tight, tight.

Memory hooks

  • Warehouse properties: "SITN", Subject, Integrated, Time-variant, Non-volatile.
  • Tiers: "Bottom stores, Middle serves (OLAP), Top shows".
  • Schemas by fact count: star and snowflake have one fact, constellation has many; snowflake is a star with its dimensions "melted" into more tables.
  • Metadata types: "Operate, Extract, End-user", from the back room to the front room.
  • Cleaning order: missing, noisy, inconsistent.

Coverage checklist

  • Introduction: definition and four properties (no past question).
  • Delivery Process: lifecycle stages (no past question).
  • Data warehouse Architecture: Dec 2020, Nov 2023, Dec 2024, Dec 2025 three-tier architecture.
  • Data Preprocessing: Data cleaning: Dec 2020, Dec 2024 cleaning activities; Jun 2025 missing values.
  • Data Integration and transformation: Dec 2020 integration issues; Dec 2024, Jun 2025 sourcing, acquisition, clean-up and transformation tools.
  • Data reduction: Dec 2025 preprocessing techniques.
  • Data warehouse Design: Datawarehouse schema: Nov 2023, Dec 2024, Jun 2025, Dec 2025 star, snowflake and constellation.
  • Partitioning strategy: horizontal and vertical partitioning (no past question).
  • Data warehouse Implementation: Jun 2025 implementation; Nov 2023 mining and warehouse integration; Jun 2025 Madhya Pradesh case study.
  • Data Marts: dependent and independent marts (no past question).
  • Meta Data: Nov 2023, Jun 2025 metadata; Dec 2024 building blocks and metadata challenges.
  • Example of a Multidimensional Data model: data cube and cuboids (no past question).
  • Introduction to Pattern Warehousing: pattern storage (no past question).
Go to where you left off?

Quick Add to Notes

Save questions, your own notes and screenshots into notes filed by unit. It takes a free account.

Create free account

Have an account? Log in