Mtel Mediation Devices

1
All Oracle Features in Action Mobiltel Mediation
Platform
Georgi Hristov Integration Service Enabling
Department
2
What is Mediation?

• Files transfers
• Decodes data that has been encoded by the ASN.1
  into the internal standard format.
• Validate, formatting and conversion data by
  mapping rule.
• Aggregates partial CDR belonging to the same
  session by the CDR type.
• Correlates different CDRs belonging to the same
  session.
• Filters CDRs and their fields according to the
  systems that need charging data.
• Create flexible and wide CDR for Business System
  Software

3
What is CDR?
IMSI 284011234567890
CALLINGNUMBER 359888654321
CALLINGIMEI 3561270129573530
TIMEDATE 25/10/2008 000023
CALLDURATION 16
CALLEDNUMBER 359888123456
SEQUENCENUMBER 819391
LOCATION 284-01-21900-51991
4
Old Mediation

• CDR Files
• No Database
• Perl scripts
• Hard to make statistics
• Hard to make new features
• Slow flexibility

5
Mediation Needs

• Flexibility
• Performance
• Real-time processing
• Searching
• Statistics
• Archive
• Guiding

6
Mediation Platform
7
Mediation Platform in Numbers

• 60M Input CDRs per Day
• 120M Output CDRs per Day
• More than 5M subscribers
• More than 200 interfaces
• 45 days History
• More than 600GB Table space

Call Durations and Call Count
8
Mediation Platform

• Indexing
• Partitions
• Statistics
• Compression
• Fast access
• REG_EXP
• Logic Into Database
• Oracle GRID Control Monitoring
• Oracle GRID Control Managing
• Oracle Clustering

MNP
Mediation
SWAT
9
CDR Database
CDR Repository

• Storage
• Indexing
• Partitions
• Compression
• Processing

64 Billion records All CDRs since 1998 15 TB
uncompressed 4.3 TB compresses 65 speedup lt 1
sec to find a call
10
Box Services (HomeBox VoiceBox OfficeBox)

• Guiding
• Processing
• Fraud Preventing

11
HomeBox Architecture
12
Home Fraud
13
SWAT is not Subscribers Wild Attribute
Transformations

• Real-Time Processing
• Subscribers information
• Real-Time Guiding
• Always in Memory
• Oracle Coherence, Oracle TimesTen

14
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

15
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) subscriptioniddatafrom
  (select from ( select
  i.eventtimestamp,i.imschargingidentifier,i.account
  ingrecordtype, case when
  (instr(sdpmedianame,'mvideo')gt0) then 'V'
  when (instr(sdpmedianame,'maudio')gt0)
  then 'A' else '_' end call_type,
  i.causecode,i.calledpartyoriginaladdress,i
  .originhost,i.serverpartyipaddress,
  i.callingpartyaddress,i.subscriptioniddata,i.desti
  nationrealm FROM ims_table_records I WHERE
  FILE_ID 7 AND ((i.accountingrecordty
  pe 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),imschargingide
  ntifier from ims_table_records
  where FILE_ID 7 group by
  imschargingidentifier having
  count(imschargingidentifier) gt1 ))
  )x where not (accountingrecordtype 3 and
  call_type '_'))

  SCHARGINGIDENTIFIER call EVENTTIMESTAMP B_TIME call CalledNumber CallingNumber
1 "3f07db0902fbd60866df283952556" 2 2008-04-18 123712     sippesho_at_sip.mtel.bg 359885623924
2 "3f07db0902fbd60866df283952556" 4 2008-04-18 123755 2008-04-18 123712 A sippesho_at_sip.mtel.bg 359885623924
3 "47af208f0335200866de8c7350dc4" 2 2008-04-18 122636     sip0888123456_at_10.253.68. 359885623924
4 "47af208f0335200866de8c7350dc4" 4 2008-04-18 122719 2008-04-18 122636 A sip0888123456_at_10.253.68. 359885623924
16
Example (2)

• select a.chargingID,a.servedIMSI,a.servedMSI
  SDN, 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(v
  alue(s1), '/ContentTypeIDSequence/DownlinkCount'),
  gx.dataVolumeGPRSDownlink) Down
  ,file_id,a.localsequencenumber
  ,min(mn.column_value) over (partition by
  a.localsequencenumber) sgsnaddressfrom
  ipc_table_ggsn a ,table(XMLSequence(
• extract(a.RECORDEXTENSIONS,
• '/ExtensionXML/ExtensionSequence/informationSequen
  ce/ContainerSequence/ContentTypeIDSequence')))()
  s1 ,table(a.listoftrafficvolumes)
  gx,table(a.sgsnaddress) mn

17
Whats New

• HP Oracle Exadata Storage Server

18
Q

A