Контроль расходов мобильной связи в рамках организации

в 15:19, , рубрики: olap, sql, Песочница, Телекомы, метки: ,

Не будет новостью, что контролировать расходы на связь в организации с более чем двадцатью сим карт дело не простое и многие предпочитают просто платить, не озадачиваясь анализом расходов.
На рынке есть специализированные продукты, позволяющие следить за расходами в основном в ручном режиме.
Хотел бы поделиться опытом создания системы контроля расходов для нескольких сотен тысяч сим карт построенной на безе MS SSAS.
В качестве поля для действия была выбрана компания имеющая на тот момент около 120 тыс сим карт, 90% из которых был провайдер с логотипом в виде яйца и устанавливаемых в м2м устройства.

Идея

Немного углубившись в работу с провайдерами мобильной связи, процесс выглядел так:

  • получаем электронную версию счета за связь на почту
  • получаем бумажный оригинал
  • оплачиваем
  • почтовый ящик не резиновый, удаляем электронные копии

Хотелось выстроить процесс примерно так:

  • получаем электронную версию счета за связь на почту
  • загружаем в базу, анализируем
  • пере выставляем необоснованные расходы(провайдеру или тому кто пользуется сим)
  • оплачиваем

Решение

Первое, что нужно было сделать, загрузить данные в БД. В организации использовалась Axapta, в ее базу и решено было складировать данные от провайдеров. Программный код с помощью которого данные из html счетов попадали в БД описывать не буду, т.к. не писал его и суть поста не в этом.
Файл приходящий от провайдера в html формате делится на две составляющие: детализации за прошедший период и сводка по каждому номеру о начислениях. Сводку в итоге мы будем грузить в базу.
В итоге, из вот такого файла будем вытаскивать информацию:
image

В базе создаем таблицу такого вида:

 CREATE TABLE [dbo].[month_money_traffic](
	[PERIODBEGIN] [datetime] NOT NULL, --отчетный период
	[personalaccount] [nvarchar](20) NOT NULL, --лицевой счет
	[phonenumber] [nvarchar](12) NULL, -- телефон
	[VENDACCOUNT] [varchar](7) NULL, -- провайдер
	[typeconid] [varchar](50) NOT NULL, --тип списания
	[traffic] [numeric](30, 7) NULL, -- трафик
	[money] [numeric](32, 14) NULL --деньги
) 

И загружаем туда данные из наших счетов.
Получается примерно так:

image

Поле [typeconid] нужно предварительно привести к стандартным 8-10 значения, иначе мы рискуем иметь несколько сотен типов связи, т.к. примерно один и тот же тип связи у провайдера может назваться по разному, к примеру: «исходящая» и «исх.» или «Контент категория ..(и номер категории)..».

делается с помощью case:

 case when [typeconid] like 'Контент категория%' then 'контент' 
 case when [typeconid] like 'исх%' then 'исходящая связь'
 else  [typeconid]
 end

В итоге получаем таблицу фактов для нашего куба.

Далее нужно спроектировать измерения.
Каждая сим карта в организации имеет следующие свойства:

  • расположение(в м2м устройстве, выдана сотруднику, в серверах и т.д.)
  • статус(активна, блокирована)
  • регион обслуживание, продажи
  • провайдер
  • контракт(юр лицо, которое платит по счетам)
  • и тд

Часть измерений берем из таблицы фактов: лицевой счет, провайдер, тип связи
Часть извлекаем из нашей базы, сделав join необходимых таблиц

Далее настраиваем инкрементальное пополнение таблиц измерений из операционной базы в хранилище. Здесь кому как нравиться, можно использовать timestamp, cdc, для извлечения только измененных данных.
У меня таблицы не большие и я использовал обычный left join чтоб вытащить разницу двух таблиц.

В итоге параметры были сведены в одну таблицу с первичным ключом по полю «телефон»:

image

Там где параметры не известны, заменяем значения на 'n/a'.

Открываем BIDS и создаем новый проект интеллектуального анализа данных.
В качестве источника данных подключаем базу с созданными таблицами.
В представление источника данных добавляем таблицу фактов и таблицы измерений.
Я сделал еще несколько измерений по аналогии с описанным, а также таблица времени осталась у меня от другого проекта.
Создаем связи от таблицы фактов на таблицы измерений, при этом ключи в таблицах измерений не обязательно должны быть физическими, при создании связи они добавятся в качестве логических.

image

Далее создаем куб:
image

Так как «Кубом» пользуются в разных регионах, нужно разграничить права. Сделал я это по измерению «контракт сим», создав отдельную роль для каждого региона и прописав на вкладке «данные измерения» следующее(пример для Урала):
image
И обязательно включить галку «активировать визуальные итоги»

В итоге, каждый регион может видеть только свои сим карты и свои расходы.
Аналитики подключаются к кубу через Excel и просматривают расходы.
Ниже несколько скринов, как это выглядит глазами аналитика:
Интерфейс — обычная сводная таблица

image

Расходы за три месяца по типам связи
image

Расходы по сим за месяц, наглядно видны «пики», с которыми нужно разбираться.

image

Отчет по трафику по одной из сим (цифры в минутах)

image

На данный момент системой пользуются в 7 регионах РФ.
В первые два месяца после внедрения системы, расходы удалось снизить примерно на 20% и это не предел.

Данный топик не преследует целью описать до мелочей создание OLAP куба, а всего лишь описывает концепцию, которая может быть реализована в любой компании имеющей большое кол-во сим карт.

Систему можно совершенствовать с помощью MDX запросов, вытаскивая «интересные» случаи и обрабатывая их.

Если будет интерес к статье, можно развернуть технические подробности проекта.

Автор: alexxerm

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js