Back to Study Material GATE Exam

Last Minute Notes Data Warehousing

Core definitions: Data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management decision-making....

Last Minute Notes Data Warehousing

Quick Revision: Data Warehousing Definitions and Schemas

Core definitions: Data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management decision-making. OLTP (Online Transaction Processing) handles day-to-day transactions with frequent small updates and queries. OLAP (Online Analytical Processing) handles complex analytical queries over large historical datasets. Data marts are subject-specific subsets of the data warehouse for a single department or business area.

ETL stands for Extract, Transform, Load. Extract: pull data from source systems (databases, files, APIs). Transform: clean data (remove duplicates, handle nulls), standardize formats, enforce business rules, integrate data from multiple sources. Load: insert transformed data into warehouse. ETL is the most time-consuming part of warehouse implementation in practice.

Schema types: Star schema has one fact table at center, directly connected dimension tables (denormalized). Fastest for queries due to fewer joins. Snowflake schema has normalized dimension tables with additional levels of tables. More storage efficient but slower queries. Galaxy (fact constellation) schema has multiple fact tables sharing dimension tables. Used for multiple related business processes.

Fact tables contain measures (numerical values like sales, count, revenue) and foreign keys to dimension tables. Measures are additive (can be summed across all dimensions), semi-additive (can be summed across some dimensions like inventory balance across time), or non-additive (cannot be summed like price or ratio). Dimension tables contain descriptive text attributes used for filtering and grouping in queries.

Dimension hierarchy examples: time dimension has year, quarter, month, week, day. Geography dimension has country, region, state, city, store. Product dimension has category, subcategory, product. Hierarchies support roll-up and drill-down operations.

Slowly changing dimensions: Type 1 overwrites old value with new (no history). Type 2 adds a new row with new value and timestamps (full history preserved). Type 3 adds a new column for old value (limited history).

Data granularity: grain of the fact table defines what one row represents. Finer grain (individual transaction) means more rows but allows more flexible analysis. Coarser grain (daily summary) means fewer rows but limits detailed analysis.

GATE tip: if asked about fact versus dimension tables, remember that fact tables grow rapidly over time as new transactions occur, while dimension tables are relatively static and small.

Quick Revision: OLAP Operations and Data Mining Basics

Five core OLAP operations to memorize with one-line definitions:

Roll-up: aggregate data by moving up a hierarchy (city to region to country). Reduces detail, increases summary. Corresponds to GROUP BY with fewer attributes in SQL.

Drill-down: disaggregate data by moving down a hierarchy (year to quarter to month). Increases detail, reduces summary. Reverse of roll-up.

Slice: fix one dimension at a specific value to produce a lower-dimensional view (select only Q1 data, removing time as a free dimension). One dimension becomes fixed.

Dice: select a range or subset of values across two or more dimensions to produce a sub-cube (select Q1 and Q2 of years 2022 and 2023 and products A and B). Multiple dimensions restricted.

Pivot (rotate): reorient the view by rotating axes. Rows become columns and vice versa. Same data, different presentation. Useful for comparing across a different dimension.

OLAP implementation types: MOLAP stores data in proprietary multidimensional arrays, very fast for pre-aggregated queries, less scalable for large sparse datasets. ROLAP stores data in relational tables and translates OLAP queries to SQL, scales well, slower for complex cross-dimensional queries. HOLAP is a hybrid: detailed data in relational form, aggregates in multidimensional form.

Data mining basics for GATE context: data mining discovers patterns and knowledge from large datasets. Classification assigns data items to predefined categories using algorithms like decision trees, k-nearest neighbors, naive Bayes, and neural networks. Clustering groups data items into natural clusters without predefined categories using algorithms like k-means and hierarchical clustering. Association rule mining discovers co-occurrence patterns using the Apriori algorithm, generating rules like if item A is purchased then item B is likely purchased. Key metrics: support is the fraction of transactions containing the itemset. Confidence is the fraction of transactions with A that also contain B. Lift is confidence divided by the expected confidence if A and B were independent.

Common GATE question areas in this domain: identifying the correct OLAP operation from a description, distinguishing star from snowflake schema in a diagram, identifying fact versus dimension tables, and occasionally a basic data mining definition.

Last-minute checklist: can you name all five OLAP operations with examples, distinguish star and snowflake schemas from a diagram, identify what ETL stands for and what each step does, define the four Inmon properties of a data warehouse, and name the three OLAP implementation types. If yes, you are well prepared for any Data Warehousing question GATE is likely to ask.