Turkcell Transforms Its Business With Oracle Data Integrator & Exadata

1. TurkcellTransformsItsBusinessWith Oracle Data Integrator & Exadata Gürcan Orhan, Fatih Lütfi Feran September 22, 2010

2. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

3. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

4. AboutTurkcellTechnology Turkcell Technology has more than 15 years of development experience with its solutions applied and proven at leading operators in more than 10 countries. More than 10 years of experience in Turkcell ICT TTECH Center was put into service HC: 255 engineers Focus: Turkcell Group Focus: Turkcell & Telia Sonera Group + Regional Sales HC: 360 engineers 1994 - 2006 2007 2008 2009 Today TTECH company formed with its 44 engineers in TÜBİTAK-MAM Technological Free Zone Focus: Turkcell Focus: Turkcell & Telia Sonera Group HC: 321 engineers

5. Areas of Competency From assisting the operation of network resources to improving business oriented intelligence, TTECH’s experts provide an expanding portfolio of packaged and custom solutions for telecom network operators. Network Services & Enablers SIM Asset & Services Management Mobile Marketing Mobile Internet & Multimedia Business Intelligence & Support Systems

6. Turkcell Technology IMS Group • More than 10 years of BI experience in Telecommunications industry • Designed, Built and Running one of the largest data warehouses in telecom industry • Team of morethan 100 highly talented professionals and consultants • Has a proven record of success in BI operations • Flawless operation, providing data for finance and even for NYSE • Early adopter of the newest BI technologies • Complex Event Processing,Text Mining, etc. • Game changer in DWH industry

7. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

8. What is NODI? Network Operations Data Infrastructure Heterogeneous Environment A DWH Approach • Various Vendors • Combining network inventory, performance, alarms, work orders, customer complaints, configuration and traffic in a historical way • Designed and Built for only Network Operations Division usage Reporting Statistical Methods • Finding correlations and relations between different operational systems and making trend analysis • Online and offline value added reporting • Real-time data warehousing

9. WhyNODI? Intelligent Combinations DecisionSupport Productive Network Planning • Decision Support System in Network Operations eco-system • Lights a way from history to future to manage network better and increase performance • Reporting idle equipments in field Trend BasedAnalysis All-in-oneReporting • Reporting different Network related operational systems • Integrating different kinds of data, determining correlations and relations • Determining networking trends in a timely fashion period

10. NODI Architecture What is Heterogeneous Environment? (Online NODI) EasyForms Merlin Sigos MYSQL Oracle MYSQL Application Integration Application Integration Application Integration MSSQL MSSQL Oracle Toledo Papirus Optima SysLog NG Sigos NOTS OSS file MYSQL MSSQL Sybase ASE daily load for Offline Reporting Offline Reporting Offline Reporting Offline Reporting Oracle Oracle Oracle Oracle Reportmaster Reportmaster Reportmaster Reportmaster

11. NODI Architecture Solution Architecture (Offline NODI) MAXIMO TeMIP Merlin Optima shareplex replication daily extraction daily extraction daily extraction OPERATIONAL DATA STORE (ODS layer) STAGING AREA (Staging layer) data warehouse (DWH Layer) STAGING AREA (Staging layer) data marts (DM Layer)

12. NODI Architecture What is thedifference? PARTY LOCATION ADDRESS EQUIPMENT NETWORK ALARMS CONTRACT SUB-CONTRACT COMPLAINTS RESPONSIBILITY MATERIAL TRANSFER PARTY & PARTY RELATION LOCATION HIERARCHY NETWORK PERFORMANCE WORKORDERS

13. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

14. WhatWeHaveGainedWithNODI • Reducing Network Operations costs • Decreasing alarms and network faults • Faster responses to alarms to improve customer satisfaction • Decreasing network deduction and forecasting network alarms • Supporting Purchase Orders for equipment choices • Answer to which equipment works better with which one • Periodic material requirements • Field and Warehouse based material requirement trend analysis • Network Optimization • Gathering information about complete Network Infrastructure

15. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

16. Best Practices in NODI Modeling of DWH & DM DM ALARM RELATIONSHIP ANALYSIS DM COMPLAINT ANALYSIS DM ALARM ANALYSIS DM FAULT WORKORDER DM MATERIAL TRANSFER DM QUALITY WORK ORDER DM NETWORK PERFORMANCE DWH DIM DATE & TIME DWH DIM RESPONSIBILITY DWH DIM EQUIPMENT DWH DIM LOCATION DWH FCT WORKORDER DWH FCT COMPLAINT HISTORY DWH FCT MATERIAL TRANSFER DWH FCT NETWORK PERFORMANCE DWH FCT NETWORK ALARMS

17. Best Practices in NODI Modeling of other database objects Reverse Engineering Model Extraction Model Database Objects Model Staging Area Model

18. Best Practices in NODI ODI Knowledge Module - Incremental Update (restructured) Standard Incremental Update Methodology Restructured Incremental Update Methodology Create target table Drop flow table Create flow table I$ Delete target table Truncate target table Analyze target table Insert flow into I$ table Recycle previous errors Create Index on flow table Analyze integration table Remove deleted rows from flow table Flag rows for update Update existing rows Flag useless rows Update existing rows Insert new rows Commit transaction Analyze target table Drop flow table Drop flow table (I$) Create flow table (I$) Insert flow into I$ table Flag rows for update Create Unique Index on flow table (I$) Update existing rows Insert new rows Commit transaction Analyze target table Drop flow table ODI KM optimizedfor NODI

19. Best Practices in NODI ODI Knowledge Module - Slowly Changing Dimensions (restructured) Standart Slowly Changing Dimension Methodology Restructured Slowly Changing Dimension Methodology Drop flow table (I$) Create flow table I$ Insert flow into I$ table Create Unique Index on flow table (I$) Analyze integration table (I$) Flag rows for update Flag rows for historization Update existing rows Historize old rows Insert changing and new dimensions Commit transaction Analyze target table Drop flow table (I$) Create target table Truncate target table Delete target table Drop flow table (I$) Create flow table (I$) Analyze target table Insert flow into I$ table Recycle previous errors Analyze integration table Create Index on flow table Flag rows for update Update existing rows Historize old rows Insert changing and new dimensions Commit transaction Analyze target table Drop flow table ODI KM optimizedfor NODI

20. BestPractices in NODI ODI Knowledge Module - Direct Load via DBLink (the new approach) Create target table Faster data load Truncate target table Load data via DBLink Parallelexecution in sourcesystem Analyze target table SupportsmanytablesfromDBlink

21. BestPractices in NODI ODI Knowledge Module – SQL Direct Load (the new approach) Truncate target table Drop target table Create target table Load data direct Analyze target table Faster data load Supports ANSI SQL databases

22. BestPractices in NODI Oracle Implementations to perform faster querying • Range Partitioning • Hash • List • Bitmap Indexing • B-Tree

23. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

24. Data Mining ETL Reengineering Powered by ORACLE

25. Data Mining ETL Reengineering? SAS vs ODI Need For Reengineering • 6 years of development • Different analysts & developers • Continuously changing business • Continuously changing sources How to change ? • Change data mining architecture • Leave SAS as mining engine • Data preparation in Oracle using Oracle Data Integrator • Redesign and Rewrite whole data mining ETL

26. Before Pain Points : Query Performance, Extensibility, ETL Performance Enterprise Datawarehouse Oracle 9i Data Preparation & Mining SAS SAS DWH data transformation SAS Dataset preperation, Score Calculation, Model End User DWH MINER (staging) VIPER (mining) SAS Extraction SASFtp ORACLE SAS Ftp / Remote Table Creation BSCS UDB FCMS SAS Ftp / Remote Table Creation ORACLE Extraction SAS Extraction SP2DB ODS BSCS QDB UDB

27. After Pain Points : Query performance, Extensibility, ETL Performance Enterprise Datawarehouse & Data Marts Oracle 10g Mining SAS SAS ODI Crosstab, Feed, Target SAS ScoreCalculation, Model ODI DWH DATA MARTS MINER ODI SAS Load EndUser Abinitio Graph&Load SAS Ftp / RemoteTable Creation EDWH ETL Abinitio Abinitio Load Abinitio Load Abinitio Extraction Abinitio Load BSCS UDB FCMS Abinitio Extraction ABINITIO Abinitio Extraction AMANOS SP2DB ODS BSCS QDB UDB

28. Results Timely delivery, less system resource usage, flexible refresh DATA PREPARATION 23-27 DAYS DATA PREPARATION 2-3 DAYS

29. Agenda About Turkcell Technology Introduction to NODI Results Obtained with NODI Best Practices in NODI BIS Datamining Exadata Benefits

30. BIArchitecture Pain Points : Query performance, Extensibility, ETL Performance 250 TB CORPORATE DM Analysis Cubes CHURN DM 50000 Query run/Month CAMPAIGN DM VAS DM CALL DM Enterprise Data Warehouse AdHoc Reports DatamartEtl’s TARIFF DM Average Response Time : 23 mins SALES DM INVOICE DM Scorecards Dashboards DATA MINING OTHER DMs Data Mining

31. WhyExadata?

32. Results

33. Data Mining ETL on Exadata 5X 1,5X % 55 Jobs % 20 Jobs % 25 Jobs 2X

34. Data Mining ETL Reengineering Powered by ORACLE 2-3 days ETL run 25 to 27 days ETL run

35. Turkcell Technology Research and DevelopmentTÜBİTAK MAMTeknolojiSerbest Bölgesi Gebze –Kocaeli TURKEY' : +90 (262) 677 40 007 :+90 (262) 677 40 018 : www.turkcelltech.com THANK YOU!