Не будет новостью, что контролировать расходы на связь в организации с более чем двадцатью сим карт дело не простое и многие предпочитают просто платить, не озадачиваясь анализом расходов.
На рынке есть специализированные продукты, позволяющие следить за расходами в основном в ручном режиме.
Хотел бы поделиться опытом создания системы контроля расходов для нескольких сотен тысяч сим карт построенной на безе MS SSAS.
В качестве поля для действия была выбрана компания имеющая на тот момент около 120 тыс сим карт, 90% из которых был провайдер с логотипом в виде яйца и устанавливаемых в м2м устройства.
Идея
Немного углубившись в работу с провайдерами мобильной связи, процесс выглядел так:
- получаем электронную версию счета за связь на почту
- получаем бумажный оригинал
- оплачиваем
- почтовый ящик не резиновый, удаляем электронные копии
Хотелось выстроить процесс примерно так:
- получаем электронную версию счета за связь на почту
- загружаем в базу, анализируем
- пере выставляем необоснованные расходы(провайдеру или тому кто пользуется сим)
- оплачиваем
Решение
Первое, что нужно было сделать, загрузить данные в БД. В организации использовалась Axapta, в ее базу и решено было складировать данные от провайдеров. Программный код с помощью которого данные из html счетов попадали в БД описывать не буду, т.к. не писал его и суть поста не в этом.
Файл приходящий от провайдера в html формате делится на две составляющие: детализации за прошедший период и сводка по каждому номеру о начислениях. Сводку в итоге мы будем грузить в базу.
В итоге, из вот такого файла будем вытаскивать информацию:
В базе создаем таблицу такого вида:
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 --деньги
) И загружаем туда данные из наших счетов.
Получается примерно так:
Поле [typeconid] нужно предварительно привести к стандартным 8-10 значения, иначе мы рискуем иметь несколько сотен типов связи, т.к. примерно один и тот же тип связи у провайдера может назваться по разному, к примеру: «исходящая» и «исх.» или «Контент категория ..(и номер категории)..».
делается с помощью case:
case when [typeconid] like 'Контент категория%' then 'контент'
case when [typeconid] like 'исх%' then 'исходящая связь'
else [typeconid]
end
В итоге получаем таблицу фактов для нашего куба.
Далее нужно спроектировать измерения.
Каждая сим карта в организации имеет следующие свойства:
- расположение(в м2м устройстве, выдана сотруднику, в серверах и т.д.)
- статус(активна, блокирована)
- регион обслуживание, продажи
- провайдер
- контракт(юр лицо, которое платит по счетам)
- и тд
Часть измерений берем из таблицы фактов: лицевой счет, провайдер, тип связи
Часть извлекаем из нашей базы, сделав join необходимых таблиц
Далее настраиваем инкрементальное пополнение таблиц измерений из операционной базы в хранилище. Здесь кому как нравиться, можно использовать timestamp, cdc, для извлечения только измененных данных.
У меня таблицы не большие и я использовал обычный left join чтоб вытащить разницу двух таблиц.
В итоге параметры были сведены в одну таблицу с первичным ключом по полю «телефон»:
Там где параметры не известны, заменяем значения на 'n/a'.
Открываем BIDS и создаем новый проект интеллектуального анализа данных.
В качестве источника данных подключаем базу с созданными таблицами.
В представление источника данных добавляем таблицу фактов и таблицы измерений.
Я сделал еще несколько измерений по аналогии с описанным, а также таблица времени осталась у меня от другого проекта.
Создаем связи от таблицы фактов на таблицы измерений, при этом ключи в таблицах измерений не обязательно должны быть физическими, при создании связи они добавятся в качестве логических.
Далее создаем куб:
Так как «Кубом» пользуются в разных регионах, нужно разграничить права. Сделал я это по измерению «контракт сим», создав отдельную роль для каждого региона и прописав на вкладке «данные измерения» следующее(пример для Урала):
И обязательно включить галку «активировать визуальные итоги»
В итоге, каждый регион может видеть только свои сим карты и свои расходы.
Аналитики подключаются к кубу через Excel и просматривают расходы.
Ниже несколько скринов, как это выглядит глазами аналитика:
Интерфейс — обычная сводная таблица
Расходы за три месяца по типам связи
Расходы по сим за месяц, наглядно видны «пики», с которыми нужно разбираться.
Отчет по трафику по одной из сим (цифры в минутах)
На данный момент системой пользуются в 7 регионах РФ.
В первые два месяца после внедрения системы, расходы удалось снизить примерно на 20% и это не предел.
Данный топик не преследует целью описать до мелочей создание OLAP куба, а всего лишь описывает концепцию, которая может быть реализована в любой компании имеющей большое кол-во сим карт.
Систему можно совершенствовать с помощью MDX запросов, вытаскивая «интересные» случаи и обрабатывая их.
Если будет интерес к статье, можно развернуть технические подробности проекта.
Автор: alexxerm
