Иерархии PowerPivot на примере иерархии дат

Иерархии PowerPivot, что говорит нам справка об иерархиях – это доступный для просмотра список, коллекции столбцов, которая создается в виде дочерних уровней для размещения в любом порядке в иерархии. Запутанно? Немного, но что бы было понятнее в иерархию можно объединить по определенному признаку столбцы списка, что позволит улучшить представление данных.

Немного теории

Некоторые справочники организованны иерархический, одним из наиболее часто используемых иерархических справочников является справочник ОКАТО. В нем на каждую строку справочника приходится три уровня классификации, при этом можно четко определить принадлежность того или иного уровня другой записи справочника, к первому уровню классификации справочника относятся республики, края, города федерального подчинения, ко второму уровню классификации автономные округа, районы, города к третьему уровню городские районы, поселки городского типа, сельсоветы.

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

В SQL Server Analysis Service есть отдельная функция позволяющая создать меру дат. В PowerPivot можно только отметить таблицу как «таблицу дат» что только добавит множество фильтров в сводной таблице, но создать меру дат как в SSAS нельзя. Но ни чего невозможного не бывает, такую таблицу можно легко создать через запрос к источнику данных и функции DAX.

Для начала нам надо создать таблицу, содержащую определенный диапазон дат. Сделать это можно двумя способами, выполнить отдельный запрос к таблице фактов источника данных или выполнить SQL запрос возвращающий набор данных. В первом случае мы получим только даты, которые есть в таблице фактов, во втором случае получим все даты периода. С точки зрения оптимальности хранения данных первый вариант лучше, так как будут исключены даты, которые не встречаются в таблице фактов.

Первые шаги и данные

Откроем PowerPivot и на вкладке «Конструктор» нажмем кнопку «Существующие соединения».

Мы уже импортировали данные из источника, по этому выбираем «Существующие соединения» если бы нажали «Из базы данных» на главной вкладке ленты инструментов, то в существующих соединениях у нас появилось бы новое соединение с базой данных, даже не смотря на то что использовали бы ту же самую базу данных.

Два раза щелкаем мышкой на имени необходимого соединения или нажимаем кнопку «Открыть» и на следующем шаге выбираем «Написать запрос, указывающий данные импорта».

В источнике данных необходимая таблица называется «FactSales» а поле, которое мы хотим анализировать, как дату называется «DateKey», соответственно наш запрос будет выглядеть следующим образом:

select DateKey from FactSales group by DateKey

Вводим инструкцию SQL, указываем понятное имя для запроса и нажимаем кнопку «Готово». У нас в таблицах появится результат запроса содержащий необходимые нам данные. Остается только связать результат запроса с таблицей фактов «FactSales». Переходим в представление диаграммы и перетаскиванием поля из таблицы дат устанавливаем необходимую связь.

Так мы определили новую таблицу и установили связь между нашей новой мерой и таблицей фактов.

Если бы мы хотели получить в таблице дат диапазон, например с 1 января 2015 по 31 декабря 2016 можно было бы в запросе импорта написать, такой запрос:

WITH Days(D) AS

(

 SELECT CONVERT(smalldatetime, ‘01.01.2015’) WHERE CONVERT(smalldatetime,’01.01.2015′) <= CONVERT(smalldatetime,’31.12.2016′)

 UNION ALL

 SELECT DATEADD(DAY,1,D) FROM Days WHERE D < CONVERT(smalldatetime,’31.12.2016′)

)

SELECT D FROM Days ORDER BY D

OPTION (MAXRECURSION 0);

Или его упрощенный вариант с использованием переменных начальной и конечной даты.

DECLARE @Begin SMALLDATETIME, @End SMALLDATETIME;

SET @Begin = CONVERT(SMALLDATETIME, ‘01.01.2015’);

SET @End = CONVERT(SMALLDATETIME, ‘31.12.2016’);

WITH Days(D) AS

(

 SELECT @Begin WHERE @Begin <= @End

 UNION ALL

 SELECT DATEADD(DAY,1,D) FROM Days WHERE D < @End

)

SELECT D FROM Days ORDER BY D

OPTION (MAXRECURSION 0);

Обратите внимание, что приведенный пример выбора диапазонных дат характерен для SQL Server и ряда других серверов баз данных.

Вычисляемые поля для иерархии дат

Теперь введем в нашу таблицу вычисляемые поля рассчитывающие следующие значения год-полугодие-квартал-месяц-номер недели-день недели. В таблице поле даты называется «DateKey», соответственно во всех формулах вычисляемых полей будет использоваться это имя. После ввода формулы каждое вычисляемое поле будем сразу переименовывать для наглядности.

Функция DAX для года:

=YEAR([DateKey])&» год»

Функция DAX для полугодия:

=IF(MONTH([DateKey])>=1 && MONTH([DateKey])<=6;»1 полугодие «&[Год]&»а»;»2 полугодие «&[Год]&»а»)

Функция DAX для квартала:

=IF(MONTH([DateKey])>=1 && MONTH([DateKey])<=3;»1 квартал «&[Год]&»а»;IF(MONTH([DateKey])>=4 && MONTH([DateKey])<=6;»2 квартал «&[Год]&»а»;IF(MONTH([DateKey])>=7 && MONTH([DateKey])<=9;»3 квартал «&[Год]&»а»;»4 квартал «&[Год]&»а»)))

Функция DAX для месяца:

=IF(MONTH([DateKey])=1;»Январь «&[Год]&»а»;IF(MONTH([DateKey])=2;»Февраль»&[Год]&»а»;IF(MONTH([DateKey])=3;»Март «&[Год]&»а»;IF(MONTH([DateKey])=4;»Апрель «&[Год]&»а»;IF(MONTH([DateKey])=5;»Май «&[Год]&»а»;IF(MONTH([DateKey])=6;»Июнь «&[Год]&»а»;IF(MONTH([DateKey])=7;»Июль «&[Год]&»а»;IF(MONTH([DateKey])=8;»Август «&[Год]&»а»;IF(MONTH([DateKey])=9;»Сентябрь «&[Год]&»а»;IF(MONTH([DateKey])=10;»Октябрь «&[Год]&»а»;IF(MONTH([DateKey])=11;»Ноябрь «&[Год]&»а»;»Декабрь «&[Год]&»а»)))))))))))

Функция DAX для номера недели:

=WEEKNUM([DateKey];2)&» неделя «&LOWER([Месяц])

Функция DAX для дня недели:

=IF(WEEKDAY([DateKey];2)=1;»Понедельник»;IF(WEEKDAY([DateKey];2)=2;»Вторник»;IF(WEEKDAY([DateKey];2)=3;»Среда»;IF(WEEKDAY([DateKey];2)=4;»Четверг»;IF(WEEKDAY([DateKey];2)=5;»Пятница»;IF(WEEKDAY([DateKey];2)=6;»Суббота»;»Воскресенье»))))))

Несколько замечаний: в формулах максимально использовались значения вычисляемых столбцов определенных ранее. Функции WEEKNUM и WEEKDAY принимают второй параметр, определяющий, что неделя начинается с понедельника (1 день) и заканчивается воскресеньем (7 день), если не указать параметр со значением 2, будут приниматься что неделя начинается с воскресенья. Функция LOWER приводит все символы в нижний регистр.

Итак, у нас получилась такая таблица:

Определяем иерархию

Переходим в представление диаграммы и в нашей таблице нажимаем кнопку «создать иерархию».

Назовем ее «Полная» и последовательно перетащим мышкой каждое поле на название нашей иерархии, в результате чего получим:

Посмотрим теперь что у нас получилось в сводной таблице. В сводной таблице в качестве значений выберем поле «TotalCost» таблицы «FactSales», а в «Метки строк» выберем поле «Полная» таблицы «Мера дат». Поле «Полная» в этом случае иерархическое, что можно понять по дополнительному символу слева от названия поля.

Теперь в сводной таблице мы будем видеть не простые даты, а привычную иерархию дат.

PowerPivot поддерживает создание нескольких иерархий в одной таблице. Вернемся представление диаграммы таблицы «Мера дат» и создадим новую иерархию «Год-квартал-месяц».

Теперь используя эту иерархию в сводной таблице получим более простую иерархию значений, состоящую только из трех уровней.

Последнее замечание, в вычисляемых столбцах намеренно определялось полное значение вроде «30 неделя сентябрь 2009 года». В этой модели данных пользователю оставляется возможность проводить анализ по отдельным полям, те вместо иерархии в строках или столбцах можно выбрать отдельный элемент даты, если определять значение, например месяца как «Январь» то это значение будет вычислено для любого года и при анализе пользователи увидят не отдельно январь определенного года, а агрегированные значения за январи всех лет. Можно определить для иерархии отдельные вычисляемые поля, объединить их в иерархии, а сами поля скрыть из набора клиентских средств, можно оставить только иерархии убрав вычисляемые поля, тогда можно отказаться от вычисления даты в полном объеме. В любом случае поведение определяет разработчик исходя из текущих потребностей для анализа.