Lecture 07 - Data Warehousing Concepts

Data Warehouse

created within an organization as a separate data store whose primary purpose is data analysis.

THE DATA WAREHOUSE DEFINITION

A structured repository of integrated, subject-oriented, enterprise-wide, historical, and time-variant data. The purpose of the data warehouse is the retrieval of analytical information. A data warehouse can store detailed and/or summarized data.

Two main reasons for the creation of a data warehouse

1 The performance of operational day-to-day tasks involving data use can be severely diminished if such tasks have to compete for computing resources with analytical queries 2 It is often impossible to structure a database which can be used in an efficient manner for both operational and analytical purposes

Application Oriented vs. Subject Oriented- Example

A subject-oriented database for the analysis of the subject revenue in the Vitality Health Club

Application Oriented vs. Subject Oriented-Example

An application-oriented database serving the Vitality Health Club Visits and Payments Application

ETL includes the following tasks

Extracting analytically useful data from the operational data sources Transforming such data so that it conforms to the structure of the subject-oriented target data warehouse model (while ensuring the quality of the transformed data) Loading the transformed and quality assured data into the target data warehouse

Operational Data Sources

Include the databases and other data repositories which are used to support the organization's day-to-day operations

Enterprise-wide (THE DATA WAREHOUSE DEFINITION)

The data warehouse provides an organization-wide view of the analytically useful information it contains

Subject-oriented (THE DATA WAREHOUSE DEFINITION)

The fundamental difference in the purpose of an operational database system and a data warehouse. An operational database system is developed in order to support a specific business operation A data warehouse is developed to analyze specific business subject areas

Analytical information

The information collected and used in support of analytical tasks - •Analytical information is based on operational (transactional) information

Operational information (transactional information)

The information collected and used in support of day to day operational needs in businesses and other organizations

ETL infrastructure

The infrastructure that facilitates the retrieval of data from operational databases into the data warehouses

The data warehouse is sometimes referred to as the target system

This indicates the fact that it is a destination for the data from the source systems - •A typical data warehouse periodically retrieves selected analytically useful data from the operational data sources

Data warehouse front-end (BI) applications

Used to provide access to the data warehouse for users who are engaging in indirect use

source systems

operational databases and other operational data repositories (in other words, any sets of data used for operational purposes) that provide analytically useful information for the data warehouse's subjects of analysis

Retrieval of analytical information

•A data warehouse is developed for the retrieval of analytical information, and it is not meant for direct data entry by the users.

Detailed and/or summarized data

•A data warehouse, depending on its purpose, may include the detailed data or summary data or both •A data warehouse that contains the data at the finest level of detail is the most powerful

Integrated (THE DATA WAREHOUSE DEFINITION)

•The data warehouse integrates the analytically useful data from the various operational databases (and possibly other sources) •Integration refers to this process of bringing the data from multiple data sources into a singular data warehouse.

Structured repository (THE DATA WAREHOUSE DEFINITION)

•The data warehouse is a database containing analytically useful information •Any database is a structured repository with its structure represented in its metadata

Historical (THE DATA WAREHOUSE DEFINITION)

•The term historical refers to the larger time horizon in the data warehouse than in the operational databases

Time variant (THE DATA WAREHOUSE DEFINITION)

•The term historical refers to the larger time horizon in the data warehouse than in the operational databases•The term time variant refers to the fact that a data warehouse contains slices or snapshots of data from different periods of time across its time horizon With the data slices, the user can create reports for various periods of time within the time horizon