Работа с ключевыми показателями эффективности в 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-запросом
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_FUNCTIONS</RequestType>
<Restrictions />
<Properties />
</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, т.е. показывать и видимые, и невидимые):
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_MEASURES</RequestType>
<Restrictions>
<RestrictionList>
<CUBE_NAME>Adventure Works</CUBE_NAME>
<MEASURE_VISIBILITY>3</MEASURE_VISIBILITY>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<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:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList>
<DatabaseID>Adventure Works DW 2008R2</DatabaseID>
<CubeID>Adventure Works</CubeID>
<ObjectExpansion>ObjectProperties</ObjectExpansion>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<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 селлсет, то свойства каждой ячейки передаются в виде ее дочерних элементов. Пример:
<Execute xmlns='urn:schemas-microsoft-com:xml-analysis'>
<Command>
<Statement>
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>
<Properties>
<PropertyList>
<Catalog>Adventure Works DW 2008R2</Catalog>
</PropertyList>
</Properties>
<Parameters/>
</Execute>
Скрипт 10
Рис.10
То же касается свойств KPI StatusGraphic и TrendGraphic. Клиентское приложение в результате запроса может получить рекомендации OLAP-сервера по отображению того или иного KPI, но это никоим образом не ограничивает применять свои предпочтительные способы визуализации.
В следующей части Мерлезонского балета мы, обогатившись знаниями об устройстве KPI в кубе, нарисуем донельзя красивую скоркарду в виде отчета в Reporting Services.
Алексей Шуленин