Transcript Business Intelligence and Data Warehousing

Business Intelligence
and Data Warehousing
Copyright © Oracle Corporation, 2002. All rights reserved.
Introductions
Tell us about yourself:
• What is your name and company?
• What is your role in the organization?
• What is your level of Oracle expertise?
• Why are you considering building a data
warehouse?
• What is your data warehouse experience?
• What are your expectations for this class?
1-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Course Objectives
After completing this course, you should be able to do
the following:
• Describe the role of business intelligence (BI) and
data warehousing in today’s marketplace
• Describe data warehousing terminology and the
various technologies that are required to
implement a data warehouse
• Explain the implementation and organizational
issues surrounding a data warehouse project
• Identify data warehouse modeling concepts
• Explain the extraction, transformation, and loading
processes for building a data warehouse
1-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Course Objectives
•
•
•
•
•
•
1-4
Identify management and maintenance processes
that are associated with a data warehouse project
Describe methods for refreshing warehouse data
Explain warehouse metadata concepts
Identify tools that can be employed at each stage
of the data warehouse project
Describe user profiles and techniques for
querying the warehouse
Identify methods and tools for accessing and
analyzing warehouse data
Copyright © Oracle Corporation, 2002. All rights reserved.
Lessons
1. Business Intelligence and Data Warehousing
2. Defining Data Warehouse Concepts and
Terminology
3. Planning and Managing the Data Warehouse
Project
4. Modeling the Data Warehouse
5. Building the Data Warehouse: Extracting Data
6. Building the Data Warehouse: Transforming Data
7. Building the Data Warehouse: Loading Warehouse
Data
1-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Lessons
8. Refreshing Warehouse Data
9. Leaving a Metadata Trail
10. Managing and Maintaining the Data Warehouse
1-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Let’s Get Started
Lesson 1
1-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Lesson 1 Objectives
After completing this lesson, you should be able to do
the following:
• Describe the role of business intelligence in
today’s marketplace
• Describe why an online transaction processing
system (OLTP) is not suitable for analytical
reporting
• Describe how extract processing for decision
support querying led to data warehouse solutions
that are employed today
• Explain why businesses are driven to employ data
warehouse technology
1-8
Copyright © Oracle Corporation, 2002. All rights reserved.
What Is Business Intelligence?
“Business Intelligence is the process of transforming
data into information and through discovery
transforming that information into knowledge.”
Gartner Group
1-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Purpose of Business Intelligence
The purpose of business intelligence is to convert the
volume of data into business value through analytical
reporting.
Decision
Knowledge
Information
Data
Value
Volume
1-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Early Management
Information Systems
•
•
•
•
MIS systems provided business data.
Reports were developed on request.
Reports provided little analysis capability.
Decision support tools gave personal ad hoc
access to data.
Ad hoc access
Production
platforms
Operational reports
1-12
Decision makers
Copyright © Oracle Corporation, 2002. All rights reserved.
Analyzing Data from
Operational Systems
•
•
•
•
•
Data structures are complex.
Systems are designed for high performance and
throughput.
Data is not meaningfully represented.
Data is dispersed.
OLTP systems may be unsuitable for intensive
queries.
Production
platforms
Operational reports
1-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Why OLTP Is Not Suitable
for Analytical Reporting
OLTP
1-14
Analytical Reporting
Information to support
day-to-day service
Historical information
to analyze
Data stored at transaction
level
Data needs to be integrated
Database design:
Normalized
Database design:
Denormalized, star schema
Copyright © Oracle Corporation, 2002. All rights reserved.
Data Extract Processing
•
•
End user computing offloaded from the
operational environment
User’s own data
Operational
systems
1-15
Extracts
Decision
makers
Copyright © Oracle Corporation, 2002. All rights reserved.
Management Issues with
Data Extract Programs
Operational
systems
Extracts
Extract Explosion
1-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Decision
makers
Productivity Issues with
Extract Processing
•
•
•
•
1-17
Duplicated effort
Multiple technologies
Obsolete reports
No metadata
Copyright © Oracle Corporation, 2002. All rights reserved.
Data Quality Issues with
Extract Processing
•
•
•
•
•
•
•
•
•
1-18
No common time basis
Different calculation algorithms
Different levels of extraction
Different levels of granularity
Different data field names
Different data field meanings
Missing information
No data correction rules
No drill-down capability
Copyright © Oracle Corporation, 2002. All rights reserved.
Data Warehousing and
Business Intelligence
Legacy
Data
Enterprise Data
Warehouse
Operations
Data
External
Data
1-19
Data Marts
Copyright © Oracle Corporation, 2002. All rights reserved.
Analytical
Reporting
Advantages of Warehouse
Processing Environments
•
•
•
•
Controlled
Reliable
Quality information
Single source of data
Internal and
external systems
1-20
Data
warehouse
Copyright © Oracle Corporation, 2002. All rights reserved.
Decision
makers
Advantages of Warehouse
Processing Environments
•
•
•
•
•
•
1-21
No duplication of effort
No need for tools to support many technologies
No disparity in data, meaning, or representation
No time period conflict
No algorithm confusion
No drill-down restrictions
Copyright © Oracle Corporation, 2002. All rights reserved.
Success Factors for a Dynamic
Business Environment
•
•
•
•
•
•
•
•
•
1-22
Know the business
Reinvent to face new challenges
Invest in products
Invest in customers
Retain customers
Invest in technology
Improve access to business information
Provide superior services and products
Be profitable
Copyright © Oracle Corporation, 2002. All rights reserved.
Business Drivers for
Data Warehouses
•
•
Provide supporting information systems
Get quality information:
– Reduce costs
– Streamline the business
– Improve margins
1-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Technological Advances
Enabling Data Warehousing
•
•
•
•
•
Hardware
Operating system
Database
Query tools
Applications
•
•
•
•
•
•
1-24
Large databases
64-bit architectures
Indexing techniques
Affordable, cost-effective
open systems
Robust warehouse tools
Sophisticated end user tools
Copyright © Oracle Corporation, 2002. All rights reserved.
Oracle9i Business Intelligence
BI Developer Components
• Oracle Warehouse Builder
• Discoverer Administrator
• JDeveloper
• Reports Developer
Reporting
OLAP
Data
Mining
ETL
Web
Analytics
Ad-hoc
Query
Personalization
Portal
1-26
Copyright © Oracle Corporation, 2002. All rights reserved.
BI Beans
Oracle’s Business Intelligence and
Data Warehousing Products
Analysis Tools & Applications
Discoverer
Reports
Clickstream
Personalization
Design & Development Tools
OWB
Designer
JDeveloper
Portal
Database & Server Technology
ETL & Data mining
1-27
Oracle9i with OLAP Services
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe the role of business intelligence in
today’s marketplace
• Describe why an online transaction
processing system (OLTP) is not suitable for
analytical reporting
• Describe how extract processing for decision
support querying led to data warehouse solutions
employed today
• Explain why businesses are driven to employ data
warehouse technology
1-32
Copyright © Oracle Corporation, 2002. All rights reserved.
Practice 1-1 Overview
This practice covers the following topics:
• Answering questions about data warehousing
• Discussing how data warehousing meets business
needs
1-33
Copyright © Oracle Corporation, 2002. All rights reserved.