Работа с ключевыми показателями эффективности в SQL Server Analysis Services. Часть 1.

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

В состав SQL Server Analysis Services входят средства централизованного ведения KPI (ключевых показателей эффективности) и их визуализации в виде скоркарды (системы оценочных индикаторов). В данной статье мы разберем их устройство и основные функции по работе с ними.

Для практических упражнений возьмем текущую версию Microsoft SQL Server 2008 R2 редакции не ниже Standard, т.к. будем работать с OLAPовским кубиком. Developer Edition из MSDN по функциональности = Enterprise Edition, т.е. тоже подойдет. Чтобы не создавать кубик с нуля, используем готовый пример в виде многомерной базы Adventure Works DW 2008R2, который можно взять здесь, и установим его в соответствии с инструкцией.

Запустим SQL Server Business Intelligence Development Studio (в группе программ SQL Server) и откроем (File -> Open -> Analysis Services Database) многомерную базу Adventure Works DW 2008R2. Кликните два раза по кубу Adventure Works в панели Solution Explorer (обычно справа) и встаньте на закладку KPI (вверху). KPI появились в Analysis Services, начиная с SQL Server 2005. Соответственно, в языке MDX появился оператор CREATE KPI. По своей природе это обычные меры, только, помимо абсолютного численного значения (Value), они имеют еще связанные с ними количественные атрибуты. Наиболее часто используются величины Goal, Status и Trend. Из названий понятно, что Goal - то значение, которое стремимся достичь, Status - насколько хорошо мы его достигли, а Trend - как изменилась динамика поведения по сравнению с предыдущим периодом. Они задаются при помощи обычных MDX-выражений. Откройте для примера KPI под названием Product Gross Profit Margin и посмотрите, как для него считаются эти характеристики.

Рис.1

Обратите внимание, что значения Status и Trend обычно носят дискретный характер, например, -1, 0, 1, т.к. предназначены для условной визуализации их клиентским приложением. Например, увидев -1 в качестве статуса, клиентское приложение воспримет это как сигнал того, что с данным KPI все плохо и отрисует красный светофорчик в скоркарде, если 0 - желтый, если 1 - зеленый. Аналогично, -1 в качестве тренда символизирует ниспадающую стрелочку, 0 - ровную, 1 - возрастающий тренд. Количество градаций и их значения никак не ограничиваются Analysis Services. Мы могли бы возвращать, скажем, 0, 1, 2, где 0 означает "все плохо", 2 - "все хорошо" или, напротив, 2 может означать, что "все плохо", 0 - что "все хорошо". Это предмет договоренности с клиентским приложением. То же самое справедливо относительно количества градаций. Набор возвращаемых значений не обязательно должен быть ограничен 3-мя. Статус или тренд может принимать, например, значения 0, 1, 2, 3, 4, 5 или -1, -0.5, 0, 0.5, 1 и т.д.

Пусть вас не смущает Description для значения, статуса и тренда KPI в соответствующих функциях (см. Скрипт 1, 2 следующего поста), что возвращаемый результат должен укладываться в интервал от -1 до 1. Это влияет только на отображения KPI в BIDS. Обратите внимание на Рис.1, что для статуса и тренда можно задавать, какими значками они должны отображаться -

 

Рис.2

Эти значки используются при просмотре KPI в BIDS. Нажмите на кнопку Browser View в панели инструментов, чтобы перейти из режима редактирования KPI в просмотр скоркарды:

 

Рис.3

Встроенные значки действительно откалиброваны под диапазон [-1; 1]. Если при редактировании KPI зайти в выражение Status (Рис.2), поставить в качестве возвращаемых значений вместо 1, 0, -1, скажем, 20, 15, 10 и сохранить, это не вызовет ошибки, но на Рис.3 статус всегда будет зеленым, т.к. он полагает, что все, что >= 1, - это супер. Что касается клиентского приложения, это предмет договоренности с клиентским приложением. главное, чтобы оно понимало, какую иконку когда ему рисовать на основе полученного значения статуса или тренда. Верните возвращаемые статусные значения для KPI обратно в 1, 0, -1.

Для запроса текущего значения, цели, статуса и тренда KPI существуют функции, соответственно, KPIValue, KPIGoal, KPIStatus, KPITrend. Предположим, мы хотим нарисовать скоркарду, показывающую эффективность продаж в разрезе по продуктам за период. Cлева будет колонка с измерением Product, а справа - абсолютные цифры продаж и KPI под названием Product Gross Profit Margin (рентабельность валовой прибыли по данному товару). Мы его видели на Рис.1. Простейший MDX-запрос, который возвратит нам необходимую для отрисовки информацию, будет выглядеть примерно так:

select

{

 Measures.[Internet Sales Amount], KpiValue("Product Gross Profit Margin")

                                 , KpiGoal("Product Gross Profit Margin")

                                 , KpiStatus("Product Gross Profit Margin")

                                 , KpiTrend("Product Gross Profit Margin")

} on 0

, non empty Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER) on 1

from [Adventure Works]

where [Date].Calendar.[Calendar Year].[CY 2008]

Скрипт 1

Функция Descendants в данном случае возвращает по второй оси всех потомков всех членов уровня Category иерархии Product Categories измерения Product вплоть до листового уровня. Это эквивалентно множеству всех членов измерения, от которого отняли верхний член: Except(Product.[Product Categories].Members, Product.[Product Categories].[All Products]).

 

Рис.4

 

Помимо наиболее часто используемых значения, цели, статуса и тренда, у KPI в Analysis Services имеются дополнительные свойства, которые можно посмотреть, например, в Books On-Line, в разделе документации по ASSL (Analysis Services Scripting Language - XML-язык, при помощи которого описывается структура объектов многомерной базы). Внутри элемента Kpi каждому свойству соответствует свой дочерний элемент. Например, здесь присутствуют уже знакомые нам элементы Value, Goal, Status и Trend. Существуют и другие свойства, не столь хорошо документированные.

По названию свойства CurrentTimeMember можно было бы предположить, что оно играет роль Default Member для KPI, т.е. с каким элементом по какому измерению, выполняющему роль времени, пересекается данный KPI, если элемент времени не задан явно. У каждого атрибута каждого измерения, или Attribute Hiearchy, существует член по умолчанию (Рис.5), который участвует в запросе, если данный атрибут в нем явно не фигурирует.

 

Рис.5

По умолчанию, член по умолчанию равен служебному члену (All), если данный атрибут является агрегируемым (св-во атрибута IsAggregatable = True). В противном случае член по умолчанию назначается произвольно, если не задан явно в св-ве DefaultMember (Рис.5). Посмотреть член по умолчанию можно при помощи функции DefaultMember:

with member Measures.aaa as [Date].[Calendar].DefaultMember.MEMBER_UNIQUE_NAME

select Measures.aaa on 0 from [Adventure Works]

Скрипт 2

 

Рис.6

В данном случае атрибут Month Name спрятан (AttributeHierarchyVisible = false), он доступен через иерархию Calendar, поэтому в запросе в качестве аргумента ф-ции DefaultMember фигурирует Calendar.

Если убрать указание периода, т.е. условие where из запроса Скрипт 1, это будет означать неявную подстановку [Date].[Calendar].[All Periods] (DefaultMember). Можно сравнить результаты

select

{

 Measures.[Internet Sales Amount], KpiValue("Product Gross Profit Margin")

                                 , KpiGoal("Product Gross Profit Margin")

                                 , KpiStatus("Product Gross Profit Margin")

                                 , KpiTrend("Product Gross Profit Margin")

} on 0

, non empty Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER) on 1

from [Adventure Works]

Скрипт 3

и

select

{

 Measures.[Internet Sales Amount], KpiValue("Product Gross Profit Margin")

                                 , KpiGoal("Product Gross Profit Margin")

                                 , KpiStatus("Product Gross Profit Margin")

                                 , KpiTrend("Product Gross Profit Margin")

} on 0

, non empty Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER) on 1

from [Adventure Works]

where  [Date].[Calendar].[All Periods]

Скрипт 4

чтобы убедиться, что они идентичны:

Рис.7

CurrentTimeMember у KPI изначально равен null. Было бы логично ожидать, что в случае его изменения непустоевыражение члена будет подставляться для KPI по образу where в Скрипте 4. Опустимся на Рис.1 донизу и кликнем на стрелочку, чтобы раскрыть Additional Properties. Внесем какой-нибудь член измерения Date, например, [Date].[Calendar].[Month].[January 2007], в качестве Current time member:

Рис.8

Нажмем кнопку Save. Обратите внимание, как заметно долго происходит процесс сохранения измененного KPI. Это потому, что на данный момент в Analysis Services нет возможности отдельного сохранения KPI. KPI сохраняется вместе со всей структурой куба целиком. Повторяем запрос Скрипт 3 и видим, что несмотря на сделанные изменения в Current time member результаты не изменились. Свойство CurrentTimeMember не применяется автоматически в запросах. Его нужно достать из определения KPI и задействовать в запросе самостоятельно - см., напр., Does "Current time member" on the KPIs designer do anything? (http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b28b41b4-4165-42c6-9450-bc8f723303fa/). Для его получения используется функция KpiCurrentTimeMember. Пример:

with member Measures.aaa as KpiCurrentTimeMember("Product Gross Profit Margin").MEMBER_UNIQUE_NAME

select Measures.aaa on 0 from [Adventure Works]

Скрипт 5

 

Рис.9

 

Пример фильтрации на основе KpiCurrentTimeMember - добавляем функцию в where по образу Скрипта 4:

select

{

 Measures.[Internet Sales Amount], KpiValue("Product Gross Profit Margin")

                                 , KpiGoal("Product Gross Profit Margin")

                                 , KpiStatus("Product Gross Profit Margin")

      , KpiTrend("Product Gross Profit Margin")

} on 0

, non empty Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER) on 1

from [Adventure Works]

where  KpiCurrentTimeMember("Product Gross Profit Margin")

Скрипт 6

Теперь результаты отражают срез на период [Date].[Calendar].[Month].[January 2007].

Следует обратить внимание, что в свойстве CurrentTimeMember сохраняется именно член измерения как объект, а не строковая формула его вычисления, оцениваемая всякий раз динамически при выполнении запроса. Именно поэтому в CurrentTimeMember можно класть только статическое выражение члена, как на Рис.8. Предположим, мы хотим получить результат, аналогичный Рис.4, только в каждой строчке должны браться данные за последний непустой месяц, когда были продажи данного товара. Это решается MDX-запросом

with

member [Date].Calendar.LastNonEmptyMonth as Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Internet Sales Amount]), 1).Item(0).Item(0)

select {Measures.[Internet Sales Amount], KpiValue("Product Gross Profit Margin")} on 0,

non empty Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER) on 1

from [Adventure Works]

where [Date].Calendar.LastNonEmptyMonth

Скрипт 7

Пояснения. Вычисляемый член по измерению даты [Date].Calendar.LastNonEmptyMonth означает последний непустой месяц, за который были продажи того или иного продукта. На выходе функции nonempty получается набор непустых кортежей. Функция Tail(..., 1)возвращает множество из одного последнего кортежа. Первый Item(0) выбирает первый элемент этого множества, т.е. на выходе уже не множество из одного кортежа, а сам этот кортеж Наконец, второй Item(0) возвращает элемент по первому измерению, участвующему в этом кортеже, т.е. дату.

 

Рис.10

 

Однако если поместить выражение Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Internet Sales Amount]), 1).Item(0).Item(0) в Current time member (Рис.8), сохранить и попробовать выполнить запрос Скрипт 6, получится пустота.

Домашнее задание: найдите ошибку :)

 

Во второй части мы познакомимся с остальными свойствами объекта KPI в Analysis Services.

 

 

Алексей Шуленин