Data Warehousing and Business Intelligence

1. Data Warehousing and Business Intelligence

2. 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 are your expectations for this class?

3. Course Objectives • After completing this course, you should be able to do the following: • Describe the role of data warehousing and business intelligence (BI) in today’s marketplace • Define the terminology and explain the basic concepts of data warehousing • Define the decision support purpose and end goal of a data warehouse • Develop familiarity with the various technologies required to implement a data warehouse • Identify the technology and tools from Oracle to implement a successful data warehouse • Identify data warehouse modeling concepts

4. Course Objectives • Describe methods and tools for extracting, transforming, and loading data • Identify the tools for accessing and analyzing warehouse data • Identify the features of Oracle Database 10g that aid in implementing the data warehouse • Describe the OLAP and data mining techniques and tools • Explain the implementation and organizational issues surrounding a data warehouse project

5. Lessons • Data Warehousing and Business Intelligence • Defining Data Warehouse Concepts and Terminology • Business, Logical, and Dimensional Modeling • Physical Modeling: Sizing, Storage, Performance, and Security Considerations • The ETL Process: Extracting Data • The ETL Process: Transforming Data • The ETL Process: Loading Data

6. Lessons • Refreshing Warehouse Data • Summary Management • Leaving a Metadata Trail • OLAP and Data Mining • Data Warehouse Implementation Considerations

7. Let’s Get Started Lesson 1

8. Lesson 1 Objectives • After completing this lesson, you should be able to do the following: • Describe the evolution of data warehouses from management information systems (MIS) • Describe why an online transaction processing (OLTP) system is not suitable for analytical reporting • Describe how extract processing for decision support querying led to data warehouse solutions • Identify the role of business intelligence (BI) in today’s market • Identify the BI tools and technology from Oracle • Identify the business drivers for data warehouses • Explain why businesses are driven to employ data warehouse technology • Identify the components of Oracle E-Business Intelligence

9. Evolution of BI • Executive information systems (EIS) • Decision support systems (DSS) • Data warehousing (DW) and business intelligence (BI) DW&BI DSS EIS

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 Productionplatforms Operational reports Decision makers

11. 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. Productionplatforms Operational reports

12. OLTP Analytical Reporting Information to supportday-to-day service Historical informationto analyze Data stored at transaction level Data needs to be integrated Database design: Normalized Database design: Denormalized, star schema Why OLTP Is Not Suitable for Analytical Reporting

13. Data Extract Processing • End-user computing offloaded from the operational environment • User’s own data Operational systems Extracts Decisionmakers

14. Issues with Data Extract Programs Operationalsystems Extracts Decisionmakers Extract explosion

15. Productivity Issues with Extract Processing • Duplicated effort • Multiple technologies • Obsolete reports • No common metadata

16. Data Quality Issues with Extract Processing • 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

17. Data Warehousing and Business Intelligence Enterprise Data Warehouse Legacy data Operations data Analytical reporting External data Data marts

18. Technological Advances Enabling Data Warehousing • Hardware • Operating system • Database • Query tools • Applications • Large databases • 64-bit architectures • Indexing techniques • Affordable, cost-effective open systems • Robust warehouse tools • Sophisticated end-user tools

19. Advantages of Warehouse Processing Environments • Controlled • Reliable • Quality information • Single source of data Internal and external systems Datawarehouse Decisionmakers

20. Advantages of Warehouse Processing Environments • 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

21. Business Intelligence: Definition and Purpose • “Business intelligence is the process of transforming data into information and through discovery transforming that information into knowledge.” – Gartner Group • The purpose of business intelligence is to convert the volume of data into business value through analytical reporting. Value Decision Knowledge Information Data Volume

22. Success Factors for a Dynamic Business Environment • 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.

23. Business Drivers for Data Warehouses • Provide supporting information systems. • Get quality information: • Reduce costs. • Streamline the business. • Improve margins.

24. Business Intelligence: Requirements • Efficient design of data warehouses • Enterprise reporting • Ad hoc query and analysis (relational and multidimensional) • Advanced analytics • Integration with portals • Easy administration • Integrated environment and/or tools

25. Problem: Multivendor, Unintegrated Environment ETL tool Lineage OLAPengine Analytic apps. Transformationengine Miningengine Portal Query & analysis ETL tool Database Transformationengine Reportingengine Name/address scrubbing Enterprise reporting

26. Oracle Business Intelligence Oracle Business Intelligence tools and applications Build DW Ad hoc query Analytics BI Beans Oracle Application Server Integration Portal HTTP server, J2EE, Web services Oracle Database with OLAP, data mining, and ETL features Wireless Business intelligence

27. Introduction to Oracle Business Intelligence Tools and Applications • Oracle Warehouse Builder 10g (OWB) • OracleBI Discoverer • OracleBI Spreadsheet Add-In • OracleBI Beans • Oracle Reports 10g • Analytical Workspace Manager 10g (AWM) • Oracle Data Miner 10g (ODM)

28. Oracle’s Complete and Integrated Solution Publish BI content on portal OracleAS Portal Ad hoc query/OLAP analysis OracleBI Discoverer Oracle Reports 10g Enterprise reporting OracleBI Beans Develop custom BI applications Oracle Warehouse Builder/ Analytical Workspace Manager Manage metadata Oracle Warehouse BuilderAnalytical Workspace Manager ETL/ Design EUL, AW Oracle Database Database

29. Oracle E-Business Intelligence Develop • Oracle Daily Business Intelligence (DBI) • Oracle Corporate Performance Management (CPM) • Oracle Enterprise Reporting and Delivery • Oracle XML Publisher • PeopleSoft Enterprise Performance Management (EPM) Contracts Market E-Business Suite Projects Sell HR Order Customers, Suppliers, Products, … Finance Plan Maintain Procure Service Make Fulfill Daily Business Intelligence

30. OracleBI Suite Enterprise Edition – Based on Siebel Analytics • Siebel Analytics has the following two sets of products: • Siebel Business Analytics Platform, the application server, administration and query tools (now the components of Oracle BI Suite Enterprise Edition) • Siebel Analytics Applications (a set of pre-packaged, applications build using the Analytics Platform tools)

31. Summary • In this lesson, you should have learned how to: • Describe the evolution of data warehouses from MIS • 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 • Identify the role of business intelligence in today’s market • Identify the BI tools and technology from Oracle • Identify the business drivers for data warehouses • Explain why businesses are driven to employ data warehouse technology • Identify the components of Oracle E-Business Intelligence

32. Practice 1-1 Overview • This practice covers the following topics: • Answering questions about data warehousing • Identifying Oracle’s BI technology and tools • Discussing how data warehousing meets business needs

33. Defining Data Warehouse Concepts and Terminology

34. Objectives • After completing this lesson, you should be able to do the following: • Identify a common, broadly accepted definition of a data warehouse • Describe the differences of dependent and independent data marts • Identify some of the main warehouse development approaches • Define some of the operational properties and common terminology of a data warehouse

35. Data Warehouse: Definition • “A data warehouse is a subject-oriented, integrated, non-volatile, and time-variant collection of data in support of management’s decisions.” • — W.H. Inmon • “An enterprise structured repository of subject-oriented, time-variant, historical data used for information retrieval and decision support. The data warehouse stores atomic and summary data.” • — Oracle’s definition of a data warehouse

36. Integrated Nonvolatile Time variant Data Warehouse Properties Subject oriented Data Warehouse

37. OLTP applications Equity plans Shares Insurance Loans Savings Subject Oriented • Data is categorized and stored by business subject rather than by application. Data warehouse subject Customer financial information

38. Integrated • Data on a given subject is defined and stored once. Savings Current accounts Loans Customer OLTP applications Data Warehouse

39. Time Variant • Data is stored as a series of snapshots, each representing a period of time. Data warehouse

40. Nonvolatile • Typically, data in the data warehouse is not updated or deleted. Operational Warehouse Load Insert, update, delete, or read Read

41. First-time load Refresh Refresh Purge or archive Refresh Changing Warehouse Data Operational databases Warehouse database

42. Property OLTP Data Warehouse Response time Subseconds to seconds Seconds to hours Operations DML Primarily read-only Nature of data 30–60 days Snapshots over time Data organization Application Subject, time Size Small to large Large to very large Data sources Operational, internal Operational, internal, external Activities Processes Analysis Data Warehouse Versus OLTP

43. Enterprisewide Data Warehouse • Large scale implementation • Scopes the entire business • Data from all subject areas • Developed incrementally • Single source of enterprisewide data • Synchronized enterprisewide data • Single distribution point to dependent data marts

44. Property Data Warehouse Data mart Scope Enterprise Department Subjects Multiple Single-subject, LOB Data source Many Few Implementation time Months to years Months Data Warehouses Versus Data Marts

45. Dependent Data Mart Data marts Operationalsystems Flat files Data Warehouse Legacy data Marketing Operations data Sales Marketing Sales Finance HR External data External data Finance

46. Independent Data Mart Operationalsystems Flat files Legacy data Sales ormarketing Operations data External data External data

47. ODS Typical DataWarehouse Components Source systems Staging area Presentation area Access tools Legacy Data Warehouse External Data marts Operational Metadata repository

48. Warehouse Development Approaches • “Big bang” approach • Incremental approach: • Top-down incremental approach • Bottom-up incremental approach

49. Analyze enterprise requirements. Build enterprise data warehouse. Report in subsets or store in data marts. “Big Bang” Approach

50. Top-Down Approach • Analyze requirements at the enterprise level. • Develop conceptual information model. • Identify and prioritize subject areas. • Complete a model of selected subject area. • Map to available data. • Perform a source system analysis. • Implement base technical architecture. • Establish metadata, extraction, and load processes for the initial subject area. • Create and populate the initial subject area data mart within the overall warehouse framework.