1 / 45

Thierry Winckelmans Lead Area Architect thierry.winckelmans@sybase August 7, 2003

BID204 - Fortis : The ROI of a Mainframe Downsize. Thierry Winckelmans Lead Area Architect thierry.winckelmans@sybase.com August 7, 2003. From Decisional to Operational warehouse. I absolutely need to understand how my company behaves. Basically, data warehouse is an end user need.

robbin
Download Presentation

Thierry Winckelmans Lead Area Architect thierry.winckelmans@sybase August 7, 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BID204 - Fortis : The ROI of a Mainframe Downsize Thierry WinckelmansLead Area Architectthierry.winckelmans@sybase.comAugust 7, 2003

  2. From Decisional to Operational warehouse I absolutely need to understand how my company behaves Basically, data warehouse is an end user need. It is not driven by the business itself of the company, but by people who want to understand how the company behaves. The number of DWH users is low, but those are generally in the management of the company.

  3. From Decisional to Operational warehouse IT will analyze the problem using their own vision… OK guys, Let’s see how we can satisfy our users We will need a query tool to retrieve the data from our standard RDBMS. We will need an ETL to store the data in our RDBMS. …And will use the company’s standard RDBMS

  4. From Decisional to Operational warehouse To optimize the performance, we absolutely need to compute pre-build results. We also need to de-normalize our schema, etc… Don’t forget to synchronize all the metadata of the query tools with your modified schema.

  5. Traditional Datawarehouse Architecture RDBMS • SOURCE DATABASE, • supporting corporate • applications • Oracle • Informix • DB/2 • Microsoft • SybaseASE ETL Extraction Transformation Transport Load • ANALYSIS, QUERY & REPORTING TOOL • Business Objects • Brio • Cognos • Microstrategy • ... • USERS • Marketing • Line of Business • Senior Management • Help-desk • Customer support • SYBASE • Direct Connect, • Replication Server, • PARTNERS’ • PRODUCTS • Informatica, Genio, Datastage...

  6. From Decisional to Operational warehouse Well, IT built something that works, but… …what is the exact cost of this ? The infrastructure is very expensive. We have a very few users and they complains … • The data are not updated frequently • The performance is low. • They cannot query what they need

  7. Gartner’s view of TCO

  8. Datawarehouse Costs • Technology Costs • Hardware • Storage • Software Costs • Database • ETL • Query tools • People Costs • DBA • Business analysts • Developpers

  9. App CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU App App App Enterprise Datawarehouse architecture Close relationship between data volume, CPU number, memory size and number of applications

  10. App CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU App App App Datamart architecture

  11. Dept Reader Dept Reader App CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU CPU App CPU CPU Writer/Reader SAN / NAS App App Dept Reader Dept Reader AppMart Architecture

  12. Y5 Y4 Y4 Y3 Y3 Y2 Y1 Y2 Y1 Y2 Y3 Y4 Y1 CPUs DW growth: 2x, 3x, 10x per year 200 175 150 125 100 75 50 25 12 0 0 5 10 15 20 25 30 35 40 45 50 Input data (TB)

  13. Datawarehouse Costs • Technology Costs • Hardware • Storage • Software Costs • Database • ETL • Query tools • People Costs • DBA • Business analysts • Developpers

  14. 13.57 TB 10.18 TB 9.71 TB 0.97 TB Data Compression (1 TB of raw data) Sybase IQ is the only database that REDUCES storage requirements 9 TB • DB2 UDB • NCR • Informix • Oracle9 • Sybase IQ • IBM AIX • HP • Sun • Sun

  15. The Costs Of A Data Warehouse TPC-H proved that an RDBMS is the most expensive tool to store the data warehouse Some facts Fortis estimates operational costs of 1 GB = 500€/GB

  16. Summaries Aggregates 1-2 TB Same INPUT Data: “Conventional DW” is 4x-10x larger than IQ-M DW (Conventional DBMS “compression”: reduces explosion from 5x-15x4x-10x ) This dominates large DW (>1 TB input data) 2.4-6 TB Indexes 0.5-3TB Base table (“RAW data”) (no indexes) LOAD INPUT DATA: 1TB -Source: flat files, ETL, replication,ODS 0.9-1.1TB Aggr/Summ: 0-0.1TB 0.25 - 0.9 TB LOAD Indexes: 0.05-0.3TB Base table(FP):0.2-0.5TB Amount of Detailed data Conventional DBMS IQ Multiplex

  17. Datawarehouse Costs • Technology Costs • Hardware • Storage • Software Costs • Database • ETL • Query tools • People Costs • DBA • Business analysts • Developpers

  18. row-centric DBMS (designed +20 years ago for OLTP) DB2, MS SQL, ASE, NCR etc. IQ Multiplex 10x- 1000x 10x- 1000x DSS vs OLTP workload: • Performance & efficiency • scalability(data & users) OLTP=simple query -process 10s of rows -1000s of rows per second DSS=complex query -process M(B)ILLIONS of rows -Millions of rows per second

  19. From Decisional to Operational warehouse • Do you know that ASIQ can : • Accelerate the data loading time • Speed up your queries • Simplify your data schema • Increase the number of end users • Decrease the administration cost • Reduce the disk volume The real costs of a datawarehouse are due to the RDBMS You’d better use a CBRD.

  20. Vertical Partitioning and Bit Wise Indexing • Data is stored by COLUMN instead of ROW • Each COLUMN is the INDEX • INDEXES designed for Data Warehousing and not OLTP • Benefits: • Only access data needed for query • We ZIP through BITS • Indexes take advantage of data characteristics • FAST ACCESS and LOAD • Easy to compress • Easy to alter and manage • Few if Any Aggregates • Reduce I/O by over 90% Calculate Average Sales for “A” stores in New York No More CHUGGING Through BYTES

  21. ... Advanced Bit-Mapped Indexes • 25 Year Old Technology - Model 204 • Only usable for low cardinality data before IQ (<100 values) • ASIQ extends range to 1,000 possible values by combining bitmaps and compression • Of limited value without other types of advanced indexing • Very few queries require only the bit-maps to complete

  22. Datawarehouse Costs • Technology Costs • Hardware • Storage • Software Costs • Database • ETL • Query tools • People Costs • DBA • Business analysts • Developpers

  23. Any change in schema impacts ETL&Query tools RDBMS • SOURCE DATABASE, • supporting corporate • applications • Oracle • Informix • DB/2 • Microsoft • SybaseASE ETL Extraction Transformation Transport Load • ANALYSIS, QUERY & REPORTING TOOL • Business Objects • Brio • Cognos • Microstrategy • ... • USERS • Marketing • Line of Business • Senior Management • Help-desk • Customer support • SYBASE • Direct Connect, • Replication Server, • PARTNERS’ • PRODUCTS • Informatica, Genio, Datastage...

  24. Schema workarounds to speed up performances • Table Partitioning • By Year, By Month, By Week… • Table splitting • Create several tables with one-one relationship • Summary/Aggregate/Pre-computed tables Increase DB schema complexity  Increase ETL complexity Increase query tool complexity Increase DWH costs

  25. Eurostat : wide table – 10 Mio rows

  26. Eurostat : Horizontal Partitioning

  27. Eurostat : Vertical Partitioning

  28. Eurostat : In IQ-M In IQ : 757 FP, 45 HG, 512 LF, 103 HNG = 1417 index

  29. Datawarehouse Costs • Technology Costs • Hardware • Storage • Software Costs • Database • ETL • Query tools • People Costs • DBA • Business analysts • Developpers

  30. DBA/Volume Ratio Source : Gartner Symposium ITxpo 2002

  31. IT view : Ad-Hoc Query Costs • To answer an adhoc/unplanned query, DBA have to work • Allocate resource/Establish task priority • Understand end-user query • Define/Write the sql query • Check/Adapt database/index design • Run query and send results • In average 1 man/day to answer an adhoc query(Elapsed time may vary from 24h to 3 weeks) Most of the time, adhoc queries are forbidden

  32. FORTIS ROI

  33. What is Fortis Bank Belgium ? • Is a part of FORTIS group: • second largest financial services provider in Benelux; • Belgian/Dutch company • Benelux home base; international presence • active in Insurance, banking and investment • Fortis Bank Belgium • 5.750.000 customers • 2.100 branches • 20.000 employees

  34. Operational systems Accept Publish End-user Computing environments Outflow Inflow IC-Publisher Functional BI Infrastructure C.I.W. Datamarts IC -Infocentre

  35. BI end users environment “3270” PCxfer Client MS Office SAS for Windows WebFocus for Windows Time Sharing Option Program to program Enterprise Data Access SAS-MVS Nomad-MVS Focus-MVS DB2 tables MVS

  36. PCxfer MS Office SAS for Windows WebFocus for Windows Business Objects Time Sharing Option Program to program Enterprise Data Access SAS Intranet WEB Focus Enterprise Data Access SAS-MVS Nomad-MVS Focus-MVS SAS-ds Sybase IQ tables DB2 tables Seq Other RDBMS tables MVS NT BI end users environment “3270” Intranet Client WEBI FTP

  37. WEBI SAS Intranet WEB Focus MS Office WebFocus for Windows SAS for Windows Business Objects Sybase IQ tables Seq FTP MVS NT Sybase IQ-context and some figures Client Intranet 114.136 connections (~ queries) / month 0.4 TB COMPRESSED for 1440 tables 1.5 TB refreshes 1000 tables / month

  38. Fortis ROI • 250 NOMAD users moved to BO-IQ • 250 * 1000€ = 250,000€ • Mainframe upgrade 2 years ago  2,500,000€ • Current Infrastructure : 2 servers in multi-databases • Compaq Proliant 5500 • 4 processors Pentium III Xeon/500MHz with 2 GB RAM • 250 GB Raid 5 • Compaq Proliant DL580 • 4 processors Pentium III Xeon/700MHz with 2 GB RAM • 360 GB Raid 5 • Upgrade : 2 servers in mono-database (one backup of the other) • Compaq Proliant DL580 • 4 processors Pentium III Xeon/900MHz with 2GB RAM • 800 GB Raid 5

  39. IT view : Ad-Hoc Query Costs • To answer an adhoc/unplanned query, DBA have to work • Allocate resource/Establish query priority • Discuss with end-user • Define/Write the sql query • Check/adapt database/index design • In average 1 man/day to answer an adhoc query • 1 dba/day in Belgium  350€ • At Fortis ((assume 80,000 adhoc) * 12) * 350€ = 336,000,000€ • Nobody has such a budget for BI only

  40. Business view : Ad-Hoc Query Costs • A refused or not executed query costs the most • IT should not have the right to say ‘NO’ to execute a query : this is clearly an IT failure • At Fortis, reconciliating BackOffice and MiddleOffice detected transactions entered in Zaïre and paid in $, exchange rate of 1 • Fraud detection query answered in 23 seconds on 525Mio rows table This is a real operational datawarehouse

  41. Advantages for Fortis • It ’s very easy to switch from another RDBMS to Sybase IQ ……. And vice-versa !!! • High performance for BI activities: unequalled response time • Scalability of the solution • Compression-ratio • simplicity (RDBMS-like) • low DBA-cost : 1 FTE (versus 3-4 according to Gartner)

  42. 3-10s 1-3 s 10-60s 11% 16% 12% 1-3 min 3% >3min 1% <1 sec 57% Performances Monthly Figures • Number of queries : 115.000 • Loaded volumes : 1.5 TB (versus 6/7.5TB according to Gartner) • Load speed : 15 - 30 GB /hr =f(#indexes) • Response time :

  43. Some typical applications Accounts&Payments:investigations on operations • volumes : 525.000.000 operations • raw data : 170 GB • loaded data : 48,5 GB • avg response time : 2 sec (BO-presentation time inc.) • example: 0,84 s for 372 rows fetched Steering System for District managers seq data : 11 GB loaded data : 4 GB

  44. Evolution of Sybase-IQ

  45. Proof Of Concept • Definition of the POC (Volume, schema, queries,…) • POC Execution (PowerDesigner, Templates,…) • Technical reports (Performance,POC report ,Platform ROI…) • ASIQ integration plan : • How to integrate into your current environnement ? • Time to integrate ? • Maintenance efforts estimates(Admin, Support, Training,…) ? • Business reports presented to management

More Related