Введение

В этой статье я продолжу рассказ о своем опыте работы с Microsoft Analysis Services. В дополнение к предыдущей статье, я хочу написать про нестандартные решения, которые были сделаны в последнем проекте. Эти решения более тесно сблизили меня с Microsoft Analysis Services, я стал больше его уважать и делать с его помощью то, что ранее мне казалось невероятным.

Сумма средних за период

Заказчик просил сумму средних значений по каждому элементу за период, как показано на картинке ниже:

$display$

То есть идея в том, чтобы в разрезе элементов elem1, elem2, …, elemN куб считал среднее по каждому элементу за выбранный период, во всех остальных случаях считалась сумма. Требуется, как бы поменять поведение куба для одного измерения, а для всех остальных оставить стандартное (в описываемом случае стандартное поведение это СУММА).

Варианты решений:

Первый вариант:

Для этого варианта надо создать скрытое измерение [ELEM COPY], просто создав копию [ELEM] и поставив ему свойство Visible на False. Далее в разделе куба “Calculations” выбрать “New Calculated Member”, как показано на рисунке:

и в появившемся окне для ввода выражений написать:
```
iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)
```
, где [ELEM COPY].[ELEM SK] – это ключевой атрибут скрытого измерения.

[Measures].[FCT VAL] – это AvarageOfChildren агрегация, т.е. при сборке куба создали измерение типа AvarageOfChildren и он его автоматически рассчитал. Пример того как создать измерение с AvarageOfChildren агрегацией показан на рисунке ниже:
Решение с использованием SCOPE:
Это решение показал мне мой коллега, на мой взгляд, оно более простое для понимания, хоть и чуть сложнее в реализации.

Что надо сделать:
Переписать селект для таблицы фактов:

Вместо:
```
SELECT [DATE]
	,ID_CO
	,ID_CUST
	,ID_SYS
	,ID_VOL
	,ID_QUAL
	,GB_used_DATA
FROM [000_REP].NAS_FACTS
```
Написать:
```
SELECT [DATE]
	,ID_CO
	,ID_CUST
	,ID_SYS
	,ID_VOL
	,ID_QUAL
	,GB_used_DATA
	,CONVERT(VARCHAR(10), [DATE], 120) + '|' + CAST(ID_VOL AS VARCHAR(MAX)) AS VolDate
	,NULL AS Avg_GB_used_DATA
FROM [000_REP].NAS_FACTS
```
, где GB_used_DATA это факт, который мы хотим добавить в куб. Нестандартное поведение мы хотим сделать для измерения с ключом ID_VOL, для остальных измерений с ключами ID_CO, ID_CUST, ID_SYS и ID_QUAL поведение должно быть стандартным и в нашей задаче всё должно просто суммироваться. Только по ID_VOL должно считаться среднее за период по каждому элементу, а итог по ID_VOL элементам так же должен суммироваться, должна получиться сумма средних значений по ID_VOL.

Во втором запросе добавили 2 колонки:
- 1-ая колонка уникально определят связку даты с ID элементами, по которым надо считать сумму средних значений. В кубе по этой колонке добавляется измерение с функцией агрегации DistinctCount. Пример на рисунке ниже:
- 2-ая колонка всегда хранит значение NULL во всех строках таблицы. У второй колонки важно её название, оно нужно для того чтобы можно было создать измерение в кубе, к которому можно было бы привязаться с помощью функции SCOPE. Так же важно то, что для данной меры используется функция агрегации Sum. Пример ниже:
  
  Далее в разделе куба “Calculations” выбрать “Script View”, и в окне для редактирования скрипта надо вставить такой код:
```
SCOPE([Measures].[Avg GB Used DATA]);           
        SCOPE([ID_VOL Items].[ID VOL].[ID VOL].MEMBERS);       
            THIS = [Measures].[Sum GB Used DATA]/[Measures].[Vol Date  Distinct Count];       
        END SCOPE;         
END SCOPE;
```
  где [ID_VOL Items] это измерение, ключ у которого ID_VOL.
  
  На рисунке показана последовательность действий для этого шага:
  
  В этом решении мы суммируем только то выражение, которое прописано в SCOPE, так как без формулы в SCOPE у нас там хранится значение NULL, которое приходит из запроса к Базе Данных.

Оба решения дали один и тот же результат и на выходе считали сумму средних, что и требовалось в задании.

Statistical Average

Спустя какое-то время заказчик вернулся к теме подсчета средних величин. На этот раз, он потребовал не сумму средних, а классическое среднее, то есть тот же функционал, что и у функции AVERAGE в Excel. Заказчик постоянно оперировал понятием “Statistical Average”, отсюда название этой главы.

Требовалось рассчитать среднее значение по всему диапазону. Берётся среднее по всем элементам за каждый день, суммируется и делится на количество дней в периоде. В итоге получается усредненное значение на один элемент за период. Было предложено следующее решение:

CREATE MEMBER CURRENTCUBE.[Measures].[Avg GB Used DATA (AvgAll Only valid days)]
as 
[Measures].[Sum GB Used DATA]/[Measures].[Count VCMDB Only valid days],
VISIBLE = 1  ;

CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB Only valid days]
as 
Count(NonEmpty({crossjoin([DIM Business Time HD].[DAY].currentmember.Children,[DIM NAS  Provider Configuration Item HD].[NAS Volume CMDBID].currentmember.Children)}, [Measures].[Sum GB Free Data] )),
VISIBLE = 1  ;

В данном решении брались только дни, где были значения у элементов. Так же использовался трюк со скрытым измерением (это измерения [DIM Business Time HD].[DAY] и [DIM NAS Provider Configuration Item HD].[NAS Volume CMDBID]). Количество дней, когда были значения, я получил с помощью crossjoin.

Если же надо получить среднее по всем значениям за все дни, где отсутствие значения за какой либо день приравнивается к 0, то я использовал следующее выражение:

CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days]
as 
[Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count],
VISIBLE = 1  ;
CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days]
as 
[Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count],
VISIBLE = 1  ;

где [Measures].[NAS Volume CMDBID Distinct Count] и [Measures].[NAS BTIME Count] это меры куба, построенные по таблицам для измерений (временное измерение и измерение элемента):

Ещё одна полезная функция

В процессе работы с кубом выдвигались требования, в которых расчет значений менялся от того, на каком уровне иерархии его хотели видеть. То есть, если выбраны дни, то мы видим, среднее за период, если месяцы то сумму. Это было сделано с помощью функции level:

CREATE MEMBER CURRENTCUBE.[Measures].[ML]
as 
 case
     when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[YEAR] then 3
     when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[PERIOD KAL] then 2
     when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[DAY] then 1
     else 4
 end,
VISIBLE = 1;

Итог

Честно говоря, когда я видел требования по расчету средних значений, мне хотелось смеяться и плакать одновременно. Смеяться от того, что порой среднее значение было либо больше всех значений в выбранном диапазоне (как в примере с суммой средних), либо меньше. Случай с меньше я не стал описывать в статье, так как не смог понять смысл — заказчик требовал делить значение за каждый день на количество дней в месяце. А плакать приходилось от того, что эти требования надо было как-то реализовывать.

Попытки ограничится стандартным AvarageOfChildren, которая суммирует все показатели за день, затем складывает эти суммы и делит на количество дней в периоде, успеха не имели. Заказчик настойчиво требовал соответствия реальности его картине мира. Все наши решения подвергались жесткому и скрупулёзному тестированию. Вопрос о доверии между нами и клиентом отпал на ранних этапах взаимодействия. Он постоянно искал подвоха и решение, вместо простого и интуитивно понятного, превратилось в нечто странное и монструозное. Но во всем есть своя польза. Благодаря таким требованиям, пришлось более детально разобраться с работой OLAP куба и научится работать с произвольными агрегациями. Чтобы этот опыт был не напрасен и помогал в будущем решать похожие задачи не только мне, я решил поделиться им с Вами. В общем, как в анекдоте про сантехников:

К сантехнику прикрепили практиканта. Вызывают на выезд. Приезжают. Канализационный люк. Из него течет дерьмо. Сантехник подходит к люку и ныряет.
Через минуту выныривает, кричит:
— Ключ на 19.
Снова ныряет. Через полминуты выныривает:
— Прокладку No.6.
Опять ныряет. Выныривает:
— Ключ на 26.
Ныряет. Через минуту выныривает. Выходит, отряхивается и закуривает. Сел, отдышался и говорит практиканту:
— Вот так!.. Учись, студент! А то так и будешь всю жизнь ключи подавать...

Дисциплина, точность, внимание к деталям, часть вторая (OLAP, SSAS)