Data Mining

Answer: (iv) Web server

The architecture of a data warehouse is built from a fixed set of components: source data, ETL (Extract, Transform, Load) tools that move and clean the data, the warehouse database itself, metadata (data about the data — schemas, definitions, mappings, lineage), an OLAP/DSS engine for analytical processing, and front-end reporting and analysis tools. A web server is not one of them: it may host an application that uses the warehouse, but it belongs to the web/application tier, not to the warehouse architecture.

Metadata, ETL tools and OLAP are all standard textbook components of a data warehouse — which is exactly why they are wrong answers in a 'NOT' question. The task is to spot the option that belongs to general web infrastructure rather than to the warehouse itself.

Answer: (iii) metadata

Metadata is 'data about data': it describes the structure, origin, format, quality and other attributes of the actual data — for example a schema or table definition in a data dictionary, the data type and length of a column, a file's author and creation date, or the number of rows in a relation.

A table is only the physical structure that stores the data, and a database is an organised collection of that data — neither of them describes the data. Integration is a preprocessing step that merges data from several sources, so it is a process rather than descriptive information about data. Hence only (iii) fits.

Answer: (i) Star schema

The star schema is the most commonly used schema in data warehouse design. It places a single central fact table (measures such as sales amount and quantity) directly beside several dimension tables (time, product, customer, store). Because the dimensions are denormalised, the join graph looks like a star, join paths are short and queries run fast — the reason it is the default choice for dimensional modelling.

The snowflake schema is a normalised variant of the star schema and is also used, but less commonly, because its extra joins slow queries down. Network schema and tree schema are general data-modelling concepts (network and hierarchical models); they are not data warehouse modelling schemes, since they do not follow the fact–dimension pattern.

Answer: (ii) DOLAP

The three standard implementations of OLAP are MOLAP (multidimensional storage — data held in specialised cube structures), ROLAP (relational storage — OLAP operations pushed down to SQL on relational tables) and HOLAP (hybrid — MOLAP for aggregated data plus ROLAP for detail data). 'DOLAP' is not part of this standard classification of OLAP architectures.

MOLAP, ROLAP and HOLAP are precisely the trio listed in every textbook, so each of them is a genuine OLAP type. In a 'NOT a type' question the odd one out must therefore be DOLAP.

Answer: (iii) 2ⁿ

With no hierarchy associated with any dimension, each of the n dimensions can appear either in its base level or be fully aggregated away. The cuboids of the cube therefore correspond one-to-one with the subsets of the n dimensions, and the number of subsets of an n-element set is $2^n$. This includes the base cuboid (all n dimensions present) and the apex cuboid (no dimensions, i.e. the empty subset), with $\binom{n}{k}$ cuboids containing exactly k dimensions.

The options n³, n and n² are not powers of two and cannot count $2^n$ subsets, so they are incorrect.

Answer: (ii) A specific business area

'Subject-oriented' is one of the four defining characteristics of a data warehouse (the others being integrated, non-volatile and time-variant). It means the warehouse is organised around the major subjects of the enterprise — customers, products, sales, orders — i.e. a specific business area, rather than around the applications or day-to-day processes that happen to produce the data. Analysis then runs subject by subject across many years of history.

Individual transactions and day-to-day operations describe an OLTP system, which is application-oriented and holds current state; real-time data describes operational/real-time systems. A warehouse instead stores integrated, historical, subject-oriented data for decision support, so (i), (iii) and (iv) are incorrect.

Solution

Correct Answer: (ii) 240

Explanation

In a data cube, the number of base-level (apex-excluded, lowest-level) cells is the product of the number of distinct values in each dimension:

Cells = products x regions x months = 5 x 4 x 12 = 240

Each cell stores one measure value (for example total sales) for one unique combination of product, region and month.

Why other options are incorrect:

• (i) 120 - ignores one dimension (5 x 4 x 6 or similar).
• (iii) 180 and (iv) 360 - not the product of 5, 4 and 12.

Answer: (ii) 240

Answer: (ii) Star or Snowflake Schema

Data warehouses are optimized for read-heavy analytical queries, not transactional integrity, so they use dimensional modeling instead of normalized relational schemas.

• Star Schema: one central fact table (measures/metrics) connected directly to several dimension tables (denormalized, descriptive attributes).
• Snowflake Schema: same idea, but dimension tables are further normalized into sub-dimensions.

        Dim_Time        Dim_Product
             \              /
              \            /
               FACT_TABLE
              /            \
             /              \
        Dim_Customer     Dim_Store

Both schemas are purpose-built for OLAP-style slicing, dicing, and aggregation — which is why (ii) is correct over normalized relational schema (iii), which is used in OLTP systems.

Solution

Correct Answer: (iii) Star schema

Explanation

A star schema has one large central fact table (measures such as sales amount, plus foreign keys) directly connected to several dimension tables (such as time, product, customer, location). The diagram looks like a star, and the dimension tables are usually denormalized, which keeps queries simple and fast.

Why other options are incorrect:

• (i) Network schema and (ii) Hierarchical schema - old database models, not data warehouse schemas.
• (iv) Object schema - used in object-oriented databases.

Key Points to Remember:

• Snowflake schema = star schema with normalized dimension tables.
• Fact constellation = several fact tables sharing dimensions.

Answer: (iii) Star schema

Answer: (iii) Word Processing Tools

The standard components of a data warehouse are all built around moving, cleaning, storing, and describing data:

Component	Role
Data Extraction Tools	Pull data from source/operational systems
Data Cleaning Tools	Fix inconsistencies, remove noise/duplicates
Data Transformation Tools	Convert data into the warehouse's target format
Load/Refresh Tools	Populate the warehouse and keep it updated
Metadata Repository	Stores "data about data" — schemas, mappings, lineage
OLAP Servers	Support multidimensional query/analysis

Word Processing Tools are general office-productivity software (like MS Word) — they have no functional role in the ETL/warehouse pipeline, making (iii) the odd one out.

Slice and Dice (OLAP Operations)

Think of a data cube as a 3D box of numbers indexed along multiple dimensions (e.g., Time, Product, Region).

Slice

Selects one specific value on one dimension, "cutting" a single 2D layer out of the cube.

        Product
       /|  /|  /|
      / | / | / |
     /__|/__|/__|
    |   |   |   |     <- Slice at Time = "2024"
Time|___|___|___|
    |   |   |   |
    |___|___|___|
        Region

Example: "Sales for Time = 2024" → gives a 2D Product × Region table.

Dice

Selects a sub-cube by picking a range/subset of values on two or more dimensions simultaneously.

Full cube ──dice──▶  smaller cube
(Time × Product × Region)   (Time∈{2023,2024} × Product∈{A,B} × Region=West)

Example: "Sales where Time ∈ {2023, 2024} AND Product ∈ {A, B} AND Region = West."

Key Difference

Operation	Dimensions restricted	Result
Slice	1 dimension fixed to a single value	One lower-dimension "layer" of the cube
Dice	2+ dimensions restricted to ranges/subsets	A smaller sub-cube (same dimensionality)

Both are used for interactive, ad-hoc drill-down analysis in OLAP tools without needing to re-query the source data.

Role of Metadata in Data Warehousing

Definition. Metadata is data about data: it describes the structure, origin, meaning, transformation history and usage of the data held in the warehouse. A warehouse without metadata is only a data dump — the numbers exist, but nobody can interpret them reliably.

1. Types / content of metadata

1. Technical metadata — table and column definitions, data types, keys and relationships, source-system details, ETL mappings, transformation and cleansing rules, load schedules and logs, indexes and partitions.
2. Business metadata — business definitions and calculation rules (what exactly counts as 'net sales'?), units of measure, hierarchies, data owners.
3. Operational metadata — who loaded what and when, job status, data-quality and error reports.
4. Access / usage metadata — who queries which data, permissions, and usage statistics.

2. Role of metadata in the warehouse

1. Drives the ETL process — source-to-target mapping, cleansing rules and load scheduling are all described in metadata.
2. Populates the metadata repository / system catalogue — the warehouse catalogue stores schemas, business terms and lineage so that data can be discovered and understood.
3. Enables navigation — analysts use it to locate tables, understand relationships and interpret measures without reading underlying code.
4. Ensures consistency — each business metric is defined once and reused, so different reports and dashboards cannot disagree.
5. Provides lineage and auditability — a figure in a report can be traced back through every transformation to its source system.
6. Supports administration and performance — indexing, aggregation, partitioning and storage decisions are informed by usage metadata.

3. Importance in decision support systems (DSS)

• Credibility of decisions — a manager trusts a recommendation only when the provenance and calculation of the numbers are known.
• Self-service analysis — with a data dictionary and documented OLAP cubes, users can slice, dice and drill down on their own instead of depending on the DBA, which is what makes ad-hoc query and OLAP practical.
• Data quality and timeliness — load logs and quality statistics show whether the data is fresh and complete before a decision is taken.
• Change impact analysis — when a source system or a calculation rule changes, metadata reveals which reports and KPIs are affected.
• Governance and compliance — ownership, retention and access rights are recorded, supporting audit and regulatory requirements.

Conclusion

Metadata is often called the backbone (or the nerve centre) of the data warehouse: ETL tools, the warehouse database, the OLAP engine and the front-end tools all consume it. For a DSS it is the layer that converts stored data into understandable, trustworthy information — which is why metadata is treated as a core component of every data warehouse.

Star Schema and Snowflake Schema in Data Warehouse Design

Definition. Both are dimensional modelling schemes: a central fact table holding measurable events (foreign keys plus measures) surrounded by dimension tables holding the context (who, what, where, when). They differ only in how the dimensions are normalised.

1. Star schema

• One fact table at the centre; each dimension is a single, denormalised table joined directly to the fact table.
• The join pattern looks like a star; every query needs a fixed, small number of joins.
• Attributes such as city, state and country repeat inside one store-dimension table — some redundancy, but simple and fast queries.

2. Snowflake schema

• The normalised form of the star: each dimension is split into several related tables following its hierarchy (Store → City → State → Country).
• The join pattern looks like a snowflake; hierarchies are stored once, with no redundancy.
• Saves storage and prevents update anomalies, but needs more joins, so queries are slower and the model is harder for end users to navigate.

3. Comparison

Basis	Star schema	Snowflake schema
Normalisation	Dimensions denormalised	Dimensions normalised
Structure	Fact + one table per dimension	Fact + several related dimension tables
Joins per query	Few	More
Query performance	Faster	Slower
Storage	Redundant, larger	Less redundancy, smaller
Design / maintenance	Simple	More complex
Hierarchy integrity	Repetition, possible inconsistency	Clean, single source of truth
Popularity	Most common default	Chosen for large / hierarchical dimensions

4. Example — star schema for retail sales

DimDate(dDateKey, day, month, year, quarter)        -----+
DimProduct(prodKey, name, category, brand)          -----+---- FactSales(salesKey, dDateKey, prodKey,
DimStore(storeKey, name, city, state, country)      -----+     storeKey, custKey, qty, amount)
DimCustomer(custKey, name, segment)                 -----+

FactSales carries the foreign keys plus the measures qty and amount; the dimensions supply the attributes used in filters, group-by and drill-down. For the snowflake version, DimStore is normalised into Store → City → State → Country and DimProduct into Product → Category, giving the flaked extension of the star.

Conclusion

Star = speed and simplicity; snowflake = storage efficiency and hierarchy integrity. Most warehouses begin with a star schema and snowflake a dimension only when it is very large or deeply hierarchical.

Data Warehouse — Definition & Architecture

Definition

A Data Warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data organized to support management decision-making (Bill Inmon's definition). It consolidates data from multiple heterogeneous operational sources into one consistent store optimized for analysis rather than transactions.

Architecture Diagram

┌───────────────┐   ┌───────────────┐   ┌───────────────┐
│  Source: RDBMS│   │ Source: Files │   │ Source: ERP   │
└───────┬───────┘   └───────┬───────┘   └───────┬───────┘
        │                   │                   │
        └────────┬──────────┴──────────┬────────┘
                  ▼                     
        ┌─────────────────────┐
        │   ETL / Data Staging │  Extract, clean, transform,
        │        Area          │  integrate, load
        └──────────┬───────────┘
                    ▼
        ┌─────────────────────┐
        │   Metadata           │◀── describes structure,
        │   Repository          │    source mappings, lineage
        └──────────┬───────────┘
                    ▼
        ┌─────────────────────┐
        │   Data Warehouse      │  (central repository —
        │   (Fact + Dimension   │   star/snowflake schema)
        │    tables)             │
        └──────────┬───────────┘
                    ▼
     ┌──────────────┼──────────────┐
     ▼              ▼              ▼
┌─────────┐   ┌───────────┐  ┌───────────┐
│ Data     │   │  OLAP      │  │  Reporting/│
│ Marts    │   │  Server    │  │  Mining   │
│ (dept-   │   │ (ROLAP/    │  │  Tools    │
│ specific)│   │  MOLAP)    │  │           │
└─────────┘   └───────────┘  └───────────┘
                    │
                    ▼
             End Users / Analysts

Key Layers

1. Source layer – operational systems (RDBMS, flat files, ERP, external feeds).
2. ETL/Staging layer – extraction, cleaning, transformation, integration, and loading of data.
3. Metadata repository – records schema definitions, source-to-target mappings, and data lineage.
4. Warehouse storage layer – central repository modeled as fact tables (measures) linked to dimension tables (context), often broken into smaller data marts for individual departments.
5. OLAP/access layer – ROLAP, MOLAP, or HOLAP servers that let users slice, dice, drill down, and roll up.
6. Front-end tools – reporting, dashboards, ad-hoc query tools, and data mining applications used by decision-makers.

This layered design keeps the warehouse decoupled from source systems (so analytics never slows down production databases) while giving analysts a single, consistent, historical view of the business.

Components of a Data Warehouse and Their Functions

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data that supports management's decision-making.

Architecture

Operational DBs, files,    ETL / Staging area            Warehouse layer          Front-end tools
external sources           (extract, clean,              (DW database,            (query, reports,
                            transform, load)              data marts)              OLAP, mining)
   [Source 1] \                                                                  
   [Source 2] ---> [ Extraction -> Cleaning -> Transformation -> Load ] ---> [ Data Warehouse ] ---> [ Users ]
   [Source 3] /                         ^                                   ^
                                        |______ [ Metadata repository ] ____|

Major components

Component	Role
Data sources	Operational databases, flat files, external data feeding the warehouse
ETL / staging area	Extracts, cleans, transforms and loads data
Data warehouse database	Central integrated store, usually on a relational DBMS or multidimensional server
Data marts	Subject-specific subsets (sales, finance)
Metadata repository	Data about the data: schema, mappings, rules, lineage
Query, reporting and OLAP tools	Let users analyse and view the data
Management tools	Scheduling, security, backup, monitoring

Functions in the warehouse environment

1. Data extraction: Pulls the required data from heterogeneous sources, either fully or incrementally (only changes), without disturbing operational systems.
2. Data cleaning: Detects and fixes errors: missing values, duplicates, wrong formats, inconsistent codes. This makes the warehouse data reliable.
3. Data transformation: Converts data into one consistent format: unit and format conversion, key mapping, aggregation, derived fields, and applying business rules.
4. Metadata management: Stores and maintains technical metadata (schema, mappings, ETL logic), business metadata (definitions, ownership) and operational metadata (load times, history). It helps users find and trust data and supports lineage.
5. Reporting: Gives users standard and ad hoc reports, dashboards, OLAP analysis and data mining outputs that turn warehouse data into decisions.

Conclusion

ETL fills the warehouse with clean, consistent data, metadata describes and controls it, and query/reporting tools turn it into information for decision-making.

Data Warehouse vs Operational Database (OLTP)

Feature	Operational DB (OLTP)	Data Warehouse (OLAP)
Purpose	Day-to-day transaction processing	Analysis, decision support
Users	Clerks, DBAs, front-line staff	Managers, analysts, executives
Data content	Current, detailed data	Historical, summarized, consolidated data
Data model	Normalized (E-R model), application-oriented	Denormalized — star/snowflake schema, subject-oriented
Access pattern	Short, atomic transactions (read/write/update)	Complex queries, mostly read-only, large scans
Operations	Insert, update, delete (many small txns)	Aggregate, drill-down, roll-up (few large queries)
Design goal	High concurrency, fast single-record access	Fast aggregation over large volumes
Data volume per query	Small	Large
Time orientation	Reflects current value	Reflects data over time (historical snapshots)
Redundancy	Minimized (normalization)	Encouraged (denormalization for query speed)
Backup/recovery	Critical, transaction-consistent	Less frequent, can be reloaded from source

Conceptual Diagram

 OLTP Systems                     Data Warehouse
 ┌───────────┐   ETL (Extract,   ┌────────────────┐
 │ Sales DB  │──Transform,Load──▶│                │
 ├───────────┤                   │  Subject-      │
 │ HR DB     │──────────────────▶│  oriented,     │──▶ OLAP / Reports
 ├───────────┤                   │  integrated,   │
 │ Inventory │──────────────────▶│  time-variant, │
 │ DB        │                   │  non-volatile  │
 └───────────┘                   └────────────────┘
   (many small,                    (one large,
   normalized DBs)                  denormalized store)

In short: OLTP is built for doing the business (fast writes, current data), a Data Warehouse is built for understanding the business (fast reads, historical/aggregated data). Bill Inmon defines the DW as subject-oriented, integrated, time-variant, and non-volatile — all properties an OLTP system deliberately does not have.

What is OLAP?

OLAP (Online Analytical Processing) is a category of software technology that enables analysts to interactively examine multidimensional data from multiple perspectives — slicing, dicing, drilling down, rolling up, and pivoting — to support fast, complex analytical queries over large historical datasets (as opposed to OLTP, which handles fast simple transactions).

Main Components of a Data Warehouse System

 ┌────────────┐    ┌───────────────┐    ┌────────────────┐    ┌──────────┐
 │  Source    │───▶│  Extraction/  │───▶│   Data          │───▶│  OLAP /  │
 │  Systems   │    │  Transform/   │    │   Warehouse     │    │  Query   │
 │            │    │  Load (ETL)   │    │   Storage       │    │  Tools   │
 └────────────┘    └───────────────┘    └────────────────┘    └──────────┘
                            │                     │
                            ▼                     ▼
                    ┌───────────────┐    ┌────────────────┐
                    │   Metadata     │    │   Data Marts    │
                    │   Repository   │    │  (subject-wise) │
                    └───────────────┘    └────────────────┘

1. Source/Operational Systems — OLTP databases, external feeds, legacy files that supply raw data.
2. Extraction, Transformation, Loading (ETL) tools — pull data out of sources, clean/standardize it, and load it into the warehouse.
3. Metadata Repository — stores "data about data": schema definitions, source mappings, transformation rules, and refresh schedules.
4. Data Warehouse (central repository) — the integrated, subject-oriented store, typically organized as fact and dimension tables.
5. Data Marts — smaller, department/subject-specific subsets of the warehouse (e.g., Sales mart, HR mart) for faster, focused access.
6. OLAP Servers — ROLAP (relational), MOLAP (multidimensional), or HOLAP (hybrid) engines that serve multidimensional queries.
7. Front-End Analysis/Reporting Tools — dashboards, query tools, data mining tools used by business analysts and decision-makers to consume the data.

Together, these components move data from scattered operational systems into a single, query-optimized structure, and then expose it through OLAP for fast, flexible analysis.

Data Cube: Sales by Region, Month and Product Category

Dimensions: Region = 4, Month = 12, Product category = 5. Measure: total sales.

(i) Total number of possible multidimensional cells

Each combination of (region, month, category) is one cell:

Cells = 4 x 12 x 5 = 240

(ii) Cells required for one region across all months and products

Fix the region (1 value) and vary the other two dimensions:

Cells = 1 x 12 x 5 = 60

(iii) OLAP operations on this cube

Operation	What it does	Example on this cube	Result
Roll-up	Aggregate to a higher level of a dimension hierarchy (or remove a dimension)	Months to quarters (12 to 4); sales then shown per region, quarter and category	4 x 4 x 5 = 80 cells
Drill-down	Go from summary to more detailed data	Quarter to month, or month to day; Region to city	More cells than before (e.g. back to 240)
Slice	Select one value on one dimension, giving a sub-cube with one dimension fewer	Region = "North": a 2-D table of month x category	12 x 5 = 60 cells
Dice	Select ranges/values on two or more dimensions, giving a smaller cube	Regions {North, South}, months Jan to Mar, categories {Electronics, Clothing}	2 x 3 x 2 = 12 cells
Pivot (rotate)	Rotate axes to see the data from another view	Swap month and category axes	Same cells, new view

Extra: number of cuboids

With 3 dimensions and no hierarchies, the cube has 2^3 = 8 cuboids: the base cuboid (region, month, category), three 2-D cuboids, three 1-D cuboids and the apex (grand total).

Conclusion

The cube holds 240 base cells; one region slice needs 60 cells; roll-up, drill-down, slice and dice let a user move between summary and detail views.

Data Warehouse Backend Process

The backend process is the pipeline that moves data from scattered operational sources into the clean, integrated warehouse. It runs "behind the scenes," invisible to end users, and is commonly called ETL (Extract–Transform–Load).

 Operational      ┌─────────┐      ┌───────────┐      ┌────────┐      ┌───────────┐
 Source Systems ─▶│ Extract  │─────▶│ Clean/     │─────▶│Transform│─────▶│  Load      │──▶ Data
 (RDBMS, files,   │          │      │ Scrub      │      │        │      │  (+Refresh)│    Warehouse
  ERP, legacy)     └─────────┘      └───────────┘      └────────┘      └───────────┘

Steps

1. Extraction — Data is pulled from multiple heterogeneous source systems (databases, flat files, ERP/CRM, external feeds).
2. Cleaning (Scrubbing) — Detects and corrects errors, inconsistencies, missing values, and duplicate records to improve data quality.
3. Transformation — Converts data into the warehouse's standard format: normalizing units, renaming fields, applying business rules, aggregating, and generating derived/summary values.
4. Integration — Merges data from different sources into a unified, subject-oriented schema, resolving naming and structural conflicts (schema/semantic integration).
5. Loading — Writes the cleaned, transformed, integrated data into the warehouse's fact and dimension tables, often in bulk/batch mode.
6. Refreshing — Periodically propagates updates from the source systems into the warehouse (full refresh or incremental refresh) to keep it current.

Why it matters

The backend process is what actually enforces the warehouse's defining properties — integrated (step 4), non-volatile (loaded once, not transaction-updated), and time-variant (refresh keeps historical snapshots) — turning raw, messy operational data into a trustworthy analytical resource.

OLAP Operations

OLAP tools let users navigate a multidimensional data cube through five core operations:

1. Roll-up (Drill-up)

Aggregates data by climbing up a concept hierarchy or reducing dimensions.

Day → Month → Quarter → Year        (climbing the Time hierarchy)

Example: Sales by City → Sales by State → Sales by Country.

2. Drill-down

The reverse of roll-up — moves down the hierarchy to reveal finer detail.

Year → Quarter → Month → Day

Example: Yearly sales → Quarterly sales → Monthly sales.

3. Slice

Selects a single value on one dimension, producing a lower-dimensional sub-cube.

Cube(Time, Product, Region) --Time="2024"--> Table(Product, Region)

4. Dice

Selects a sub-cube by specifying ranges/values on two or more dimensions.

Dice: Time∈{2023,2024}, Region="West" → smaller cube

5. Pivot (Rotate)

Rotates the data axes to provide an alternate presentation — e.g., swapping rows and columns in a cross-tab.

Before:                 After pivot:
        Q1  Q2  Q3            Prod A  Prod B
Prod A  10  20  15    ──▶  Q1   10      12
Prod B  12  18  22          Q2   20      18
                             Q3   15      22

Summary Table

Operation	Effect	Dimensionality change
Roll-up	Aggregate, coarsen granularity	Decreases (or stays, fewer dims)
Drill-down	Disaggregate, finer granularity	Increases
Slice	Fix one dimension's value	Decreases by 1
Dice	Restrict 2+ dimensions	Same dims, smaller sub-cube
Pivot	Re-orient axes for viewing	Unchanged

These operations give analysts an interactive way to explore a cube from summary to detail and back, without re-running queries against the raw source data.

Star Schema Design — University Performance Data Warehouse

Requirements Analysis

We need to analyze: grade-vs-enrollment relation, courses with zero registrations, gender-wise performance, state/city-wise average grade per course, and overall CGPA. This means our grain (finest fact-table row) should be one student's performance in one course, in one academic session.

Fact Table

FACT_PERFORMANCE

Column	Type	Key
student_key	FK → DIM_STUDENT	PK (composite)
course_key	FK → DIM_COURSE	PK (composite)
session_key	FK → DIM_SESSION	PK (composite)
grade_point	Measure
sgpa	Measure
is_registered	Measure (flag, 1/0)

Using a is_registered flag (or simply having a row exist) lets us detect courses offered but with zero enrolled students via a LEFT JOIN from DIM_COURSE × DIM_SESSION against the fact table.

Dimension Tables

DIM_STUDENT
| student_key (PK) | student_id | name | gender | state | city | cgpa |

DIM_COURSE
| course_key (PK) | course_id | course_name | department | credits |

DIM_SESSION
| session_key (PK) | academic_year | semester_type (Odd/Even) |

Schema Diagram

                  DIM_STUDENT
                 (student_key PK)
                  gender, state, city, cgpa
                        │
                        │FK
                        ▼
DIM_COURSE ──FK──▶ FACT_PERFORMANCE ◀──FK── DIM_SESSION
(course_key PK)     (student_key,        (session_key PK)
course_name,         course_key,          academic_year,
department           session_key,         semester_type
                      grade_point,
                      sgpa,
                      is_registered)

How it satisfies the requirements

• Grade vs enrollment relation → aggregate AVG(grade_point) and COUNT(student_key) grouped by course_key.
• Courses with no students → DIM_COURSE LEFT JOIN FACT_PERFORMANCE, filter WHERE fact IS NULL.
• Gender-wise performance → join DIM_STUDENT.gender, group by course & gender.
• State/city-wise average grade → join DIM_STUDENT.state/city, group by course.
• Overall CGPA → stored directly in DIM_STUDENT.cgpa (or derived as AVG(sgpa) across sessions per student).

This is a classic star schema: one fact table with numeric measures (grade_point, sgpa) surrounded by denormalized dimension tables, which keeps analytical queries fast (few joins, simple filters).

SQL Query — Avg SGPA of Boys from Karnataka, Odd Semesters (2005–2009)

Using the star schema from Q4a (FACT_PERFORMANCE, DIM_STUDENT, DIM_SESSION):

SELECT
    ds.academic_year,
    AVG(fp.sgpa) AS avg_sgpa
FROM
    FACT_PERFORMANCE fp
    JOIN DIM_STUDENT  st ON fp.student_key  = st.student_key
    JOIN DIM_SESSION  ds ON fp.session_key  = ds.session_key
WHERE
    st.gender = 'M'
    AND st.state = 'Karnataka'
    AND ds.semester_type = 'Odd'
    AND ds.academic_year BETWEEN 2005 AND 2009
GROUP BY
    ds.academic_year
ORDER BY
    ds.academic_year;

How it maps to the requirement

• st.gender = 'M' → restricts to boys.
• st.state = 'Karnataka' → restricts to the given state.
• ds.semester_type = 'Odd' → restricts to Autumn semesters only.
• ds.academic_year BETWEEN 2005 AND 2009 → restricts the year range.
• GROUP BY ds.academic_year → produces one average SGPA row per year, as required ("for each Autumn semester during 2005–2009").
• The joins connect the fact table's measures (sgpa) to the descriptive attributes (gender, state, academic_year) stored in the dimension tables — this is exactly why a star schema keeps such analytical queries simple (only two joins, no deep nesting).

Data Warehouse; OLAP vs. OLTP

1. Definition of a data warehouse

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process.

• Subject-oriented: organised around major subjects such as customer, product, sales rather than around applications or transactions.
• Integrated: data from many heterogeneous sources (files, relational DBs, external feeds) are merged under a single schema with consistent naming, units and codes.
• Time-variant: every attribute records the time period; the data covers a long historical period (years), unlike operational data which holds only the current value.
• Non-volatile: once loaded, data are not updated or deleted - they are read-only, so historical data remain stable for analysis.

It is built by ETL (extract, transform, load) from operational systems and organised as a data cube (fact and dimension tables) to support analytical queries.

2. OLAP vs. OLTP

Feature	OLTP (On-Line Transaction Processing)	OLAP (On-Line Analytical Processing)
Purpose	Run day-to-day transactions (insert, update, delete)	Analysis, reporting and decision support
Users	Clerks, operators, customers	Managers, analysts, executives
Design	Application-oriented, ER / 3NF normalised schema	Subject-oriented, star / snowflake schema
Operations	Simple, short read-write queries	Complex queries: roll-up, drill-down, slice, dice, pivot
Data	Current, detailed, small volume per query	Historical, aggregated, very large volume
Response time	Very fast (milliseconds), few records touched	Seconds to minutes, large portions of data scanned
Query volume	Many concurrent short transactions	Fewer, long-running analytical queries
Indexing	Heavy indexing for fast writes	Bitmap / join indexes for fast aggregation
Failure impact	Affects business operations immediately	Affects reporting only

In short: OLTP supports the operations of the organisation (current, fast, transactional), while OLAP supports the analysis of the organisation (historical, aggregated, multidimensional) over a data warehouse.

Star Schema (Data Warehouse)

Definition. A star schema is the simplest dimensional-modelling scheme used to implement a data warehouse. It consists of one central fact table, which stores the business measures (numeric facts) plus foreign keys, and several dimension tables placed around it, which store the descriptive, contextual attributes. Because the fact table sits at the centre with the dimensions radiating out, the resulting ER diagram looks like a star.

Example - retail sales warehouse

• Fact table: Sales(Time_key, Product_key, Store_key, Promo_key, Quantity, Amount)
• Dimensions: Time(Time_key, Date, Month, Quarter, Year), Product(Product_key, Name, Category, Brand), Store(Store_key, City, Region, Manager), Promo(Promo_key, Type, Discount)

              Time
               |
  Product --- Sales --- Store
               |
              Promo

Key characteristics

1. The fact table is large and narrow (keys + measures); dimension tables are small and fully denormalised.
2. Queries need only a few simple joins (star-join optimisation), so performance is high and the structure is easy for business users to understand.
3. Well suited to OLAP operations such as roll-up, drill-down, slice and dice.
4. The snowflake schema is the normalised variant in which dimension hierarchies are split into additional tables: it saves storage but adds joins and slows queries. Star schema is therefore preferred for performance and simplicity.

ROLAP, MOLAP and HOLAP - Implementing the Multidimensional View

These are the three techniques used to physically implement the multidimensional (data-cube) view of a data warehouse.

1. ROLAP - Relational OLAP

The multidimensional view is implemented on top of a relational database: facts and dimensions are stored as relational tables in a star or snowflake schema, and every OLAP operation (roll-up, drill-down, slice) is translated into SQL queries with GROUP BY.

• Handles very large and even detail-level data (scales with the RDBMS).
• Uses aggregate tables, BITMAP indexes and join indexes to speed queries.
• No fully pre-computed cube, so complex aggregations are slower; dependent on SQL tuning.

2. MOLAP - Multidimensional OLAP

Data (including all pre-aggregated summaries) are stored in a specialised multidimensional array / cube structure, not in rows of a table.

• Queries are extremely fast because aggregates are pre-computed and stored.
• Sparse cubes waste storage; cube-building (ETL) takes time and the data volume that fits in memory is limited - less scalable than ROLAP.

3. HOLAP - Hybrid OLAP

Combines both: detailed (base) data remain in relational tables while the aggregates are stored in a MOLAP cube.

• Drill-through to detail uses SQL on the relational store; summary queries are answered from the fast cube.
• Best balance of storage, performance and scalability - a compromise of ROLAP and MOLAP.

Comparison

Parameter	ROLAP	MOLAP	HOLAP
Storage	Relational tables	Multidimensional arrays	Detail in RDBMS, aggregates in cube
Data volume	Very large, detail-level	Moderate, summaries	Large detail + summaries
Query speed	Slower for complex aggregation	Fastest (pre-computed)	Fast for summaries, slower for detail
Pre-aggregation	Limited / none	Full	Aggregates only
Scalability	Best	Limited by cube size	Good
Best suited to	Huge detail data, ad-hoc SQL	Small-to-medium data needing speed	Enterprises needing both

Data Warehouse Usage Types & Motivation for OLAM

Three Types of Data Warehouse Usage

Aspect	Information Processing	Analytical Processing	Data Mining
Goal	Query, basic statistics, reporting	Multidimensional analysis (OLAP)	Discover hidden patterns/knowledge
Operations	Simple queries, cross-tabs, charts	Roll-up, drill-down, slice, dice, pivot	Classification, clustering, association, prediction
User interaction	Passive — user asks, system reports	Interactive — user explores the cube	Can be automatic — system discovers patterns
Output	Reports, summary tables	Multidimensional views	Rules, models, clusters, predictions
Example	"Total sales last quarter"	"Sales by Region × Product × Time, drilled to month"	"Which customers are likely to churn?"

• Information processing = basic querying/reporting — answers known questions.
• Analytical processing (OLAP) = interactive multidimensional exploration — user still drives the analysis by choosing what to slice/dice.
• Data mining = automated discovery — finds patterns the user didn't know to ask about (association rules, clusters, classifiers, outliers).

Motivation for OLAP Mining (OLAM)

OLAM (On-Line Analytical Mining) integrates OLAP with data mining, applying mining techniques directly on multidimensional data cubes rather than flat/raw data. Motivations:

1. High quality of data in warehouses — DW data is already cleaned, integrated, and consistent, so mining results are more reliable than mining raw operational data directly.
2. Available information processing infrastructure — ETL, metadata, indexing, and OLAP engines built for the warehouse can be reused for mining, avoiding duplicate infrastructure.
3. OLAP-based exploratory data analysis — users can interactively drill/roll/slice/dice to first narrow down interesting regions of data, then apply mining algorithms only where needed — much more efficient than mining the whole raw dataset blindly.
4. On-line selection of mining functions — integrating mining with OLAP navigation lets users flexibly switch between different mining tasks (classification, association, clustering) at different cube cells/granularities during the same interactive session.

        Data Warehouse (Cube)
               │
     ┌─────────┼─────────┐
     ▼         ▼         ▼
   OLAP     OLAM       Mining
 (explore) (integrated) (patterns)
     └─────────┴─────────┘
        Combined interactive,
        multi-level analysis

In short: OLAM lets analysts interactively navigate to the right subset of data using OLAP, then mine that subset for deeper patterns — combining the strengths of both technologies instead of treating them as separate tools.

OLAP and Its Role in Organisational Decision-Making

Definition. OLAP (Online Analytical Processing) is a technology for fast, interactive, multidimensional analysis of summarised data held in a data warehouse. It presents data as an OLAP cube whose dimensions are business perspectives (time, product, region, customer), whose cells hold measures (sales, revenue, quantity) and whose dimensions are organised into hierarchies (day $\to$ month $\to$ quarter $\to$ year).

1. Core operations

Operation	Meaning	Example
Roll-up (aggregation)	Climb up a hierarchy, summarise	Store sales $\to$ regional sales
Drill-down	Descend a hierarchy, refine	Year $\to$ quarter $\to$ month sales
Slice	Fix one dimension	Sales for the year 2024 only
Dice	Fix a sub-cube on several dimensions	Sales of electronics in the north region
Pivot (rotate)	Reorient the cube	Swap product and time axes

2. How OLAP supports decision-making

1. Multidimensional view. Managers see the same measure from any angle — region by product by month — instead of flat reports.
2. Speed. Pre-aggregated summaries (materialised cubes) give sub-second responses, so what-if questions can be asked interactively.
3. Drill-through detail. A KPI anomaly at the summary level can be traced down to the underlying transaction.
4. Trend and comparison analysis. Time hierarchies expose seasonality, growth and year-on-year comparison.
5. Consistency. All departments read the same governed numbers, so reports agree.
6. Integration with dashboards and data mining. OLAP supplies clean aggregated features for forecasting, association rules and reporting tools.

3. Real-world examples

• Retail: a supermarket chains analyses sales by store $\to$ region, product category $\to$ SKU and week; roll-up identifies underperforming regions, drill-down finds the specific SKUs responsible, informing stocking and pricing.
• Banking: a bank rolls up portfolio value by branch and product, slices by risk class and drills into delinquent loans for credit decisions.
• Telecom: call-volume cubes by plan, region and hour support capacity planning and churn campaigns.
• Healthcare / insurance: claim costs by hospital, treatment and patient group support cost control and fraud review.
• Manufacturing / supply chain: inventory by warehouse, part and day supports reorder and supplier decisions.

4. Types of OLAP servers

Type	Storage	Trade-off
Multidimensional (MOLAP)	Native cube array	Fastest queries, data duplicated
Relational (ROLAP)	Star/snowflake schema in the RDBMS	Scales to large detail data, slower aggregates
Hybrid (HOLAP)	Aggregates in MOLAP, detail in ROLAP	Balances both

Conclusion: OLAP turns raw warehouse data into an interactive multidimensional model, so managers can summarise, compare and trace figures quickly — which is why it remains a core tool for analytical decision support alongside OLTP, which only handles day-to-day transactions.

Data Warehouse — Definition, Key Features & OLTP vs OLAP

Definition

A Data Warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data used to support management's decision-making process (Bill Inmon).

Key Features

1. Subject-oriented — organized around major business subjects (Sales, Customer, Product), not individual applications.
2. Integrated — data pulled from multiple heterogeneous sources is made consistent (naming conventions, units, encoding).
3. Time-variant — stores historical data with a time dimension; every record is time-stamped, enabling trend analysis.
4. Non-volatile — once loaded, data is not updated/deleted by users; it's read-only for analysis, refreshed only via scheduled batch loads.
5. Summarized/denormalized — data is often pre-aggregated and stored in star/snowflake schemas for fast querying.

OLTP vs OLAP

Feature	OLTP	OLAP
Purpose	Run day-to-day operations	Support analysis & decisions
Data	Current, detailed	Historical, summarized
Schema	Normalized (E-R)	Denormalized (star/snowflake)
Query type	Simple, short transactions	Complex, multidimensional queries
Operations	Insert/Update/Delete	Roll-up, drill-down, slice, dice
Response time	Milliseconds	Seconds to minutes (large scans)
Users	Large number of clerks/staff	Fewer analysts/executives
Design goal	Concurrency, integrity	Query throughput, aggregation speed

     OLTP                              OLAP
┌───────────┐  periodic ETL       ┌───────────────┐
│ Live       │ ───────────────▶  │  Data          │
│ Transaction│                     │  Warehouse     │──▶ Analysis/Reports
│ System     │                     │  (subject-     │
└───────────┘                     │  oriented)     │
                                    └───────────────┘

In short: OLTP powers the business (fast writes, current state), while OLAP powers understanding the business (fast reads, historical trends) — the data warehouse exists specifically to bridge them without letting analytical load slow down live transactions.

Data Warehouse Architectures and Roles of Its Components

Comparison of single-tier, two-tier and three-tier architectures

Basis	Single-tier	Two-tier	Three-tier
Structure	One layer: the warehouse and the operational data are kept together; users query it directly	Two layers: a data source/warehouse (server) layer and a client layer, usually with data marts and a staging area	Three layers: bottom (warehouse DB server + ETL), middle (OLAP server), top (front-end tools)
Aim	Minimize the amount of stored data (remove redundancy)	Separate analysis from operational systems	Best separation and scalability
Performance	Poor: analytical queries slow down operational work	Better, but limited as the number of users grows	Best: OLAP server handles heavy analysis
Scalability	Very low	Limited (connectivity and network bottlenecks)	High
Complexity and cost	Lowest	Moderate	Highest
Use today	Rare, not suitable for real warehousing	Small or departmental warehouses	Most common in enterprises

Three-tier architecture in more detail

Top tier:     Front-end tools   (query, reports, dashboards, OLAP clients, data mining)
                    |
Middle tier:  OLAP server       (ROLAP / MOLAP / HOLAP)
                    |
Bottom tier:  Data warehouse DB  <--  ETL  <--  Operational sources + Metadata repository

Roles of components in decision-making

Component	Role
DBMS	Stores and manages warehouse data; provides indexing, partitioning, storage, security and efficient query processing for large volumes
ETL tools	Extract data from sources, clean and transform it, and load it into the warehouse, giving decision-makers accurate, consistent and integrated data
Metadata repository	Holds data about data: schema, mappings, lineage, business definitions, load history. It helps users understand and trust the data and helps administrators maintain it
Query and reporting tools	Let users run queries, produce reports and dashboards, perform OLAP analysis and data mining, so information reaches managers in usable form

How they support decisions

ETL supplies clean and consistent data; the DBMS stores it for fast access; metadata explains it; and query/reporting/OLAP tools let managers see trends, compare periods and drill into causes. Together they turn operational data into decision-ready information.

Conclusion

Single-tier is simple but impractical, two-tier is adequate for small systems, and three-tier is the preferred architecture for scalable, high-performance decision support.

OLAP vs MOLAP vs HOLAP

These are three server architectures for implementing OLAP, differing in where and how the multidimensional data is physically stored.

ROLAP (Relational OLAP)

• Stores data in standard relational tables (star/snowflake schema).
• Multidimensional operations are translated into SQL at query time.
• Pros: scales to very large data volumes, reuses existing RDBMS technology.
• Cons: slower query performance (SQL translation overhead), limited by SQL's multidimensional expressiveness.

MOLAP (Multidimensional OLAP)

• Stores data in a proprietary multidimensional array/cube structure (pre-computed).
• Pros: very fast query response (data pre-aggregated), rich analytical functions.
• Cons: cube-build time can be long, storage can explode for high-cardinality/sparse dimensions ("data explosion"), limited scalability for very large datasets.

HOLAP (Hybrid OLAP)

• Combines both: detailed data stays in relational tables (ROLAP), while summary/aggregated data is stored in MOLAP cubes.
• Pros: balances MOLAP's speed for aggregates with ROLAP's scalability for detailed data.
• Cons: more complex architecture to build and maintain.

Comparison Table

Aspect	ROLAP	MOLAP	HOLAP
Storage	Relational tables	Multidimensional array/cube	Mixed (relational + cube)
Query speed	Slower (SQL translation)	Fastest (pre-computed)	Balanced
Scalability	Very high (large data)	Limited (cube size)	High
Storage overhead	Low	High (sparse data explosion)	Moderate
Best for	Very large, detailed datasets	Fast, smaller/aggregated cubes	Mix of both needs

ROLAP:  [Relational DB] ──SQL──▶ query engine ──▶ result
MOLAP:  [Precomputed Cube] ──direct lookup──▶ result   (fast!)
HOLAP:  [Detail: Relational] + [Summary: Cube] ──▶ result (best of both)

In short: ROLAP trades speed for scalability, MOLAP trades scalability for speed, and HOLAP tries to get both by splitting detail vs summary data across the two storage models.

Data Warehouse Architecture (with diagram) + ROLAP, MOLAP, HOLAP

Architecture Diagram

┌───────────┐ ┌───────────┐ ┌───────────┐
│ Source DB1│ │ Source DB2│ │ Ext. Files│
└─────┬─────┘ └─────┬─────┘ └─────┬─────┘
      └──────────────┼──────────────┘
                      ▼
            ┌───────────────────┐
            │   ETL: Extract,     │
            │   Clean, Transform, │
            │   Load               │
            └──────────┬──────────┘
                        ▼
            ┌───────────────────┐
            │  Data Warehouse     │◀──▶ Metadata Repository
            │ (fact + dimension   │
            │  tables)            │
            └──────────┬──────────┘
                        │
        ┌───────────────┼───────────────┐
        ▼                ▼                ▼
  ┌───────────┐   ┌────────────┐   ┌────────────┐
  │ Data Marts │   │ OLAP Server │   │  Mining    │
  │ (dept-wise)│   │(ROLAP/MOLAP/│   │  Engine    │
  │            │   │  HOLAP)     │   │            │
  └───────────┘   └────────────┘   └────────────┘
                        │
                        ▼
              Front-end Tools (Query,
              Reporting, Dashboards)

Layers

1. Source systems — operational databases, files, external feeds.
2. ETL layer — extracts, cleans, transforms, and loads data into the warehouse.
3. Metadata repository — describes schema, source mappings, and lineage.
4. Central warehouse — stores integrated, subject-oriented, time-variant data as fact/dimension tables.
5. Data marts / OLAP servers / mining engine — provide focused or multidimensional access.
6. Front-end tools — dashboards, reports, ad-hoc query tools for end users.

ROLAP, MOLAP, HOLAP (OLAP server types)

Type	Storage	Speed	Scalability
ROLAP (Relational OLAP)	Star/snowflake tables in an RDBMS; multidimensional queries translated to SQL	Slower (query-time computation)	Very high — handles huge data volumes
MOLAP (Multidimensional OLAP)	Pre-computed proprietary cube (array structure)	Fastest (pre-aggregated lookups)	Limited — cube can explode in size for sparse/high-cardinality data
HOLAP (Hybrid OLAP)	Detail data in relational tables, summary data in cubes	Balanced	High — combines both strengths

In short: the architecture moves data from many raw sources through ETL into one integrated warehouse, which is then served up via an OLAP layer — and ROLAP/MOLAP/HOLAP are simply three different engineering trade-offs (storage vs speed vs scale) for implementing that OLAP layer.

ETL Framework

ETL stands for Extract, Transform, Load. It is the process that moves data from many source systems into the data warehouse in a clean, consistent form.

Flow

Sources (DBs, files, apps) -> EXTRACT -> Staging area -> TRANSFORM -> LOAD -> Data warehouse

Stages

Stage	What happens
Extract	Read the required data from heterogeneous sources (databases, flat files, APIs). Can be full extraction or incremental (only new or changed data, using timestamps or change data capture), without affecting the operational systems
Transform	Done in a staging area. Includes cleaning (fix errors, missing values, duplicates), standardization (formats, units, codes), integration (merge sources, key mapping), derivation (calculated fields), aggregation and applying business rules
Load	Write the transformed data into the warehouse: initial full load first, then periodic incremental/refresh loads; also update indexes, aggregates and data marts

Supporting elements

• Staging area: temporary storage where raw data is held and transformed.
• Metadata: records mappings, rules and lineage.
• Scheduling, logging and error handling: to run loads on time and track rejected records.

ETL vs ELT

In ELT the raw data is loaded first and transformed inside the target system (common with cloud and big data platforms).

Tools

Informatica, Talend, Microsoft SSIS, Apache NiFi, Pentaho.

Importance and challenges

• ETL gives decision-makers accurate, integrated and consistent data.
• Challenges: data quality, large volumes, load-time windows, changing source schemas and keeping history.

Data Warehouse Usage Types & Motivation for OLAM

(Same underlying question as the 2022 paper — see below for the full breakdown.)

Three Types of Data Warehouse Usage

Aspect	Information Processing	Analytical Processing	Data Mining
Goal	Query, basic statistics, reporting	Multidimensional analysis (OLAP)	Discover hidden patterns/knowledge
Operations	Simple queries, cross-tabs, charts	Roll-up, drill-down, slice, dice, pivot	Classification, clustering, association, prediction
User interaction	Passive — user asks, system reports	Interactive — user explores the cube	Can be automatic — system discovers patterns
Output	Reports, summary tables	Multidimensional views	Rules, models, clusters, predictions
Example	"Total sales last quarter"	"Sales by Region × Product × Time, drilled to month"	"Which customers are likely to churn?"

• Information processing answers known questions with simple queries/reports.
• Analytical processing (OLAP) lets users interactively explore a multidimensional cube.
• Data mining automatically discovers patterns the user didn't know to ask about.

Motivation for OLAP Mining (OLAM)

OLAM integrates OLAP with data mining, applying mining directly on multidimensional cubes instead of raw flat data:

1. High-quality warehouse data — already cleaned/integrated, giving more reliable mining results than mining raw operational data.
2. Reusable infrastructure — ETL, metadata, and OLAP engines built for the warehouse serve mining too, avoiding duplicated effort.
3. OLAP-based exploratory analysis — analysts drill/roll/slice/dice to narrow down interesting regions before mining, far more efficient than mining an entire raw dataset.
4. Flexible, on-line mining function selection — users can switch between classification, association, clustering, etc. at different cube cells/granularities within the same interactive session.

        Data Warehouse (Cube)
               │
     ┌─────────┼─────────┐
     ▼         ▼         ▼
   OLAP     OLAM       Mining
 (explore) (integrated) (patterns)
     └─────────┴─────────┘

In short: OLAM combines OLAP's interactive navigation with mining's automatic pattern discovery, letting analysts first zoom into the relevant cube region, then mine it for deeper insight.