Data and Info. Management Chapters 4 and 7 (Eitel Lauria Marist College)

Operational Data: Data Makeup: Typical Time-Horizon: Years or Days/Months? Is it detailed? Current or Values over time (Snapshots)?

Days/Months Yes Current

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

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 without a data warehouse (without one such tasks have to compete for computing resources with analytical queries) To have a data store that can be used in an efficient manner for both operational and analytical purposes (It is often impossible to structure a database that can be used in an efficient manner for both operational and analytical purposes)

Full key functional dependency occurs when what?

a primary key functionally determines the column of a relation and no separate component of the primary key partially determines the same column.

deletion anomaly occurs when what?

a user who wants to delete data about a real-world entity is forced to delete data about another real-world entity.

insertion anomaly occurs when what?

a user who wants to insert data about one real-world entity is forced to enter data about another real-world entity.

Operational Data: Functional aspects: Used by who? How is it Oriented?

all types of employees Application

trivial functional dependencies occur when what?

an attribute (or a set of attributes) functionally determines itself (e.g., A → A; or A, B → A, B) or its subset (e.g., A, B → A)

An operational database system is developed in order to support a specific what?

business operation

A data warehouse is developed to analyze specific what?

business subject areas

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

data analysis.

Integration refers to this process of bringing the data from what into a what?

multiple data sources singular data warehouse.

redundant data

multiple instances of the data referring to the same occurrence

Transitive functional dependency occurs when what?

non-key columns functionally determine other non-key columns of a relation.

Analytical information is based on what information?

operational (transactional)

In the context of data warehousing, source systems are what and what that provide what for what

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

Production Data: Data from the various what?

operational systems

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

organization-wide

Nonvolatile Data: Existing data in the warehouse is not what or what? So, this is a read-only repositor or can you write?

overwritten or updated. read-only

functional dependency occurs when what?

the value of one (or more) column(s) in each record of a relation uniquely determines the value of another column in that same record of the relation.

The data warehouse integrates the analytically useful data from where?

the various operational databases (and possibly other sources)

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 what?

time horizon

A table is in 1NF if each row is what and no column in any row contains what?

unique multiple values from the column's domain.

Internal Data

Data from users' "private" spreadsheets, documents, customer profiles, and sometimes even departmental database

External Data

Data from external sources for information that most executives use

Three major functions need to be performed for getting the data ready (ETL)

- Extract the data - Transform the data - and then Load the data into the data warehouse storage

The lowest level of detail → small or large amounts of data in the data warehouse?

A lot of data

Time-variant: Data is stored as a what which what?

A series of snapshots or views which record how it is collected across time.

Analytical Data: Technical aspects: Small or large Amounts used in a Process? High or Low/Modest frequency of Access? Read (and Append) Only or Can be Updated? Redundancy not an Issue or Non-Redundant?

Large Amounts Low/Modest frequency Read (and Append) Only Redundancy not an Issue

Time-variant: Data is available on-line for long or short periods of time for what?

Long trend analysis and forecasting

Is a DW an integrated, volatile, and time variant collection of data?

No, it's an integrated, nonvolatile, and time variant collection of data

Archived Data does what?

Periodically take the old data and store it in archived files in an operational system

Operational Data: Technical aspects: Small or large Amounts used in a Process? High or Low/Modest frequency of Access? Read (and Append) Only or Can be Updated? Redundancy not an Issue or Non-Redundant?

Small High Can be Updated Non-Redundant

4 Data warehouse components:

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

Data Granularity: The analysis begins at a high or low level and moves down or up to lower or higher levels of detail?

The analysis begins at a high level and moves down to lower levels of detail

Analytical Data: Data Makeup: Typical Time-Horizon: Days/Months or Years? Is it detailed? Current or values over time (Snapshots)?

Years Summarized (and/or Detailed) Values over time (Snapshots)

Can source systems include external data sources?

Yes

Is a DW subject oriented?

Yes

Partial functional dependency occurs when what?

a column of a relation is functionally dependent on a component of a composite primary key.

A typical organization maintains and utilizes a number of operational data sources. The operational data sources include the what and what which are used to support the organization's day-to-day operations?

databases and other data repositories

Analytical Data: Functional aspects: Used by a narrower set of users for what? Oriented how?

decision making Subject oriented

Production Data: on what platforms? by what systems? From many vertical or horizontal applications?

different hardware different database systems and operating systems vertical

The lower the level of detail, the what the data granularity?

finer

A data warehouse can store data to different what?

granularity (detailed and/or summarized data).

normalization is a process that is used to what?

improve the design of database tables.

modification anomaly occurs when what?

in order to modify one value, the same modification has to be made multiple times.

three types of update anomalies

insertion anomaly, deletion anomaly, and modification anomaly.

A table is in 2NF if what and what?

it is in 1NF and if it does not contain partial functional dependencies.

A table is in 3NF if what and what?

it is in 2NF and if it does not contain transitive functional dependencies.

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

larger

Data granularity in a data warehouse refers to the what?

level of detail

Structured repository: Any database is a structured repository with its structure represented in its what?

metadata

normalization eliminates what and resolves what?

redundancy update anomalies.

With the data slices, the user can create what for what within the time horizon?

reports for various periods of time

Integrated: Data is stored once in a what?

single integrated location

A DW is a what containing what?

structured, enterprise-wide repository historical data

Data Granularity: Start by looking at what data? Look at the what?

summary breakdown

Analytical information

the information collected and used in support of analytical tasks

Operational information (transactional information)

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

Update operations

the operations for inserting data into the relations, modifying (changing) the existing data in the relations, and deleting data from the relations