Seventh Semester Information Technology Subject Code: BTIT701T

Syllabus:

UNIT I: Introduction to Data Warehousing: What is Data Warehousing?. Operational v/s decision support systems, Data warehousing Architecture. Data Warehouse Models, Metadata Repository. Data Marts and Metadata. Data warehouses versus Data Marts.

UNIT ll: Data Preprocessing: Why preprocess the data?. Major Tasks in data preprocessing. Descriptive data summarization, Data Cleaning, Data Integration. Data Reduction, Data Transformation and Data Discretization.

UNIT Ill: OLAF Analytical Processing: Data Cube. Multidimensional Data Model, Schemes for Multidimensional Data Model, Concept Hierarchies. Measures, OLAP Operations, Data Cube Computations, OLAP Servers, major, OLAP models- ROLAP MOLAP, HOLAP.

UNIT IV: Introduction of Data Mining: Why Data Mining. What is Data Mining, Data Mining functionalities, Interesting Patterns, Data Mining techniques from many domains, Architecture, Classification of Data Mining. Applications of Data Mining, Major issues in Data Mining.

UNIT V: Mining Frequent Patterns and Association Rules: Market Basket Analysis, Frequent Item sets, Closed Item sets, and Association Rule. Frequent helmet Mining Methods: Apriori Algorithm, Generating Association Rules from Frequent Itemsets. Improving the Efficiency of Apriori, FP growth, Mining Frequent Itemsets using Vertical Data Format

Textbooks:

Professional Hadoop Solutions by Lublinsky, Smith, Yakubovich, Wiley.

Data Mining (Concepts and Techniques) – by lan and !Camber.

Data Mining and Business Intelligence – by Shinde and Chandrashekhar.

Unit I: Introduction to Data Warehousing – Lecture Notes

1.   What is Data Warehousing?

A data warehouse (DW or DWH) serves as a central repository of information that can be analyzed to make more informed decisions. Data flows into a data warehouse from transactional systems, relational databases, and other sources, typically on a regular cadence. Business analysts, data engineers, data scientists, and decision-makers access the data through Business Intelligence (BI) tools, SQL clients, and other analytics applications.

Data warehousing is the process of constructing and using a data warehouse. It involves cleaning, integrating, and consolidating data from various sources to provide a unified view of the organization’s information. The primary goal is to support business intelligence activities such as reporting, analytics, and data mining, ultimately enabling better strategic decision-making.

Perhaps the most widely accepted definition of a data warehouse was coined by Bill Inmon, often referred to as the “father of data warehousing”. According to Inmon, a data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data organized to support management’s decision-making process [1][2]. Let’s break down these key characteristics:

• Subject-Oriented: Data warehouses are designed around the major subjects of the enterprise (e.g., customers, products, sales, suppliers) rather than the application- specific operations (e.g., order processing, inventory control). This focus allows for analysis centered on key business elements. For instance, instead of focusing on individual transactions, a subject-oriented warehouse might focus on analyzing sales trends for a specific product category over time [3].
  • Integrated: Data warehouses integrate data from multiple, often heterogeneous, sources (e.g., relational databases, flat files, online transaction records). This integration involves resolving inconsistencies in naming conventions, data formats, units of measure, and data structures to provide a cohesive view. For example, customer data from sales, marketing, and support systems might be integrated

into a single, consistent customer record [3].

• Time-Variant: Data in a warehouse provides information from a historical perspective. Unlike operational systems that typically store only current values, a

data warehouse maintains historical data, allowing analysis of trends, changes, and comparisons over time. Data is often timestamped or associated with specific time periods (e.g., daily, weekly, monthly snapshots) [3].

• Non-Volatile: Data in the warehouse is typically read-only and is not updated or

deleted in real-time like operational data. Once data enters the warehouse, it remains stable and is not overwritten. New data is added periodically as snapshots, preserving the history. This stability is crucial for consistent analysis and reporting [3].

The fundamental purpose of a data warehouse is to transform operational data into meaningful information that supports strategic analysis and decision-making, moving beyond the day-to-day transactional focus of operational systems.

2.   Operational Systems vs. Decision Support Systems (DSS)

Understanding the distinction between operational systems and decision support systems (DSS) is fundamental to grasping the purpose of data warehousing. While both are crucial for an organization, they serve vastly different functions and have distinct characteristics.

Operational Systems (OLTP – Online Transaction Processing): Operational systems, often powered by Online Transaction Processing (OLTP) databases, are designed to support the day-to-day, real-time business operations of an organization [4]. Their primary focus is on transaction processing – recording, updating, and managing the data generated by ongoing business activities. Examples include systems for order entry, inventory management, customer relationship management (CRM), and point-of-sale (POS) transactions.

Key characteristics of operational systems include: * Purpose: Support daily business processes and transactions. * Data: Primarily current, detailed, and frequently updated data reflecting the present state of the business. * Processing: Optimized for high- volume, fast transaction processing (inserts, updates, deletes). * Users: Typically front- line workers, clerks, and administrators who perform operational tasks. * Data Structure: Often highly normalized to minimize data redundancy and ensure data integrity during transactions [4]. * Focus: Application-oriented, designed around specific business functions or applications.

Decision Support Systems (DSS) / Data Warehouses (OLAP – Online Analytical Processing): Decision Support Systems, often implemented using data warehouses and powered by Online Analytical Processing (OLAP) techniques, are designed specifically to

support managerial decision-making, business intelligence, and analytical activities [4]. They provide a platform for analyzing historical and consolidated data to identify trends, patterns, and insights that inform strategic planning.

Key characteristics of DSS/Data Warehouses include: * Purpose: Support analysis, reporting, querying, and strategic decision-making. * Data: Primarily historical, summarized, and consolidated data drawn from multiple operational systems over time.

* Processing: Optimized for complex queries, reporting, and analytical processing involving large volumes of data. * Users: Typically managers, business analysts, data scientists, and executives who require insights for planning and strategy. * Data Structure: Often denormalized (e.g., using star or snowflake schemas) to simplify and speed up complex analytical queries and reporting [4]. * Focus: Subject-oriented,

organized around key business subjects like customers, products, or sales.

Key Differences Summarized [4]:

In essence, operational systems are optimized for running the business efficiently, while data warehouses (as the core of many DSS) are optimized for analyzing the business effectively to make better strategic choices. The data warehouse extracts, transforms,

and loads data from various operational systems to create an integrated, historical view suitable for analysis, bridging the gap between daily operations and strategic insight.

3.   Data Warehousing Architecture

Data Warehouse Architecture refers to the overall structure and framework used to design, build, and manage a data warehouse. It defines how data is collected from various sources, transformed, stored, and made accessible for analysis and reporting. A well-designed architecture is crucial for the efficiency, scalability, and effectiveness of a data warehouse.

While specific implementations can vary, a typical data warehouse architecture comprises several key layers and components [5][6]:

1. Data Source Layer: This layer consists of the various source systems that provide the raw data for the warehouse. These can be internal operational systems (like OLTP databases for sales, inventory, HR), external data sources (like market data feeds, social media data), or even existing legacy systems. The data can be structured (databases, spreadsheets), semi-structured (XML, JSON), or unstructured (text documents, emails) [5].
2. Data Staging Layer (ETL Area): Before data enters the central warehouse storage, it typically passes through a staging area. This is a temporary storage space where data undergoes the Extract, Transform, Load (ETL) process (or sometimes ELT – Extract, Load, Transform).

• Extract: Data is pulled from the various source systems.
  • Transform: Data is cleaned, validated, standardized, integrated, and consolidated. This is often the most complex part, involving tasks like handling missing values, resolving inconsistencies, converting data types and units, and combining data from different sources.
  • Load: The transformed and cleansed data is loaded into the data warehouse storage layer [5]. ETL tools are commonly used to automate and manage these processes.
• Data Storage Layer (Central Repository): This is the core of the architecture where the integrated and transformed data is stored. It usually consists of a large relational database optimized for query and analysis rather than transaction processing. This layer houses the detailed historical data, summarized data (aggregates), and crucial metadata. The actual data warehouse database is stored here [6].

• Metadata Repository: Metadata, often described as “data about data,” plays a critical role throughout the architecture. The metadata repository stores information about the data warehouse’s structure, content, and processes. This includes:

• Business Metadata: Definitions of data elements in business terms, ownership information, usage rules.
  • Technical Metadata: Database schemas, table and column definitions, data types, source-to-target mappings, transformation rules, data lineage.
  • Operational Metadata: Load statistics, error logs, audit trails, usage statistics, data quality metrics. Metadata provides context, facilitates data discovery and understanding, aids in managing the warehouse, and supports

data governance [3][7].

• Data Access/Presentation Layer (BI Layer): This is the front-end layer through which end-users interact with the data warehouse. It provides tools and interfaces for querying, reporting, analysis, and data mining. Common tools include:
  • Query and reporting tools (e.g., SQL clients, report writers)
  • Online Analytical Processing (OLAP) tools (for multidimensional analysis)
  • Data mining tools (for discovering patterns and predictive modeling)
  • Business Intelligence (BI) dashboards and visualization tools. This layer translates user requests into queries that run against the warehouse storage and presents the results in an understandable format [6].
• (Optional) Data Marts: Instead of accessing the central warehouse directly for all analyses, organizations often create smaller, focused subsets of the warehouse called data marts. Each data mart is typically designed for a specific business line, department (e.g., Sales, Marketing, Finance), or subject area. Data marts contain a subset of the warehouse data relevant to their specific users, making access faster and simpler for targeted analysis. They can be populated directly from the central warehouse [5].

Architectural Approaches: Two primary architectural design philosophies often discussed are: * Top-Down (Inmon): Starts by building a comprehensive, centralized Enterprise Data Warehouse (EDW) first. Data marts are then created as dependent subsets derived from the EDW. This approach emphasizes consistency and integration across the enterprise [5]. * Bottom-Up (Kimball): Starts by building individual, independent data marts focused on specific business processes or departments. These marts are designed using conformed dimensions and facts, allowing them to be integrated later into a logical enterprise warehouse. This approach often delivers results faster for specific business needs [5].

Modern architectures also increasingly incorporate concepts like data lakes and cloud- based warehousing solutions, sometimes blending elements of these traditional approaches.

4.   Data Warehouse Models

Data warehouse models refer to the specific architectural approaches or structures used to implement a data warehouse environment. The choice of model depends on factors like organizational scope, complexity, budget, and specific analytical requirements.

Three common models are often discussed [8][9]:

1. Enterprise Data Warehouse (EDW):
   1. Description: An EDW is a large-scale, centralized data warehouse that integrates data from various systems across the entire organization. It provides a unified, comprehensive view of all aspects of the business, covering multiple subject areas (e.g., sales, finance, HR, supply chain).
   1. Approach: Typically follows a top-down design methodology (like Inmon’s approach), where the central EDW is built first, and then specific data marts can be derived from it if needed.
   1. Characteristics: Aims to be the single source of truth for the entire enterprise.

It involves significant effort in data modeling, integration, and governance to ensure consistency and quality across all business units.

• Pros: Provides a holistic view, ensures data consistency across the

organization, strong governance.

• Cons: Can be complex, time-consuming, and expensive to build and maintain. May take longer to deliver initial value compared to focused data marts [10].
• Data Mart:
  • Description: A data mart is a smaller, simpler form of a data warehouse that is focused on a single subject area or line of business (LOB), such as Marketing, Sales, or Finance. It contains a subset of data relevant to a specific group of users or department.
  • Approach: Can be built using a bottom-up methodology (like Kimball’s approach), where individual data marts are created first based on specific business needs and potentially integrated later. Alternatively, data marts can be dependent, meaning they are sourced directly from a central EDW.
  • Characteristics: Limited scope, tailored to specific user needs, quicker to implement, and easier to manage than a full EDW. Contains summarized or detailed data specific to its domain.

• Pros: Faster implementation, lower initial cost, addresses specific departmental needs quickly, simpler structure.
  • Cons: Can lead to data redundancy and inconsistency if multiple independent data marts are created without proper coordination (data silos). Integrating independent marts later can be challenging [10].
• Virtual Data Warehouse:
  • Description: A virtual data warehouse provides a way to access and query data from source systems directly without physically integrating and storing the data in a separate warehouse repository. It uses middleware or data virtualization tools to create a logical view of data distributed across different operational systems.
  • Approach: Does not involve the traditional ETL process into a central storage layer. Instead, queries are decomposed and routed to the appropriate source systems in real-time.
  • Characteristics: Provides access to current operational data. Avoids the need

for large-scale data storage and complex ETL processes.

• Pros: Lower cost for storage and ETL development, provides real-time access to source data, relatively quick to set up for specific analytical tasks.
  • Cons: Can put a significant query load on operational systems, potentially impacting their performance. Data consistency and quality depend heavily on the source systems. Complex queries across multiple sources can be slow.

Limited historical data analysis capabilities unless source systems retain history [8][9].

Choosing a Model: The best model (or combination of models) depends on the organization’s specific context. Large organizations often aim for an EDW for consistency, potentially feeding dependent data marts. Smaller organizations or specific

departments might start with data marts. Virtual warehouses can be useful for specific real-time reporting needs but are generally not a replacement for a full physical warehouse for complex historical analysis.

5.   Metadata Repository

Metadata is often defined as “data about data.” In the context of a data warehouse, metadata provides the essential context, definitions, and lineage information needed to understand, manage, and utilize the warehouse data effectively. The Metadata Repository is the component within the data warehouse architecture specifically designed to store and manage this metadata [7][11]. It acts as a central directory or catalog for the entire data warehouse environment.

Importance and Role of Metadata: Metadata is not just supplementary information; it is fundamental to the usability and maintainability of a data warehouse. Its key roles include:

• Data Understanding and Interpretation: Metadata explains what the data represents (business definitions), where it came from (source systems), how it was transformed (ETL logic), and its structure (schemas, tables, columns). This allows users (analysts, developers, business users) to correctly interpret and use the data [11][12].
  • Data Governance and Compliance: It supports data governance initiatives by documenting data ownership, access rules, security classifications, and data quality standards. This is crucial for compliance with regulations and internal policies [12].
  • Data Lineage and Impact Analysis: Metadata tracks the flow of data from source systems through ETL processes to the final reports or analyses (data lineage). This is vital for understanding dependencies, troubleshooting issues, and performing impact analysis when changes are proposed to source systems or transformation logic [7].
  • System Management and Automation: Technical metadata is used by ETL tools, BI tools, and database management systems to automate processes like data loading, query generation, and report creation. It helps manage the complexity of the warehouse environment [11].
  • User Navigation and Discovery: Metadata acts as a guide, helping users navigate the warehouse, discover relevant data sets, and understand how different data elements relate to each other [7].

Types of Metadata Stored: The metadata repository typically stores various types of metadata, often categorized as:

1. Business Metadata: Provides context from a business perspective. Includes business definitions, data ownership, contact information, data quality rules, usage guidelines, and descriptions meaningful to non-technical users [7].
2. Technical Metadata: Describes the technical aspects of the data and processes.

Includes database schemas, table/column definitions, data types, primary/foreign keys, source-to-target mappings, transformation rules, ETL job details, report definitions, and access permissions [7][11].

• Operational Metadata: Provides information about the processing and usage of

the data warehouse. Includes ETL job execution statistics (start/end times, rows processed), error logs, audit trails, data access patterns, query performance data, and data backup/archive information [7].

The Metadata Repository: This repository is essentially a database designed to store and manage these different types of metadata. It needs to be accessible to various tools (ETL, BI, data modeling) and users. A well-managed metadata repository ensures that metadata is consistent, accurate, and up-to-date, acting as the central nervous system of the data warehouse environment [11]. Without a robust metadata strategy and repository, a data warehouse can quickly become a confusing and untrustworthy “data swamp.”

6.   Data Marts and Metadata

A Data Mart, as previously introduced in the context of Data Warehouse Models, is a subset of a data warehouse focused on a specific business line, department, or subject area (e.g., Sales, Finance, Marketing) [5]. It contains data tailored to the needs of a particular user group, making analysis simpler and faster for that group. Data marts can be structured as smaller, independent warehouses or as dependent subsets drawing data from a larger Enterprise Data Warehouse (EDW).

Defining Data Marts: Key characteristics of data marts include: * Focused Scope: Limited to a specific business process or subject area. * Targeted Users: Designed for a particular department or user group. * Simplified Structure: Often uses a dimensional model (like a star schema) optimized for the specific analytical needs of its users. * Smaller Size: Contains less data than a full EDW. * Implementation: Can be implemented more quickly and at a lower cost than an EDW.

Relationship with Metadata: Metadata is just as crucial for data marts as it is for the enterprise data warehouse, perhaps even more so if multiple independent data marts exist. The metadata associated with a data mart serves several purposes:

• Understanding Mart Content: Metadata defines the tables, columns, measures, and dimensions within the data mart, explaining their business meaning and calculation logic. This is essential for users to correctly interpret the mart’s data.
  • Tracking Data Origin (Lineage): If the data mart is dependent (sourced from an

EDW), metadata tracks which EDW tables and columns were used to populate the mart and what transformations were applied. If the mart is independent (sourced directly from operational systems), metadata documents these sources and the ETL processes used. This lineage is vital for trust and troubleshooting [7].

• Ensuring Consistency (Conformed Dimensions): In environments with multiple

data marts (especially in a Kimball-style bottom-up approach), metadata plays a critical role in managing “conformed dimensions.” These are dimensions (like Customer, Product, Time) that are defined consistently across different data marts,

allowing results to be integrated or compared meaningfully. The metadata repository documents these standard definitions [5].

• Facilitating Integration: Metadata helps understand how different data marts

relate to each other and potentially to a central EDW, facilitating integrated analysis across business functions.

Essentially, each data mart should have its own associated metadata, describing its specific contents and processes. In an ideal scenario, particularly with an EDW, the metadata for all data marts would be managed centrally within the main metadata repository, providing an integrated view of all analytical assets. Without proper metadata management, data marts can become isolated silos of information, difficult to understand, trust, or integrate.

7.   Data Warehouses versus Data Marts

While both data warehouses and data marts serve analytical purposes, they differ significantly in scope, complexity, and implementation. Understanding these differences is key to choosing the right approach for specific organizational needs.

Here’s a comparison highlighting the key distinctions:

Key Takeaways:

• Scope is the primary differentiator: An EDW aims for a holistic, enterprise-wide view, while a data mart focuses on a specific segment of the business.
  • Complexity and Cost: Building an EDW is a major undertaking requiring significant resources, whereas data marts are comparatively simpler and cheaper to implement.
  • Time-to-Value: Data marts generally deliver analytical capabilities to specific user

groups much faster than a full EDW project.

• Data Consistency: EDWs, by their nature, enforce data consistency across the organization. Independent data marts risk creating inconsistencies (data silos) if not carefully managed with conformed dimensions and centralized metadata.

Relationship: It’s important to note that data warehouses and data marts are not mutually exclusive. In many large organizations, they coexist. Data marts can be dependent, meaning they are populated with data sourced directly from a central EDW. This approach combines the enterprise consistency of an EDW with the focused, simplified access of a data mart. Alternatively, independent data marts can be built first (bottom-up), but this requires careful planning (using conformed dimensions and facts) to enable future integration and avoid inconsistencies [5][10].

Choosing between starting with an EDW or data marts depends on the organization’s strategy, resources, and immediate analytical needs.

References:

[1] Inmon, W. H. (1992). Building the Data Warehouse. Wiley.

[2] Wikipedia contributors. (Accessed 2025). Bill Inmon. Wikipedia, The Free Encyclopedia. Retrieved from https:// en.wikipedia.org/wiki/Bill_Inmon

[3] Astera. (2024, September 3). Data Warehouse Concepts: Kimball vs. Inmon Approach. Astera Blog. Retrieved from https:// www.astera.com/type/blog/data-warehouse-concepts/

[4] GeeksforGeeks. (2023, April 20). Differences between Operational Database Systems and Data Warehouse. Retrieved from https://www.geeksforgeeks.org/differences-between-operational-database- systems-and-data-warehouse/

[5] GeeksforGeeks. (2025, January 27). Data Warehouse Architecture. Retrieved from https://www.geeksforgeeks.org/data-warehouse- architecture/

[6] SAP. (Accessed 2025). What is a data warehouse?. SAP Insights. Retrieved from https://www.sap.com/products/data-cloud/datasphere/what-is-a-data- warehouse.html

[7] GeeksforGeeks. (2023, May 2). What is Meta Data in Data Warehousing?. Retrieved from https://www.geeksforgeeks.org/what-is-meta-data-in- data-warehousing/

[8] Panoply. (Accessed 2025). Data Warehouse Architecture: Traditional vs. Cloud Models. Panoply Blog. Retrieved from https://panoply.io/data- warehouse-guide/data-warehouse-architecture-traditional-vs-cloud/

[9] Washington University – CSE P573. (Accessed 2025). Three Data Warehouse Models. Course Lecture Notes. Retrieved from https://courses.cs.washington.edu/courses/csep573/01sp/ lectures/class1/tsld046.htm

[10] Bluwave. (2023, February 24). Data Warehouse Types: How To Choose the Right One. Bluwave Blog. Retrieved from https://www.bluwave.net/ data-warehouse-types-comparison/

[11] Sprinkledata. (2024, June 25). Understanding Metadata and its Role in Data Warehouse. Sprinkledata Blog. Retrieved from https:// www.sprinkledata.com/blogs/metadata-data-warehouse-understanding-metadata-and-  its-role-in-data-warehouse

[12] Atlan. (2023, December 1). Metadata in Data Warehouse: 8 Ways It is Beneficial For You. Atlan Blog. Retrieved from https://atlan.com/metadata-in- data-warehouse/

Question Bank:

Unit I: Introduction to Data Warehousing – Question Bank

Instructions: Answer the following questions comprehensively. Marks may vary based on the depth and accuracy of the answer.

1. Define a Data Warehouse according to W. H. Inmon. Explain each of the four key characteristics (subject-oriented, integrated, time-variant, non-volatile) with suitable examples.

2.Compare and contrast Operational Database Systems (OLTP) with Data Warehousing Systems (OLAP/DSS). Discuss at least five key differences, including their purpose, data characteristics, and typical users.

3.Describe the typical multi-layered architecture of a Data Warehouse. Explain the purpose and function of each major layer (e.g., Data Source, Staging Area, Data Storage, Metadata Repository, Presentation Layer).

4.What is a Metadata Repository in the context of data warehousing? Explain the different types of metadata (Business, Technical, Operational) stored within it and discuss why metadata management is crucial for the success of a data warehouse.

5.Discuss the three common Data Warehouse Models: Enterprise Data Warehouse (EDW), Data Mart, and Virtual Data Warehouse. Explain the primary characteristics, advantages, and disadvantages of each model.

6.Explain the concept of a Data Mart. How does it differ from an Enterprise Data Warehouse? Discuss the relationship between Data Marts and Metadata, particularly concerning data lineage and consistency.

7.Describe the ETL (Extract, Transform, Load) process within the data warehouse architecture. Why is the ‘Transform’ step often considered the most critical and complex? Provide examples of common transformation tasks.

8.Differentiate between the Top-Down (Inmon) and Bottom-Up (Kimball) approaches to data warehouse development. Discuss the pros and cons of each approach.

9.Why is data integration a fundamental characteristic and challenge in data warehousing? Discuss the types of inconsistencies that need to be resolved during the integration process.

10.Explain the concept of non-volatility in a data warehouse. How does this characteristic support the goals of decision support and analysis, and how does it differ from the volatility found in operational systems?