Программа Vk2crm3 используется для выгрузки звонков, протарифицированных в системе ICS, Intec Interconnect (инстанс Онлайн), в систему CRM3 (звонковое хранилище) и в систему PartnerReports (система отображения отчетности).
В билинговой системе ICS (инстанс Онлайн) расчитываются звонковые данные мобильного сегмента сети Вымпелкома.
Мобильный сегмент сети Вымпелкома характеризуется наличием большого объема данных (около 1 млрд. звонков в месяц), при этом референсные данные для мобильного сегмента сети Вымпелкома (тарифные планы и характеристики операторов) изменяются редко, поэтому трафик в билинговую систему может быть подан онлайн.
Бизнес-процесс долгое время был построен таким образом, что в системе crm3 производилось агрегирование полученной звонковой информации по направлениям мастер-справочника направлений (по операторам по стороне А и Б). Очевидно, что это было нерационально: проводить процесс выгрузки большого количества данных, на который тратится 5-6 часов, после чего производить обработку и получать необходимую группировку (и тратить на это еще несколько часов).
Поэтому было решено внести изменения в процесс таким образом, чтобы программа vk2crm3 имела возможность выгружать не звонки, а суммарные данные, сагрегированные по направлением мастер-справочника, отдельно по каждой стороне (А и Б).
При этом объем передаваемых данных сократился на порядок (несколько десятков миллионнов агрегаций вместо миллиарда звонков), соответственно сократилось время выгрузки (полтора часа вместо восьми), а также время обработки данных в системе crm3.
Для того, чтобы выполнить поставленную задачу, процесс был изменен следующим образом:
1) ежедневно в БД INTEXP запускается джоб с процедурой, которая производит агрегирование звонковых данных (как новых,
появившихся в системе после момента последнего запуска процедуры, так и тех, для которых в исходной системе производились изменения).
Данные помещаются в соответствующую таблицу.
2) при выгрузке, если задан параметр производить выгрузку агрегированных данных (/agr=yes), производится формирование файла
на основании данных из подготовленной таблицы.
Ниже приведен пример запроса, который используется при выполнении задачи агрегирования звонков:
SELECT dlys, bd, a_dir, b_dir, SUM(duration) AS duration, SUM(actual) AS actual, SUM(calls) AS calls FROM (
SELECT /*+ DRIVING_SITE (c) */
NVL(a12.d, NVL(a11.d, NVL(a10.d, NVL(a9.d, NVL(a8.d, NVL(a7.d, NVL(a6.d
, NVL(a5.d, NVL(a4.d, NVL(a3.d, NVL(a2.d, NVL(a1.d, 0)))))))))))) AS a_dir
, NVL(b12.d, NVL(b11.d, NVL(b10.d, NVL(b9.d, NVL(b8.d, NVL(b7.d, NVL(b6.d
, NVL(b5.d, NVL(b4.d, NVL(b3.d, NVL(b2.d, NVL(b1.d, 0)))))))))))) AS b_dir
, c.anum, c.bnum, c.duration, c.actual, c.calls, c.dlys, t.bd
FROM (
SELECT /*+ PARALLEL (c,4) */
c.anum
, substr(c.anum,1,1) AS an1, substr(c.anum,1,2) AS an2, substr(c.anum,1,3) AS an3
, substr(c.anum,1,4) AS an4, substr(c.anum,1,5) AS an5, substr(c.anum,1,6) AS an6
, substr(c.anum,1,7) AS an7, substr(c.anum,1,8) AS an8, substr(c.anum,1,9) AS an9
, substr(c.anum,1,10) AS an10, substr(c.anum,1,11) AS an11, substr(c.anum,1,12) AS an12
, c.bnum
, substr(c.bnum,1,1) AS bn1, substr(c.bnum,1,2) AS bn2, substr(c.bnum,1,3) AS bn3
, substr(c.bnum,1,4) AS bn4, substr(c.bnum,1,5) AS bn5, substr(c.bnum,1,6) AS bn6
, substr(c.bnum,1,7) AS bn7, substr(c.bnum,1,8) AS bn8, substr(c.bnum,1,9) AS bn9
, substr(c.bnum,1,10) AS bn10, substr(c.bnum,1,11) AS bn11, substr(c.bnum,1,12) AS bn12
, c.duration, c.actual, c.calls, c.dlys, c.bd
FROM (
SELECT c.anum
, case when substr(c.bnum,1,1)='D' then 'D'||substr(c.bnum,4,2)||substr(c.bnum,2,2)||substr(c.bnum,6) else c.bnum end AS bnum
, c.charged_usage AS duration, c.actual_usage AS actual
, c.call_count AS calls, c.dlys_detail_id AS dlys, trunc(billing_date) AS bd FROM
ictprdi.CDR_DETAIL_20170101_0008@icton
c
) c) c
, tmp_cdr t
, (select d, c.code from a_direction_code c where length(c.code) = 1) a1
, (select d, c.code from a_direction_code c where length(c.code) = 2) a2
, (select d, c.code from a_direction_code c where length(c.code) = 3) a3
, (select d, c.code from a_direction_code c where length(c.code) = 4) a4
, (select d, c.code from a_direction_code c where length(c.code) = 5) a5
, (select d, c.code from a_direction_code c where length(c.code) = 6) a6
, (select d, c.code from a_direction_code c where length(c.code) = 7) a7
, (select d, c.code from a_direction_code c where length(c.code) = 8) a8
, (select d, c.code from a_direction_code c where length(c.code) = 9) a9
, (select d, c.code from a_direction_code c where length(c.code) = 10) a10
, (select d, c.code from a_direction_code c where length(c.code) = 11) a11
, (select d, c.code from a_direction_code c where length(c.code) = 12) a12
, (select d, c.code from a_direction_code c where length(c.code) = 1) b1
, (select d, c.code from a_direction_code c where length(c.code) = 2) b2
, (select d, c.code from a_direction_code c where length(c.code) = 3) b3
, (select d, c.code from a_direction_code c where length(c.code) = 4) b4
, (select d, c.code from a_direction_code c where length(c.code) = 5) b5
, (select d, c.code from a_direction_code c where length(c.code) = 6) b6
, (select d, c.code from a_direction_code c where length(c.code) = 7) b7
, (select d, c.code from a_direction_code c where length(c.code) = 8) b8
, (select d, c.code from a_direction_code c where length(c.code) = 9) b9
, (select d, c.code from a_direction_code c where length(c.code) = 10) b10
, (select d, c.code from a_direction_code c where length(c.code) = 11) b11
, (select d, c.code from a_direction_code c where length(c.code) = 12) b12
where
c.dlys = t.dlys_id
and c.an1 = a1.code(+) and c.an2 = a2.code(+) and c.an3 = a3.code(+)
and c.an4 = a4.code(+) and c.an5 = a5.code(+) and c.an6 = a6.code(+)
and c.an7 = a7.code(+) and c.an8 = a8.code(+) and c.an9 = a9.code(+)
and c.an10 = a10.code(+) and c.an11 = a11.code(+) and c.an12 = a12.code(+)
and c.bn1 = b1.code(+) and c.bn2 = b2.code(+) and c.bn3 = b3.code(+)
and c.bn4 = b4.code(+) and c.bn5 = b5.code(+) and c.bn6 = b6.code(+)
and c.bn7 = b7.code(+) and c.bn8 = b8.code(+) and c.bn9 = b9.code(+)
and c.bn10 = b10.code(+) and c.bn11 = b11.code(+) and c.bn12 = b12.code(+)
)
group by
dlys, bd, a_dir, b_dir
;
Источником данных служит звонковая таблица ictprdi.CDR_DETAIL_20170101_0008@icton, находящаяся в удаленной БД. Таких звонковых таблиц несколько (содержат часть данных ежедневного трафика). Звонковая таблица по А и Б-номерам соединяется с помощью левого соединения со срезами таблицы кодов (a_direction_code), селектированными по длине кода (для поиска направления, соответствующего максимально длинному номеру).
Кроме того выборка селектируется только по нужным данным трафика ( выбираются только данные для ключей из темперной таблицы tmp_cdr, которая заполняется предварительно).
Также следует отметить, что для обработки дневной порции данных, нужно выполнить данный запрос для всех звонковых таблиц.