datawarehouse by Jorge Neira

Business Intelligence and Data Warehousing

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

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

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

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

Lessons 8. Refreshing Warehouse Data 9. Leaving a Metadata Trail 10. Managing and Maintaining the Data Warehouse

1-6

Let’s Get Started

Lesson 1

1-7

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

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

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

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

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

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

Data Extract Processing • •

End user computing offloaded from the operational environment User’s own data

Operational systems

1-15

Extracts

Decision makers

Management Issues with Data Extract Programs

Operational systems

Extracts

Extract Explosion 1-16

Decision makers

Productivity Issues with Extract Processing • • • •

1-17

Duplicated effort Multiple technologies Obsolete reports No metadata

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

Data Warehousing and Business Intelligence

Legacy Data

Enterprise Data Warehouse

Operations Data

External Data

1-19

Data Marts

Analytical Reporting

Advantages of Warehouse Processing Environments • • • •

Controlled Reliable Quality information Single source of data

Internal and external systems

1-20

Data warehouse

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

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

Business Drivers for Data Warehouses • •

Provide supporting information systems Get quality information: – Reduce costs – Streamline the business – Improve margins

1-23

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

Oracle9i Business Intelligence BI Developer Components • Oracle Warehouse Builder • Discoverer Administrator • JDeveloper • Reports Developer

OLAP Data Mining

ETL

Reporting Web Analytics Ad-hoc Query Personalization Portal

1-26

BI Beans

Oracleâ&#x20AC;&#x2122;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

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

Practice 1-1 Overview This practice covers the following topics: • Answering questions about data warehousing • Discussing how data warehousing meets business needs

1-33