Построение скоркарды средствами SQL Server Reporting Services 2008R2. Часть 1.
В двух предыдущих постах "Работа с ключевыми показателями эффективности в SQL Server Analysis Services", части 1 и 2, мы рассмотрели устройство KPI в Analysis Services. В данном посте будет показано, как построить отчет на основе этих показателей.
Обычно, если брать технологии Microsoft, термины скоркарда и KPI ассоциируются с покойным PerformancePoint Server 2007, он же PerformancePoint Services в SharePoint 2010. Вообще говоря, не совсем он же, но бюджетирование сейчас опускаем. PerformancePoint позволяет самостоятельно вести KPI, формируя их из различных источников. Однако, как пишут в толстых книжках, на предприятии должно иметься единое корпоративное хранилище, в которое заранее сведены разнородные источники, и KPI, по уму, должны строиться на основе централизованной картины. В этом случае SQL Server самодостаточен. Иногда, правда, жизнь вносит свои коррективы в стройную теорию, и сбоку от централизованного хранилища образуется какой-нибудь Access, куда аналитический отдел напрямую вносит готовые значения KPI на основе своих тайных знаний, бумажек с пометками и наколеночных Exceleй. Начальник нажимает кнопку, PerformancePoint радостно рисует заботливо вбитые KPI, и все счастливы, но мы читерством заниматься не будем. Если не брать продвинутые возможности типа сбалансированных скоркард с рисованием стратегии организации в Visio, SQL Server обладает возможностями по ведению KPI в Analysis Services, которые мы рассмотрели выше, и средствами их отображения в виде отчетов Reporting Services. В качестве шаблона отчета примем за основу рассмотренный ранее запрос, в результатах которого за заданный период слева выводится продуктовая иерархия, а справа - показатели по каждой продуктовой строчке: общая сумма продаж, отношение к суммарным продажам по родительской группе товаров и хорошо знакомый нам KPI Product Gross Profit Margin (рентабельность валовой прибыли по данному продукту). Запрашивающий необходимую информацию MDX выглядит примерно следующим образом
with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])
select
{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0
, nonempty(Descendants(Product.[Product Categories].Category.Members, , SELF_AND_AFTER), Measures.[Sales Amount]) on 1
from [Adventure Works]
where [Date].Calendar.[Calendar Year].[CY 2008]
Скрипт 1
Рис.1
По-моему, замечательно. Из этого получится превосходная скоркарда.
Запускаем Report Builder 3.0 (Start -> All Programs -> Microsoft SQL Server 2008 R2 Report Builder 3.0). Если он у вас еще не стоит, поставьте его скорее отсюда. Отказывайтесь от визардов, которые он сразу предлагает при входе. Визардами можно пользоваться потом для ускорения процесса, сначала желательно уяснить суть. Процесс начинаем с создания источника, то бишь соединения, с которого будут браться отображаемые в отчете данные. Слева должна находиться панель под названием Report Data. Если по каким-либо причинам она не показывается, в пункте меню View отметьте галку в чекбоксе Report Data. В панели Report Data кликните правой кнопкой на папку Data Sources и скажите Add Data Source:
Рис.2
Создайте обычное соединение к серверу Analysis Services, на котором в прошлом и позапрошлом постах рассматривались KPI. Shared connection подразумевает, что оно уже создано на сервере Reporting Services. Выбираем соединение, которое будет встроено в данный отчет:
Рис.3
Нажимаем кнопочку Build и задаем свойства многомерного соединения:
Рис.3
Теперь нужно определить, какие именно данные будут вытягиваться из источника в отчет. Аналогично добавьте новый Dataset (запрос) в Report Data.
Рис.4
Нажмите на кнопочку Query Designer. Откроется окно построителя запросов, в котором можно натаскивать измерения и меры по строкам и столбцам, задавать параметры фильтрации и т.д., как в сводной таблице Excel.
Рис.5
Настоящие пацаны построителем запросов не пользуются. Отожмите кнопку Design Mode, чтобы перейти в режим ручного ввода текста запроса. У нас уже имеется практически готовый запрос (Скрипт 1), единственно, его требуется немного доточить под использование в Reporting Serices:
with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])
select
{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0
, nonempty(Product.[Product Categories].Product.Members, Measures.[Sales Amount]) on 1
from [Adventure Works]
where [Date].Calendar.[Calendar Year].[CY 2008]
Скрипт 2
Рис.6
Вместо функции Descendants, которая выбирает категории, подкатегории и сами продукты, я просто выбираю все продукты с листового уровня Product. По законам жанра OLE DB, вышестоящие уровни подтянутся в резалтсет автоматически. Reporting Serices - штука очень умная и будет сама группировать по подкатегориям и категориям. В случае Descendants родительские строки будут создавать лишние пустоты и только мешаться.
Теперь давайте заменим заглушку [Date].Calendar.[Calendar Year].[CY 2008] на параметр. При запуске отчета период времени будет вводиться как параметр отчета и передаваться параметром в запрос. Нажмите на кнопку параметра запроса и задайте параметр по имени TimePeriod, который будет браться из членов измерения Date, иерархия Calendar. Значение по умолчанию оставьте пока [All Periods]:
Рис.7
Нажмите положенное число раз ОК. В отчете создался параметр отчета TimePeriod, который, как можно посмотреть в его свойствах, берется из некоторого датасета TimePeriod1, причем этот датасет обладает, как минимум, двумя полями: ParameterValue, из которого будет взято значение параметра, и ParameterCaptionIndented, которое будет показываться при выборе значений параметра.
Рис.8
Какой интересный, однако, датасет. Жалко, что Report Builder его не показывает. Ну ничего. Сохраните отчет в виде файла (не на сервер отчетности), откройте образовавшийся rdl любым текстовым редактором и найдите в нем датасет TimePeriod1
Рис.9
Он построен на основе MDX-запроса, который собирает членов измерения Date, и дополнительно имеет вычисляемое поле ParameterCaptionIndented, в котором отступает членов сообразно их уровню. Чтобы понять идею, давайте пойдем своим путем. Можете удалить этот датасет из rdl, коль скоро его не видно в редакторе и переоткрыть rdl:
Рис.10
Создайте новый датасет DataSet2 против того же источника DataSource1. Вбейте ему запросом следующий MDX:
with member [Measures].[ParameterCaption] as space([Measures].[ParameterLevel] * 2) + [Date].Calendar.CurrentMember.NAME
member [Measures].[ParameterValue] as [Date].Calendar.CurrentMember.UNIQUENAME
member [Measures].[ParameterLevel] as [Date].Calendar.CurrentMember.LEVEL.ORDINAL
select
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} on 0,
except([Date].Calendar.members, [Date].Calendar.[Date].Members) on 1
from [Adventure Works]
Скрипт 3
Рис.11
Идейно это то же самое, что мы видели на Рис.9, просто я здесь выпендрился и засунул строку с отступами внутрь MDX. Свойство порядковый номер уровня (CurrentMember.LEVEL.ORDINAL) используется для отсчета глубины отступа. Свойство CurrentMember.NAME как наиболее читабельное используется для отображения члена измерения при выборе периода в параметре. Свойство CurrentMember.UNIQUENAME является значением параметра и будет подставляться в запрос DataSet1 в условие where. В данном примере для простоты предполагаем, что детализация выбранного периода ограничивается месяцем. При выборе дня можно было бы использовать выпадающий календарик, если сделать отчету фиктивный параметр типа Date/Time и собрать из него член измерения Date во внутренний параметр. Разница между скрытыми и внутренними параметрами отчета состоит в том, что скрытый параметр не высвечивает приглашения для своего ввода при вызове отчета, тем не менее его можно ему передать, например, в урловой строке, когда мы вызываем отчет через GET-запрос. Внутренний параметр наружу не засвечивается и используется для собственных нужд отчета, как в данном случае. Для совсем красоты нужно писать свое ASP.NET или WinForms-приложение, класть на него контрол ReportViewer, а рядом для выбора периода времени пришпандорить контрол, который по душе.
Перейдем в свойства параметра отчета TimePeriod, как показано на Рис.8, скажем, что параметр будет предлагаться из запроса, что запросом для него будет DataSet2 (см. Скрипт 3), что значения параметра будут выбираться из поля ParameterValue данного датасета, а соответствующие надписи, которые будут отображаться при выборе, будут браться из поля ParameterCaption:
Рис.12
Поменяем заодно значение по умолчанию для данного параметра, т.е. с которым будет открываться отчет, если никакого значения параметра ему при обращении не передавалось. На Рис.7 значение по умолчанию задавалось статически - [Date].Calendar.[Calendar Year].[CY 2008]. Давайте чуть-чуть усложним и сделаем его равным последнему непустому месяцу, т.е. последнему месяцу, за который была ненулевая мера Measures.[Sales Amount]. Формулу для него мы писали в посте "Работа с ключевыми показателями эффективности в SQL Server Analysis Services. Часть 1", Скрипт 7:
Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Sales Amount]), 1).Item(0).Item(0).UNIQUENAME
Скрипт 4
Я только добавил справа функцию UNIQUENAME по аналогии со Скриптом 3, т.к. параметр TimePeriod имеет тип Text (Рис.12, закладка General). Жмем на Рис.12 Default Values слева и видим, что значение по умолчанию для параметра отчета можно либо вбить константой (Specify values), либо тоже выбрать из запроса (Get values from a query). К сожалению, под константой понимается действительно константа. При выборе Specify values можно указать конкретный член измерения или сделать с ним что-нибудь на языке службы отчентости, но воспринимать MDX-выражение (Скрипт 4) она отказывается и делает вид, что никакого значения по умолчанию у параметра TimePeriod нет. Придется добавить в отчет еще один датасет DataSet3
with member Measures.aaa as Tail(nonempty([Date].[Calendar].[Month].Members, Measures.[Sales Amount]), 1).Item(0).Item(0).UNIQUENAME
select Measures.aaa on 0 from [Adventure Works]
Скрипт 5
Рис.13
По результату похоже на правду. Просмотр куба Adventure Works убеждает, что последний непустой (по Sales Amount) месяц действительно июль 2008 г.
Рис.14
Зададим DataSet3 в качестве Default Values для параметра TimePeriod:
Рис.15
И нам осталось засунуть параметр TimePeriod в условие where запроса DataSet1 (Скрипт 2, Рис.6) вместо заглушки [Date].Calendar.[Calendar Year].[CY 2008]:
with member Measures.ProductShare as (Product.[Product Categories].CurrentMember, Measures.[Sales Amount]) / (Product.[Product Categories].CurrentMember.Parent, Measures.[Sales Amount])
select
{Measures.[Sales Amount], Measures.ProductShare, KPIValue("Product Gross Profit Margin"), KPIStatus("Product Gross Profit Margin"), KPITrend("Product Gross Profit Margin")} on 0
, nonempty(Product.[Product Categories].Product.Members, Measures.[Sales Amount]) on 1
from [Adventure Works]
where StrToMember(@TimePeriod)
Скрипт 6
Применяем MDX-функцию StrToMember, потому что Reporting Services не знают типа параметра Member. Параметр отчета TimePeriod имеет тип Text; чтобы превратить строку с названием члена в член, используется эта функция.
Рис.16
Жмем на кнопку Run слева вверху и видим, что все работает. По умолчанию выбирается июль 2008 г.
Рис.17
По-моему, отличный отчет. Хотя его не мешало бы сделать более насыщенным.
Сейчас давайте ненадолго прервемся, по-быстрому встретим Новый год и продолжим. Кстати, от имени департамента стратегических технологий российского представительства Microsoft всех с Новым годом.
Алексей Шуленин