Subjective Question Bank

Detailed answers to the core subjective questions from Unit 1: Data Warehouse. These answers are specifically formulated to cover all key points and examples found in the source images.

Core Questions and Answers​

1. Define a Data Warehouse and explain its key features with examples.​

A Data Warehouse is a centralized system used to store large amounts of data from different sources for analysis and decision-making.

Key Features:

• Subject-Oriented: Organized by subject (e.g., sales, customers) rather than by department or process.
• Integrated: Combines data from multiple sources into a consistent format.
• Time-Variant: Stores historical data to identify trends over time.
• Non-Volatile: Data is stable and not frequently changed; once entered, it is read-only.

Example: A company stores sales data from physical stores and websites in one place to analyze trends and improve business decisions.

2. Differentiate between Operational Databases and Data Warehouses.​

Feature	Operational Database (OLTP)	Data Warehouse (OLAP)
Purpose	Used for daily operations (transactions)	Used for analytical decision-making
Data Type	Current, up-to-date data	Historical + Current data
Usage	Day-to-day tasks (insert, update, delete)	Mainly read and analyze data
Users	Employees, clerks, applications	Managers, data analysts
Speed Focus	Fast processing of transactions	Fast querying and reporting

Example: An ATM system recording daily transactions (Operational) vs. a bank analyzing the past 5 years of transactions to find trends (DW).

3. Describe the various Data Warehouse Models.​

1. Enterprise Data Warehouse (EDW): A centralized warehouse for the entire organization that stores data from all departments. Used for overall business analysis. E.g, A company stores sales, HR, and finance data in one system.
2. Data Mart: A small part of a data warehouse focused on one specific department. It is easier and faster to use. E.g, Only sales department data stored for analysis.
3. Virtual Warehouse: A view of data from operational databases (not physically stored separately). Quick to create but depends on original data sources. e.g, Data shown directly from existing databases without full physical storage.

4. What are Concept Hierarchies? How are they useful in Data Warehousing?​

Concept hierarchies are a way of organizing data into different levels of detail, from detailed (low level) to general (high level).

• Example: Location hierarchy (City → State → Country) or Time hierarchy (Day → Month → Year).

Usefulness:

1. Data Summarization: Helps view data at a higher level (e.g., Sales by city → sales by country).
2. Drill-Down and Roll-Up: Enables navigating between detailed and summary views.
3. Efficient Querying: Reduces complexity and speeds up data analysis.
4. Better Analysis: Makes it easier to find patterns and trends.

5. Explain the OLAP operations with examples for each.​

1. Roll-Up (Summarize Data): Moves from detailed data → higher level summary. Example: Sales of Kolkata → West Bengal → India.
2. Drill-Down (Detailed View): The opposite of roll-up; moves from summary → detailed data. Example: Sales of India → West Bengal → Kolkata.
3. Slice (One Dimension Filter): Selects one specific value from a dimension. Example: Sales data for only 2024.
4. Dice (Multiple Filters): Selects multiple conditions. Example: Sales for 2024 + West Bengal + Electronics category.
5. Pivot (Rotate Data View): Changes the data perspective (swapping rows and columns). Example: Rows: Products, Columns: Years → changed to Rows: Years, Columns: Products.

6. Discuss the steps involved in designing and implementing a Data Warehouse.​

1. Requirement Analysis: Understand business needs and goals.
2. Data Source Identification: Identify where data will come from (databases, files, apps).
3. Data Design (Schema Design): Design structures like star or snowflake schemas.
4. ETL Process: Extract (collect data), Transform (clean and format), and Load (store into warehouse).
5. Data Storage: Store the processed data in the warehouse.
6. OLAP & Tools Setup: Use tools for analysis and reporting (e.g., dashboards).
7. Testing: Check if data is correct and the system works properly.
8. Deployment & Maintenance: Start using the warehouse and update it regularly.

7. Define Fact and Dimension.​

• Fact: Numerical data (numbers) used for analysis.
  Example: sales, profit, quantity

• Dimension: Descriptive data that explains the fact (gives context).
  Example: customer, product, city, date

8. What is a Star Schema?​

A star schema is a data warehouse design where one central fact table is shared by multiple dimension tables, forming a shape like a star.

• Structure: A central Fact Table containing numerical data (e.g., sales, profit) surrounded by Dimension Tables containing descriptive data (e.g., customer, product, time).
• Use Case: Best for simplicity and fast queries in small/medium systems.

9. What is a Galaxy Schema (Fact Constellation)? When is it used?​

A galaxy schema is a data warehouse design where common dimension tables are shared by multiple fact tables. It looks like a collection of star schemas connected together.

When is it used?

• When a system has multiple related business processes.
• When different facts need to be analyzed together.
• In large and complex organizations (e.g., analyzing sales + shipments + inventory together).

10. Write the comparison of common Data Warehouse schemas.​

Feature	Star Schema	Snowflake Schema	Galaxy Schema
Structure	Simple (Star shape)	Complex	Very complex
Fact Tables	One	One	Multiple
Dimension Tables	Denormalized (High redundancy)	Normalized (Low redundancy)	Shared across facts
Query Performance	Fast	Slower (due to joins)	Depends (complex queries)
Ease of Design	Easy	Moderate	Difficult
Best Use Case	Simplicity and fast queries	Reducing redundancy	Complex systems with multiple processes