Mtel Mediation Devices
Download
Report
Transcript Mtel Mediation Devices
All Oracle Features in Action
Mobiltel Mediation Platform
Georgi Hristov
Integration & Service Enabling Department
What is Mediation?
Network Elements
Files transfers
Decodes data that has been encoded by the
NE 1
NE 2
ftp
ftp
...
NE n
ftp
Mediation
ASN.1 into the internal standard format.
ASN.1 Parser
Validate, formatting and conversion data by
Validation
Formatting & Conversion
mapping rule.
Aggregates partial CDR belonging to the same
Aggregation
Correlation
session by the CDR type.
Filtering
Correlates different CDRs belonging to the
Create custom CDR
same session.
Filters CDRs and their fields according to the
systems that need charging data.
Create flexible and wide CDR for Business
BS 1
BS 2
...
BS n
Business Systems
System Software
25.10.2008
Page 2
What is CDR?
Mobile Switch
CDR
(Call Data Record)
IMSI
284011234567890
CALLINGNUMBER
359888654321
CALLINGIMEI
3561270129573530
TIMEDATE
25/10/2008 00:00:23
CALLDURATION
16
CALLEDNUMBER
359888123456
SEQUENCENUMBER
819391
LOCATION
284-01-21900-51991
25.10.2008
Page 3
Old Mediation
■ CDR Files
■ No Database
■ Perl scripts
■ Hard to make statistics
■ Hard to make new features
■ Slow flexibility
25.10.2008
Page 4
Mediation Needs
Flexibility
Performance
Real-time processing
Searching
Statistics
Archive
Guiding
25.10.2008
Page 5
Mediation Platform
Siemens
Ericsson Siemens
msc1 msc2 msc3 msc4 msc5 msc6 msc7 msc8 MSCS IN3
Comverse Ericsson Nortel Nokia
Openmind
IN4 IN_MAC
SMSC
SMSC
SGSN
GGSN
mmsc
CH
TAP
NetCentrex TIBCO Applicata Star@Home Avaya Oberthur Ferma Siemens
SoftSwitch
Events
GreenLine
eSMS
MtelContact
STK
VoMS
MSP
Siemens Kapsch
MSM
JNX
ARCH File
Archive Server
MEDIKU File Transfer Server
MED ORACLE SERVER
HOME FRAUD
MNP RefDB
SWAT
MEDARCH
MAMMUT GUI
MEDIKU File Transfer Server
STRUMA
Siemens
HLR1
Kapsch
Comverse
JNX
VoiceMail
HLR2 HLR3 HLR4 HLR5
CDR DB
DIBONA
LEAP
ROBIGA
FMS
ESSB
MACH DMZ File Server
MAC PRIMA
DOTI
MTEL.BG
INTRANET
DWH
WEBMEDIA
MAC DWH
MAC FMS
Interconnect
PRIMA CRM
MAC ASMP
25.10.2008
MAC CRM
Page 6
Mediation Platform in Numbers
60M Input CDRs per Day
120M Output CDRs per Day
More than 5M subscribers
More than 200 interfaces
Call Durations and Call Count
45 days History
More than 600GB Table space
25.10.2008
Page 7
Mediation Platform
Indexing
Partitions
MNP
Statistics
Mediation
Compression
Fast access
REG_EXP
Logic Into Database
Oracle GRID Control Monitoring
SWAT
Oracle GRID Control Managing
Oracle Clustering
25.10.2008
Page 8
CDR Database
CDR
Repository
Storage
Indexing
Partitions
Compression
64 Billion records;
All CDRs since 1998
15 TB uncompressed
4.3 TB compresses 65% speedup
< 1 sec to find a call
Processing
25.10.2008
Page 9
Box Services (HomeBox VoiceBox OfficeBox)
Guiding
Processing
Fraud Preventing
25.10.2008
Page 10
HomeBox Architecture
25.10.2008
Page 11
Home Fraud
25.10.2008
Page 12
SWAT is not
Subscribers Wild Attribute Transformations
Subscriber
Info
Real-Time Processing
Subscribers information
Real-Time Guiding
CDR
Guiding
Guided CDR
Always in Memory
*Oracle Coherence, Oracle TimesTen
25.10.2008
Page 13
Oracle Standard useful features used
in Mediation Platform
Lag
SELECT last_name, hire_date, salary,LAG(salary, 1, 0) OVER (ORDER BY
hire_date) AS PREV_SAL FROM employees
Partition by
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees
XML object
Nested tables
Partition Management
Interval Partitioning
25.10.2008
Page 14
Example (1)
select imschargingidentifier,
accountingrecordtype calltype,eventtimestamp,
LAG(eventtimestamp) OVER (Partition by imschargingidentifier ORDER BY eventtimestamp) b_time
,LAG(call_type) OVER (Partition by imschargingidentifier ORDER BY eventtimestamp) call_type,
max(calledpartyoriginaladdress) OVER (Partition by imschargingidentifier) calledpartyoriginaladdress,
max(subscriptioniddata) OVER (Partition by imschargingidentifier) subscriptioniddata
from
(select * from
( select
i.eventtimestamp,i.imschargingidentifier,i.accountingrecordtype,
case when (instr(sdpmedianame,'m=video')>0) then 'V'
when (instr(sdpmedianame,'m=audio')>0) then 'A'
else '_' end call_type,
i.causecode,i.calledpartyoriginaladdress,i.originhost,i.serverpartyipaddress,
i.callingpartyaddress,i.subscriptioniddata,i.destinationrealm FROM ims_table_records I WHERE FILE_ID = 7
AND ((i.accountingrecordtype = 2 and i.roleofnode = 0) or
(i.accountingrecordtype in (3,4) and (i.roleofnode =0 or i.roleofnode is null)))
and imschargingidentifier in
(select imschargingidentifier from (
select count(imschargingidentifier),imschargingidentifier from ims_table_records
where FILE_ID = 7
group by imschargingidentifier
having count(imschargingidentifier) >1 ))
)x where not (accountingrecordtype =3 and call_type = '_'))
SCHARGINGIDENTIFIER
call
EVENTTIMESTAMP
1
"3f07db0902fbd60866df283952556"
2
2008-04-18 12:37:12
2
"3f07db0902fbd60866df283952556"
4
2008-04-18 12:37:55
3
"47af208f0335200866de8c7350dc4"
2
2008-04-18 12:26:36
4
"47af208f0335200866de8c7350dc4"
4
2008-04-18 12:27:19
B_TIME
2008-04-18 12:37:12
2008-04-18 12:26:36
25.10.2008
call
A
A
CalledNumber
CallingNumber
sip:[email protected]
359885623924
sip:[email protected]
359885623924
sip:[email protected].
359885623924
sip:[email protected].
359885623924
Page 15
Example (2)
select
a.chargingID,a.servedIMSI,a.servedMSISDN, a.AccessPointNameNI
,a.recordOpeningTime start_rectime
,nvl(extractValue(value(s1), '/ContentTypeIDSequence/ContentTypeID'),'102') ContentTypeID
,nvl(extractValue(value(s1), '/ContentTypeIDSequence/UplinkCount'), gx.dataVolumeGPRSUplink) Up
,nvl(extractValue(value(s1), '/ContentTypeIDSequence/DownlinkCount'),gx.dataVolumeGPRSDownlink) Down
,file_id,a.localsequencenumber
,min(mn.column_value) over (partition by a.localsequencenumber) sgsnaddress
from ipc_table_ggsn a
,table(XMLSequence(
extract(a.RECORDEXTENSIONS,
'/ExtensionXML/ExtensionSequence/informationSequence/ContainerSequence/ContentTypeIDSequence')))(+) s1
,table(a.listoftrafficvolumes) gx,table(a.sgsnaddress) mn
25.10.2008
Page 16
What’s New
HP Oracle Exadata Storage Server
25.10.2008
Page 17
25.10.2008
Page 18