Chapters 7-9- Data Warehousing Concepts

Analytical Data - technical differences: Small or large amounts used in a process? Low or high frequency of access? Read only or can be updated? Non-redundant or redundancy not an issue?

-Large amounts used in a process -Low/modest frequency of access -Read (and Append) only -Redundancy not an issue

Operational Data - technical differences: Small or large amounts used in a process? Low or high frequency of access? Read only or can be updated? Non-redundant or redundancy not an issue?

-Small amounts used in a process -High frequency of access -Can be updated -Non-redundant

Operational data- data makeup differences: Typical time horizon: Detailed or Summarized? Current or Values over time?

Days/months, detailed, current

What 3 tasks does ETL include?

Extracting, transforming and loading 1) Extracting analytically useful data from the operational data sources 2) 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) 3) Loading the transformed and quality assured data into the target data warehouse

T/F? Data is removed from the operational sources in integration

False. No data is actually removed from the operational sources; instead, it is copied to the data warehouse.

T/F? Source systems can include internal data sources

False. Source system can include EXTERNAL data source

Business Intelligence applications are front end or back end applications?

Front end

Operational Information (transactional information)

The information collected an used in support of day-to-day operational needs in businesses and other organizations -E.g. an ATM withdrawal

Analytical Information

The information collected and used in support of analytical tasks -analytical information is based on operational (transactional) information -E.g. What hours are the busiest for ATM withdrawals

What is ETL infrastructure?

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

Why is the data warehouse sometimes referred to as the target system?

To indicated the fact that it is a destination for the data from the sources systems

T/F? A data warehouse that contains the data at the finest level of data is the most powerful

True

T/F? The only functionality available to the users of the data warehouse is retrieval.

True

T/F? The same fact can have both an operational and an analytical purpose

True

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

data analysis

Dimension tables - columns in dimension tables contain:

descriptive information that is often textual (e.g., product brand, product color, customer gender, customer education level) -can also be numeric (product weight, customer income level)

Dimensional modeling is a data design methodology used for..

designing subject-oriented analytical databases, such as data warehouses or data marts

A data warehouse can store....

detailed and/or summarized data.

A data warehouse, depending on its purpose, may include what two things?

detailed data or summary data or both

The result of dimensional modeling is a dimensional schema containing ____ and ____

facts and dimensions

Subject oriented refers to the

fundamental difference in the purpose of an operational database system and a data warehouse

BI applications: using data for....

improved business decisions

Historical refers to the....

larger time horizon in the data warehouse than in the operational databases ex) many traditional operational databases have a time horizon of 60 to 90 days, where it is quite common fordata warehouses to contain multiple years' worth of data.

Fact tables contain..

measures related to the subject of analysis and the foreign keys (associating fact tables with dimension tabes)

Integration refers to this process of bringing the data from...

multiple data sources into a singular data warehouse

The data in the data warehouse is referred to as... ____-_____, ____, or

non-volatile, static, or read-only

The measures in the fact tables are typically..

numeric and intended for mathematical computation and quantitative analysis -for example) if the subject of the business analysis is sales, one of the measures in the fact table sales could be the sale's dollar amount

A typical data warehouse periodically retreives selected analytically useful data from the ____ ____ _____

operational data sources

Source systems

operational databases and other operational data repositories that provide analytically useful information for the data warehouse's subject of analysis

Enterprise-wide refers to the fact that the data warehouse provides an...

organization-wide view of the analytically useful information it contains ex) if one of the subjects of the data warehouse is cost, then all of the analytically useful data regarding cost within the operational data sources in the entire organization will be brought into the data warehouse.

BI applications perform ___ and ___ business analysis

quantitative and qualitative business analysis

Dimensional modeling is employed as what kind of technique?

relational data modeling technique

A data warehouse is developed for the ._____ ___ _____ _____, and it is not meant for direct data entry by the users

retrieval of analytical information

The purpose of the data ware is the .......

retrieval of analytical information

Data mart

single-subject analytical database with more limited scope than a data warehouse -used in dimensional modeling

The dimensional schema is often referred to as the

star schema

The data warehouse is a _____ ____ of _____, _____ _____, ____ ____, ____, and ____ ___ data.

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

Dimension tables - dimensions contain descriptions of...

the business, organization or enterprise to which the subject of analysis belongs

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

1) The performance of operational day-today 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

Dimensional modeling distinguishes two types of tables:

1) Dimensions 2) Facts

Data warehouse components (4)

1) Source Systems 2) Extraction-transformation-load (ETL) infrastructure 3) Data warehouse 4) Front-end applications

Every operational data store that is used as a source system for the data warehouse has two purposes:

1) The original operational purpose 2) As a source system for the data warehouse

T/F? The data in the data warehouse IS subject to changes.

False. The data in the data warehouse IS NOT subject to changes other than appending the new data

Analytical data - Functional Differences Used by a narrower set of users for decision making OR used by all types of employees for tactical purposes? Application or subject oriented?

-used by a narrower set of users for decision making -Subject oriented

Operational Data - Functional Differences Used by a narrower set of users for decision making OR used by all types of employees for tactical purposes? Application or subject oriented?

-used by all types of employees for tactical purposes -Application oriented

Dimensional modeling is a modeling technique tailored specifically for...

analytical database design purposes

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 -for example, if the subject of analysis is cost and the time horizon is a number of years, we can analyze and compare the cost for the first quarter from a year ago versus the cost for the first quarter from two years ago.

An operational database system is developed in order to...

support a specific business operation

What are BI applications used for?

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

Analytical Data- data makeup differences: Typical time horizon: Detailed or Summarized? Current or Values over time?

Years, summarized (and/or detailed), values over time (snapshots)

A data warehouse is developed to...

analyze specific business subject areas

Extraction-transformation-load (ETL) is the process of...

retrieving the relevant data from the designated sources for insertion into the data warehouse