BID204 - Fortis : The ROI of a Mainframe Downsize

Thierry Winckelmans

Lead Area Architect

thierry.winckelmans@sybase.com

August 7, 2003

BID204
-

Fortis : The ROI of a Mainframe Downsize

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.

From Decisional to Operational warehouse

OK guys,

Let’s see how
we can satisfy
our users

IT will analyze the problem using their own vision
…

We will need an
ETL to store the
data in our
RDBMS.

We will need a
query tool to
retrieve the data
from our
standard
RDBMS.

…And will use the company’s standard RDBMS

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.

From Decisional to Operational warehouse

ETL Extraction

Transformation
Transport

Load

ANALYSIS,
QUERY &
REPORTING
TOOL


•
Business
Objects

•
Brio

•
Cognos

•
Microstrategy

•
...

Traditional Datawarehouse Architecture

SYBASE

•
Direct Connect,

•
Replication Server,


PARTNERS’

PRODUCTS

•
Informatica, Genio,
Datastage...

SOURCE DATABASE,

supporting corporate

applications


•
Oracle

•
Informix

•
DB/2

•
Microsoft

•
SybaseASE

USERS


•
Marketing

•
Line of Business

•
Senior Management

•
Help
-
desk

•
Customer support

RDBMS

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
…

From Decisional to Operational warehouse

•
The data are not updated
frequently

•
The performance is low.

•

They cannot
query

what
they need


Gartner
’s

view of TCO

Datawarehouse Costs


Technology Costs

Hardware

Storage


Software Costs

Database

ETL

Query tools


People Costs

DBA

Business analysts

Developpers

App

CPU

Enterprise Datawarehouse architecture


CPU

CPU

CPU

CPU

Close relationship between data volume, CPU number,
memory size and number of applications

App

App

App

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

CPU

Datamart architecture

App

CPU

CPU

CPU

CPU

App

CPU

CPU

CPU

CPU

App

CPU

CPU

CPU

CPU

App

CPU

CPU

CPU

CPU

AppMart Architecture

App

CPU

App

CPU

CPU

Dept Reader

Dept Reader

App

CPU

CPU

CPU

CPU

Dept Reader

App

CPU

CPU

CPU

CPU

CPU

Dept Reader

SAN / NAS

CPU

CPU

CPU

CPU

Writer/Reader

DW growth: 2x, 3x, 10x per year

CPUs

0 5 10 15 20 25 30 35 40 45


50

Input data (TB)

Y4

Y3

Y2

Y
1

200




175




150




125




100




75




50




25


12


0


Y5

Y4

Y3

Y2

Y1

Datawarehouse Costs


Technology Costs

Hardware

Storage


Software Costs

Database

ETL

Query tools


People Costs

DBA

Business analysts

Developpers

Sybase IQ is
the only
database
that
REDUCES
storage
requirements

9.71 TB

9 TB

10.18 TB

13.57 TB

0.97 TB

•
DB2 UDB

•
IBM AIX

•
NCR

•
Informix

•
HP

•
Oracle9

•
Sun

•
Sybase IQ

Data Compression (1 TB of raw data)

•
Sun

The Costs Of A Data Warehouse

Some facts

TPC
-
H proved that an RDBMS is the most expensive tool to store the data warehouse

Database Software
Scale
Informix XPS 8.31 FD1
300
Microsoft SQL Server 2000
300
Informix XPS 8.30 FC3
300
Microsoft SQL Server 2000
300
Informix EPS 8.30 FC2
300
Informix EPS 8.30 FC2
300
IBM DB2 UDB 7.1
300
IBM DB2 UDB 7.1
300
IBM DB2 UDB 7.1
300
ASIQ 12.4.3
300
Oracle9i Enterprise
1000
Informix EPS 8.31FD1
1000
IBM DB2 UDB 7.1
1000
Teradata V2R4.1
1000
IBM DB2 UDB EEE 7.2
1000
ASIQ 12.4.3
1000
Raw/DB
DB Size
14.07
4221
12.1
3630
11.12
3336
7.35
2205
6.95
2085
10.46
3138
9.38
2814
14.01
4203
19.05
5715
0.97
291
13.57
13570
10.18
10180
9.71
9710
9
9000
6
6000
0.97
970
Disk Price $
GB Price $
1,504,338
356.39
299,764
82.58
1,307,774
392.02
151,909
68.89
457,984
219.66
961,857
306.52
464,703
165.14
697,055
165.85
922,906
161.49
62,033
213.17
6,505,521
479.40
5,425,930
533.00
3,766,620
387.91
2,624,899
291.66
642,751
107.13
349,025
359.82
ASIQ Storage Price $
Gain $
103,711
1,400,627
24,031
275,733
114,077
1,193,697
20,048
131,861
63,920
394,064
89,197
872,660
48,056
416,647
48,261
648,794
46,993
875,913
62,033
0
465,023
6,040,498
517,009
4,908,921
376,274
3,390,346
282,906
2,341,993
103,911
538,840
349,025
0
Fortis estimates operational costs of 1 GB = 500
€
/GB

Amount of Detailed data

LOAD

LOAD

INPUT DATA:

1TB

-
Source: flat files,


ETL, replication,ODS

Base table(FP):0.2
-
0.5TB

Indexes: 0.05
-
0.3TB

Aggr/Summ: 0
-
0.1TB

Summaries

Aggregates

1
-
2
TB

Indexes

0.5
-
3TB

Base table

(“RAW data”)

(no indexes)

0.9
-
1.1TB

2.4
-
6

TB

IQ Multiplex

Conventional DBMS

Same INPUT Data:
“Conventional DW” is


4
x
-
10
x larger
than IQ
-
M DW

(Conventional DBMS “compression”:


reduces explosion from
5
x
-
15
x



4
x
-
10
x

)

This dominates large DW (>
1
TB input data)

0.25
-

0.9 TB

Datawarehouse Costs


Technology Costs

Hardware

Storage


Software Costs

Database

ETL

Query tools


People Costs

DBA

Business analysts

Developpers

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

IQ Multiplex

row
-
centric DBMS

(designed +20 years ago for OLTP)

DB2, MS SQL, ASE, NCR etc.

10
x
-

1000
x

10x
-

1000x

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
.


Date
Store
State
Class
Sales
3/1/96
32
NY
A
6
3/1/96
36
MA
A
9
3/1/96
38
NY
B
5
3/1/96
41
CT
A
11
3/1/96
43
NY
A
9
3/1/96
46
RI
B
3
3/1/96
47
CT
B
7
3/1/96
49
NY
A
12
Vertical Partitioning and Bit Wise Indexing

Calculate
Average
Sales for

“A” stores
in New
York

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

No More CHUGGING Through BYTES

Bitmap Index for STATE
row-id
AK
AL
AR
CA
CO
DC
DE
FL
1
0
0
0
1
0
0
0
0
2
0
0
0
0
0
0
0
1
3
0
1
0
0
0
0
0
0
4
1
0
0
0
0
0
0
0
5
0
1
0
0
0
0
0
0
...


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

Advanced Bit
-
Mapped Indexes

Datawarehouse Costs


Technology Costs

Hardware

Storage


Software Costs

Database

ETL

Query tools


People Costs

DBA

Business analysts

Developpers

ETL Extraction

Transformation
Transport

Load

ANALYSIS,
QUERY &
REPORTING
TOOL


•
Business
Objects

•
Brio

•
Cognos

•
Microstrategy

•
...

Any change in schema impacts ETL&Query tools

SYBASE

•
Direct Connect,

•
Replication Server,


PARTNERS’

PRODUCTS

•
Informatica, Genio,
Datastage...

SOURCE DATABASE,

supporting corporate

applications


•
Oracle

•
Informix

•
DB/2

•
Microsoft

•
SybaseASE

USERS


•
Marketing

•
Line of Business

•
Senior Management

•
Help
-
desk

•
Customer support

RDBMS

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



䥮捲I慳攠䕔e 捯浰汥c楴i


Increase query tool complexity


Increase DWH costs

Eurostat : wide table
–

10 Mio rows

Eurostat : Horizontal Partitioning

Eurostat : Vertical Partitioning

Eurostat : In IQ
-
M

Datawarehouse Costs


Technology Costs

Hardware

Storage


Software Costs

Database

ETL

Query tools


People Costs

DBA

Business analysts

Developpers

DBA/Volume Ratio

Source : Gartner Symposium ITxpo 2002


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

FORTIS ROI

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

Operational

systems

End
-
user

Computing

environments

IC
-
Publisher

Accept

Publish

IC
-
Infocentre

Datamarts

Inflow

Outflow

C.I.W.

Functional BI Infrastructure

“
3270
”

Client

MVS

DB2

tables

Focus
-
MVS

T
ime


S
haring
O
ption

MS Office

WebFocus for

Windows

SAS for

Windows

Program

to

program

E
nterprise


D
ata
A
ccess

BI end users environment

“
3270
”

Client

Intranet

MVS

NT

DB2

tables

Seq

Other

RDBMS

tables

Sybase IQ

tables

SAS
-
ds

FTP

Focus
-
MVS

T
ime


S
haring
O
ption

MS Office

WebFocus for

Windows

SAS for

Windows

Business


Objects

Program

to

program

E
nterprise


D
ata
A
ccess

WEB

Focus

SAS

Intranet

E
nterprise


D
ata
A
ccess

WEBI

BI end users environment

Sybase IQ
-
context and some figures

Client

Intranet

MVS

NT

Seq

Sybase IQ

tables

FTP

Business


Objects

SAS for

Windows

MS Office

WebFocus for

Windows

WEBI

WEB

Focus

SAS

Intranet

1.5 TB refreshes 1000 tables / month

0.4
TB
COMPRESSED

for
1440
tables

114.136 connections (~ queries) / month

Fortis ROI


250 NOMAD users moved to BO
-
IQ


250 * 1000
€

= 250,000
€


Mainframe upgrade 2 years ago


㈬㔰〬〰2
€


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

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


㌵3
€


At Fortis ((assume 80,000 adhoc) * 12) * 350
€

= 336,000,000
€


Nobody has such a budget for BI only


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

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)


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

:

<1 sec

57%

1
-
3
s

16%

1
-
3 min

3%

>3min

1%

10
-
60s

12%

3
-
10
s

11%

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



Evolution of Sybase
-
IQ

# of Connects/month and tables
0
50.000
100.000
150.000
200.000
250.000
janv-00
juin-00
janv-01
juin-01
janv-02
juin-02
oct-02
déc-03
Connections
0
500
1000
1500
2000
2500
3000
3500
Available tables
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 :

1.
How to integrate into your current environnement ?

2.
Time to integrate ?

3.
Maintenance efforts estimates(Admin, Support, Training,…) ?

5.
Business reports presented to management