t urkcell t ransforms i ts b usiness with oracle data integrator & exadata gürcan orhan, fatih...
Post on 03-Jan-2016
215 Views
Preview:
TRANSCRIPT
TURKCELL TRANSFORMS ITS
BUSİNESSWith Oracle Data Integrator & ExadataGürcan Orhan, Fatih Lütfi Feran
September 22, 2010
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
Turkcell Technology has more than 15 years of development experience with its solutions applied and proven at leading operators in more than 10 countries.
2009
More than 10 years of experience in Turkcell ICT
TTECH Center was put into serviceHC: 255 engineersFocus: Turkcell Group
Focus: Turkcell & Telia Sonera Group + Regional SalesHC: 360 engineers
TTECH company formed with its 44 engineers in TÜBİTAK-MAM Technological Free ZoneFocus: Turkcell
Focus: Turkcell & Telia Sonera GroupHC: 321 engineers
2008 Today20071994 - 2006
About Turkcell Technology
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 technologiesComplex Event Processing, Text Mining, etc.
Game changer in DWH industry
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
Network Operations Data Infrastructure
What is NODI?
Online and offline value added reporting
Real-time data warehousing
A DWH Approach
Designed and Built for only Network Operations Division usage
Reporting Statistical Methods Finding correlations and relations
between different operational systems and making trend analysis
Heterogeneous Environment
Various Vendors Combining network inventory,
performance, alarms, work orders, customer complaints, configuration and traffic in a historical way
Intelligent Combinations
Why NODI?
Determining networking trends in a timely fashion period
Productive Network Planning
Reporting idle equipments in field
Trend Based Analysis All-in-one Reporting Reporting different Network related
operational systems Integrating different kinds of data,
determining correlations and relations
Decision Support
Decision Support System in Network Operations eco-system
Lights a way from history to future to manage network better and increase performance
MSSQL MSSQL
Oracle OracleOracle
What is Heterogeneous Environment? (Online NODI)
Application Integration
Application Integration
Offline Reporting Offline Reporting
EasyForms Merlin
NOTS OSS
Offline Reporting
Sigos
daily load forOffline Reporting
SysLog NG
MYSQL Oracle
MSSQL Sybase ASEMYSQLfile
Toledo Papirus
Reportmaster
Reportmaster
Reportmaster
Oracle
Application Integration
Sigos
MYSQL
Optima
OracleReportmaste
r
NODI Architecture
data warehouse (DWH Layer)
Solution Architecture (Offline NODI)
shareplex replication daily extraction daily extraction daily extraction
MAXIMO TeMIP Merlin Optima
OPERATIONAL DATA STORE (ODS layer)
STAGING AREA (Staging layer)
data marts (DM Layer)
STAGING AREA (Staging layer)
NODI Architecture
ADDRESS
What is the difference?
NODI Architecture
PARTY
CONTRACTSUB-CONTRACT
LOCATION
EQUIPMENT
PARTY & PARTY
RELATION
NETWORK ALARMS
RESPONSIBILITY COMPLAINTS
MATERIAL TRANSFER
NETWORK PERFORMANCE
WORKORDERS
LOCATION HIERARCHY
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in 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
What We Have Gained With NODI
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
Modeling of DWH & DM
DM ALARM ANALYSIS
DM ALARM RELATIONSHIP ANALYSIS DM COMPLAINT ANALYSIS
DM FAULT WORKORDER
DM MATERIAL TRANSFER
DM NETWORK PERFORMANCE
DM QUALITY WORK ORDER
DWH DIM RESPONSIBILITY
DWH FCT WORKORDER
DWH FCT NETWORK PERFORMANCE
DWH DIM LOCATION
DWH DIM DATE & TIME
DWH DIM EQUIPMENT
DWH FCT COMPLAINT HISTORY
DWH FCT MATERIAL TRANSFER
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
ODI Knowledge Module - Incremental Update (restructured)
Standard Incremental Update Methodology Restructured Incremental Update Methodology
Best Practices in NODI
1. Create target table2. Drop flow table3. Create flow table I$4. Delete target table5. Truncate target table6. Analyze target table7. Insert flow into I$ table8. Recycle previous errors9. Create Index on flow table10. Analyze integration table11. Remove deleted rows from flow table12. Flag rows for update13. Update existing rows14. Flag useless rows15. Update existing rows16. Insert new rows17. Commit transaction18. Analyze target table19. Drop flow table
1. Drop flow table (I$)2. Create flow table (I$)3. Insert flow into I$ table4. Flag rows for update5. Create Unique Index on flow table (I$)6. Update existing rows7. Insert new rows8. Commit transaction9. Analyze target table10. Drop flow table
ODI KM optimized for NODI
Standart Slowly ChangingDimension Methodology
Restructured Slowly ChangingDimension Methodology
Best Practices in NODI
1. Create target table2. Truncate target table3. Delete target table4. Drop flow table (I$)5. Create flow table (I$)6. Analyze target table7. Insert flow into I$ table8. Recycle previous errors9. Analyze integration table10. Create Index on flow table11. Flag rows for update12. Update existing rows13. Historize old rows14. Insert changing and new dimensions15. Commit transaction16. Analyze target table17. Drop flow table
ODI KM optimized for
NODI
ODI Knowledge Module - Slowly Changing Dimensions (restructured)
1. Drop flow table (I$)2. Create flow table I$3. Insert flow into I$ table4. Create Unique Index on flow table (I$)5. Analyze integration table (I$)6. Flag rows for update7. Flag rows for historization8. Update existing rows9. Historize old rows10. Insert changing and new dimensions11. Commit transaction12. Analyze target table13. Drop flow table (I$)
ODI Knowledge Module - Direct Load via DBLink (the new approach)
Best Practices in NODI
Create target table
Truncate target table
Analyze target table
Load data via DBLink
Faster data load
Parallel execution in source system
Supports many tables from DBlink
ODI Knowledge Module – SQL Direct Load (the new approach)
Best Practices in NODI
Truncate target table
Drop target table
Create target table
Load data direct
Analyze target table
Faster data load
Supports ANSI SQL databases
Oracle Implementations to perform faster querying
Best Practices in NODI
Partitioning
Range
Hash
List
Bitmap
B-Tree
Indexing
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
SAS vs ODI
Data Mining ETL Reengineering?
Need For Reengineering
6 years of developmentDifferent analysts & developers Continuously changing businessContinuously changing sources
How to change ?
Change data mining architectureLeave SAS as mining engineData preparation in Oracle using Oracle Data IntegratorRedesign and Rewrite whole data mining ETL
Before
Data Preparation & MiningSAS
Enterprise DatawarehouseOracle 9i
Pain Points : Query Performance, Extensibility, ETL Performance
DWH MINER(staging)
DWH data transformation
SAS Dataset preperation, Score Calculation,
Model
SASExtraction
ORACLEExtraction
BSCSUDBFCMS
BSCS
QDB
ODS
UDB
SP2DB
SASExtraction
SASFtp
VIPER(mining)SAS Ftp /
Remote Table Creation
SAS Ftp /Remote Table Creation
End User
ORACLE
SAS
After
EDWH ETL Abinitio
MiningSAS
Enterprise Datawarehouse & Data MartsOracle 10g
Pain Points : Query performance, Extensibility, ETL Performance
DWH DATAMARTS MINER
BSCS
ODI Crosstab, Feed,
Target
ODI SAS Load
SAS Score Calculation,
Model
AbinitioGraph&Load Abinitio
LoadAbinitioExtraction
AbinitioLoad
AbinitioLoad
AMANOS
UDBFCMS
BSCS
QDB
ODS
UDB
SP2DB
AbinitioExtraction
SAS Ftp /Remote Table
Creation
End User
ODI
ABINITIO
SAS
AbinitioExtraction
Timely delivery, less system resource usage, flexible refresh
Before SAS for ETL coding More than 600 tables ~20.000 Columns 3200 variables
500 jobs
8TB
Monthly refresh
ETL runs almost full month
DATA PREPARATION
23-27 DAYS
Results
AfterOracle Data Integrator361 tables~10.000 Columns 3906 variables
320 ODI Interfaces
5,1 TB
Monthly , weekly, Daily refresh
2-3 days beginning of month
DATA PREPARATION
2-3 DAYS
Agenda
Exadata Benefits
BIS Datamining
Results Obtained with NODI
Introduction to NODI
About Turkcell Technology
Best Practices in NODI
Pain Points : Query performance, Extensibility, ETL Performance
Enterprise Data
Warehouse
VA SD M
C A M PA I G ND M
C H U R ND M
TA R I F FD M
C O R P O R A T E
D M
I N V O I C ED M
S A L E SD M
D ATA M I N I N GO T H E R
D M s
C A L LD M
Analysis Cubes
AdHoc Reports
ScorecardsDashboards
Data Mining
Datamart Etl’s
BI Architecture
250 TB
50000Query
run/Month
Average Response Time : 23
mins
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
Why Exadata?
Performance
• 5 to 400 times ( Average 10 times ) faster query response
Simplified Architecture
• Single sistem• Single Vendor
Size
• 100 TB compressed ( ~250TB uncompressed ) database reduced to 25 TB
Results
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
5X
% 55 Jobs
1,5X
% 20 Jobs
% 25 Jobs
2X
Powered by ORACLE
Data Mining ETL Reengineering
Redesign
Oracle Data Integrator
Exadata
25 to 27 days ETL
run
2-3 days ETL run
top related