1 / 32

BID201: Sybase IQ Data Warehouse

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 Old DW system Requirements Looking for solutions Benchmark Choose vendors Benchmarking Site visit. Implementation

trish
Download Presentation

BID201: Sybase IQ Data Warehouse

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BID201: Sybase IQ Data Warehouse Can Alhas Application Development Mng. Yapi Kredi Technology Calhas@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. 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.

  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 Chose Vendors For Benchmarking (Candidates) Our Main Prequisite was the OS it should have work on Unix (AIX) to keep the previous investments. 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. 3-10s 1-3 s 10-60s 11% 16% 12% 1-3 min 3% >3min 1% <1 sec 57% 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 00:45 – 08:00 • 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

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

  28. Our New Dw System Query Results

  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. MVS FTP Data Propagator SEQUENTIALS CAMPAIGN EDS CDS (DB2) SOURCE TABLES (DB2) UNLOAD FTP UNLOAD SEQUENTIALS FTP SORT SOURCE FILES (VSAM) FTP CAMPAIGN DATA &FEEDBACK PERSONAL DATAFILES 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@ykb.com Engin Tavşanlı Database Administration Mng. Yapi Kredi Technology etavsanli@ykb.com

More Related