iAnywhere Wireless Server

1
BID204 - Fortis The ROI of a Mainframe Downsize
Thierry WinckelmansLead Area Architectthierry.wi
nckelmans_at_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
OK guys, Lets see how we can satisfy our users
IT will analyze the problem using their own
vision
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 companys 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
Dont forget to synchronize all the metadata of
the query tools with your modified schema.
5
Traditional Datawarehouse Architecture
RDBMS
ETL Extraction Transformation Transport Load

• ANALYSIS, QUERY REPORTING TOOL
• Business Objects
• Brio
• Cognos
• Microstrategy
• ...

• SOURCE DATABASE,
• supporting corporate
• applications
• Oracle
• Informix
• DB/2
• Microsoft
• SybaseASE

• 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
Gartners view of TCO
8
Datawarehouse Costs

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

9
Enterprise Datawarehouse architecture
Close relationship between data volume, CPU
number, memory size and number of applications
10
Datamart architecture
11
AppMart Architecture
12
DW growth 2x, 3x, 10x per year
CPUs
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
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
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
DSS vs OLTP workload

• Performance efficiency
• scalability(data users)

OLTPsimple query -process 10s of rows -1000s of
rows per second
DSScomplex 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 Youd 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
  (lt100 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 ETLQuery tools
RDBMS
ETL Extraction Transformation Transport Load

• ANALYSIS, QUERY REPORTING TOOL
• Business Objects
• Brio
• Cognos
• Microstrategy
• ...

• SOURCE DATABASE,
• supporting corporate
• applications
• Oracle
• Informix
• DB/2
• Microsoft
• SybaseASE

• 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
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
BI end users environment
3270
Intranet
Client
WEBI
FTP
37
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
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

• AccountsPaymentsinvestigations 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

1. Definition of the POC (Volume, schema, queries,)
2. POC Execution (PowerDesigner, Templates,)
3. Technical reports (Performance,POC report
   ,Platform ROI)
4. ASIQ integration plan
5. How to integrate into your current environnement
   ?
6. Time to integrate ?
7. Maintenance efforts estimates(Admin, Support,
   Training,) ?
8. Business reports presented to management