Data Warehousing Architectures Overview

Data Warehousing Overview

• Data warehousing architectures are divided into three parts: the data warehouse, data acquisition software, and client software.
• Factors affecting architecture selection include information interdependence, urgency of need, resource constraints, strategic view, and compatibility.
• Data integration involves data access, federation, and change capture processes to move data into a data warehouse.
• The ETL process includes extraction, transformation, and loading of data into the data warehouse, contributing to data quality.
• Companies can purchase ETL tools like IBM InfoSphere DataStage, Microsoft SSIS, or Oracle Data Integrator for the ETL process.
• Data warehousing projects are complex and can influence multiple departments and business strategy.
• Two main data warehouse development approaches are the Inmon Model (EDW) and the Kimball Model (Data mart).
• Inmon's approach focuses on enterprise-wide view and data reusability, while Kimball's approach emphasizes quick end-user capability.
• Both methods can produce enterprise data warehouses and data marts, with differences in scope, development time, cost, difficulty, sources, size, hardware, and users.
• Real-time data warehousing has emerged to meet the need for operational decision support, allowing data to be updated as it becomes available.
• Real-time data collection can eliminate the need for nightly batch processes in operational analysis.
• Data warehouse administration requires a data warehouse administrator with familiarity in high-performance hardware, software, and networking technologies.

Data Warehousing and OLTP vs OLAP

• Data warehousing involves integrating data from various sources into a consistent format, dealing with naming conflicts and discrepancies, and applying data cleaning and techniques for consistency.
• The 4 main characteristics of data warehousing include being subject-oriented, integrated, time-variant (maintaining historical data), and non-volatile (data cannot be changed).
• Data warehouses run on DBMS such as Oracle, SQL, and DB2, and they store large amounts of data for long periods, with data that cannot be overwritten by users.
• OLTP (on-line transaction processing) focuses on day-to-day operations and is designed for repetitive, structured access, while OLAP (Online Analytical Processing) supports decision support and ad-hoc, unstructured access.
• The operational database (OLTP) is relational and designed for short, simple transactions, while the data warehouse (OLAP) uses star schema for complex queries and access.
• Data marts are smaller subsets of data warehouses, either dependent (created directly from the data warehouse) or independent (designed for a strategic business unit or department).
• An enterprise data warehouse (EDW) is a large-scale data warehouse used for decision support across the enterprise, providing data for various types of decision support systems.
• Metadata in a data warehouse describe the contents and use of the data, providing information about the data's content and characteristics.
• The data warehousing process involves importing data from internal and external sources, cleansing and organizing the data, loading it into the enterprise data warehouse, creating data marts if desired, and performing analyses as needed.
• The major components of a data warehousing process include data sources, data extraction, data loading, the data warehouse/comprehensive database, and middleware tools for access to the data warehouse from various front-end applications.
• The goal of OLAP is multidimensional data analysis, providing fast and flexible data summarization, analysis, and reporting capabilities, and the ability to view trends over time.
• The operational database (OLTP) is designed for day-to-day operations, while the data warehouse (OLAP) is designed for decision support and reporting on subjects.