Title Arial 28pt - PowerPoint by lonyoo

VIEWS: 21 PAGES: 32

									BID201: Sybase IQ Data Warehouse

Can Alhas Application Development Mng. Yapi Kredi Technology Calhas@ykb.com August 15-19, 2004

Overview
 

Yapi Kredi Bank Why
  

•Implementation
•Project planning •How We Implement •ETL Tool

Old DW system Requirements Looking for solutions

•Our New DW System


Benchmark
  

Choose vendors Benchmarking Site visit

•Technical advantages •Price advantages

•ROI •Today •Questions

Yapı 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.

Old DW System
Summer- 2003

Old System
IBM S80 Model Server 12 CPU 12 GB RAM Oracle 8i RDBMS 840 GB Database size

Old Target System
IBM P690 Model Server 10 CPU 16 GB RAM Oracle 9i RDBMS 2 TB Database size

30 Users 2 DBA
2 DataMart 96 Tables Total refresh time : 11 days

100 Users 2 DBA
+ DataMart + Tables Total refresh time : 1 day

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 ?

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

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

Our Main Prequisite was the OS it should have work on Unix (AIX) to keep the previous investments. We Choosed 4 main DB vendors to investigate; one of them was Sybase IQ

Benchmarking

Site Visit

Product Selection

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

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

Benchmarking

Site Visit

Product Selection and Implementation

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

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

Benchmarking

Site Visit

Product Selection and Implementation

When we try to measure these criterias we realize that

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias
Time(Second)

ETL times : Incremental and Full Refresh

Increm ental Refresh Full Refresh 4500 140 4000 3500

120

Minutes

Benchmarking

100 3000 80 2500
2000 60 1500 40 1000 500

Site Visit

20 0 0 DB-A DB-A DB-B DB-B DB DB IQ IQ

Product Selection and Implementation

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias
30000 25000 20000

Storage usage :

DB Size

MB

Benchmarking

15000 10000

Site Visit

5000 0 DB-A DB-B DB IQ

Product Selection and Implementation

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

Query Performance :

Benchmarking

Site Visit

Product Selection and Implementation

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

Connectivity : We are successfully using following tools in our DW and BI enviroments by connecting to Sybase IQ

Benchmarking

Site Visit

Reporting: Business Objects Data Mining: SAS Campaign Managements : In-House OLAP: MS SQL Server

Product Selection

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

Connectivity : We are successfully using following tools in our DW and BI enviroments by connecting to Sybase IQ

Benchmarking

Site Visit

Reporting: Business Objects Data Mining: SAS Campaign Managements : In-House OLAP: MS SQL Server

Product Selection

DB Selection For DW
Product Selection Phase
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

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 :

Benchmarking

1-3 s 16%

3-10s 11% 10-60s 12%
1-3 min 3% >3min 1%
Second largest financial services provider in Benelux

Site Visit

Product Selection

<1 sec 57%

DB Selection For DW
Product Selection Phase We conclude that
Chose Vendors For Benchmarking (Candidates) Define new Tehcnical Criterias

Sybase IQ is not a ordenary DB that we used to use in our OLTP Systems.

Benchmarking

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.

Site Visit

Product Selection

Implementation
PROJECT PLAN

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

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

How We Implement - Flowsak

How We Implement - Flowsak

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

Daily Refresh
   

Daily refresh methodology is used in DW Batch window is between 00:45 – 08:00 About %90 of all data in DW is refreshed daily The rest is refreshed monthly

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

Our New Dw System
Autumn - 2003

Prev. Target System
IBM P690 Model Server 10 CPU 16 GB RAM 1 TB Database size 100 Users 2 DBA + DataMart + Tables Refresh period : Daily

IQ DW Solution
IBM P690 Model Server 3 CPU 8 GB RAM 300 MB Database size 100 Users 1 DBA + DataMart + Tables Refresh period : Daily

Our New Dw System
After Conversion
Saving in CPU Saving on disk storage Saving on RDBMS software licences Query Response time DBA 60% 65% 60% 10 times 50%

Nucleus Research – ROI Case Study
ROI: 154% Payback: 10 months

Our New Dw System
Query Results Reponse Time Number of Queries
> 5 min > 60-120 sec 424 502

%
(0.59 %) (0.70 %)

> 30- 60 sec
> 10 -30 sec > 1- 10 sec < 1 sec

696
1.197 5.006 62.499

(0.98 %)
(1.69 %) (7.07 %) ( 88.37 %)

DW Environment
Current System
System Model: IBM,7040-681
Number Of Processors: 4 Memory Size: 24576 MB AIX 5.2 Adaptive Server IQ 12.5 ( 1704 MHz)

TOTAL DB SIZE : 1.300TB

DW Environment
Current System # of TABLE
# of VIEW
# of STORED PROCEDURE # of COLUMNS # of INDEX # of HG # of HNG # of LF

: 1397
: 648
: 887 : 24354 : 10544 : 3986 %39 : 155 %2 : 6183 %59

TABLE WITH MAX ROW COUNT 2.197.961.237 [2 BILLION]

# of UNIX & SQL SCRIPT # of USERS

: 9566 : 123

MVS
Data Propagator
SEQUENTIALS

CAMPAIGN DATA & FEEDBACK

PERSONAL DATA FILES

FTP

CAMPAIGN
LOAD

EDS

(DB2)

CDS

LOTUS DOMINO DB

SOURCE TABLES
(DB2)

LEI
FTP
FLAT FILES (UNIX)

UNLOAD

CDS

ODS

UNLOAD

DW STG

DW

DM STG

DM

P690 Regatta (UNIX)

SEQUENTIALS

PROCESS (ON SYBASE IQ)
FTP

SOURCE FILES SORT
(VSAM)

FTP

PROCESS (ON SYBASE IQ)
OPEN SYSTEMS (Teletel on Oracle etc.)

SAS
CUBES (MS Analysis Server)

UNIX (S80)

Thank you

Can Alhas Application Development Mng. Yapi Kredi Technology calhas@ykb.com Engin Tavşanlı Database Administration Mng. Yapi Kredi Technology etavsanli@ykb.com


								
To top