Введение:

В этой статье речь пойдет о работе с Microsoft Analysis Services и немного о хранилище на Microsoft SQL Server, с которым SSAS работает. Мне пришлось столкнуться с не совсем тривиальными вещами и порой приходилось “прыгать через голову” ради того, чтобы сделать то, что от меня хотят. Работать приходилось в перерывах между совещаниями. Порой новый функционал обсуждался дольше, чем разрабатывался. Часто на совещаниях, по несколько раз, приходилось рассказывать одно и тоже. Когда я сказал, что мне сложно совещаться дольше одного часа, на меня посмотрели с удивлением и непониманием. Во многом, благодаря такой обстановке и появились эти, не совсем тривиальные вещи, о которых я решил написать.

Среднее за период

Требовалось посчитать среднее значение за период. В MS Analysis Services есть для этого функция Average of Children, которая считает среднее по всем не пустым значениям за выбранный период.



Но у заказчика, после скрупулёзного изучения результатов, появились вопросы? Он показал мне следующую выборку, и сказал что она не правильная:


Так как, по его мнению, должно быть так:


На мой вопрос: Почему? Он ответил, что ему нужно не среднее значение за период, а сумму средних значений по каждому элементу за период, то есть

НЕ ТАК:


( 5 + 6 + 7 ) / 3 = 6

А ВОТ ТАК:


( 2,5 + 3,5 + 3 ) = 9

Это желание заставило меня пройти все стадии принятия неизбежного:

1. Отрицание (Это что угодно, но не среднее за период);
2. Гнев (Кто его математике учил?);
3. Торг (Давайте оставим так и спросим тех, кто будет этим пользоваться?);
4. Депрессия (А говорили, что здесь все очень добрые и хорошие…);
5. Принятие ( Можно и зайца курить научить. Надо так надо, сделаю, как попросили).

Решение было не совсем очевидным. В разделе 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 дополнительных поля:

1. Business_date_ from
2. Business_date_to
3. Transaction_date_from
4. 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

Отсюда:

1. 	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 )
   

   
   
2. Далее надо как-то сгруппировать этот результат, объединить пары для тех периодов, которые длились более одного дня и оставить те, период у которых длился один день. Группировку я сделал, пронумеровав строки и объединив четные с нечетными.
   
   Итак, весь запрос:
   
   

   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
   

Итог:

В кабинете клиента, на которого я работаю, висит плакат с лозунгом. Этим лозунгом я решил назвать эту статью, так как, на мой взгляд, он, отчасти, объясняет причину тех трудностей, с которыми мне приходится сталкиваться. Проект ещё не закончен и я думаю, что всё самое интересное ещё впереди. С совещаниями я уже смирился и когда меня о чем-то спрашивают на них, порой на ум приходит фраза из КВН: “Молодец, задавай умные вопросы, получай глупые ответы…”, которая помогает мне собраться и попытаться ответить что-то вразумительное.

В данной статье я рассказал только о самых, на мой взгляд, интересных вещах, которые были в проекте. Кроме них было много рутины, споров и других, не столь оригинальных, решений. Надеюсь, что то, о чем я написал, будет интересным и полезным.