TURKCELL TRANSFORMS ITS BUSINESS Grcan Orhan Integrator Fatih

TURKCELL TRANSFORMS ITS BUSINESS Gürcan Orhan, Integrator Fatih Lütfi Feran With Oracle Data & Exadata September 22, 2010

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

About 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 1994 - 2006 Focus: Turkcell & Telia Sonera Group + Regional Sales HC: 360 engineers TTECH Center was put into service HC: 255 engineers Focus: Turkcell Group 2007 2008 TTECH company formed with its 44 engineers in TÜBİTAK-MAM Technological Free Zone Focus: Turkcell 2009 Focus: Turkcell & Telia Sonera Group HC: 321 engineers Today

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

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 more than 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

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

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

Why NODI? Intelligent Combinations Productive Network Planning § Reporting idle equipments in field Trend Based Analysis § Determining networking trends in a timely fashion period Decision Support § Decision Support System in Network Operations ecosystem § Lights a way from history to future to manage network better and increase performance All-in-one Reporting § Reporting different Network related operational systems § Integrating different kinds of data, determining correlations and relations

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

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

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

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

What We Have Gained With NODI 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

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

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 DWH DIM DATE & TIME DM NETWORK PERFORMANCE 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

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

Best Practices in NODI Knowledge Module - Incremental Update (restructured) Standard Incremental Update Methodology Restructured Incremental Update Method 1. Create target table 1. Drop flow table (I$) 2. Drop flow table 2. Create flow table (I$) 3. Create flow table I$ 3. Insert flow into I$ table 4. Delete target table 4. Flag rows for update 5. Truncate target table 5. Create Unique Index on flow table 6. Analyze target table (I$) 7. Insert flow into I$ table 6. Update existing rows 8. Recycle previous errors 7. Insert new rows 9. Create Index on flow table 8. Commit transaction 10. Analyze integration table 9. Analyze target table 11. Remove deleted rows from flow 10. Drop flow table 12. Flag rows for update ODI KM 13. Update existing rows optimized for 14. Flag useless rows 15. Update existing rows NODI 16. Insert new rows 17. Commit transaction 18. Analyze target table 19. Drop flow table

Best Practices in NODI Knowledge Module - Slowly Changing Dimensions (restructured) Standart Slowly Changing Restructured Slowly Changing Dimension Methodology 1. Create target table 2. Truncate target table 3. Delete target table 4. Drop flow table (I$) 5. Create flow table (I$) 6. Analyze target table 7. Insert flow into I$ table 8. Recycle previous errors 9. Analyze integration table 10. Create Index on flow table 11. Flag rows for update 12. Update existing rows 13. Historize old rows 14. Insert changing and new dimensions 15. Commit transaction 16. Analyze target table 17. Drop flow table 1. 2. 3. 4. Drop flow table (I$) Create flow table I$ Insert flow into I$ table Create Unique Index on flow table (I$) 5. Analyze integration table (I$) 6. Flag rows for update 7. Flag rows for historization 8. Update existing rows 9. Historize old rows 10. Insert changing and new dimensions 11. Commit transaction ODI KM 12. Analyze target table optimized for 13. Drop flow table (I$) NODI

Best Practices in NODI Knowledge Module - Direct Load via DBLink (the new approach) Create target table Faster data load Truncate target table Load data via DBLink Parallel execution in source sy Analyze target table Supports many tables from DB

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

Best Practices in NODI Oracle Implementations to perform faster querying § Range Partitioning § Hash § List § Bitmap Indexing § B-Tree

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

Data Mining ETL Reengineering Powered by ORACLE Exadata Oracle Data Integrator Redesign

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

Before Pain Points : Query Performance, Extensibility, ETL Performance SAS Dataset preperation, Score Calculation, Model DWH data transformatio n SAS Extraction DWH SAS Ftp / Remote Table Creation ORACLE Extraction SP 2 D B BSC QD S B MINER (stagin g) SAS Extraction OD S UDB Enterprise Datawarehouse Oracle 9 i SAS Ftp VIPER (mining ) SAS Ftp / Remote Table Creation BSC FCM S UDB S Data Preparation & Mining SAS End User

After Pain Points : Query performance, Extensibility, ETL Performance Enterprise Datawarehouse & Data Marts Oracle 10 g ODI SAS Score Calculation, Model Crosstab, Feed, Target DATA MARTS DWH Abinitio Graph&Load Abinitio Extraction Abinitio Load AMANOS Abinitio Load Abinitio Extraction ODI SAS Load MINER End User SAS Ftp / Remote Table Creation FCM S BSC S UDB Abinitio Extraction SP 2 D B QDB BSC S ODS UDB EDWH ETL Abinitio Mining SAS

Results Timely delivery, less system resource usage, flexible refresh Before SAS for ETL coding More than 600 tables ~20. 000 Columns 3200 variables After Oracle Data Integrator 361 tables ~10. 000 Columns 3906 variables 500 jobs 320 ODI Interfaces 8 TB 5, 1 TB Monthly , weekly, Daily refresh 2 -3 days beginning of DATA month Monthly refresh ETL runs almost full month DATA PREPARATI ON 23 -27 DAYS PREPARATI ON 2 -3 DAYS

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

BI Architecture Pain Points : Query performance, Extensibility, ETL Performance 250 TB CORPORAT E CHURN DM DM CAMPAI GN DM VAS DM Enterpris e Data Warehou se Analysis Cubes 50000 Query C A Lrun/Month L DM Datamart Etl’s Ad. Hoc Reports TARIFF DM INVOICE DM OTHER DMs SALES DM DATA MINING Average Response Scorecards Time : 23 Dashboard mins s Data Mining

Why Exadata? Performance • Data intensive processing runs in Exadata storage • Columnar compression Linear Scalability • Massively parallel storage grid Simplified Architecture • Replace a complex system with many storage units • Single Vendor strategy

Results Performance • 5 to 400 times ( Average 10 times ) faster query response Simplified Architecture • Single sistem • Single Vendor Size • 100 TB compressed ( ~250 TB uncompressed ) database reduced to 25

Data Mining ETL on Exadata improvement level # of steps average % perf. impr. avg duration before avg duration Exadata avg duration improvement GOOD 459 4, 8 X 3802 796 3005 OK 178 1, 4 X 1648 1169 479 NOK 214 2, 1 X 1794 3753 -1958 5 X 1, 5 X % 55 Jobs % 20 Jobs % 25 Jobs 2 X

Data Mining ETL Reengineering Powered by ORACLE 2 -3 days ETL run Exadata 25 to 27 days ETL run Oracle Data Integrator Redesign

Turkcell Technology Research and Development TÜBİTAK MAM Teknoloji Serbest Bölgesi Gebze – Kocaeli TURKEY ' : +90 (262) 677 40 00 7: +90 (262) 677 40 01 8 : www. turkcelltech. com THANK YOU!