Data Warehousing

UNIT-2 Data Warehousing: Introduction, Definition, three-tier data warehousing architecture, OLTP vs OLAP Systems, Data Warehouse Models(Enterprise Warehouse, Data Mart, and Virtual), Warehouse Schemas for Multidimensional Data Models (Stars, Snowflakes, and Fact Constellations), Typical OLAP Operations. Data Warehouse: A data warehouse is an enterprise system used for the analysis and reporting of structured and semi-structured data from multiple sources, such as point-of-sale transactions, marketing automation, customer relationship management, and more. A data warehouse is suited for ad hoc analysis as well custom reporting. A data warehouse can store both current and historical data in one place and is designed to give a long-range view of data over time, making it a primary component of business intelligence. The term "Data Warehouse" was first coined by Bill Inmon in 1990. According to William H.Inmon, a leading architect in the construction of data warehouse systems, “A data warehouse is a subject-oriented, integrated, timevariant, and nonvolatile collection of data in support of management’s decision making process”. Characteristics of Data ware house/terms of data warehouse Subject-oriented: A data warehouse is organized around major subjects such as customer, supplier, product, and sales. Rather than concentrating on the day-to-day operations and transaction processing of an organization, a data warehouse focuses on the modeling and analysis of data for decision makers. Integrated: A data warehouse is usually constructed by integrating multiple heterogeneous sources, such as relational databases, flat files, and online transaction records. Data cleaning and data integration techniques are applied to ensure consistency in naming conventions, encoding structures, attribute measures, and so on. Time-variant: Data are stored to provide information from an historic perspective (e.g., the past 5–10 years). Every key structure in the data warehouse contains, either implicitly or explicitly, a time element. Nonvolatile: A data warehouse is always a physically separate store of data transformed from the application data found in the operational environment. Due to this separation, a data warehouse does not require transaction processing, recovery, and concurrency control mechanisms. It usually requires only two operations in data accessing: initial loading of data and access of data. Operational Database Systems and Data Warehouse Operational Database: The Operational Database is the source of data for the information distribution center. It incorporates point by point data utilized to run the day to day operations of the trade. The information as often as possible changes as upgrades are made and reflect the current esteem of the final transactions. Operational Database Administration Frameworks too called as OLTP (Online Transactions Processing Databases) Data Warehouse: A Data Warehouse is a system that is used by the users or knowledge managers for data analysis and decision-making. It can construct and present the data in a certain structure to fulfill the diverse requirements of several users. Data warehouses are also known as Online Analytical Processing (OLAP) Systems. In a data warehouse or OLAP system, the data is saved in a format that allows the effective creation of data mining documents. The data structure in a data warehousing has demoralized schema. Performance-wise, data warehouses are quite fast when it comes to analyzing queries. Data warehouse systems do the integration of several application systems. These systems then provide data processing by supporting a solid platform of consolidated historical data for analysis. Differences between operational database systems and data warehouses Purpose Data Structure Data Volume Performance operational database systems Operational database systems are used to support day-today operations of an organization Operational database systems typically have a normalized data structure, which means that the data is organized into many related tables to reduce data redundancy and improve data consistency. Operational database systems typically store a smaller volume of data compared to data warehouses Operational database systems are optimized for transaction processing and are designed to support high-volume, highspeed transaction processing. data warehouse data warehouses are used to support decision-making and analysis activities. Data warehouses, on the other hand, typically have a de normalized data structure, which means that the data is organized into fewer tables optimized for reporting and analysis. which may store years of historical data(Large amount of data will store) ata warehouses, on the other hand, are optimized for querying and reporting and are designed to support complex analytical queries that may involve large volumes of data. Differences between OLAP and OLTP Criteria Purpose Data source Data structure OLAP OLAP helps you analyze large volumes of data to support decisionmaking. OLAP uses historical and aggregated data from multiple sources. OLAP uses OLTP OLTP helps you manage and process real-time transactions. OLTP uses real-time and transactional data from a single source. OLTP uses relational Data model Volume of data Response time Example applications multidimensional (cubes) or relational databases. OLAP uses star schema, snowflake schema, or other analytical models. OLAP has large storage requirements. Think terabytes (TB) and peta bytes (PB). OLAP has longer response times, typically in seconds or minutes. OLAP is good for analyzing trends, predicting customer behavior, and identifying profitability. databases. OLTP uses normalized or de normalized models. OLTP has comparatively smaller storage requirements. Think gigabytes (GB). OLTP has shorter response times, typically in milliseconds OLTP is good for processing payments, customer data management, and order processing. Three-tier architecture of data warehouse The Three-Tier Data Warehouse Architecture is the commonly used Data Warehouse design in order to build a Data Warehouse by including the required Data Warehouse Schema Model, the required OLAP server type, and the required front-end tools for Reporting or Analysis purposes, which as the name suggests contains three tiers such as Top tier, Bottom Tier and the Middle Tier that are procedurally linked with one another from Bottom tier(data sources) through Middle tier(OLAP servers) to the Top tier(Front-end tools). Data Warehouse Architecture is the design based on which a Data Warehouse is built, to accommodate the desired type of Data Warehouse Schema, user interface application and database management system, for data organization and repository structure. The type of Architecture is chosen based on the requirement provided by the project team. Three-tier Data Warehouse Architecture is the commonly used choice, due to its detailing in the structure. The three different tiers here are termed as:  Top-Tier  Middle-Tier  Bottom-Tier Each Tier can have different components based on the prerequisites presented by the decision-makers of the project but are subject to the novelty of their respective tier.