BID201: Sybase IQ Data Warehouse

1
BID201 Sybase IQ Data Warehouse

• Can Alhas
• Application Development Mng.
• Yapi Kredi Technology
• Calhas_at_ykb.com
• August 15-19, 2004

2
Overview

• Yapi Kredi Bank
• Why
• Old DW system
• Requirements
• Looking for solutions
• Benchmark
• Choose vendors
• Benchmarking
• Site visit

• Implementation
• Project planning
• How We Implement
• ETL Tool
• Our New DW System
• Technical advantages
• Price advantages
• ROI
• Today
• Questions

3
Yapi Kredi Bank

• Yapi Kredi has over 420 domestic branches and
  various other subsidiaries, affiliated companies
  that are active in leasing, factoring, investment
  banking, insurance, brokerage and new economy
  companies. Complementing its wide domestic
  network, the Bank also maintains an important
  international presence with a subsidiary bank in
  Dusseldorf and Amsterdam, a bank in the Russian
  Federation, an off-shore banking unit in Bahrain
  and four representative offices in Moscow,
  Munich, Cologne and Stuttgart. It's retail
  services rank top in the Turkish marketplace with
  a marketshare greater than 20 percent. Yapi Kredi
  also ranks number one with respect to the number
  of issued cards and respective card business
  volume. It ranks number two among private banks
  with respect to number of available ATM's.

4
Old DW System
Summer- 2003
Old System
Old Target System
IBM S80 Model Server 12 CPU 12 GB RAM Oracle 8i
RDBMS 840 GB Database size 30 Users 2 DBA
2 DataMart 96 Tables Total refresh time 11
days
IBM P690 Model Server 10 CPU 16 GB RAM Oracle 9i
RDBMS 2 TB Database size 100 Users 2 DBA
DataMart Tables Total refresh time 1 day
5
Requirements
Requirements

• Daily refresh
• Changing ETL process design
• Additional functions
• New DataMarts

Continue with existing System ?
Investment need

• H/W investments
• Needs growing in size
• ( New Storage units )
• Needs growing in CPU
• ( HW change )
• S/W Investment
• Additional RDBMS licencing
• Consultancy fee

Looking for Alternative solutions ?
6
Looking for alternative solutions ?

• Must be in same or low total price
• Must be in production in october 2003 ( appr. 3
  months )
• Must fit all requirements
• Must increase query response times
• Must integrate with existing tools systems
• Must Keep the Investments For Previous DW

7
DB Selection For DW
Product Selection Phase
Our Main Prequisite was the OS it should have
work on Unix (AIX) to keep the previous
investments.
Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
We Choosed 4 main DB vendors to investigate one
of them was Sybase IQ
Site Visit
Product Selection
8
DB Selection For DW
Product Selection Phase

• Our Technical Criterions
• Managability
• Concurrency
• Partitioning
• Compression
• Self-tuning memory management
• Administration Tools
• Programming Language
• Performance
• Connectivity
• Resource Usage

Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection and Implementation
9
DB Selection For DW
Product Selection Phase

• Benchmarking
• We have measured followings
• Query Performance
• Storage usage
• ETL times
• Connectivity

Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection and Implementation
When we try to measure these criterias we realize
that
10
DB Selection For DW
Product Selection Phase
ETL times Incremental and Full Refresh
Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection and Implementation
11
DB Selection For DW
Product Selection Phase
Storage usage
Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection and Implementation
12
DB Selection For DW
Product Selection Phase
Query Performance
Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection and Implementation
13
DB Selection For DW
Product Selection Phase
Connectivity
Chose Vendors For Benchmarking (Candidates)
We are successfully using following tools in our
DW and BI enviroments by connecting to Sybase
IQ Reporting Business Objects Data Mining
SAS Campaign Managements In-House OLAP MS SQL
Server
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection
14
DB Selection For DW
Product Selection Phase
Connectivity
Chose Vendors For Benchmarking (Candidates)
We are successfully using following tools in our
DW and BI enviroments by connecting to Sybase
IQ Reporting Business Objects Data Mining
SAS Campaign Managements In-House OLAP MS SQL
Server
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection
15
DB Selection For DW

• 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

Product Selection Phase
Chose Vendors For Benchmarking (Candidates)
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection
Second largest financial services provider in
Benelux
16
DB Selection For DW
Product Selection Phase
We conclude that
Chose Vendors For Benchmarking (Candidates)
Sybase IQ is not a ordenary DB that we used to
use in our OLTP Systems. It is designed
especially for the datawarehouse and datamart
applications. Not for OLTP systems. And it
satisfied all our DW requirments Especially for
Query performance and Storage saving it was much
more better than we expected.
Define new Tehcnical Criterias
Benchmarking
Site Visit
Product Selection
17
Implementation
PROJECT PLAN
18
How We Implement ETL Tool For IQ

• Flowsak is an ETL tool
• Developed using Java
• Consists of about 70000 LOC
• Used in all stages of the DW process, from
  extracting data from MVS-DB2 to loading into
  Unix-IQ
• In YKB DW Migration Project Total of 2159 files
  and 178.000 LOC was generated by using Flowsak

19
How We Implement - Flowsak

• Flowsak is used in the following tasks in DW
• Create DB2 extract scripts and Mainframe JCLs
• Create tables and table indices in IQ DB
• Detect column cardinalities in Sybase IQ DB
• Create views to make transformations
• Create Sybase IQ Procedures
• Flowsak is used by all developers in the project
• It is not necessary for developers to have deep
  knowledge in Unix or Mainframe scripting. All
  scripts are generated by Flowsak

20
How We Implement - Flowsak
21
How We Implement - Flowsak
22
How We Implement - Flowsak

• Metadata database is SQL Server 2000 or Sybase
  ASA
• First, DW project was developed using Oracle,
  then it was transformed into Sybase IQ within 2
  months
• One of the biggest reasons for easy
  transformation is Flowsak, the other one is
  Sybase IQ

23
Daily Refresh

• Daily refresh methodology is used in DW
• Batch window is between 0045 0800
• About 90 of all data in DW is refreshed daily
• The rest is refreshed monthly

24
Sybase IQ Advantages Used

• Created LF, HG or DT indices on every column in
  all tables in DB
• Used views to make transformations
• Used cardinalities for columns in create table
  and create index scripts, resulting in less space
  usage and performance gain
• Very high load and index create performance
• Used JDBC driver to connect from Java

25
Our New Dw System
Autumn - 2003
Prev. Target System
IQ DW Solution
IBM P690 Model Server 10 CPU 16 GB RAM 1 TB
Database size 100 Users 2 DBA DataMart
Tables Refresh period Daily
IBM P690 Model Server 3 CPU 8 GB RAM 300 MB
Database size 100 Users 1 DBA DataMart
Tables Refresh period Daily
26
Our New Dw System
After Conversion
Saving in CPU 60
Saving on disk storage 65
Saving on RDBMS software licences 60
Query Response time 10 times
DBA 50
27
Nucleus Research ROI Case Study
ROI 154 Payback 10 months
28
Our New Dw System
Query Results
Reponse Time Number of Queries
gt 5 min 424 (0.59 )
gt 60-120 sec 502 (0.70 )
gt 30- 60 sec 696 (0.98 )
gt 10 -30 sec 1.197 (1.69 )
gt 1- 10 sec 5.006 (7.07 )
lt 1 sec 62.499 ( 88.37 )
29
DW Environment
Current System
System Model IBM,7040-681 Number Of
Processors 4 ( 1704 MHz) Memory Size 24576
MB AIX 5.2 Adaptive Server IQ 12.5 TOTAL DB
SIZE 1.300TB
30
DW Environment
Current System
of TABLE 1397 of VIEW 648 of
STORED PROCEDURE 887 of COLUMNS 24354
of INDEX 10544 of HG 3986 39
of HNG 155 2 of LF
6183 59 of UNIX SQL
SCRIPT 9566 of USERS 123
TABLE WITH MAX ROW COUNT 2.197.961.237 2
BILLION
31
PERSONAL DATAFILES
CAMPAIGN DATA FEEDBACK
LOTUS DOMINO DB
LEI
LOAD
FLAT FILES (UNIX)
CDS
ODS
DW STG
DW
DM STG
DM
P690 Regatta (UNIX)
PROCESS (ON SYBASE IQ)

PROCESS (ON SYBASE IQ)
OPEN SYSTEMS (Teletel on Oracle etc.)
SAS
UNIX (S80)
CUBES (MS Analysis Server)
32
Thank you
Can Alhas Application Development Mng. Yapi
Kredi Technology calhas_at_ykb.com
Engin Tavsanli Database Administration Mng. Yapi
Kredi Technology etavsanli_at_ykb.com