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