Jerry Held

Size: px

Start display at page:

Download "Jerry Held"

주형 영
5 years ago
Views:

2 Principal Sales Consultant

3 e-business Intelligence

4 Topics Business Intelligence e-business Intelligence BI System Oracle9i BI Applications BI architectures BI architecture Conclusions 4

5 5

6 Typical Data Warehousing Topology, Data Marts Business Analyst Clients On-line Transaction Processing Systems Speed of implementation Department ROI 6

7 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

8 Typical Data Warehousing Topology, 1995 Data Marts Business Analyst Clients Enterprise Data Warehouse On-line Transaction Processing Systems Central IT management Consistency 8

9 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

10 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

11 Oracle Warehouse Any Source Any Information Any Access Operational Data Query & Reporting Tools Warehouse OLAP Tools External Data Applications 11

12 BI 12

13 Business Intelligence WEB Summary tables / hierarchies SQL OLAP extensions Data mining algorithm APIs 13

14 Business Intelligence IT (10TB ) (24x7x365) DBMS ETL 14

15 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

16 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

17 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

18 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

19 E-Business Intelligence challenges e-business: BIGGER : FASTER Business Intelligence Real-time MORE USERS closed-loop BETTER INTEGRATION PACKAGED SOLUTIONS 19

20 20 Oracle9i BI Applications

21 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

22 Strategic Enterprise Management Strategy Formulation Balanced Scorecard Financial Analysis Activity Based Mgmt Value Based Mgmt 22

23 Enterprise Intelligence - Operational & Analytical Supply Chain Manufacturing Purchasing Finance Travel HR 23

24 Enterprise Intelligence - Operational & Analytical - CRM Marketing Sales Service Call Center Online Sales Loyalty Call Center Just click Click-thru 24

25 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

26 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

27 : Best of Breed Brand D ETL Engine OLAP Engine Database Data Warehouse Engine Reports Engine Brand A Brand B Brand C Brand E 27

28 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

29 Oracle BI Applications Products The Enterprise Balanced Scorecard Analyzers: Financial Analyzer Sales Analyzer Performance Analyzer Demand Planning Activity Based Management 29

30 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

31 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 32 ACTIVE Warehouse

33 Web External Data Off-Line Analytical Warehouse Cust 1 data 1 data 2 data 3 Cust Cust Cust 4 On-Line Cust Cust 6 data Data 1 data 2 data 3 Cust Cust Cust 1 data Store 1 data 2 data 3 Cust Cust Cust Cust Cust Sales & Marketing E-Business Suite Call-Center Business Intelligence Applications Operational Reporting Back-Office 33

34 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 Cust Data Cust Store Cust Cust 6 data 1 data 2 data 3 Cust Single Cust Cust 1 data 1 data 2 data 3 CustCustomer Cust Cust Cust View (SCV) Cust XML EAI Direct Access API or Integration Server e-business Suite Web Sales & Marketing Independent Applications Call Center ERP 34

35 Real-Time Rules based Personalisation Collaborative Filtering Web External Data Off-Line Analytical Warehouse Cust 1 data 1 data 2 data 3 Cust Cust 3 On-Line Cust Cust 5... Data Cust 6 data 1 data 2 data 3 Cust Store Cust Cust 1 data 1 data 2 data 3 Cust Cust Cust Cust Cust Sales & Marketing E-Business Suite Call-Center BI Applications Customer Profiling Clickstream Analysis Analysis & Reporting Back-Office 35

36 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> Computer Consulting Html code 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

37 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 Cust On-Line Cust Cust Customer Cust 6 data 1 data 2 data 3 Data Cust Cust Cust 1 data 1 data 2 data 3 Cust 9... Store Cust Cust Cust Cust Web Marketing Sales Call Centre Customer Profiling 37

38 Off-Line Analytical Warehouse On-Line Customer Data Store Data Mining OLAP Query & Reporting Query & Reporting Alerts Workflow Alerts & W/Flow Portal 38

39 39

40 40

41 Common technology infrastructure BI too slow too costly in particular Common Warehouse Metadata 41

42 ( ) Near real-time Pro-active (automated) Operational DW = Analytical (DW) transactional (OLTP) 42

43 Oracle9i Personalisation External Data Analytical Structures and Performance Management Framework Cust 1 data 1 data 2 data 3 Cust Cust Cust 4 On-Line Cust Cust 6 data 1 data 2 data 3 Customer Cust Cust Cust 1 data Data 1 data 2 data 3 Cust Cust Cust Cust Cust Web Sales & Marketing Call-Centre Back-Office BI Applications E-Business Suite Data Mining OLAP Query & Reporting Alerts Workflow Portal Wireless Access 43

44 44 ORACLE BI SOLUTION

45 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

46 Oracle9i Intelligent Infrastructure Single Vendor, Integrated 46

47 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

48 DW DBMS Core Capabilities Performance Scalability Manageability Fast Analysis of Lots of for Lots of Users 48

49 DW DBMS e-bi Platform Extract, Transform, and Load Analytics Personalization Quality for Knowledge you can Action 49

50 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

51 Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Data Mining 51

52 Oracle Solution for BI Performance Scalability Manageability OLAP Services ETL Services Services 52

53 Bitmap Join Index Join Bitmap Index Join A Column B Bitmap Index Benefits Bitmap Index Table Access Performance Dimension Table Fact Table Join 53

54 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

55 Materialized View Fact Table Summary Table Benefits join aggregation Dimension hierarchy rollup Application Base Table Access Optimizer Summary Table Redirect summary 55

56 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) > ; (2GB) SELECT brand, country, month, tot_sales FROM FROM sales_sumry WHERE tot_sales > ; 56

57 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

58 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

59 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

60 Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 60

61 List Partitioning Partitions Partition Column Composite Partition (Range + List)... 61

62 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

63 Automatic Parallel Tuning System Parallel Degree User Query Runtime Two : AUTOMATIC_PARALLEL_TUNING = TRUE PARALLEL_THREADS_PER_CPU = <number> 63

64 Data Compression Most beneficial for DW Table Tablespace level compress Tablespace level tablespace DEFAULT NOCOMPRESS Algorithm value Block size sorting 64

65 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

66 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

67 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

68 Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 68

69 Memory Management Automated SQL Execution Memory User SQL User Memory PGA dynamic Benefits throughput ( ) response time 69

70 Memory Management Dynamic SGA Oracle SGA shutdown alter system Buffer cache Shared pool Large pool Benefits 70

71 Resource Manager (, ) OLTP : resource 20%, DW : resource 80% Benefits job resource 71

72 72 Resource Manager

73 Enhanced Resource Manager Oracle9i introduces: Proactive Query Governing: Automatic Queuing: : 73

74 Enhanced Statistics Gathering GATHER_SCHEMA_STATISTICS(<schema_name>, OPTIONS=> GATHER AUTO ); Benefits 74

75 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

76 Oracle Solution for BI Performance Scalability Manageability ETL Services OLAP Services Services 76

77 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

78 Change Capture Benefits 78

79 External Table DB Flat file Read Only Table DB,, Benefits DW ETL STAGING source 79

80 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

81 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

82 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

83 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

84 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

85 Oracle BI Tools Oracle Warehouse Builder EDW Oracle Discoverer Data Oracle OLAP Service Oralce BI Beans BI Application (Deploy) 85

Portal_9iAS.ppt [읽기 전용]

Portal_9iAS.ppt [읽기 전용] Application Server iplatform Oracle9 A P P L I C A T I O N S E R V E R i Oracle9i Application Server e-business Portal Client Database Server e-business Portals B2C, B2B, B2E, WebsiteX B2Me GUI ID B2C