CHAPTER 7 - Data Warehousing Concepts

Enterprise-wide

The term enterprise-wide refers to the fact that the data warehouse provides an organization-wide view of the analytically useful information it contains

Historical

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

Subject-oriented

The term subject-oriented refers to the fundamental difference in the purpose of an operational database system and a data warehouse. oAn operational database system is developed in order to support a specific business operation oA data warehouse is developed to analyze specific business subject areas

ETL includes the following tasks:

o Extracting analytically useful data from the operational data sources o 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) o Loading the transformed and quality assured data into the target data warehouse

Data warehouse use

the retrieval of the data in the data warehouse •Indirect use Via the front-end (BI) applications •Direct use Via the DBMS Via the OLAP (BI) tools

Requirements collection, definition, and visualization

- results in the requirements specifying the desired capabilities and functionalitiesof the future data warehouse •The requirements are based on the analytical needs that can be met by the data in the internal data source systems and available external data sources •The requirements are collected through interviewing various stakeholders of the data warehouse •In addition to interviews, additional methods for eliciting requirements from the stakeholders can be used

Two main reasons for the creation of a data warehouse as a separate analytical database

-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 -It is often impossible to structure a database which can be used in an efficient manner for both operational and analytical purposes

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

data analysis.

Dependent data mart

•Does not have its own source systems •The data comes from the data warehouse

Data warehouse modeling (logical data warehouse modeling )

- creation of the data warehouse data model that is implementable by the DBMS software

Developing front-end (BI) applications

- designing and creating applications for indirect useby the end-users •Front-end applications are included in most data warehousing systems and are often referred to as business intelligence (BI) applications •Front-end applications contain interfaces (such as forms and reports) accessible via a navigation mechanism (such as a menu)

Data warehouse deployment

- releasing the data warehouse and its front-end (BI) applications for useby the end users

Creating the data warehouse

- using a DBMS to implement the data warehouse data model as an actual data warehouse •Typically, data warehouses are implemented using a relational DBMS (RDBMS) software

Data mart

A data store based on the same principles as a data warehouse, but with a more limited scope

Detailed and/or summarized data

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

ETL infrastructure

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

Independent data mart

Stand-alone data mart, created in the same fashion as the data warehouse •Independent data mart has its own source systems and ETL infrastructure

THE DATA WAREHOUSE DEFINITION

The data warehouse is 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.

Time variant

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 oWith the data slices, the user can create reports for various periods of time within the time horizon

Data warehouse front-end (BI) applications

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

Data warehouse administration and maintenance

performing activities that support the data warehouse end user, including dealing with technical issues, such as: •Providing security for the information contained in the data warehouse •Ensuring sufficient hard-drive space for the data warehouse content •Implementing the backup and recovery procedures

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.oThe only functionality available to the users of the data warehouse is retrieval oThe data in the data warehouse is not subject to changes. oThe data in the data warehouse is referred to as non-volatile, static, or read-only

THE NEXT VERSION OF THE DATA WAREHOUSE

The new version of the data warehouse follows the same development steps as the initial version

A typical organization maintains and utilizes a number of operational data sources.

The operational data sources include the databases and other data repositories which are used to support the organization's day-to-day operations

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

Creating ETL infrastructure

•Creating necessary procedures and code for: oAutomatic extraction of relevant data from the operational data sources oTransformation of the extracted data, so that its quality is assured and its structure conforms to the structure of the modeled and implemented data warehouse oThe seamless load of the transformed data into the data warehouse •Due to the amount of details that have to be considered, creating ETL infrastructure is often the most time- and resource-consuming part of the data warehouse development process

Source systems

•In the context of data warehousing, source systems are 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 •Every operational data store that is used as a source system for the data warehouse has two purposes: o The original operational purpose o As a source system for the data warehouse --Source systems can include external data sources

Data warehouse components

•Source systems •Extraction-transformation-load (ETL) infrastructure •Data warehouse •Front-end applications

Requirements collection, definition, and visualization

•The collected requirements should be clearly defined and stated in a written document, and then visualized as a conceptual data model

Integrated

•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 is a database containing analytically useful information •Any database is a structured repository with its structure represented in its metadata

Data warehouse

•The data warehouse is sometimes referred to as the target system, to indicate 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