Fundamentals of Data Warehousing: Ms. Liza Mae P. Nismal

Fundamentals of Data
Warehousing
Ms. Liza Mae P. Nismal

Business Intelligence
• BI is the act of transforming raw/ operational data into useful information for business analysis
• Both are fundamental and foundation of the company’s success
• BI - activity which contributes to the growth of the company.
• BI based on DWH technology extracts information from a company’s operational

systems.
• The data is transformed (cleaned and integrated) and loaded into data warehouses.
• Generate business insights
Data Gathering
Business Actions
Planning
Data Analysis
What is data warehouse?
• Data warehouse is considered a fundamental component of business intelligence (BI)
• A central location where consolidated data from one or multiple locations (databases) are
stored.
• A repository of data to later transform them into useful information for the user
• DWH is maintained separately from an organization’s operations databases
• End users access it whenever any information is needed
• Data warehouses often contain large amounts of information that are sometimes subdivided
into smaller logical units depending on the subsystem of the entity they come from or for which
they are needed.

Why data warehouse?

• Data collected from various sources and stored in various databases cannot be directly
visualized
• The data rst needs to be integrated and then processed before visualization
fi

Advantages
• Strategic questions can be answered by studying past data, trends
• Data warehousing is faster and more accurate
• Note: Data warehouse is not a product that a company can go and purchase, it needs
to be designed and depends on the company’s requirement
• DWH makes a more readable information
• Information - is a processed data, easier to understand, easier to relate to and easier

easier to use.
• DW contains structured and related data

Query
Take data from operational Integrate the data from
systems multiple sources
Data Warehouse
Store the data in format

Standardize data to remove
suitable for easy access
inconsistencies
Result
Properties of a Data Warehouse
“ A data warehouse is a subject-oriented, integrated, time-variant and nonvolatile
management’s decision-making process’ - Bill Inmon
• Subject - oriented
• Data is categorized and stored by business subject rather than by application.
• Integrated
• Data on a given subject is collected from disparate sources and stored in a single place.
• Time-variant
• Data is stored as a series of snapshots, each representing a period of time
• Non-volatile
• - Typically data in the data warehouse is not updated or deleted.
Online transaction processing (OLTP)-

supports transaction-oriented applications in a
3-tier architecture. OLTP administers day to
day transaction of an organization.
The primary objective is data processing

and not data analysis
(DB)
Online Analytical Processing (OLAP) - a

category of software tools which provide
analysis of data for business decisions. OLAP
systems allow users to analyze database
(DWH)
information from multiple database systems at
one time
The primary objective is data analysis and

not data processing.
.
ETL Extract, Transform

and Load
• The process of extracting the data CRM

from various sources, transforming
this data to meet your requirement LOB
ERP
and then loading it into a target data
warehouse.
Data Marts
• Data Mart is a smaller version of the
data warehouse which deals with a
single subject satisfying a single user
(marketing, sales, operations)
• It is focused on one area. Hence, they
draw data from a limited number of
sources.
• A “department wide data” compared
to the “enterprise wide data” in data
warehouse
OLTP Data
Dependent Data Mart Data
Warehouse
Source
Mart
OLTP Data
Independent Data Mart
Source Mart
Data
Hybrid Data Mart Warehouse Data
OLTP Mart
Source
Metadata
• De ned as the data about data
• In DWH, metadata de nes the source

data I.e. at le, relational database, and
other objects
• It is used to de ne which table is the
source and target, and which concept is
used to build business logic called
transformation to the actual output.
fi
fl
fi

fi
fi

Data Warehouse Architecture

Fundamentals of Data Warehousing: Ms. Liza Mae P. Nismal

Uploaded by

Copyright:

Available Formats

Fundamentals of Data Warehousing: Ms. Liza Mae P. Nismal

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Fundamentals of Data Warehousing: Ms. Liza Mae P. Nismal

Uploaded by

Copyright:

Available Formats

What is the difference between OLTP and OLAP?

What is the difference between OLTP and OLAP?

What are the different types of data marts?

What are the different types of data marts?

Fundamentals of Data

Ms. Liza Mae P. Nismal

• Both are fundamental and foundation of the company’s success

• BI - activity which contributes to the growth of the company.

• BI based on DWH technology extracts information from a company’s operational

• DWH is maintained separately from an organization’s operations databases

• End users access it whenever any information is needed

Why data warehouse?

• Strategic questions can be answered by studying past data, trends

• Data warehousing is faster and more accurate

• Information - is a processed data, easier to understand, easier to relate to and easier

Store the data in format

• Data is categorized and stored by business subject rather than by application.

• Data is stored as a series of snapshots, each representing a period of time

• - Typically data in the data warehouse is not updated or deleted.

Online transaction processing (OLTP)-

The primary objective is data processing

Online Analytical Processing (OLAP) - a

The primary objective is data analysis and

ETL Extract, Transform

• The process of extracting the data CRM

• De ned as the data about data

• In DWH, metadata de nes the source

Data Warehouse Architecture

You might also like