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

Хотелось бы рассказать некоторые технические подробности создания системы контроля расходов мобильной связи, концепция которой была описана в предыдущем посте.
В качестве примера будем рассматривать счет за один из месяцев от МТС и создадим не сложный OLAP проект.
Судя по откликам, эта тема довольно актуальна, так что, кому интересно, прошу под кат.

Информацию о расходах мы берем из файла счета, а точнее из сводной его части по каждому номеру:

Из файла мы извлекаем:

• номер телефона
• ICCID — id сим карты
• период счета
• сводные данные по услугам связи с суммами

Также из «шапки» счета извлекаем:

• лицевой счет
• наименование провайдера

Мы пропустим описание парсера для извлечения всех этих данных, по причине того, что в каждом регионе и у каждого провайдера формат счетов будет различаться и универсального софта просто не может быть.

Можно обратить внимание на некоторые особенности, а именно:

• для проверки корректности парсера, необходимо встроить сравнение общей суммы по каждому номеру и сумм составляющих по видам расхода
• ICCID — единственный уникальный id для каждой сим. Со временем голосовые номера могут меняться, но ICCID остается постоянный. Наиболее правильно строить связи именно по этому параметру
• лицевой счет — если возможно, сим карты используемые в разных составляющих вашей системы «развешивать» на разные л/с(сотрудники, м2м устройства, связь для серверов и тд), так будет максимально удобно отслеживать расходы

Импортируем данные в таблицу sql в соответствии с вышеописанными составляющими.
Сырые данные данные выглядят так:

Если выбрать все уникальные значения по типу трафика, то мы получим список из нескольких сотен строк, который никак не может способствовать комфортной работе аналитика.
Ниже лишь небольшая часть полученных данных. Можно видеть например что внутри сетевой роуминг указывается для каждого города отдельно.

Необходимо привести данные по типу связи к стандартным 10-15 значениям
Сделать это можно через оператор case, проанализировав все возможные варианты одного значения, к примеру так:

CASE  	WHEN rom.SERVICEDESCRIPTION LIKE '%роуминг%sms%' THEN 'роуминг смс'  	WHEN (rom.SERVICEDESCRIPTION LIKE '%sms%' AND rom.SERVICEDESCRIPTION NOT LIKE '%роуминг%') THEN 'смс'      WHEN (((rom.SERVICEDESCRIPTION LIKE '%gprs%' OR rom.SERVICEDESCRIPTION LIKE '%мобильный интернет%') and rom.SERVICEDESCRIPTION not LIKE '%hsdpa%') AND rom.SERVICEDESCRIPTION NOT LIKE '%роуминг%') THEN 'gprs'      WHEN ((rom.SERVICEDESCRIPTION LIKE '%роуминг%gprs%' OR rom.SERVICEDESCRIPTION LIKE '%роуминг%мобильный интернет%') and rom.SERVICEDESCRIPTION not LIKE '%роуминг%hsdpa%') THEN 'роуминг gprs'      WHEN ((rom.SERVICEDESCRIPTION  LIKE '%hsdpa%') AND rom.SERVICEDESCRIPTION NOT LIKE '%роуминг%') THEN 'hsdpa'      WHEN (rom.SERVICEDESCRIPTION  LIKE '%роуминг%hsdpa%') THEN 'роуминг hsdpa'      WHEN ((rom.SERVICEDESCRIPTION LIKE '%исходящая связь%' OR rom.SERVICEDESCRIPTION LIKE '%исходящие%вызовы%' OR rom.SERVICEDESCRIPTION LIKE '%исх. связь%' OR rom.SERVICEDESCRIPTION LIKE '%Передача данных, факсов, WAP и Интернет%') AND rom.SERVICEDESCRIPTION NOT LIKE '%роуминг%') THEN 'исходящая связь'  	WHEN (rom.SERVICEDESCRIPTION LIKE '%роуминг%исходящая связь%' OR rom.SERVICEDESCRIPTION LIKE '%роуминг%исходящие%вызовы%' OR rom.SERVICEDESCRIPTION LIKE '%роуминг%исх. связь%' OR rom.SERVICEDESCRIPTION LIKE '%за предел.Дом%') THEN 'роуминг исходящая связь'  	WHEN rom.SERVICEDESCRIPTION LIKE '%роуминг%входящая связь%' THEN 'роуминг входящая связь'  	WHEN ((rom.SERVICEDESCRIPTION LIKE '%входящая связь%') AND rom.SERVICEDESCRIPTION NOT LIKE '%роуминг%') THEN 'входящая связь'  	WHEN rom.SERVICEDESCRIPTION LIKE '%абонентская плата%' THEN 'абонентская плата'  	WHEN rom.SERVICEDESCRIPTION LIKE '%детализация%' THEN 'детализация' ELSE 'остальные' END 

В итоге получаем стандартные значения:

Далее нам нужно преобразовать текстовые значения по количеству трафика в числовые
С помощью того же оператора case

CASE  	WHEN rom.serviceqtydescription LIKE '0.00 Мб' THEN '0'  	WHEN rom.serviceqtydescription LIKE '%.%.%' THEN '1'  	WHEN rom.serviceqtydescription LIKE ' ' OR rom.serviceqtydescription LIKE '' THEN '0'  	WHEN rom.serviceqtydescription LIKE '%минута' THEN CAST(replace(rom.serviceqtydescription, ' минута', '') AS numeric(20, 2))  	WHEN rom.serviceqtydescription LIKE '%мин.' THEN CAST(replace(rom.serviceqtydescription, ' мин.', '') AS numeric(20, 2))      WHEN rom.serviceqtydescription LIKE '%мин. 0 сек.' THEN CAST(replace(rom.serviceqtydescription, ' мин. 0 сек.', '') AS numeric(20, 2))      WHEN rom.serviceqtydescription LIKE '%секунда' THEN CAST(replace(rom.serviceqtydescription, ' секунда', '') AS numeric(20, 2)) / 60      WHEN rom.serviceqtydescription LIKE '%килобайт' THEN CAST(replace(rom.serviceqtydescription, ' килобайт', '') AS numeric(20, 2))     WHEN rom.serviceqtydescription LIKE '%байт' THEN CAST(replace(replace(rom.serviceqtydescription, ' байт', ''),',','.') AS numeric(20, 2)) / 1024      WHEN (rom.serviceqtydescription LIKE '%.% Мб' AND rom.serviceqtydescription NOT LIKE '0.0 Мб') THEN CAST(replace(rom.serviceqtydescription, ' Мб', '') AS numeric(20, 2)) * 1024 	WHEN rom.serviceqtydescription LIKE '%штука' THEN CAST(replace(rom.serviceqtydescription, ' штука', '') AS numeric(20, 2)) 	when rom.serviceqtydescription = 'не указано' then 0     ELSE CAST(replace(rom.serviceqtydescription, ' факт', '') AS numeric(20, 2)) END AS 

Получаем: смс в штуках, голос в минутах, передача данных в киллобайтах

Итоговая таблица выглядит так

Теперь данные по суммам и трафику поддаются арифметическим операциям.
В таком виде данные уже вполне поддаются анализу либо из окна managment studio, либо через интерфейс Excel, подключив базу, как внешний источник данных.
Как вы поняли, мы сделали таблицу фактов для будущего OLAP Куба.
Для создания простейшего Куба, необходимо выделить измерения из таблицы фактов:

• период
• лицевой счет
• телефон
• провайдер
• тип трафика

Очень желательно вывести данные в отдельные таблицы — справочники с отдельным первичным ключом формата int и заменить значения в таблице фактов на соответствующие значения из справочников. Значения в справочниках должны быть уникальными.
Все вышеописанное не касается справочника времени, он генерируется на сервере отдельно.

Начинаем создание OLAP проекта

В BIDS необходимо создать новый проект интеллектуального анализа данных

Создаем новый источник данных с именем DWH

Создаем представление источника данных, куда добавляем наши таблицы

В данном примере отсутствует таблица провайдеров, но добавляется она аналогично. А также я не следовал рекомендациям с ключами по полю формата int — работать будет но при больших объемах данных повлияет на быстродействие.

Создание измерения времени описывать не буду, в сети достаточно информации по этому поводу.

Следующим этапом создаем куб, для этого запускаем мастер создания
На первом экране выбираем — использовать существующие таблицы
Далее нужно выбрать нашу таблицу измерений и следовать за мастером

После создания куба, автоматически добавятся измерения
Куб примет следующий вид:

Также, необходимо дважды кликнуть измерение времени и перетянуть в раздел атрибутов нужные нам временные составляющие:

А также, при необходимости создать иерархии

Можно приступать к развертыванию куба на сервере, для этого кликаем по проекту и запускаем обработку.
После обработки можно подключаться к кубу через Excel, делается это так:
Данные — из других источников — из служб аналитики, в мастере вводим имя сервера на который мы разворачивали куб.

Описанное решение не совершенно, специалисты в области sql, так и BI укажут на множество недостатков, но я надеюсь, что данное описание позволит людям, мало знакомым с построением систем бизнес аналитики, начать это делать.