1 / 40

Data Warehouse Governance and Simplification with Informatica

Data Warehouse Governance and Simplification with Informatica. Yapi Kredi Bank Presented by Ahmet Vefa Erdem 14.05.2014. Agenda. About Yapı Kredi Bank Business Problem/ IT Challenge Selecting Data Integration Technology Solutions in Yapı Kredi Future Plans and Vision. 2 - 40.

zoie
Download Presentation

Data Warehouse Governance and Simplification with Informatica

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. Data WarehouseGovernanceandSimplificationwithInformatica Yapi Kredi Bank Presented by Ahmet Vefa Erdem 14.05.2014

  2. Agenda About Yapı Kredi Bank Business Problem/ IT Challenge Selecting Data Integration Technology Solutions in Yapı Kredi FuturePlansandVision 2- 40

  3. AboutYapı Kredi • Establishedin 1944, firstretailfocusedprivate bank of Turkey • #4 largestprivate bank with 952 branches, 2,920 ATMs, 17,000 employees • # 1 :Largest creditcardbasewith 10M cards, # 1 : Largest mobile bankingusers • The first computer user in Turkish Banking Sector(1967) • The first Phone Banking Application (1991) • The first Mobile POS Application in Turkey (2002) • First Mobile Banking Applications (2010) • Subsidaries: Netherlands, Russia, Azerbaijan • Stakeholders: Koç Holding andUniCredit 3- 40

  4. Agenda About Yapı Kredi Business Problem/ IT Challenge Selecting Data Integration Technology Solutions in Yapı Kredi FuturePlansandVision 4- 40

  5. Developing Data Warehouse in YapıKredi Continousimprovementin BI andData Warehouse at YapiKredi SelectingandImplement Data Integration Platform ReportingforBranches DW Assessmnt Project New CoreBankingSystem Mergerbetween YapıKredi andKocBank DW Transform Project 5- 40 Data Mining, CustomerSegment DWH CampaignMangment DWH 2012 2013 1999 2003 2011 2002 2006 2010 2001

  6. BigChangesareBigChallengesfor Data Warehouse Bank Merger (Koçbank & YapıKredi ) New CoreBankingSystem New CreditCardSystem New Credit Risk Underwriting New TreasurySystem New Collection System 6- 40

  7. Data Warehouse Life Story in 15 years Business Intelligence Others CoreBanking CreditCard ATM Internet Banking Analytics Reporting Data Mining (SAS) MIS Profitability Fraud Management ODS Merchant Reporting Budgeting & Planning ETL ETL CreditCard Cartography Basel 2 (SAS) Data Warehouse CreditCardBranchReporting CreditScoring (SAS) Presentation DataMarts Operations Dashboard (Opmis) Marketing Optimisation (SAS) Profitability DMart CRM DMart FraudDMart BranchReporting Center Customer Data Quality (SAS) Cube CampaignDMart CreditCardDMarts CRM Campaign Management (Chordiant) PotentialCustomer Management IndividualBankingCRM Corpotate Commercial BankingCRM Opportunity Management Cust.Visit Management PrivateBankingCRM 7- 40

  8. SizingandUsageLoadFigures of Data Warehouse Enterprise BI, Reporting #of processedrows/day 90 billions #of TPS (Trans/sec) 120 #of users 5,000 #of tables 17,000 #of query/day 27,000 CoreBanking & CRM Applications Replication ODS (20TB) Source1 DWH (75TB) Dmarts (9TB) Extraction Source2 ELT ELT Db Link Source.. #of ETL jobs 15,000 #of query/day 90 millions Size of accumulated data/day 8TB 8- 40

  9. Problemsknockedon thedoorfor Data Warehouse! • Lowerusersatisfaction • Lowerperformance • High development & maintenancecosts • Poor Integration capability • Poorscalability • Compatibility problemswith 3rd parties • Data qualityissues 9- 40

  10. Diagnose & Cure: 2011 : Data WarehouseAssessmentStudy Assessmentstudyfindingsareabout; • Data & processgovernance • Data integration ( ETL ) • Data model • Architecture • Data quality • Metadatamanagement • Created5 years program toevolveData Warehouseanditsperipherals 10- 40

  11. Agenda About Yapı Kredi Business Problem/ IT Challenge Selecting Data Integration Technology Solutions in Yapı Kredi FuturePlansandVision 11- 40

  12. Selecting Data Integration Technology • GOAL : • Select an integrationand data managementtoolfor data warehouseanditseco-systemstosolvemajor data managementproblemsadressed at theassessmentstudy • Thenintegrateandextend it intotheotherdomainstoreinforceenterprise data integrationandgovernancepolicies. 12- 40

  13. Selecting Data Integration Technology • Weevaluatedworldwideleading Technologies in Data Integration and Data Management Solutions with; • Informatica • IBM Data Stage • Oracle ODI • Abinitio • SAP Business Object Data Services • Evaluation teamwasestablishedfrom ; • DW Development Team • DW Administation Team • DW ETL Operation Team • Enterprise Data Architecture Team 13- 40

  14. Selecting Data Integration Technology • FunctionalRequirementswere; • Data Integration Tool ( ETL ) • Metadata Management • Data Archivingand Data Federation ( ILM ) • Test Data Management ( TDM ) • Data Profilingand Data Quality • Big Data Integration Capability • Intregrationandcompatibilityrequirementswere; • Compatibility withSybase IQ andOracleExadata • AbilitytointegratewithPower Designer Data ModellingTool • Abilitytomakedata lineageanalysisfromsourcetableto Business Objects Reports 14- 40

  15. Selecting Data Integration Technology After 6 monthsevaluationweselected : 15- 40

  16. Using Informatica Data Integration(ETL) Data Archiving Power Center Metadata Manager Governance Metadata Manager Data Virtualization Information Lifecycle Management Data Subset Data Profiler Data Masking Test Data Management 16- 40

  17. Agenda About Yapı Kredi Business Problem/ IT Challenge Selecting Data Integration Technology Solutions in Yapı Kredi FuturePlansandVision 17- 40

  18. ArchitectureDefiningBaseline Architecture of Informatica • ConsultancyforBaselineArchiteturefrom • Power Center Configuration • Project Design Methodology • NamingConvention • Best practices • TipsandtrickswithusingPower Center 18- 40

  19. ArchitectureInformatica Architecture of YapıKredi Data Warehouse Dev. Team DW MIS Dev. Team HR IT Dev. Team KOMTAŞ Dev. Team Test Management Team Power Center+ Metadata Manager+ ILM ODS Data Marts IBM Power7, AIX 16 cores CoreBanking Archives, FlatFiles Metadatadatabase CreditCard XMLs, FlatFiles Informatica Test Data Manager 19- 40

  20. ArchitectureInformatica Components in Data Warehouse Enterprise BI, Reporting InformaticaMetadata Manager, Informatica Data Services CRM Applications Replication Source1 ODS Metadata Manager DWH DMart Extraction Source2 ETL ETL DMart Db Link Sourcen DMart Archives TestData InformaticaPower Exchange 20- 40

  21. ETL Development DefiningMethodsandStandarts • Consultancyfor ETL Development Best Practicesfrom KOMTAŞ* • ETL developmentlifecyclestandarts • Documentationstandarts • Deployment methodsandstandarts • Security andadministrationstandarts • Development bestpractices • Performancetuningtips * is thedistributorandleadersolutionprovider of Informatica in Turkey 21- 40

  22. ETL DevelopmentProductivity withInformaticaPower Center • Wehavereached a certainlevelof ETL developmentexperiencessofar. • Numberof Project : 20 • Number of Developer : 55 • Number of Project Team :4 • Number of ETL Mapping : 1958 • Number of Workflow : 1428 22- 40

  23. Conventional ETL Design 1 2 Define What You Want Automatically Generate Dataflow ETL Development - Benefits from UsingInformatica • ConventionalHand coded ETL • Mustdefine every step of Complex ETL Flow • Requiresspecialized ETL skills • Significantdevelopment and maintenance efforts Declarative Design • Declarative Set-based DesignwithInformatica • SimplifiesETL developmentprocess • Significantly reduce the learning curve • Shorter implementationduration • Enforcement of best practicesandstandards • ProvidesData lineage and impact analysis • Doesn’trequirespecializedlowlevelprogrammingskills DefineHow: Built-in Templates 23- 40

  24. ETL Development - Benefits from UsingInformatica • ConventionalHand coded ETL • Difficulttoorganize of a large number of scripts • Developerwas not aware of a similar function existing already • There wasan aversion to change and therefore new data sets and jobs are added rather than risk refactoring the existing jobs • Declarative Set-based DesignwithInformatica • Automatically generates the Data Flow whatever the sources and target DB • Reducemaintenanceefforts • Providesfullfunctionality of ETL Automation (ETL, DQ, ILM, TDM, MM ) 24- 40

  25. ETL Development - Benefits from Using Informatica Old ETL JobsperTable JobReduction Source Table Extract FTP Load Transform Validate TargetTable • Decrease in UC4 jobs • 4,160 Jobsweredeleted in UC4 jobscheduler ( 1040 ODS tables * 4 jobsaving) • increaseoperationalefficiency New ETL JobperTable InformaticaMapping 25- 40

  26. Test Data Management withInformatica TDMBeforeInformatica IBM Optimwasusedas Enterprise Test Management Tool Db1 Db1 Db2 Db2 Db..n Db..n But it could not be usedfor DW because of incompatibilitywithSybase IQ OperationalSystems (Live) OperationalSystems ( Test & Dev ) For DWH, databaseadministratorswerepreparing test data DWH Live DWH Test & Dev 26- 40 Adminfor Test Data Preparation

  27. Test Data Management withInformatica TDMAfterInformatica ( for DWH Test Management) Db1 Db1 Db2 Db2 Db..n Db..n Informatica TDM wasselectedby Data Warehouse Team because of itsrichfunctionality, highperformanceandhighcompatibilitywithSybase IQ OperationalSystems (Live) OperationalSystems ( Test & Dev ) DWH Dev Test Data Subset Persistent Data Masking DWH Live DWH Test 27- 40

  28. Test Data Management withInformatica TDM AfterInformatica ( for Enterprise ) ReplacedIBM OptimwithInformaticaTDM. InformaticaTDM became «Enterprise Test Data Management Solution» for Yapı Kredi Db1 Db1 Db2 Db..n Db2 Test Data Subset Persistent Data Masking OperationalSystems (Live) Db..n OperationalSystems ( Test & Dev ) DWH Dev DWH Live DWH Test 28- 40

  29. Using Informatica Data Archivingand Data Virtualization • Inlast 3 years, eachyear data size extendedbetween %25-40 in Data Warehouse • High cost, because of usinghigh-endexpensivestoragesystemsfor Data Warehouse. • Wedecidedtouse data archivingtokeepundercontrol size andcost. • Wearchived 10TB historical data from DW data in 2013 • Now, userscan accessthe data withusingInformaticaDataVirtualization • Planing ~10TB archive in 2014 RDBMS CompressedInformatica Archive File 29- 40

  30. InformaticaMetadata Manager in Enterprise Architecture End-to-endimpactanalysis BI / Reporting Business Objects Db1 ODS Metadata Manager Metadata Manager Db2 DWH OperationalSystems 30K Tables 17K Tables Db..n DMarts ReportingMetadata 80K Reports (12K Active) DataWarehouseMetadata Source SystemsMetadata 30- 40

  31. Agenda About Yapı Kredi Business Problem/ IT Challenge Selecting Data Integration Technology Solutions FuturePlansandVision 31- 40

  32. Vision • Havingefficient, flexible, accurateandsimpler Data Warehousesolution • Using Big Data technologiesandhighperformanceanalyticsmethods in newareas. • Empowerandgeneralize Data GovernancepoliciesintothecompanybyusingInformatica. • UseInformatica platform as enterprise-wide data integrationand data managementsolution. 32- 40

  33. Plan :Evolve Data Warehouse DW AssessmentStudy Data MiningAssessment DW Re-structuring Plan SybaseAssesment ChangeOrganization GiveTrainings : TDWI , Power Designer, Informatica, SQL Change DW Development Lifecycle Define & give domain architect role Select ETL Tool:Informatica ETL : Start usingInformatica PC Select new DW Platform Start Using Exadatafor ODS & Datamarts Upgrade DW: Sybase IQ 15.4 TDM: Start usingInformatica TDM ILM: Start usingInformatica ILM DW Re-engineeringproject Create as-is data models in Power Center Simplify DW Structure IntegratePower Designer /Informatica MM / Deployment tool Db Design : Start usingPower Designer Start Using InformaticaMetadata Manager 33- 40

  34. Data WarehouseSimplification • Data Warehouse Re-Engineering • Platform Change ( Depends on POC results ) • Model Simplification • Decreasenumber of tables • Database consolidationbetween ODS + DW + Data Marts • Near-realtime Data Warehouse • Re-design ETL jobswithInformatica ( 15,000 jobs ) 34- 40

  35. As-is Data Warehouse Business Intelligence Others CoreBanking Internet Banking ATM CreditCard Analytics OperationalSystems Reporting Data Mining (SAS) MIS Profitability Fraud Management Operational Data Store ODS Merchant Reporting Budgeting & Planning ETL ETL ETL CreditCard Cartography LogicalDataWarehouse Basel 2 (SAS) Data Warehouse CreditCardBranchReporting CreditScoring (SAS) Operations Dashboard (Opmis) Marketing Optimisation (SAS) Profitability DMart FraudDMart D.MiningDMart Presentation DataMarts BranchReporting Center Customer Data Quality (SAS) CRM DMart CampaignDMart CreditCardDMarts CRM Campaign Management (Chordiant) PotentialCustomer Management IndividualBankingCRM Corpotate Commercial BankingCRM Opportunity Management Cust.Visit Management PrivateBankingCRM 35- 40

  36. To-be Data Warehouse Architecture Business Intelligence Others CoreBanking Internet Banking ATM CreditCard Analytics OperationalSystems Reporting Data Mining (SAS) MIS Profitability Fraud Management LogicalDataWarehouse ODS Merchant Reporting Budgeting & Planning Informatica Informatica CreditCard Cartography Basel 2 (SAS) Data Warehouse Informatica CreditCardBranchReporting CreditScoring (SAS) Operations Dashboard (Opmis) Marketing Optimisation (SAS) Profitability DMart FraudDMart D.MiningDMart BranchReporting Center Customer Data Quality (SAS) CRM DMart CampaignDMart CreditCardDMarts CRM Campaign Management (Chordiant) PotentialCustomer Management IndividualBankingCRM Corpotate Commercial BankingCRM Opportunity Management Cust.Visit Management PrivateBankingCRM 36- 40

  37. Data WarehouseSimplification • Business Intelligence Re-Engineering • Upgrade andmigrate Business Objects from BOXI 3.1 into BO 4.0 • Universeconsolidation ( Morethan 200 Universes ) • Report consolidation (12K activereportsamong 80K reports ) 37- 40

  38. Big Data • Weare in thelearningphase • Planning tobecomeBig Data Enabledcompany • StartingestablishHadoopplatform • Planinngto define usecases in ; • IT of things • Social CRM • N-Pathanalysisforcustomerchurn • Risk management 38- 40

  39. Social CRM • Yapi Kredi is one of themostactivebanks in thesocialmedia in thecountry • DefiningSocial CRM strategies • Working on CustomerMatchingSystem • Planning ComplexEventProcessingfor CRM • PlaningLocationBased Services 39- 40

  40. Ahmet Vefa Erdem DataWarehouse & Data Mining Development Manager Yapi Kredi Bank vefa.erdem@yapikredi.com.tr 40- 40

More Related