Введение:
В этой статье речь пойдет о работе с Microsoft Analysis Services и немного о хранилище на Microsoft SQL Server, с которым SSAS работает. Мне пришлось столкнуться с не совсем тривиальными вещами и порой приходилось “прыгать через голову” ради того, чтобы сделать то, что от меня хотят. Работать приходилось в перерывах между совещаниями. Порой новый функционал обсуждался дольше, чем разрабатывался. Часто на совещаниях, по несколько раз, приходилось рассказывать одно и тоже. Когда я сказал, что мне сложно совещаться дольше одного часа, на меня посмотрели с удивлением и непониманием. Во многом, благодаря такой обстановке и появились эти, не совсем тривиальные вещи, о которых я решил написать.
Среднее за период
Требовалось посчитать среднее значение за период. В MS Analysis Services есть для этого функция Average of Children, которая считает среднее по всем не пустым значениям за выбранный период.
Но у заказчика, после скрупулёзного изучения результатов, появились вопросы? Он показал мне следующую выборку, и сказал что она не правильная:
Так как, по его мнению, должно быть так:
На мой вопрос: Почему? Он ответил, что ему нужно не среднее значение за период, а сумму средних значений по каждому элементу за период, то есть
НЕ ТАК:
( 5 + 6 + 7 ) / 3 = 6
А ВОТ ТАК:
( 2,5 + 3,5 + 3 ) = 9
Это желание заставило меня пройти все стадии принятия неизбежного:
- Отрицание (Это что угодно, но не среднее за период);
- Гнев (Кто его математике учил?);
- Торг (Давайте оставим так и спросим тех, кто будет этим пользоваться?);
- Депрессия (А говорили, что здесь все очень добрые и хорошие…);
- Принятие ( Можно и зайца курить научить. Надо так надо, сделаю, как попросили).
Решение было не совсем очевидным. В разделе Calculations я создал Calculated Member и c помощью функций iif, isleaf и sum написал выражение.
Первый вариант:
iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)
где [ELEM] – это измерение, а [ELEM SK] это его ключ.
Результат оказался неожиданным:
Странные цифры в Итогах оказались суммами всех значений элементов в измерении, не зависимо от того, какие значения я выбирал в фильтре. То есть в итоге всегда была сумма всех элементов, а не только выбранных.
Проблема решилась не сразу. Первым решением был вариант с дополнительным скрытым измерением. Я создал копию измерения [ELEM], изменил свойство Visible на False и написал так:
iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)
Получилось так, как хотел заказчик.
Чуть позже я нашёл “более правильное” решение. Я создал Named Set.
CREATE DYNAMIC SET CURRENTCUBE.[Controller Set]
AS {
EXISTING [ELEM].[ELEM SK].currentmember.Children
};
a Calculated Member переписал на такой:
iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum([Controller Set],[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)
Таким образом, копия измерения [ELEM COPY] стала не нужна.
Я представил это решение заказчику и занялся другими задачами. Спустя какое-то время мне пришло новое техническое задание, в котором фигурировало понятие “statistical average”. На мой вопрос – Что это? Он сказал, что пользователям нужна не сумма средних значений по каждому элементу за период, а среднее за период, то есть обычный Average of Children, но мое решение он попросил сохранить.
Ещё, он попросил добавить среднее за период с учетом всех дней, а не только тех, в которых есть значения.
( 5 + 6) / 3 = 3,666667
Задачу я решил с помощью дополнительного факта, который просто посчитал количество строчек в таблице для Time Dimension (в данном решении, в этой таблице на каждый день всегда есть одна запись). Далее, добавил факты с агрегацией – сумма (AggregateFunction = Sum) и добавил Calculated Member, в котором разделил Сумму, на количество дней.
[Measures].[Sum DATA]/[Measures].[TIME Count]
Хранилище
Клиент, на которого мы работаем, оказался не так прост. Про него можно сказать – “Месье знает толк в извращениях”. После того как мы разобрались с Кубом, перед нами была поставлена более глобальная задача. Разработать хранилище (Data Vault), но не простое. Первое что нам сказали – это то, что теперь наша Библия – это «Building a Scalable Data Warehouse with Data Vault 2.0», написанная Daniel Linstedt’ом и в довесок настояли на внедрении туда “Bitemporal Database Table Design”. На основе построенного хранилища требовалось построить Куб с историчностью. У такого куба два временных измерения, одно показывает бизнес дату, другое – транзакционную дату. Если говорить о процессе разработки, то было больно, даже сейчас больно, но что-то у нас получилось.
Суть “Bitemporal Database Table Design” заключается в том, что у каждой записи есть 4 дополнительных поля:
- Business_date_ from
- Business_date_to
- Transaction_date_from
- Transaction_date_to
Первые 2 содержат интервал бизнес дат — с какой и по какую дату значение оставалось неизменным. Вторые 2 поля содержат интервал транзакционных дат. В этом интервале хранится период в течение которого значение никто не поправлял (как вариант задним числом).
Одной из проблем при решении это задачи были исходные данные, точнее вид, в котором мы их получали. Данные приходили в виде ежедневных срезов. То есть, было поле Date, в котором хранилась дата на которую брались данные, на следующий день в поле Date было новое значение и данные либо менялись в этот день, либо нет. Требовалось объединить данные в периоды. Т.е. если, например, значение не менялось 3 дня подряд, то вместо 3 строчек надо сохранить одну, в которой вместо колонки DATA было бы две колонки BEGIN и END.
Данные до преобразования:
Данные после преобразования:
Я решил эту задачу через функции LAG и LEAD. Суть решения в том, что если значение не меняется 3 дня подряд, то дата из текущей строки, минус дата из предыдущей будет равна 1:
- Если данные идут подряд, то 12.01.2017 – 11.01.2017 = 1
- Если между данными есть разрыв, то 10.01.2017 – 03.01.2017 = 7
Отсюда:
SELECT * FROM ( SELECT Volume ,[Date] dt ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead FROM dbo.Test n ) m WHERE ( difLag > 1 OR difLag IS NULL ) OR ( difLead > 1 OR difLead IS NULL )
- Далее надо как-то сгруппировать этот результат, объединить пары для тех периодов, которые длились более одного дня и оставить те, период у которых длился один день. Группировку я сделал, пронумеровав строки и объединив четные с нечетными.
Итак, весь запрос:
WITH se AS ( SELECT * FROM ( SELECT Volume ,[Date] dt ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead FROM Test n ) m WHERE (difLag > 1 OR difLag IS NULL) OR (difLead > 1 OR difLead IS NULL) ) ,p1 AS --choose only one day periods (be careful) ( SELECT Volume ,dt AS VT_BEG ,dt AS VT_END FROM se WHERE NOT (isnull(difLag, - 1) = 1 OR isnull(difLead, - 1) = 1) ) ,p2 AS ( SELECT Volume ,CASE WHEN difLead IS NOT NULL AND (difLag IS NULL OR difLag > 1) THEN dt END AS VT_BEG ,CASE WHEN difLag IS NOT NULL AND (difLead IS NULL OR difLead > 1) THEN dt END AS VT_END ,row_number() OVER (ORDER BY Volume,dt) AS rn FROM se WHERE isnull(difLag, - 1) = 1 OR isnull(difLead, - 1) = 1 ) SELECT * FROM ( SELECT min(Volume) AS data ,min(VT_BEG) AS VT_BEG ,min(VT_END) AS VT_END FROM p2 GROUP BY (CASE WHEN rn % 2 = 0 THEN rn ELSE rn + 1 END) UNION ALL SELECT Volume,VT_BEG,VT_END FROM p1 ) g ORDER BY VT_BEG ,data
Итог:
В кабинете клиента, на которого я работаю, висит плакат с лозунгом. Этим лозунгом я решил назвать эту статью, так как, на мой взгляд, он, отчасти, объясняет причину тех трудностей, с которыми мне приходится сталкиваться. Проект ещё не закончен и я думаю, что всё самое интересное ещё впереди. С совещаниями я уже смирился и когда меня о чем-то спрашивают на них, порой на ум приходит фраза из КВН: “Молодец, задавай умные вопросы, получай глупые ответы…”, которая помогает мне собраться и попытаться ответить что-то вразумительное.
В данной статье я рассказал только о самых, на мой взгляд, интересных вещах, которые были в проекте. Кроме них было много рутины, споров и других, не столь оригинальных, решений. Надеюсь, что то, о чем я написал, будет интересным и полезным.