Введение:


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

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


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

image

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

image

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

image

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

НЕ ТАК:

image

( 5 + 6 + 7 ) / 3 = 6

А ВОТ ТАК:

image

( 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] это его ключ.

Результат оказался неожиданным:

image

Странные цифры в Итогах оказались суммами всех значений элементов в измерении, не зависимо от того, какие значения я выбирал в фильтре. То есть в итоге всегда была сумма всех элементов, а не только выбранных.

Проблема решилась не сразу. Первым решением был вариант с дополнительным скрытым измерением. Я создал копию измерения [ELEM], изменил свойство Visible на False и написал так:

 iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)

Получилось так, как хотел заказчик.

image

Чуть позже я нашёл “более правильное” решение. Я создал 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, но мое решение он попросил сохранить.

Ещё, он попросил добавить среднее за период с учетом всех дней, а не только тех, в которых есть значения.

image

( 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.

Данные до преобразования:

image

Данные после преобразования:

image

Я решил эту задачу через функции LAG и LEAD. Суть решения в том, что если значение не меняется 3 дня подряд, то дата из текущей строки, минус дата из предыдущей будет равна 1:

image

  • Если данные идут подряд, то 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
    

Итог:


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

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

Комментарии (6)


  1. Ananiev_Genrih
    06.03.2017 12:55
    +2

    средняя по средней- какая прелесть))
    Особенно приятно удивление бизнеса из серии: "а шо это?" когда он (бизнес) начинает менять очередность измерений в заголовках строк сводной и видит изменение этой "средней".


  1. alex_29
    06.03.2017 13:11

    У них большое совещание было, когда я это сделал. Не знаю, может специально решили мне накинуть задачку, думали не справлюсь. А когда я сделал как они хотели, то уже им пришлось думать, а не мне им объяснять.:)


  1. ikovrigin
    09.03.2017 10:58
    +1

    Последнее преобразование у меня не работает. Формирую тестовую таблицу:
    SELECT * INTO Test FROM (Values (1,'2017-01-01'),(2,'2017-01-02'),(3,'2017-01-03'),(3,'2017-01-04'),(3,'2017-01-05'),(4,'2017-01-06'),(5,'2017-01-07'),(6,'2017-01-05')) AS t(Volume,Date)

    После работы преобразования получаю:
    data VT_BEG VT_END
    3 2017-01-03 2017-01-05


    1. alex_29
      09.03.2017 11:00

      Я поправил, спасибо за подсказку. Пример переделывал из рабочего пакета, недоглядел. Вообщем — "Поспешишь людей насмешишь!".


  1. alex_29
    13.03.2017 19:03

    Можете посмотреть execution plan на своем сервере. Итоговый запрос больше для примера, что идея, которую я описал работает. Это ответ на вопрос от AlexeyTopunov, случайно не в ту ветку попал.


  1. AlexeyTopunov
    13.03.2017 19:03
    +1

    А итоговый запрос не слишком сложный получился? Можно посмотреть execution plan?