Chapter 9, Problem 9.1RQ

Program Plan Intro

Define each of the following terms:

a. data warehouse

b. data mart

c. reconciled data

d. derived data

e. enterprise data warehouse

f. real-time data warehouse

g. star schema

h. snowflake schema

i. grain

j. conformed dimension

Explanation of Solution

Data warehouse: It is a large collection of data which is further used by companies for decision making process of management. The data collection is subject-oriented, integrated, time-variant and non-updateable.

Data mart: A data warehouse which is limited in scope is called data mart. The data stored in data warehouse is selected and summarized using extract, transform and load process.

Reconciled data: It is detailed and current data which act as single source for all decision support system. This type of data is stored for enterprise data warehouse and operational data store.

Derived data: Data which is being extracted, formatted and segregated for end-user decision support application is known as derived data.

Enterprise data warehouse: It is a centralized, integrated data warehouse which act as the control point and only source for all data which is being made available to end user for decision making.

Real-time data warehouse: It is a type of data warehouse which stores data related to transaction in near real time feeds from system of records. It can analyze data and control business rules in near real time so that appropriate actions can be taken as per current business events. It typically stores moderate size database.

Star schema: It is a database design where in dimensional data is separated from fact or event data. It is simplest type of data warehouse schema. It is called star schema as it is in the shape of start and is similar to relational model where fact table is represented at the center and multiple dimensional table are connected to the center fact table.

Snowflake schema: It is an extension to star schema where dimensional tables are normalized into several related tables. Normalization of dimensional tables splits these table into multiple tables and the schema diagram resemble that of snowflake.

Grain: Grain is the description of level of details for a fact table. For example, suppose a fact table stores information related to sales of specific product in various locations on a daily basis. Then grain for this fact table will be “information of product sale by location and by day”

Conformed dimension: It is a dimension in which multiple dimension tables which are associated with multiple fact tables has same business meaning and primary key with each fact table.