Corporate Financial &
Operational Analytical and
Reporting System
Corporate Financial &
Operational Analytical and
Reporting System
Agenda
•
Company overview
•
Business needs
•
Solution
•
Benefits
•
Further plans
I
•Company overview
•
Business needs
•
Solution
•
Benefits
•
Further plans
Some Details
•
Registered on October 28
th1993
•
Commercial operations started in July 1994
•
Leading Russian mobile operator
•
Offering GSM telephony, roaming, SMS, WAP, GPRS
services, etc …
•
3,500 employees, 70 trade centers over the Russia
2001 Business Specs
•
Total revenues
$893.2m
•
Capitalization $3.5b
•
EBITDA $424.0m
•
EBITDA margin
47.5%
•
Net Incom
$207.3m
•
ARPU $36/month
Network Investments
26
69
79
109
194
397
0
50
100
150
200
250
300
350
400
1996
1997
1998
1999
2000
2001
Annual capex
(US $mln)
Strong growth in subscribers
18 59 114 307 1194 2650 3650 0 500 1000 1500 2000 2500 3000 3500 4000 1996 1998 2000 4m 2002 ' 000 subscribers0 500 1 000 1 500 2 000 2 500 3 000 3 500 4 000 3Q00 4Q00 1Q01 2Q01 3Q01 2001 4M20 02 N o. of s ubs cr ib er s ( '000 )
Business and wealthy Mass market
Attractive Customer Base
2001
Business and
wealthy Mass market
Subscriber share 15% 85% Service revenue share 41% 59% ARPU (US$) 100 24 MOU (min) 325 149
Net Incom
4,8 59 66 85 90 207 0 50 100 150 200 250 1996 1997 1998 1999 2000 2001 Net Incom $(mln)MTS
46%
Vimpelcom
32%
SMARTS
4%
Megafone
14%
Others
4%
Vimpelcom
MTS
SMARTS
Megafone
Others
Russian GSM market share
Regional expansion highlights
•
37 operational regions
•
Recent acquisitions:
-
Telekom XXI (Saint Petersburg)
-
Kuban GSM –
4
thlargest Russian mobile operator
-
Telecom 900 (taking control over 3 leading operators in
Siberian and Far East)
# # # # ### # # # # # # # # # # # # # # # # # # # # # # # Moscow Yaroslavl' N. Novgorod Voronezh Perm' Yekaterinburg Orenburg Chelyabinsk Kurgan Tumen' Omsk Blagoveschensk Hanty-Mansiysk # Salehard Kirov Kostroma Ivanovo ## Syktyvkar # Kudymkar # Izhevsk Pskov Tver' Smolensk Bryans k Kurs k Belgorod Kaluga Orel Tambov Tula Lipetsk Vladimir Ryazan' # Kaliningrad # St.Petersburg # Petrozavods k # Arkhangelsk # Murmansk # Novgorod #Vologda # Naryan-Mar #Novosibirsk # Khabarovs k # Gorno-Altaysk # Minsk # Krasnodar
Geared Up to Be The First National
Operator
Geared Up to Be The First National
Operator (part 2)
•
Licenses to operate in 48 regions in Russia with
a population of 84 million (58%of Russia’s total)
and Belarus with a population of 10 million
•
Currently operating in 37 regions (60 million
people), including Moscow and St Petersburg
Last detail – Critical Numbers
•
Over 30 billing servers in 37 branches
•
8 subsidiaries with different accounting system
•
Data center-branch data channel overall
speed is 64kbit/sec
•
8 hours time difference between first and last
II
•
Company overview
•Business needs
•
Solution
•
Benefits
•
Further plans
Business Needs
•
To get in time all operating and financial
information critical to company top
management. To automate this process.
•
To make this process transparent and to be
able perform audit of the information. Finally,
to get one “source of truth”.
•
To be able to analyze operating and financial
General Questions
•
Customers profitability, loyalty and retention
•
New customers attraction
•
Tariff policy
•
Services usage and demand
III
•
Company overview
•
Business needs
•Solution
•
Benefits
•
Further plans
History Environment
Billing Service Moscow Billing Customer's Accounting Marketing CEO - President Cell Commutator 1 Board of Vice-PresidentsPlan and Finance Service
Russian Accounting System Eastern Accounting GAAP Accounting System SUN Accounts CFO CMO y $$$
General Requirements
•
Get information from billing systems.
•
Develop flexible reporting system based on
current company standards.
•
Create analytical decision support systems for
Planning and Finance Dept. and Customer
Accounting Division instead of current systems
with limited functionality.
Details: Get Information
-
Get information from central and regional billings on
daily and monthly basis.
-
Daily processing has time limitation of 11 hours
maximum, from 21:00 to 8:00 for each time zone.
-
Clean data and consolidate regional dictionaries.
-
Process history information from year 1998 to 2001.
Details: Develop Reporting System
-
Develop 22 complex reports for each reporting level.
-
Reporting levels: corporate, regional (branch or
subsidiary).
-
Reporting should include following analysis: total and
new number of customers, charges/income, traffic by
following classes: tariff’s, service’s and customer’s
characteristics.
-
Comparative analysis variables should have at least
12 monthly values to compare.
Details: Create Analytical DSS
-
Develop analytical system within customers, finances
and net traffic areas.
-
Data should include demographic, business, specific
and other classifiers of customers.
-
Analytical values: total and new number of
customers, traffic volume, billed/non-billed traffic,
amount of services delivered/charged,
Solution Architecture
•Analytics for
Plan&Finance Dept. •Dept.Analytics for Accounting
•Reporting for Top Mgrs
•ETL Control Application
•Data Warehouse
Critical features
•
Control all ETLs centrally
-
8 time zones
-
30+ regional billing systems
(expected grow up to 60)-
64 Kbit channels
•
Consolidate dictionaries
•
Maintain time limits for ETL
-
Night period for each region
End Day of Week End Type of Day End Fiscal Week End Fiscal Month End Day End Calendar Month End Holiday End Calendar Week
•
DWH has three logical level:
1 Normalized detail storage level
for customers and finance
2 Aggregated data marts for
traffic, customers and finances
3 Special data marts for specific
functionality of analytical
applications
Год year (date) 5 ируемая ость M,MEAN ость еальная) M,MEAN во N) M,MEAN ть UM,M ть я UM,MEA Длительность (0сек, 1сек, ...60сек, ...120сек, 2.5мин, 3мин, ...10 30мин) Комутатор (номера комутаторов) Статус приложения (не расчитано, не подтверждено; расчитано, не подтверждено; завершено) Тип пользователя мобильного телефо (организация, частное лицо) Тип услуг (регулярное) Ссылка на услугу (вид тарифа) 8 Код национального доступа (095, 902) Биллинговая группа 20 Система расчетов (кредитный, по факту) Состояние лицевого счета (активен, предзакрыт, закрыт) Тип плательщика (организация, частное лицо) Регион 5 Маркетинговая категория абонента 5 Тип абонента (организация, частное лицо) Признак освобождения от налогов (да, нет) Юридическая категория абонента (категория 0) Состояние контракта (неопределен, основной, агентский, служебный Тип юридических отношений (резидент, нерезидент) Сота (номера соты) Наличие международного доступа (номера соты) Направление/Услуга (Входящий, Местный, Зона МТС, Россия(Европа Америка Юж Америка Роуминг Спец вызовы Customer Usage Billing Billing Date Billing Month Customer Cust. Delivery Point Product Product/Service Usage Type Usage Type Pricing Package Package Date Day Area Code Prefix Cust. Date of Birth Cust. Demogr. Profile Cust. Educ. Level Household Household Profile Cust. Gender Assigned Service Region Cust. Location Zip+4 Cust. Location Zip Bill To Zip Customer Location Customer Cust. Location Street Address Bill To City Bill To State/Province Bill To Country Cust. Location City Cust. Location State/Province Cust. Location Country Customer Type Industry Industry Group Date of 1st Service Cust. Assigned Sales Region Parent Company Bill To Zip+4 Bill To Street Address Год year (date) 5 Месяц month 3 Статус приложения (не расчитано, не подтверждено; расчитано, н подтверждено; завершено) Тип пользователя мобильного телеф (организация, частное лицо) Тип услуг (регулярное) Ссылка на услугу (вид тарифа) 8 Код национального доступа (095, 902) Биллинговая группа 20 Система расчетов (кредитный, по факту) Состояние лицевого счета (активен, предзакрыт, закрыт) Тип плательщика (организация, частное лицо) Маркетинговая категория абонента 5 Тип абонента (организация, частное лицо) Признак освобождения от налогов (да, нет) (результат, заявка) Тип активности абонента (активен, отключен менее 2-х месяцев, отклю ение MEAN абонентов ная, тся в ня по м MEAN Регион 5Decrease overload level
(parallel extraction)
•
Get data from ORACLE table by N SAS
processes using appropriate index
•
All processes use their own diapasons of index
SAS ETL Process 1 SAS Table A R e gi ona l S A S DW H S e rv er
... Process nSAS ETL SAS ETLProcess n+1
SAS Table B
Maintain time limits
•
Parallel extraction decreases time
•
Start schedule for all regional ETL processes
•
In case of errors – restart from last completed
node automatically
(depends on error type)•
Start central ETL process at fixed time (do not
Consolidate dictionaries
•
Use copy of each regional dictionary in center. Extract
current regional dictionary but extract data with lag (–1)
day
(check changes and make decision)•
Add regional prefix to all regional data at the detail level
•
Create Etalon dictionaries with diapasons. Develop and
update maps for etalon dictionaries and regional
dictionaries
Control centrally
•
All regional processes support running
metadata
•
Center has monitor process that each x
minutes reads running metadata from
each region
•
In case of trouble, central process analyze
it and generate batch script starting from
the last well completed node and submit it
on the regional server.
•
Log all regional processes centrally
•
Inform operator in case of critical errors
ETL Process flow
SAS ETL SAS Regional Staging Area Transport files Dictionaries Etalon Dictionary Transportation Control metadata Proactive Transportation Clean and Consolidati on Transcoding into central DWH codes Processing data marts Central DWH Data Marts Control Application Central DWH Metadata Regional DWH Metadata DWH Managing Application C ent ra l SA S D W H Se rv er R e gi on al S A S DW H Se rv e rReports for Top Mangers
•
22 complex reports for each
region and company-wide also
•
Keep previous known and sharp
MTS standards for reporting
•
Distribute reports properly and
Analytical applications
•
Deliver information on
daily and monthly basis
•
Data has 50+
dimensions and 10+
indicators to analyze
Year 2001 – Main Results
Billing Service Customer's Accounting Marketing CEO - President Cell Commutator 1 Cell Commutator n Board of Vice-PresidentsPlan and Finance Service
Russian Accounting System SUN Accounts Eastern Accounting GAAP Accounting System SUN Accounts CFO CMO y $$$
IV
•
Company overview
•
Business needs
•
Solution
•Benefits
•
Further plans
Solution Benefits
•
Decreasing of the billing systems overload
level
•
Transparent reporting and analytical data
•
Reports distribution in due time
•
One source of truth
Solution Benefits (last)
•
One more step
to intelligence
V
•
Company overview
•
Business needs
•
Solution
•
Benefits
•Further plans
Year 2002 Prospects
•
Include information from all currently operating
regions into the corporate DWH
•
Implement SAS Financial Management
Solution for the Top company Financial
Management
•
Develop Deviation Analysis System of Finance
Dept. and Accounting Dept. data
•
Start to develop CRM solution for Fraud,
Year 2002 Results Estimate
Billing Service Customer's Accounting Marketing CEO - President Cell Commutator 1 Board of Vice-PresidentsPlan and Finance Service
Russian Accounting System SUN Accounts
Eastern Accounting
GAAP Accounting System SUN Accounts
CFO CMO
y
