Principal Sales Consultant
e-business Intelligence
Topics Business Intelligence e-business Intelligence BI System Oracle9i BI Applications BI architectures BI architecture Conclusions 4
5
Typical Data Warehousing Topology, 1984-94 Data Marts Business Analyst Clients On-line Transaction Processing Systems Speed of implementation Department ROI 6
Typical Data Warehousing Topology, 1990 Enterprise Data Warehouse Business Analyst Clients On-line Transaction Processing Systems Data gathered to single point Consistency of data model 7
Typical Data Warehousing Topology, 1995 Data Marts Business Analyst Clients Enterprise Data Warehouse On-line Transaction Processing Systems Central IT management Consistency 8
Typical Data Warehousing Topology, 1998 External Data Source Data Marts Business Analyst Clients Operational Data Store Enterprise Data Warehouse On-line Transaction Processing Systems Consolidated reporting External data Mixture of NT & UNIX Web enabled tools 9
Typical E-Business Intelligence Topology, 2000 External Data Source Data Marts Business Analyst Clients Operational Data Store Enterprise Data Warehouse On-line Transaction Processing Systems Portals to DW, OLTP EAI More data Business Analyst Portal 10
Oracle Warehouse Any Source Any Information Any Access Operational Data Query & Reporting Tools Warehouse OLAP Tools External Data Applications 11
BI 12
Business Intelligence WEB Summary tables / hierarchies SQL OLAP extensions Data mining algorithm APIs 13
Business Intelligence IT (10TB ) (24x7x365) DBMS ETL 14
There are many challenges to successful deployment of BI applications to support corporate performance management. The choice of applications is both bewildering and challenging: no one vendor has a complete solution today, but purchasing BI applications from multiple vendors can challenge the integrity of the overall BI strategy. Purchasing BI applications that can integrate with, or at least co-exist symbiotically with, the corporate data warehouse makes sense Nigel Rayner, Gartner, BI Europe 2001 15
Portal Data Mining/OLAP Design/ETL BI Applications Reporting Viador Hyperion SAS Business Objects Informatica PlumTree E-pithany Micro Strategy Cognos Sagent Hummingbird WebTrends SPSS Brio Acta Database IBM DB2 NCR Teradata SQL-Server Server 16
Business Intelligence What Happened? Query tool Detailed/Historical Data Why did it happen? OLAP Tool Monitor and Change BSC, Strategy Tools Operational Integration Execute Hypothesis Customer Data Warehouse Marts Summaries What Might Happen if? OLAP Tool What will Happen? Data Mining 17
Business Intelligence Near Real-time Data Value / Speed BI Static reports Identify customers individually Proactive information Quarterly Identify and interact differently Weekly Wider access Interaction tailored to end user needs e-business / Business Intelligence Evolution Dynamic Information Integrated enhanced view Customization Daily Via new channels such as web Real Time ebusiness Across value chain 18
E-Business Intelligence challenges e-business: BIGGER : FASTER Business Intelligence Real-time MORE USERS closed-loop BETTER INTEGRATION PACKAGED SOLUTIONS 19
20 Oracle9i BI Applications
E-Business Intelligence Infrastructure Enterprise Intelligence Operations Strategic Enterprise Management The Enterprise Operational & Analytical Corporate Strategist Enterprise Intelligence New Opportunities LOB Decision Maker ERP Business Analyst CRM LOB Decision Maker 21
Strategic Enterprise Management Strategy Formulation Balanced Scorecard Financial Analysis Activity Based Mgmt Value Based Mgmt 22
Enterprise Intelligence - Operational & Analytical Supply Chain Manufacturing Purchasing Finance Travel HR 23
Enterprise Intelligence - Operational & Analytical - CRM Marketing Sales Service Call Center Online Sales Loyalty Call Center Just click Click-thru 24
Oracle Business Intelligence, Value Based Reporting Activity Based Management Operations The Enterprise Oracle Warehouse Balanced Scorecard Sales & Marketing Analysis New Opportunities Financial Planning & Forecasting Customer Analysis 25
Oracle Warehouse E-Business Intelligence Analytic Applications Web Data Reports Operational Data ERP Data External Data 26 Warehouse Builder Pure*Integrate Pure*Extract Darwin Oracle8i Express Server CWM and Repository Internet Application Server Enterprise Manager and Workflow Discoverer Express Tools WebDB
: Best of Breed Brand D ETL Engine OLAP Engine Database Data Warehouse Engine Reports Engine Brand A Brand B Brand C Brand E 27
Oracle e-business Intelligence Operational Data E-Business Intelligence Suite Oracle9i Reports Discoverer Web Data Warehouse Builder ETL Infrastructure Analytic Services 9i Application Server BI Beans Data Mining External Data CWM Metadata Portal 28
Oracle BI Applications Products The Enterprise Balanced Scorecard Analyzers: Financial Analyzer Sales Analyzer Performance Analyzer Demand Planning Activity Based Management 29
Oracle Business Intelligence System Purchasing Financials Process Manufacturing Human Resources Operations Marketing Sales Service Customer Interaction Center Out-of-the-Box Intelligence For Every Management Area 30
Oracle s War on Complexity Build option versus Oracle s Buy or Buy and Extend Suite of BI Applications Hummingbird 31 Hyperion Business Objects Sagent Microsof t Microstrategy Crystal Reports Ardent Informatica IBM Viador SAS NCR Portal Advanced Analysis Query & Reporting ETL Metadata Database 9iAS (iportal ) 9iAS (BI Beans) 9iAS (Discoverer/Reports) ids (Warehouse Builder) CwM Oracle9i (OLAP/Data Mining)
32 ACTIVE Warehouse
Web External Data Off-Line Analytical Warehouse Cust 1 data 1 data 2 data 3 Cust 2......... Cust 3......... Cust 4 On-Line......... Cust 5......... Cust 6 data Data 1 data 2 data 3 Cust 7......... Cust 8......... Cust 1 data Store 1 data 2 data 3 Cust 9......... Cust 2......... Cust 10......... Cust 3......... Cust 4......... Sales & Marketing E-Business Suite Call-Center Business Intelligence Applications Operational Reporting Back-Office 33
Dimensional Data Structures Off-Line Analytical Warehouse Scores & Flags Batch Load and Transformation Common Application Data Cust 1 data 1 data 2 data 3 On-Line Cust 2......... Cust 3......... Data Cust 4......... Store Cust 5......... Cust 6 data 1 data 2 data 3 Cust 7......... Single Cust 8......... Cust 1 data 1 data 2 data 3 CustCustomer 9......... Cust 2......... Cust 10......... Cust 3......... View (SCV) Cust 4......... XML EAI Direct Access API or Integration Server e-business Suite Web Sales & Marketing Independent Applications Call Center ERP 34
Real-Time Rules based Personalisation Collaborative Filtering Web External Data Off-Line Analytical Warehouse Cust 1 data 1 data 2 data 3 Cust 2......... Cust 3 On-Line......... Cust 4......... Cust 5... Data...... Cust 6 data 1 data 2 data 3 Cust 7......... Store Cust 8......... Cust 1 data 1 data 2 data 3 Cust 9......... Cust 2......... Cust 10......... Cust 3......... Cust 4......... Sales & Marketing E-Business Suite Call-Center BI Applications Customer Profiling Clickstream Analysis Analysis & Reporting Back-Office 35
Personalization How Does It Work? BooksRus.com s Book Store - Netscape BooksRus.com BooksRus.com BooksRus.com 100 Hot Books Html request BooksRus.com Web Server Html Code Generation <static sections> Customer BooksRus.com ID Profession BooksRus.com Field ScoreClassic Rap ActionThriller Poetry The Brethren by John Grisham BooksRus.c BooksRus.com Auctions. om BooksRus.co Auctions. m 100 Hot Books <dynamic sections created using JSP> 23567 Computer Consulting 15 1 600 1 0 Html code www.booksrus.com Browser (Client) The session data is cleaned The profile to create is scored a in the browsing database profile and the to result be is used returned with to customer the Web data server for scoring Oracle 9i Personalization Services 36
On-Line Personalization Customer Profile Data Recommendation API Personalization Engine -> Session data <- Action score Off-Line Analytical Warehouse Cust 1 data 1 data 2 data 3 Cust 2......... Cust 3......... On-Line Cust 4......... Cust 5......... Customer Cust 6 data 1 data 2 data 3 Data Cust 7......... Cust 8......... Cust 1 data 1 data 2 data 3 Cust 9... Store...... Cust 2......... Cust 10......... Cust 3......... Cust 4......... Web Marketing Sales Call Centre Customer Profiling 37
Off-Line Analytical Warehouse On-Line Customer Data Store Data Mining OLAP Query & Reporting Query & Reporting Alerts Workflow Alerts & W/Flow Portal 38
39
40
Common technology infrastructure BI too slow too costly in particular Common Warehouse Metadata 41
( ) Near real-time Pro-active (automated) Operational DW = Analytical (DW) transactional (OLTP) 42
Oracle9i Personalisation External Data Analytical Structures and Performance Management Framework Cust 1 data 1 data 2 data 3 Cust 2......... Cust 3......... Cust 4 On-Line......... Cust 5......... Cust 6 data 1 data 2 data 3 Customer Cust 7......... Cust 8......... Cust 1 data Data 1 data 2 data 3 Cust 9......... Cust 2......... Cust 10......... Cust 3......... Cust 4......... Web Sales & Marketing Call-Centre Back-Office BI Applications E-Business Suite Data Mining OLAP Query & Reporting Alerts Workflow Portal Wireless Access 43
44 ORACLE BI SOLUTION
Traditional Business Intelligence ETL Tool OLAP Engine Analytic Apps Lineage ETLTool Transformation Engine Transformation Engine Name/Address Scrubbing Engine Query & Analysis Reporting Engine Enterprise Reporting P o r t a l Multi-Vendor, Un-integrated 45
Oracle9i Intelligent Infrastructure Single Vendor, Integrated 46
Oracle9i Intelligent Infrastructure Single Vendor, Integrated E-Business Intelligence Suite Oracle9i Reports Discoverer Warehouse Web Builder ETL Infrastructure Analytic Services 9i Application Server BI Beans External CWM Meta Portal 47
DW DBMS Core Capabilities Performance Scalability Manageability Fast Analysis of Lots of for Lots of Users 48
DW DBMS e-bi Platform Extract, Transform, and Load Analytics Personalization Quality for Knowledge you can Action 49
Oracle 7.3 Hash Join Bitmap Indexes Parallel-Aware Optimizer Partition Views Instance Affinity: Function Shipping Parallel Union All Asynchronous Read-Ahead Histograms Anti-Join Continuous Innovation Oracle 8.0 Oracle8i Oracle9i Partitioned Tables and Indexes Partition Pruning Parallel Index Scans Hash and Composite Partitioning Parallel Insert, Update, Materialized Delete Views Functional Parallel Bitmap Star Query Indexes Resource Manager ETL, OLAP, Data Mining Engines Parallel ANALYZE Progress Monitor List Partitioning Parallel Constraint Enabling Adaptive Parallel Query Bitmap Join Index Server Managed Backup/Recovery Server-based Analytic Functions Dynamic Aggregation Buffersize Point-in-Time Recovery Transportable Tablespaces Materialized Intermediate Results Direct Loader API Grouping Sets Partition-wise Joins Concatenated Security Enhancements Grouping Sets Aggregate Pruning New Analytic Functions Self-Tuning Execution Memory System Managed Undo Dynamic Resizing of Buffer Pool 50
Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Data Mining 51
Oracle Solution for BI Performance Scalability Manageability OLAP Services ETL Services Services 52
Bitmap Join Index Join Bitmap Index Join A Column B Bitmap Index Benefits Bitmap Index Table Access Performance Dimension Table Fact Table Join 53
Bitmap Join Index (Cont.) Sales Customer CREATE BITMAP INDEX cust_sales_bji ON Sales(Customer.state) FROM Sales, Customer WHERE Sales.cust_id = Customer.cust_id; Index key is Customer.State Indexed table is Sales 54
Materialized View Fact Table Summary Table Benefits join aggregation Dimension hierarchy rollup Application Base Table Access Optimizer Summary Table Redirect summary 55
Materialized View Query Rewrite x 3? (30 GB) Summary SELECT p.brand, r.country, t.month,sum(s.amt) tot_sales FROM sales s, region r, time t, product p WHERE s.region_id = r.region_id AND s.sdate = t.curdate AND s.prod_code = p.prod_code GROUP BY p.brand, r.country,t.month HAVING SUM(s.amt) > 5000000; (2GB) SELECT brand, country, month, tot_sales FROM FROM sales_sumry WHERE tot_sales > 5000000; 56
Materialized View Refresh Refresh Incremental Deferred Full Partition Manual Automatic Enhancements to MVs Broader refresh and rewrite capabilities More sophisticated summary advisor Insert Update Delete Deferred Refresh Incremental Refresh Full Refresh 57
WITH Clause With summary as ( select channel_desc,sum(amount) as amount_tot from sales, channels where channels.channel_id = sales.channel_id group by channel_desc) select channel_desc,amount_tot from summary where amount_tot > (select sum(amount_tot) * 1/8 from summary) order by channel_desc; Benefits Performance 58
Automatic Memory Tuning Automated SQL Execution Memory User SQL User Memory PGA dynamic PGA_AGGREGATE_TARGET = <size> SORT_AREA_SIZE, HASH_AREA_SIZE Benefits throughput ( ) response time 59
Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 60
List Partitioning Partitions Partition Column Composite Partition (Range + List)... 61
List Partitioning List partitioning Partition Partition Partition (,, ) : CREATE TABLE sales_by_product_line ( ) PARTITION BY LIST (state) ( PARTITION northwest VALUES IN( OR, WA ), PARTITION southwest VALUES IN ( AZ, UT, NM ), PARTITION northeast VALUES IN ( NY, VT, NJ ), PARTITION southeast VALUES IN ( FL, GA ))); 62
Automatic Parallel Tuning System Parallel Degree User Query Runtime Two : AUTOMATIC_PARALLEL_TUNING = TRUE PARALLEL_THREADS_PER_CPU = <number> 63
Data Compression Most beneficial for DW Table Tablespace level compress Tablespace level tablespace DEFAULT NOCOMPRESS Algorithm value Block size sorting 64
Data Compression Sample CREATE TABLE employees ( employee_id NUMBER, first_name VARCHAR2(20),.) COMPRESS; CREATE TABLESPACE sample DATAFILE sample01.dbf SIZE 20M DEFAULT COMPRESS; CREATE TABLE countries ( country_id CHAR(2), country_name VARCHAR2(40), region_id NUMBER ) NOCOMPRESS TABLESPACE sample; 65
Data Compression Compression Operation Direct Path SQL*Loader CTAS Parallel INSERT(or serial INSERT with as APPEND hint) statement Compression Table(include Partition table) Materialized View Index 66
Data Compression Compression Data Type LOB LOB derive data type data type Compression ratio Industry data 2:1 ~ 4:1 Load sort Large PCTFREE COMPRESSED Table PCTFREE 0 setting 67
Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 68
Memory Management Automated SQL Execution Memory User SQL User Memory PGA dynamic Benefits throughput ( ) response time 69
Memory Management Dynamic SGA Oracle SGA shutdown alter system Buffer cache Shared pool Large pool Benefits 70
Resource Manager (, ) OLTP : resource 20%, DW : resource 80% Benefits job resource 71
72 Resource Manager
Enhanced Resource Manager Oracle9i introduces: Proactive Query Governing: Automatic Queuing: : 73
Enhanced Statistics Gathering GATHER_SCHEMA_STATISTICS(<schema_name>, OPTIONS=> GATHER AUTO ); Benefits 74
Enhanced Summary Advisor Materialize View Recommendation Allows DBAs to optimize disk costs to performance gains Workload information dictionary information Summary Advisor Summary Usage Summary Recommendations Space Usage 75
Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 76
Change Data Capture extraction Changed Table From Oracle sources Synchronous Mode Log Files Source DB Trigger-based Replication Change Tables Publish and Subscribe Target DW Source DB 77
Change Capture Benefits 78
External Table DB Flat file Read Only Table DB,, Benefits DW ETL STAGING source 79
Upserts insert update Single command Merge into dept t using src_dept s on (t.deptno = s.deptno) WHEN matched then update set t.loc = s.loc WHEN NOT matched then insert (t.deptno, t.dname, t.loc) values (s.deptno, s.dname, s.loc); Benefits SQL Bulk Loading/DML Performance 80
Multi-Table Inserts Insert Single command INSERT ALL WHEN product IN (select product from promotional_items) THEN INTO promotional_sales VALUES (product, amount) WHEN coupon IS NOT NULL THEN INTO coupon_sales SELECT product, amount, coupon FROM item_lines; Benefits Performance 81
Table Functions Function Output row 82 PL/SQL, Java, C Transformation Table Function input row Pipelined and parallel procedural transformations transformation Function parallel Benefits High performance complex transformations No need for a separate ETL engine Transformation Staging table
Oracle base for BI ETL Services ETL select from flat file join to base table apply complex procedural transformations upsert into multiple target tables SQL Streamed, parallel, complex ETL in the base 83
Oracle9i: ETL Scenario Oracle8i: Multiple staging tables and SQL statements Staging Table Staging Table FLAT FILES Step 1: Load into staging table Step 2: Transform using function Step 3: Insert and update into target table TARGET LOAD TRANS- FORM MERGE Oracle9i: Single SQL statement Oracle9i: Parallel pipelining of Data 84
Oracle BI Tools Oracle Warehouse Builder EDW Oracle Discoverer Data Oracle OLAP Service Oralce BI Beans BI Application (Deploy) 85