Chapter 12
Data Warehouse
Chapter Objectives
1. To understand the need of an operational data store in OLTP and OLAP
systems
2. To understand data warehousing, its benefits and architecture
3. To do a comparative study on OLAP, OLTP and ODS.
4. To comprehend the concept of data mart.
Data Warehousing Data Mining: Principles and Practical Techniques By Parteek Bhatia
The Need for an Operational Data Store (ODS)
Companies rely on OLTP systems to get data from multiple stores
spanning the world. On these OLTP systems, queries usually run on an
indexed database, as this makes the searching fast and efficient. But, data
spread over multiple systems leads to a plethora of technical errors
when carrying out the simple task of running queries on data stored on
OLTP systems. To overcome this limitation of OLTP systems some
solutions were proposed.
• On demand query
• Eager approach
• Operational Data Store
Data Warehousing Data Mining: Principles and
Operational Data Store
Here are the some features of ODS:
• Subject-Oriented
• Integrated
• Current valued
• Volatile
• Detailed
Data Warehousing Data Mining: Principles and
Types of ODS
An ODS could be classified into different types depending
upon the needs:
Type I: ODS as a reporting tool.
Type II: ODS as a complex information tracker.
Type III: ODS as customer relation management supporter.
Data Warehousing Data Mining: Principles and
Architecture of ODS
Data Warehousing Data Mining: Principles and
How to build a ODS
Step 1 - This step involves acquisition; the processes of extracting,
transforming and loading (ETL) data from OLTP source systems.
Extracting:- The very first step is extraction of data, in which the
source system’s data is accessed first and is prepared further for
processing and extracting required values.
Transforming:- In the transform step, a set of rules are applied over
the data, in order to transform it from source format to target format.
Loading:- The final step is loading in which it is ensured that
processed data is loaded correctly into another database.
Data Warehousing Data Mining: Principles and
How to build a ODS
Step 2 - After the database gets populated, next step is
to build the ODS and check for anomalies. This also
involves testing the ODS for performance before it can
go online.
Step 3 - To support end users requirements different
web applications are developed on ODS. Sometimes
reporting tools are also used to support management
queries.
Data Warehousing Data Mining: Principles and
Advantages of the ODS
The advantages of building the ODS
are:
• It can store historical information as
well.
• Implementation of data warehouses
by extending ODS consumes less
time.
• ODS systems are more effective
because they provide a holistic view
of the enterprise's data.
Data Warehousing Data Mining: Principles and
Data Warehouse
A data warehouse is a historical
database and should be
considered as the long term
memory of an organization.
Historical data is not to be
tampered with; no insertion, up-
dation and deletion are to be
made. Usually, it is used only for
retrieval such as verification and
data analysis. Data warehouses
are typically used for Online
Analytical Processing (OLAP) to Answering management queries
support management queries.
Data Warehousing Data Mining: Principles and
Defining Data Warehousing
Data warehouse could be defined as, ‘A subject-oriented, integrated,
time-variant, and non-volatile collection of data in support of
management’s decision-making process.’
• Subject-Oriented: Data warehouses are subject oriented - they are
built around the major data entity or subjects of an organization.
• Integrated: A data warehouse integrates (combines) data from multiple
systems to provide a wide view of any enterprise’s data.
• Time variant: Data warehouses data is not always up to date as it
contains historical data which is valid or accurate till some point of time
(or time interval).
• Non-volatile: Data warehouse is described as a long term enterprise
memory due to its non-volatile nature because the data is not updated
in real time but is rather refreshed on a regular basis.
Data Warehousing Data Mining: Principles and
Data warehouse architecture
Data Warehousing Data Mining: Principles and
Data warehouse architecture
Load manager
The Load manager is responsible for Data collection from operational systems.
Warehouse Manager
The Warehouse manager is the main part of Data Warehousing system as it holds the
massive amount of information from myriad sources. It organizes data in a way so it
becomes easy for anyone to analyze or find the required information.
Query manager
Finally the Query manager is that interface which connects the end users with the
information stored in data warehouse through the usage of specialized end-user tools.
These tools are known as Data mining access tools.
Data Warehousing Data Mining: Principles and
Benefits of data warehousing
Some of its advantages and benefits are given below:
• Potential high ROI (Return on Investment)
• Unbeatable competitive advantage
• High Productivity in corporate decision making and business
intelligence
• Cost effective
• Enhanced customer service
Data Warehousing Data Mining: Principles and
Limitations of data warehousing
• Underestimation of resources for data ETL
• Erroneous source systems
• Required data not captured
• Increased end user queries or demands
• Loss of information during data homogenization
• High demand of resources
• Data ownership
• Necessary maintenance
• Long-duration projects
• Complexity of integration
Data Warehousing Data Mining: Principles and
Data Marts
Data mart can be defined as ‘a specialized, subject-oriented, integrated, time-
variant,volatile data store in support of specific subset of management’s decisions’.
Data mart and data warehouse
Limitations of data warehousing
Data Warehousing Data Mining: Principles and
Data Marts
Relationship between data mart and data warehouse
Data Warehousing Data Mining: Principles and
Comparative Study of Data Warehouse with OLTP
and ODS
Data Warehousing Data Mining: Principles and
Data warehouses versus OLTP: similarities and
distinction
Data Warehousing Data Mining: Principles and
Data warehouses versus OLTP: similarities and
distinction
Data Warehousing Data Mining: Principles and
Book Details
Table of Contents Published by
1. Beginning with machine learning Cambridge University, Press
2. Introduction to data mining (UK).
3. Beginning with Weka and R language Recommended as Six Best
New Data Warehousing
4. Data pre-processing
Books to read in 2020 and
5. Classification 43 Best Data Mining Books
6. Implementing classification in Weka and R of all time by
7. Cluster analysis [Link]
8. Implementing clustering with Weka and R
9. Association mining
10. Implementing association mining with Weka and R
11. Web mining and search engine
12. Operational data store and data warehouse
13. Data warehouse schema
14. Online analytical processing
15. Big data and NoSQL
Order Your Copy Today
[Link]: Data Mining and Data Warehousing: Principles
and Practical Techniques eBook : Bhatia, Parteek: Kindle Store
[Link]
[Link]
Other Books from Parteek Bhatia:
Machine Learning with Python
Machine learning python
principles and practical
techniques | Pattern
recognition and machine
learning | Cambridge
University Press
Other Books from Parteek Bhatia:
Simplified Approach to DBMS
Table of Contents 19. Enterprise Database Products
1. Fundamentals of Database Management System 20. Beginning with SQL
2. The architecture of Database Management 21. Invoking SQL* Plus
System 22. Performing basic SQL
3. Data Models operations
4. Relational Database Management System 23. Basic SELECT statement
5. Relational Algebra and Calculus 24. Inbuilt functions
6. Entity-Relationship Model 25. Grouping of data
7. Conversion of ER Diagrams to Tables 26. Joining of tables
8. Normalization for refinement of data 27. Sub Queries
9. Physical Database design 28. Managing Tables
10. Transaction Management 29. Database objects, DCL and
11. Concurrency Control TCL statements
12. Security and Integrity of data 30. Pl/SQL Fundamentals and its
13. Recovery of data statements
14. Distributed Database 31. Error Handling
15. Object-Oriented Databases and expert system 32. Cursor Management
16. DBTG Model 33. Subprograms and packages
17. Data Warehouse and Data mining 34. Database triggers
18. No-SQL Database 35. Advanced Topics
Other Books from Parteek Bhatia
For more information visit: [Link]
Online Courses on Udemy by Parteek Bhatia
Parteek Bhatia’s YouTube Channel
Premier resource for over 450 in-depth videos covering Machine
Learning, Data Mining, DBMS, SQL, PL/SQL, Python, and Big Data. Our
channel simplifies complex topics without compromising on depth,
catering to both beginners and seasoned professionals. Explore tutorials,
step-by-step guides, and practical tips to enhance your tech skills.
Whether you're preparing for exams, advancing your career, or exploring
new technologies, our content is designed to empower your learning
journey. Subscribe and join our community of tech enthusiasts to stay
updated with educational and engaging content!
Thanks
Happy Learning.