Data warehouse

• Data warehouses are a collection of historical data that changes over time.
• They are organized by topic and consolidated into a single database.
• They are managed in a specific storage environment to assist in business decision-making.

Course Objectives

• Introduction and definition of a data warehouse.
• Modeling and architecture of a data warehouse.
• OLAP and implementation of data warehouses.

Data Warehouse Definition by Georges GARDARIN-99

• A set of historical data varying over time.
• The data is organized by subject and consolidated into a single database.
• It is managed in a specific storage environment to aid in business decision-making.
• Data warehouses involve three essential functions: data collection, data management, and data analysis.

Definition by Stanford (1995)

• Data warehouse is a storage device for integrated information from distributed, autonomous, and heterogeneous sources.

Definition by Immon (1992)

• A data warehouse is a collection of data that is subject-oriented, integrated, non-volatile, and historical.

Subject-Oriented Data

• Data warehouses, unlike traditional organizational data, are oriented towards functional processes or applications.
• Data warehouses structure data by theme or subject, such as client or vendor.
• An ED (Entrepôt de Données) gathers and organizes data associated with different functional structures of the company.
• The ED is pertinent to a subject or theme, and responds to analysis needs.
• Data warehouses offer easy access to data that has been categorized by functional subject.
• Relational modeling involves each subject corresponding to a table managed by the data warehouse.
• Defining tables requires isolating strategic data, determining necessary details, and using summaries to preserve data.
• Data summaries are generally calculated using queries with aggregates.
• This data arrangement allows for analysis of subjects across functional and organizational structures and iterative subject analysis.

Integrated Data

• Data in a warehouse results from integrating data from different sources, which may be heterogeneous.
• Integration aims to avoid data duplication across several subjects and requires normalization, management of reference data, data coherence, and semantic rule application.

Non-Volatile Data

• Data in the data warehouse is used for querying and cannot be modified, except for updates.
• There is no data deletion.
• Repeat queries yield the same results and maintaining traceability of information ensures consistent decision-making.

Historical Data

• Data represents a company's activity over a certain period, enabling analysis of data variations over time.
• It tracks the evolution of different values of the indicators to be analyzed and uses a defined temporal reference.

Data Warehouse Construction in 3 Phases

• Construction of the decision-making database, involving conceptual modeling of multi-form and multi-source data, designing the data warehouse, feeding the warehouse (extracting, cleaning, transforming, loading), and physical storage of the data.
• Selection of data which includes, data marts, cubes, tables etc to be analyzed based on user needs.
• Data analysis, which includes, statistics, reporting, and data mining.

Functional architecture of Data Warehouse

• Data warehouses use metadata dictionaries, OLAP servers, ETL processes, data marts, and front-end tools.

Heterogeneous Information Sources

• They include internal and external data that are highly heterogeneous with issues of different sources
• Relational databases, object databases, network databases, flat files, HTML documents, etc. are used.
• The data is generally modified on a daily basis.
• The components are used for feeding, homogenization, and data integration.

DWH (Data Warehouse)

• Data warehouses store all information extracted from sources.
• Data warehouses can be relational, multidimensional, or object-oriented.
• Multidimensional databases are optimized for data access based on unforeseen queries at the time of database creation.

Data Structure

• Data have a 2 axes: synthetic and historical
• Data have 2 types: detailed and aggregated

Synthetic Axis

• A synthetic axis establishes a hierarchy of aggregation and includes detailed data representing recent events, aggregated data synthesizing detailed data, and strongly aggregated data summarizing at a higher level.

Historical Axis

• The historical axis includes detailed historical data representing past events.
• Storing metadata is necessary that concerns origin, structure, used method for aggregation.

Data Marts

• Data marts have targeted data for analysis and provide a small data warehouse focused on a specific subject, sourced from a data warehouse or directly from different sources.
• Data marts require less infrastructure than warehouses and can be implemented more quickly.

Data Warehouse vs Data Marts

• Data warehouses collect information useful for decision-makers from various sources.
• Data warehouses centralize decision-making information and integrates extracted data, guaranteeing its longevity.
• Data Marts are subject-oriented, aid in OLAP analysis processes, and extract a portion of useful data for a specific user group or analysis need.
• Data warehouses are for managing large historical detail databases, are centralized storage, and are organized to facilitate data management and history.
• Data marts are small warehouses with a lighter infrastructure for rapid implementation
• Data marts extract data from a data warehouse or existing databases to aid decision-making with a specific model facilitating decision-making processes.

Data Warehouse vs. Data Marts - Size Comparison

• Data warehouses use large machines for historical detail databases and centralized storage, whereas data marts have lighter infrastructure and quicker setup.
• Data warehouses extract data for overall decision support, while data marts extract data with models that support specific decision-making processes.
• Data warehouses support the entire enterprise with SQL-type servers and have multi-subject enterprise scope, while data marts support departments with MD or OLAP and have a department-focused scope.
• Data warehouses range in size from >100 GB with implementation times between 9-18 months, data marts range from 10-20 GB with 6-12 month implementation times.

Logical View of the Data Warehouse Structure

• Customization is performed on Data Marts, forming Customization.
• CDW (Corporate Data Warehouse) allows Aggregation.
• ODS (Operational Data Store) historical data allows Integration.
• Data cleaning and archiving are involved in the Preparation.
• The data extracted from multiple data sources are considered data sources.

Functional Architecture of an ED: 3 Levels:

• Data at the source gets extracted, filtered to a BD source, for operational data and is a legatory form.
• There is transformation and fusion when extracting data.
• At the presentation, there is the client.

Extraction Level

• The two strategies for extraction is, "push" for immediate incorporation to ED and "pull," a scheduled BD update to ED.

Level Transformations/Fusions

• Data extracted is added, stored, and loaded to an ED BD.
• Refresh the data when mise-à-jour gets updated.

Exploitation

• Use analyses, dashboards, tables, and other tools to explore data in the ED.

Data Source Monitor

• The monitor component detects and finds the updated data to send to the ED.
• Data uses the source wrapper (extraction) to translate local files and requests for a vice versa method.

Mediator

• Mediators are used in the ED, they remove redundancies in various multiple data.

OLAP Engines

• This engine looks for an OLAP in the data.

Metadata Dictionaries

• Contains info or metadata for data source in an ED, is the data dictionary for each component.