Analysis Services - показатели зависимые от параметров

Пожалуй, в каждом языке программирования, среде разработки есть возможность создания гибких параметризованных формул, макроподстановочных выражений. SQL Server Analysis Services (SSAS) не является исключением. Несмотря на собственные возможности OLAP клиентских приложений, часто требуется дополнительная функциональность. Рассмотрим пример реализации параметризованных вычисяемых аналитических показателей.

Прежде всего сформируем таблицу - список мер куба, в которую для дальнейшего удобства использования добавим такие поля как: бизнес-классификация меры, порядок представления (сортировки), формат представления, переводы, а также описательные характеристики.

На основе этой таблицы создадим измерение "Список показателей", а в нём - пользовательскую иерархию "Классификация показателей"

Вспомогательное измерение «__Список показателей»

Также создадим измерение «_Числовые параметры» - последовательность чисел 1, 2, 3, 4, 5, 10, 15, 20, ... 50 и измерения «_Начало периода», «_Окончание периода» - список дат.

Вспомогательное измерение «_Числовые параметры»

Все эти измерения будут иметь вспомогательный характер, и не будут привязаны ни к одной группе мер куба. Наименования служебных измерений будут начинаться с префикса "_", чтобы подчеркнуть их особое предназначение и хоть как-то сгруппировать в клиентских OLAP-приложениях.

Включение вспомогательных измерений в куб

---------------------------------------------------------------------------------------------------------------------------------- ALTER CUBE CURRENTCUBE UPDATE DIMENSION [_Список показателей], DEFAULT_MEMBER ='[_Список показателей].[Наименование показателя].[Остаток ОД]' ; -- поскольку для измерения определён элемент по умолчанию, то он работает даже тогда, -- когда измерение не размещено ни в одной из областей (осей) сводной таблицы/диаграммы -- в данной служебной мере запоминаем имя меры, которую пользователь выбрал из вспомогательного измерения [_Список показателей] CREATE MEMBER CURRENTCUBE.[MEASURES].[selected_measure] AS (StrToMember('[MEASURES].' + [_Список показателей].[Наименование показателя].CURRENTMEMBER.Name + '') ) ,VISIBLE = 0; -- в этой служебной мере запоминаем формат отображения для меры, -- которую пользователь выбрал из вспомогательного измерения [_Список показателей] CREATE MEMBER CURRENTCUBE.[MEASURES].[selected_measure_format_string] AS [_Список показателей].[Наименование показателя].CURRENTMEMBER.Properties("Формат представления", typed) ,VISIBLE = 0 ; -- в действующем проекте формула выглядит сложнее, т.к. учитывает пользовательский выбор -- из вспомогательного junk-измерения [_Правила оформления] (описание этого измерения выходит -- за рамки данной статьи), здесь же приводится упрощенный вариант формулы CREATE MEMBER CURRENTCUBE.[MEASURES].[К месяцу назад относительное изменение...] AS CASE WHEN [Отчетные даты].[Год-квартал-месяц-дата].CURRENTMEMBER.LEVEL.ORDINAL=0 THEN "NA" WHEN ISEMPTY(( ParallelPeriod([Отчетные даты].[Год-квартал-месяц-дата].[Год месяц], 1, [Отчетные даты].[Год-квартал-месяц-дата].CURRENTMEMBER), [MEASURES].[selected_measure] )) THEN NULL ELSE ( ( [Отчетные даты].[Год-квартал-месяц-дата].CURRENTMEMBER, [MEASURES].[selected_measure] ) - ( ParallelPeriod([Отчетные даты].[Год-квартал-месяц-дата].[Год месяц], 1, [Отчетные даты].[Год-квартал-месяц-дата].CURRENTMEMBER), [MEASURES].[selected_measure] ) ) / ( ParallelPeriod([Отчетные даты].[Год-квартал-месяц-дата].[Год месяц], 1, [Отчетные даты].[Год-квартал-месяц-дата].CURRENTMEMBER), [MEASURES].[selected_measure] ) END ,FORMAT_STRING = "Percent" ,ASSOCIATED_MEASURE_GROUP = 'Показатели зависимые от параметров' ,DISPLAY_FOLDER = 'К предыдущему периоду' ,VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[MEASURES].[С даты нарастающий итог...] AS SUM( LinkMember([_Начало периода].[Дата начала].CURRENTMEMBER, [Отчетные даты].[Отчетная дата]) : [Отчетные даты].[Отчетная дата].CURRENTMEMBER -- .PREVMEMBER -- если не нужно включать в нарастающий итог последнюю дату ,[MEASURES].[selected_measure] ) ,FORMAT_STRING = [MEASURES].[selected_measure_format_string] ,ASSOCIATED_MEASURE_GROUP = 'Показатели зависимые от параметров' ,DISPLAY_FOLDER = 'Нарастающий итог' ,VISIBLE = 1 ; CREATE DYNAMIC SET CURRENTCUBE.[Контракты TOP X по...] AS IIF( [_Список показателей].[Классификация показателей].CURRENTMEMBER.Level.Name='Наименование показателя' ,TopCount( [Кредитные контракты].[Список контрактов].[Кредитный контракт].MEMBERS ,[_Числовые параметры].[Параметр].CURRENTMEMBER.Member_key ,[MEASURES].[selected_measure] ) ,{} ) ; -- Динамические наборы поддерживаются не всеми OLAP-клиентами; так, Excel понимает DYNAMIC SET, начиная с версии Excel 2010 CREATE MEMBER CURRENTCUBE.[MEASURES].[Среди дочерних элементов MAX...] AS IIF(ISERROR(Axis(1)(0).Count), NULL, MAX( CASE Axis(1)(0).Count WHEN 1 THEN Axis(1).ITEM(0).Hierarchy.Children WHEN 2 THEN Crossjoin( Axis(1).ITEM(0).ITEM(0).Hierarchy.Children ,Axis(1).ITEM(0).ITEM(1).Hierarchy.Children ) WHEN 3 THEN Axis(1).ITEM(0).ITEM(0).ITEM(0).Hierarchy.Children * Axis(1).ITEM(0).ITEM(1).ITEM(0).Hierarchy.Children * Axis(1).ITEM(0).ITEM(2).ITEM(0).Hierarchy.Children ELSE NULL END ,[MEASURES].[selected_measure] ) ) ,FORMAT_STRING = [MEASURES].[selected_measure_format_string] ,ASSOCIATED_MEASURE_GROUP = 'Показатели зависимые от параметров' ,DISPLAY_FOLDER = 'Продвинутые\MIN_MAX' ,VISIBLE = 1 ; ----------------------------------------------------------------------------------------------------------------------------------

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

- выбранный элемент (и только один элемент) измерения «_Список показателей» как бы подставляется вместо многоточия в названии вычисляемой меры или динамического набора, т.е. указывает к какому показателю применить заданную формулу вычисления ;
- выбранный элемент измерения «_Числовые параметры» (целое число) подставляется вместо символа "X" ;
- выбранный элемент измерения «_Начало периода» определяет дату начала для вычисляемой меры [С даты нарастающий итог...]

Развернем проект на сервере OLAP, спроцессим куб, включим что-нибудь веселенькое от Magic Slim, подключимся к кубу из Excel 2010 и проверим, что всё работак так, как нам нужно.

«К месяцу назад относительное изменение...» (аналогично можно создать формулы «К неделе назад относительное изменение...», «К кварталу назад относительное изменение...», «К году назад относительное изменение...») - это вычисляемый показатель: разместив вспомогательное измерение «_Список показателей» в область фильтров, можно определить относительное изменение значение показателя, определяемого выбранным элементом в измерении «_Список показателей», по отношению к значению этого же показателя месяц (неделю / квартал / год) назад:

Параметризованная вычисляемая мера «К месяцу назад относительное изменение...»

«С даты нарастающий итог...» - вычисляемый показатель: разместив вспомогательные измерения «_Список показателей», «_Начало периода» в область фильтров, рассчитаем нарастающий итог с указанной отчетной даты по показателю, определяемого выбранным элементом в измерении «_Список показателей», например, с 16.04.2010г. нарастающий итог количества выходов в просрочку основного долга по кредиту:

Параметризованная вычисляемая мера «С даты нарастающий итог...»

Наряду с использованием любых обычных физических мер можно вытянуть максимальные / минимальные значения среди дочерних элементов, непосредственно не раскрывая / не спускаясь к уровню ниже:

Параметризованная вычисляемая мера «Среди дочерних элементов MAX...»

Подобных параметризованных формул можно создать сколь фантазии хватит (особенно, если еще изучить рецепты универсальных формул от Tomislav Piasevoli):

- На контракт среднее...
- На отчетный день среднее...
- Универсальное среднее...
- Волатильность...
- Доля %...
- Элементов уровня медиана...
- Дочерних элементов медиана...
- Ранг...
- По месяцам простое скользящее среднее...
- По кварталам простое скользящее среднее...
и так далее.

И все эти вычисляемые меры будут работать в любом OLAP клиентском приложении. Главное, не забываем документировать свои разработанные формулы.

SSAS - показатели, зависимые от параметров