MIS 430 Quiz 2

Due to the amount of details that have to be considered, ___________ is often the most time- and resource-consuming part of the data warehouse development process

creating ETL infrastructure

A data warehouse is created

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

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

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

Data warehouse components

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

Historical

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

Operational Data

Data Makeup Differences --Typical Time-Horizon: Days/Months --Detailed --Current Technical Differences --Small Amounts used in a Process --High frequency of Access --Can be Updated --Non-Redundant Functional Differences --Used by all types of employees for tactical purposes --Application Oriented

Analytical Data

Data Makeup Differences --Typical Time-Horizon: Years --Summarized (and/or Detailed) --Values over time (Snapshots) Technical Differences --Large Amounts used in a Process --Low/Modest frequency of Access --Read (and Append) Only --Redundancy not an Issue Functional Differences --Used by a narrower set of users for decision making --Subject Oriented

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

Dimensional modeling

A data design methodology used for designing subject-oriented analytical databases, such as data warehouses or data marts Commonly, dimensional modeling is employed as a relational data modeling technique

Data mart

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

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

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

Dimension tables (dimensions)

Contain descriptions of the business, organization, or enterprise to which the subject of analysis belongs Columns in dimension tables contain descriptive information that is often textual (e.g., product brand, product color, customer gender, customer education level), but can also be numeric (e.g., product weight, customer income level) This information provides a basis for analysis of the subject

Fact tables

Contain measures related to the subject of analysis and the foreign keys (associating fact tables with dimension tables) The measures in the fact tables are typically numeric and are intended for mathematical computation and quantitative analysis

Creating ETL infrastructure

Creating necessary procedures and code for: --Automatic extraction of relevant data from the operational data sources --Transformation of the extracted data, so that its quality is assured and its structure conforms to the structure of the modeled and implemented data warehouse --The seamless load of the transformed data into the data warehouse

In addition to using the regular relational concepts (primary keys, foreign keys, integrity constraints, etc.) dimensional modeling distinguishes two types of tables:

Dimensions Facts

Dependent data mart

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

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

The data warehouse is

a structured repository of integrated, subject-oriented, enterprise-wide, historical, and time-variant data.

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: --The original operational purpose --As a source system for the data warehouse Source systems can include external data sources

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

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.

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

ETL infrastructure

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

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

Subject-oriented

The term subject-oriented refers to 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

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

The purpose of the data warehouse is

the retrieval of analytical information. A data warehouse can store detailed and/or summarized data.