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
Enter the password to open this PDF file:
File name:
-
File size:
-
Title:
-
Author:
-
Subject:
-
Keywords:
-
Creation Date:
-
Modification Date:
-
Creator:
-
PDF Producer:
-
PDF Version:
-
Page Count:
-
Preparing document for printing…
0%
Comments 0
Log in to post a comment