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

Продолжение. Ссылка на первую часть.

В нашем примере слева в скоркарде находятся товары, упорядоченные в соответствии с определенной на измерении продуктовой иерархией (категория - подкатегория - продукт), справа в каждой строчке скоркарды товару соответствует KPI Product Gross Profit Margin (рентабельность валовой прибыли по данному товару). Однако сами по себе KPI можно также упорядочивать в иерархию. Например, выручка, чистый доход, операционная прибыль и т.п. могут входить в родительский KPI под названием "Финансы"; удовлетворенность клиентов, рост клиентской базы и т.п. - в "Клиенты" и т.д. - см. Рис.3 предыдущего поста. Простой способ это сделать - завести новое измерение, расставив в котором члены-названия KPI в соответствии с желаемым порядком и подчиненностью. Однако KPI обладает интересными дополнительными свойствами - см. Рис.8 предыдущего поста. Parent KPI, по идее, предназначен для выстраивания KPI в иерархию, а Weight, по идее, задает весовой коэффициент, с которым данный KPI входит в своего родителя. Я говорю "по идее", потому что, к сожалению, эти полезные свойства в Books On-Line документированы еще беднее, чем разбиравшийся нами перед этим CurrentTimeMember, т.е. вообще никак. Посмотрите на ASSL, соответствующий элементу Kpi - http://msdn.microsoft.com/ru-ru/library/ms126672.aspx. Ни Parent KPI, ни Weight там не то, что не описаны, а вообще не упомянуты, и напоминают суслика, которого не видно, но он, тем не менее, есть. Чтобы в этом убедиться, задайте в редакторе KPI в BIDS произвольные непустые значения для Parent KPI и Weight:

Рис.1

и сохраните (как мы помним, изменения KPI сохраняются целиком со структурой куба, в чем можно убедиться в профайлере). Теперь ступайте в SSMS, кликните правой кнопкой по кубику Adventure Works и скажите заскриптовать его. В создавшемся DDL-скрипте найдите KPI Product Gross Profit Margin, а в нем - элементы <Weight> и <ParentKpiID>. Вот он, суслик:

Рис.2

По аналогии со свойствами Value, Goal, Status,Trend, CurrentTimeMember для свойства Weight в MDX имеется функция KpiWeight. Для свойства ParentKpiID никакой функции не имеется. Сначала я подумал, что в Books On-Line ее просто забыли упомянуть. Те из читателей, кто сочли возможным потратить время на чтение блог-постов "Введение в SQL Server Analysis Services для разработчика. Метод Discover" и "Введение в SQL Server Analysis Services для разработчика. OLAP DMV ч.1" знают, что получить список всех доступных в MDX функций можно XMLA-запросом

<RequestType>MDSCHEMA_FUNCTIONS</RequestType>

</Discover>

Скрипт 1

Рис.3

а, начиная с версии 2008, еще и соответствующим ему DMX-запросом

select * from $system.MDSCHEMA_FUNCTIONS

Скрипт 2

Рис.4

Мы видим, что функции KpiParentKpiID все-таки нет. То есть записать Parent KPI можно (см. Рис.1), но как его прочитать из клиентского приложения? На помощь приходит еще один схемный роусет - MDSCHEMA_KPIS:

select * from $system.MDSCHEMA_KPIS where [CATALOG_NAME] = 'Adventure Works DW 2008R2' and CUBE_NAME = 'Adventure Works' order by KPI_NAME

Скрипт 3

Рис.5

Смотрите, как интересно! Оказывается, в кубе Adventure Works есть еще меры [Measures].[Product Gross Profit Margin Goal], [Measures].[Product Gross Profit Margin Status], [Measures].[Product Gross Profit Margin Trend] и др. А мужики-то не знают :( Каждому KPI соответствуют меры для его цели, статуса, тренда, но в списке мер:

select * from $system.MDSCHEMA_MEASURES order by MEASURE_UNIQUE_NAME

Скрипт 4

их нет.

Как мы проходили в посте "Введение в SQL Server Analysis Services для разработчика. Ограничения Discover", для некоторых схемных роусетов есть ограничения, которые неявно включаются в запрос со своими значениями по умолчанию. Например, из документации известно, что ограничение MEASURE_VISIBILITY участвует в Discover-запросе MDSCHEMA_MEASURES со значением по умолчанию 1, что означает видимые меры. Однако, в результатах Скрипта 4 присутствуют строки как с полем MEASURE_IS_VISIBLE = true, так и false. На всякий случай можно задать MEASURE_VISIBILITY явно в запросе (3 = 2 or 1, т.е. показывать и видимые, и невидимые):

<RequestType>MDSCHEMA_MEASURES</RequestType>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_VISIBILITY>3</MEASURE_VISIBILITY>

</RestrictionList>

</Restrictions>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Discover>

Скрипт 5

и опять ничего похожего на Product Gross Profit Margin Goal, Product Gross Profit Margin Status и др.

Тем не менее, такие меры существуют, в чем легко убедиться, модифицировав запрос Скрипт 1 из предыдущего поста. Меры, соответствующей колонке KPI_VALUE (Рис.5), нет. Как и KPIGoal(), KPIStatus(), ..., ее можно получить при помощи вспомогательной функции KPIValue(), либо вытащить строкой из колонки KPI_VALUE схемного роусета MDSCHEMA_KPIS и оценить выражение при помощи функции StrToValue().

select

{

Measures.[Internet Sales Amount]

, Measures.[Product Gross Profit Margin Goal]

, Measures.[Product Gross Profit Margin Status]

, Measures.[Product Gross Profit Margin Trend]

} 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]

Скрипт 6

Рис.6

Это все, конечно, очень любопытно, но не отвечает на поставленный вопрос о получении Parent KPI. В схемном роусете MDSCHEMA_KPIS (Скрипт 3) нет отведенной для него колонки. Кажется, это объясняет, почему и соответствующей ему функции в MDX не предусмотрено.

По-видимому, единственным способом достучаться до ParentKpiID является читать определения KPI целиком в составе куба при помощи Discover-запроса DISCOVER_XML_METADATA:

<RequestType>DISCOVER_XML_METADATA</RequestType>

<DatabaseID>Adventure Works DW 2008R2</DatabaseID>

<CubeID>Adventure Works</CubeID>

<ObjectExpansion>ObjectProperties</ObjectExpansion>

</RestrictionList>

</Restrictions>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Discover>

Скрипт 7

Рис.7

Возвращаемый результат не может быть уплощен малой кровью, поэтому данному Discover-запросу нет соответствия в виде оператора select. Результат возвращается в виде здоровенного XML, описывающего структуру целиком куба. Конечно, такой способ легковесным не назовешь, но, с другой стороны, при cохранении изменений в каком-нибудь отдельном KPI также переписывается структура всего куба. Приложение должно распарсить полученный XML, элемент return\root\row\xars:METADATA\Cube\Kpis, отыскать нужный элемент Kpi и вытащить из него ParentKpiID. Там же, в элементе Kpi, как можно видеть, находятся определения выражений Goal, Status, Trend и др., что иногда бывает нужно, т.к. фиктивные меры (Скрипт 6), равно как и MDX-функции интерфейса KPI (Рис.4, Скрипт 1 пред.поста) дают только значения.

Свойства KPI StatusGraphic и TrendGraphic (см. Рис.2 предыдущего поста), как легко догадаться из названия, задают графические изображения, которыми символически отрисовываются статус и тренд того или иного KPI в режиме просмотра скоркарды в BIDS (см. Рис.3 предыдущего поста).Их значения можно видеть в запросе Скрипт 3: Traffic Light, Road Signs, Cylinder; Standard Arrow, Status Arrow Ascending/Descending и т.д. По идее, они призваны централизовать и унифицировать интерфейс отображения KPI в клиентском приложении, которое может считать эти свойства и выбрать соответствующее изображение. Схожее назначение мы могли видеть еще до SQL Server 2005 у свойств вычисляемого члена. Например, можно задать динамическое MDX-выражение в свойство FORE_COLOR, чтобы [Gross Profit Margin] отрисовывалась зеленым цветом, если она положительна, и красным в противном случае:

iif(Measures.[Gross Profit Margin] > 0, rgb(0, 255, 0), rgb(255, 0, 0))

Скрипт 8

Рис.8

MDX-запрос возвращает свойства ячейки на клиента при помощи предиката CELL PROPERTIES:

select Measures.[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]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS

Скрипт 9

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

Рис.9

При выполнении запроса средствами ADOMD.NET свойства каждой ячейки селлсета доступны в одноименных свойствах объекта Cell: ForeColor, BackColor, FontName, FontSize и т.д. Если приложение выполняет MDX-запрос как XMLA Execute и хочет само парсить возвращенный в виде XML селлсет, то свойства каждой ячейки передаются в виде ее дочерних элементов. Пример:

select Measures.[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]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS

</Statement>

</Command>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Execute>

Скрипт 10

Рис.10

То же касается свойств KPI StatusGraphic и TrendGraphic. Клиентское приложение в результате запроса может получить рекомендации OLAP-сервера по отображению того или иного KPI, но это никоим образом не ограничивает применять свои предпочтительные способы визуализации.

В следующей части Мерлезонского балета мы, обогатившись знаниями об устройстве KPI в кубе, нарисуем донельзя красивую скоркарду в виде отчета в Reporting Services.

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

Last updated on 2010-12-29

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

Additional resources