Transcript OOW 2010 Presentation - Gurcan Orhan`s Oracle Data Integrator Blog

TURKCELL TRANSFORMS ITS BUSINESS
With Oracle Data Integrator & Exadata
Gürcan Orhan, Fatih Lütfi Feran
September 22, 2010
Agenda
About Turkcell Technology
Introduction to NODI
Results Obtained with NODI
Best Practices in NODI
BIS Datamining
Exadata Benefits
Agenda
About Turkcell Technology
Introduction to NODI
Results Obtained with NODI
Best Practices in NODI
BIS Datamining
Exadata Benefits
About Turkcell Technology
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
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
Focus: Turkcell &
Telia Sonera Group +
Regional Sales
HC: 360 engineers
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
Heterogeneous Environment
 Designed and Built for only
Network Operations Division
usage
 Various Vendors
 Combining network inventory,
performance, alarms, work orders,
customer complaints, configuration
and traffic in a historical way
Reporting
Statistical Methods
 Online and offline value added
reporting
 Real-time data warehousing
 Finding correlations and relations
between different operational
systems and making trend analysis
Why NODI?
Intelligent Combinations
Productive Network Planning
Decision Support
 Reporting idle equipments in
field
 Decision Support System in
Network Operations eco-system
 Lights a way from history to future
to manage network better and
increase performance
Trend Based Analysis
All-in-one Reporting
 Determining networking trends in
a timely fashion period
 Reporting different Network related
operational systems
 Integrating different kinds of data,
determining correlations and
relations
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
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)
NODI Architecture
What is the difference?
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
ODI Knowledge Module - Incremental Update (restructured)
Standard Incremental Update Methodology
1. Create target table
2. Drop flow table
3. Create flow table I$
4. Delete target table
5. Truncate target table
6. Analyze target table
7. Insert flow into I$ table
8. Recycle previous errors
9. Create Index on flow table
10. Analyze integration table
11. Remove deleted rows from flow table
12. Flag rows for update
13. Update existing rows
14. Flag useless rows
15. Update existing rows
16. Insert new rows
17. Commit transaction
18. Analyze target table
19. Drop flow table
Restructured Incremental Update Methodology
1. Drop flow table (I$)
2. Create flow table (I$)
3. Insert flow into I$ table
4. Flag rows for update
5. Create Unique Index on flow table (I$)
6. Update existing rows
7. Insert new rows
8. Commit transaction
9. Analyze target table
10. Drop flow table
ODI KM optimized
for NODI
Best Practices in NODI
ODI Knowledge Module - Slowly Changing Dimensions (restructured)
Standart Slowly Changing
Dimension Methodology
Restructured Slowly Changing
Dimension Methodology
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
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$)
ODI KM
optimized for
NODI
Best Practices 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
Parallel execution in source system
Analyze target table
Supports many tables from DBlink
Best Practices in NODI
ODI 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
transformation
SAS
Extraction
SAS Ftp /
Remote Table Creation
ORACLE
Extraction
Enterprise Datawarehouse
Oracle 9i
SAS
Extraction
SAS
Ftp
SAS Ftp /
Remote Table Creation
Data Preparation & Mining
SAS
End User
After
Pain Points : Query performance, Extensibility, ETL Performance
Enterprise Datawarehouse & Data Marts
Oracle 10g
ODI
Crosstab, Feed,
Target
SAS Score
Calculation,
Model
ODI
SAS Load
End User
Abinitio
Graph&Load
Abinitio
Extraction
Abinitio
Load
Abinitio
Load
Abinitio
Extraction
SAS Ftp /
Remote Table
Creation
Abinitio
Load
Abinitio
Extraction
EDWH ETL
Abinitio
Mining
SAS
Results
Timely delivery, less system resource usage, flexible refresh
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
8TB
5,1 TB
Monthly refresh
Monthly , weekly, Daily refresh
ETL runs almost full month
2-3 days beginning of month
Before
DATA
PREPARATION
23-27 DAYS
DATA
PREPARATION
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
Analysis
Cubes
250 TB
50000
Query
run/Month
Datamart Etl’s
AdHoc
Reports
Average
Response
Time : 23 Scorecards
Dashboards
mins
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 ( ~250TB uncompressed )
database reduced to 25 TB
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
1,5X
% 55
Jobs
% 20
Jobs
% 25
Jobs
2X
Data Mining ETL Reengineering
Powered by ORACLE
2-3 days
ETL run
Exadata
Oracle Data
Integrator
25 to 27
days ETL
run
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!