Блеск и нищета сводных таблиц
Часть 12
Системные ограничения OLAP-технологии
Расширение возможностей OLAP путем создания хранимых процедур
В последних статьях цикла обсуждались специальные возможности аналитического сервера Microsoft Analysis Services, предназначенные для решения различных практических задач, в первую очередь финансового характера.
Настоящий материал является в некотором роде итоговым. Он посвящен рассмотрению методов и подходов, позволяющих обойти системные ограничения, присущие OLAP-технологии в целом. У читателей могут возникнуть обоснованные сомнения: как часто на практике встречаются ситуации, в которых принципиальной сложностью являются именно ограничения технологического характера? Нужно ли вообще доводить систему до такого состояния или можно обойтись более грамотным дизайном многомерных пространств?
Системные ограничения OLAP-технологии
Выясним для начала, в каких случаях мы упремся в возможности аналитического сервера. Предположим, что перед нами стоит задача по планированию начислений и последующих выплат налога на прибыль. В большинстве случаев ее решение не составляет особого труда — начисленный за определенный период налог должен быть выплачен в течение этого же периода. Налог на прибыль рассчитывается как некоторая доля от налогооблагаемой базы, которая является разностью между доходом и расходом компании. В отдельные периоды расходы могут превышать доходы. Формально это приводит к отрицательным величинам налога (рис. 1).
Рис. 1. Корректный расчет выплат по налогу на прибыль
Для нас сейчас не столь существенно, почему планируемая деятельность убыточна в 1/3 месяцев в году. Будем довольствоваться тем, что в целом за год компания показывает положительный финансовый результат. Гораздо интереснее другой вопрос: как быть с выплатами (перечислениями в бюджет) отрицательных денежных сумм? Понятно, что в случае отрицательного налога сумма платежей в бюджет за этот период должна быть нулевой. На рис. 1 подобная ситуация наблюдается в январе. Следовательно, в феврале налогооблагаемую базу необходимо уменьшить на убыток предыдущего периода. Поэтому несмотря на то, что начисленный налог составляет 40 единиц, к перечислению в бюджет следует запланировать только 20 единиц.
Если бы в феврале сумма начисленного налога составила всего 10 единиц, то платеж за февраль также был бы равен 0. При этом на март должен быть перенесен убыток с января в размере 10 единиц. В общем случае для вычисления платежа в текущем месяце следует знать две суммы: начисленных налогов и совершенных платежей за все предшествующие периоды. Получается, что платеж на этапе к вычисляется на основе значений, определенных на предыдущих этапах: 1, 2….. к–1. Иными словами, формула является рекурсивной.
На практике ситуации, подобные описанной выше, встречаются регулярно. Сразу отметим, что в привычной для всех среде Microsoft Excel написать выражение, в котором реализована требуемая рекурсия, довольно просто (рис. 2).
Рис. 2. Расчет платежей налога на прибыль в среде Microsoft Excel
Строго говоря, на рис. 2 определены вызовы сразу 12 функций, каждая из которых получает в качестве входных аргументов значения функций, вычисленных на предыдущем шаге.
Допустим теперь, что в нашем распоряжении есть аналитический куб, в котором хранятся рассчитанные начисления налога на прибыль. Мы же хотим на базе имеющихся значений получить календарный план платежей в бюджет, должным образом скорректированный на отрицательные величины.
К решению данной задачи можно подойти двумя способами. Первый заключается в проведении всех вычислений на стороне клиентского приложения. Действительно, располагая набором функций КУБ(), несложно создать отчет, выводящий нужный ракурс многомерного пространства в определенную область рабочего листа книги, а затем ячейки из этой области использовать в стандартных функциях листа Microsoft Excel. Данный подход подкупает своей простотой. Но его фактическое использование оправданно лишь в ограниченном числе случаев — когда рассчитанный в клиентском модуле показатель не влияет на значения иных показателей пространства. В реальности, как правило, наблюдается обратная ситуация: полученный показатель должен использоваться в последующих расчетах. В рассматриваемом примере планируемые выплаты по налогам влияют на величину кассовых остатков в конце периода, которые, в свою очередь, определяют ряд статей прогнозного баланса. Таким образом, выбирая первый способ расчета, разработчики неизбежно обрекают себя на последующее вычисление во внешней среде всех производных и зависимых величин, тем самым сводя на «нет» преимущества аналитической обработки данных.
Альтернативный вариант напрашивается сам собой: попытаться рассчитать выплаты налога непосредственно в OLAP-кубе. При таком выборе производные и зависимые показатели можно оформить в виде обычных MDX-выражений. К дополнительным преимуществам метода относится значительное упрощение этапа создания выходных отчетных форм, так как отпадает необходимость вести во внешней среде какиелибо расчеты.
На первый взгляд преимущества второго варианта неоспоримы. Нужно лишь организовать выплаты налога в виде вычисляемого элемента пространства. Но попытка определить такую операцию посредством стандартных MDX-функций обречена на провал. Многомерные вычисления являются реализацией модели «лямбда-исчисления» (?-исчисления), в которой функции не могут непосредственно ссылаться на себя. Поэтому определить рекурсивный вызов в чистом виде внутри OLAP-куба не получится.
Фактически рекурсия уменьшает время вычислений, так как позволяет использовать на каждом этапе рассчитанные ранее значения. Несмотря на всю привлекательность данного принципа, от него можно отказаться и каждый раз вычислять все необходимые аргументы заново. Неизбежной платой за подобную расточительность будет значительное повышение вычислительной сложности (более чем на порядок). В реальности оно окажется не столь существенным (в 10-12 раз), так как потребность в подобных расчетах изначально возникает уже на высоких уровнях гранулярности атрибутов измерения «время» (месяцы и выше) в пределах одного года. Кроме того, в аналитическом сервере есть высокоэффективные средства балансировки нагрузки — например создание под определенные задачи отдельной группы мер. Но даже полный отказ от рекурсии всё равно не позволит реализовать желаемую логику штатными средствами OLAP. Повторимся еще раз: MDX — это функциональный язык, который является декларативным по своей сути. В нем в принципе невозможны различные императивные конструкции типа организации циклов и условных переходов.
Расширение возможностей OLAP путем создания хранимых процедур
Итак, как мы поняли, OLAP-среда содержит ряд принципиальных ограничений. К счастью, ситуация не столь безнадежна, как может показаться на первый взгляд. Как известно, препятствие, которое нельзя преодолеть, можно обойти. Применительно к OLAP-технологиям данный принцип формулируется следующим образом: если алгоритм нельзя реализовать внутри среды аналитического сервера, его нужно исполнить во внешнем модуле, а в куб передать лишь конечные результаты вычислений. В самом деле, в ячейках куба важно иметь лишь итоговые суммы платежей налога, при этом совершенно неважно, как они там оказались. Например, это могут быть значения некоторой функции, определенной самим пользователем. Такие функции называются пользовательскими (user defined function, UDF). О том, как их создавать, мы и расскажем далее.
Сначала проведем небольшую подготовительную работу. Будущей пользовательской функции потребуется массив значений, который соответствует помесячным начислениям налогов за весь временной интервал, предшествующий текущему месяцу. Фактически необходимо сформировать 12 массивов — по одному для каждого месяца года. С созданием таких массивов внутри OLAP-куба связано несколько сложностей. Вопервых, они должны быть динамическими: в январе состоять из единственного элемента, в феврале — из двух, в марте — из трех и т.д. Вовторых, их не получится хранить внутри многомерного пространства. В ячейках куба могут находиться только скалярные значения различных типов, но никак не массивы. Получается, что массивы входных аргументов следует формировать «на лету», непосредственно в момент вызова пользовательских функций. В среде OLAP-сервера для передачи во внешние функции массива аргументов существует функция SetToArray(). Поэтому для вызова функции с требуемыми параметрами достаточно уметь для каждого месяца формировать нужный многомерный набор (Set). Здесь нам пригодится другая замечательная функция из арсенала MDX-операторов — StrToSet(), возвращающая набор, который определен с помощью строки, представленной в MDX-формате.
На оси [Measures] создадим новый вспомогательный вычисляемый элемент [Current_Month] (выражение 1).
Выражение 1
CREATE MEMBER CURRENTCUBE.[Measures].[Current_Month]
AS MemberToStr([Дата].[Дата].CurrentMember),
VISIBLE = 1;
Затем на базе элемента [Current_Month] создадим еще один — [Current_Period] (выражение 2).
Выражение 2
CREATE MEMBER CURRENTCUBE.[Measures].[Current_Period]
AS «{[Дата].[Дата].[Месяц].&[1]:»+ Measures.Current_Month+»}»,
VISIBLE = 1;
Теперь с каждым элементом из измерения [Дата] у нас ассоциирована строка, хранящаяся в элементе [Current_Month]. Она представляет собой выражение в MDX-формате для набора элементов измерения [Дата] с начала года до текущего элемента. В частности, для элементов, расположенных на уровне [Месяц], выражение примет вид, показанный на рис. 3.
Рис. 3. MDX-выражения для временных периодов
Строковое выражение [Current_Period] посредством функции StrToSet() преобразуется в набор, из которого при помощи функции SetToArray() можно выделить числовой массив. На самом деле функция SetToArray() возвращает тип данных Variant, то есть VT_Array. Это важное обстоятельство, которое повлияет в дальнейшем на выбор подходящего типа данных для входного аргумента пользовательской функции.
Таким образом, была выполнена первая важная часть задачи — мы научились создавать внутри OLAP-куба подходящие массивы элементов. Осталось разработать соответствующую пользовательскую функцию. Такие функции могут генерироваться как на базе классической технологии COM, так и на управляемом коде в среде .NET Framework. Вопрос, какой из перечисленных вариантов выбрать, является открытым. Но если рассматривать его с позиций бизнес-пользователя, то есть специалиста, обычно не имеющего серьезного опыта в программировании приложений, оптимальным представляется второй вариант. В настоящее время исполняющая среда Common Language Runtime (CLR) платформы .NET Framework развернута по умолчанию на всех компьютерах, работающих под управлением ОС Windows. При этом CLR-сборки намного безопаснее, чем их COM-аналоги, требующие регистрации в системном реестре. Разработчики MS Analysis предусмотрели несколько способов, позволяющих развернуть CLR-сборки на сервере. Универсальным, но достаточно сложным методом является передача CLR-сборки в составе DDL-запроса (Data Definition Language). К счастью, указанная задача значительно упрощается, если разработчик имеет доступ к файловой системе сервера. В целях упрощения дальнейшего изложения будем считать, что у разработчика есть полномочия на запись DLL-библиотеки в один из каталогов аналитического сервера.
Создание пользовательской функции начинается с написания программы, реализующей требуемую логику вычислений, на одном из языков .Net и последующей ее компиляции в байт-код (intermediate language, IL). Данная статья ориентирована в первую очередь на обычных пользователей офисного пакета Microsoft Office, в котором штатным средством разработки является язык программирования Visual Basic for Applications. В качестве языка программирования была выбрана последняя версия Visual Basic 2010 (VB 10.0), входящая в состав интегрированной среды разработки Microsoft Visual Studio 2010. Следует отметить, что CLR-сборка всегда компилируется под определенную версию платформы .NET Framework. В случае использования Visual Studio 2010 на компьютере разработчика устанавливается ее текущая версия .NET 4.0. CLR-сборки по умолчанию создаются для данной версии и не работают на серверах с младшими версиями .NET.
У разработчика есть выбор между обновлением до актуальной версии runtime-среды на сервере с Microsoft Analysis Services, что часто невозможно в силу различных технических ограничений, и компиляцией байт-кода под конкретную версию .NET Framework. Такая операция называется Runtime Targeting, но для ее выполнения на компьютер с Visual Studio потребуется установить дополнительный пакет Microsoft .NET Framework 3.5 Service Pack 1, доступный для свободного скачивания по адресу: http://www.microsoft.com/en-us/download/details.aspx?id=22.
Создадим новую библиотеку классов (Class Library) с названием Tax_Calc под нужную версию .NET Framework — в нашем случаем это будет .NET 2.0 (рис. 4).
Рис. 4. Создание CLR-сборки под среду .NET 2.0
Добавим в проект класс Profit_Tax, содержащий единственный метод Payment_Calc() (см. листинг 1).
Листинг 1
Public Class Profit_Tax
Function Payment_Calc(ByVal a As Object) As Double
Dim finish As Integer = UBound(a)
Dim SubTotals(finish) As Integer
Dim CF_Acc(finish) As Integer
Dim CF As Integer
Select Case finish
Case Nothing
Return 0
Case 0
If a(0) < 0 Then Return 0 Else Return a(0)
Case Else
If a(0) < 0 Then SubTotals(0) = 0 Else SubTotals(0) = a(0)
CF_Acc(0) = a(0)
Dim i As Integer
For i = 1 To finish
CF_Acc(i) = CF_Acc(i - 1) + a(i)
If a(i) < 0 Then
CF = 0
ElseIf CF_Acc(i) - SubTotals(i - 1) >= 0 Then
CF = CF_Acc(i) - SubTotals(i - 1)
Else : CF = 0
End If
SubTotals(i) = SubTotals(i - 1) + CF
Next i
End Select
Return CF
End Function
End Class
Функция Payment_Calc, представленная в листинге 1, вычисляет налоговый платеж для последнего месяца периода из массива элементов, переданного ей в качестве входного аргумента. Существенным моментом, на который имеет смысл обратить внимание, здесь является только тип данных входного параметра, он объявлен как Object — так в современной нотации языка называется тип Variant.
Написанный программный код необходимо скомпилировать в .Net-сборку, для чего используется команда Build, генерирующая на выходе DLL-файл специального формата. Затем полученную сборку следует развернуть на аналитическом сервере. Примечательно, что такая операция очень требовательна к уровню полномочий выполняющего ее пользователя — она доступна только для системного администратора. Данное ограничение обусловлено тем, что сборка содержит код, не являющийся оригинальным для Microsoft Analysis Services, и последствия его исполнения неизвестны и потенциально небезопасны. В зависимости от желаемой области видимости пользовательской функции CLR-сборку можно установить как для всего сервера в целом, так и на уровне отдельной аналитической базы. В последнем случае функция будет доступна только в MDX-запросах выбранной базы. При использовании SQL Server Management Studio любая из этих операций выполняется очень просто: достаточно вызвать контекстное меню для раздела Assemblies и выбрать в нем команду New Assembly… В появившемся диалоговом окне необходимо указать адресный путь к месту физического хранения DLL-файла (рис. 5).
Рис. 5. Развертывание CLR-сборки на аналитическом сервере
Рис. 6. Расчет значений меры [Paym]
для месяцев
В процессе регистрации сборки ей можно присвоить новое имя, отличное от исходного названия. Эта опция полезна на практике, так как позволяет заменить длинное системное имя библиотеки коротким псевдонимом, уменьшив тем самым длину полного имени пользовательской функции, состоящей из трех частей:
- имени сборки (Assembly Name);
- имени класса (Class Name);
- названия метода (Method Name).
В полном имени функции названия сборки и метода являются обязательными компонентами, а имя класса — опциональным. Однако разработчики Microsoft Analysis Services настоятельно рекомендуют применять именно полные имена хранимых процедур. Это повышает производительность запросов и приводит к более эффективному использованию памяти сервера.
Создадим теперь новый вычисляемый элемент [Paym] на оси [Measures] (выражение 3).
Выражение 3
CREATE MEMBER CURRENTCUBE.[Measures].[Paym]
AS TC.Tax_Calc.Profit_Tax.Payment_Calc(SetToArray(StrToSet(Measures.Current_Period),[Measures].[Calc])),
VISIBLE = 1;
Из записи выражения видно, что вычисление значения элемента [Paym] происходит в три этапа. Сначала посредством функции StrToSet() формируется многомерный набор, представляющий собой начисление налогов за определенный период. Затем данный набор функцией SetToArray() преобразуется в обычный числовой массив. Наконец, массив, сформированный на предыдущих шагах, передается на вход внешней функции Payment_Calc(), которая вычисляет единственное значение — платеж налога в текущем месяце. Заметим, что в выражении 3 использовался полный вариант имени функции Payment_Calc() — TC.Tax_Calc.Profit_Tax.Payment_Calc, включающий название класса. В нашем случае название класса Tax_Calc.Profit_Tax является составным и содержит также наименование пространства имен (NameSpace), совпадающее с названием библиотеки классов (DLL-файла).
Финальные штрихи
Выполним процессинг куба и создадим на его базе сводный отчет в программе Microsoft Excel. На этом для случая, когда измерение [Дата] представлено единственным уровнем [Месяц], поставленную задачу можно считать решенной. Но как будет вести себя вычисляемая мера [Paym] для измерения, являющегося иерархией из нескольких уровней? Рис. 6 показывает, что ее значения для всех нелистовых элементов измерения [Дата] будут вычисляться с ошибками.
В наблюдаемой ситуации нет ничего удивительного — ведь элемент [Current_Period] на оси [Measures] определен для каждой ячейки куба, включая все кварталы и годы. Но синтаксически верную запись многомерного набора он формирует только для элементов из уровня [Месяцы]. Поэтому функция Payment_Calc(), которая также определена на всем пространстве, для узловых элементов возвращает сообщения об ошибках.
Существует несколько способов исправления ситуации. Самый простой и быстрый из них — заново переопределить значения меры [Paym] для узловых элементов измерения [Дата]. Такая операция выполняется посредством оператора Scope. Добавим в сценарий MDX после записи элемента [Measures].[Paym] несколько новых скриптовых команд (выражение 4).
Выражение 4
scope ([Дата].[Дата].[Квартал].members,[Measures].[Paym]);
this = sum([Дата].[Дата].CurrentMember.Children,[Measures].[Paym]);
end scope;
В выражении 4 оператор Scope определяет подкуб, в котором будет выполняться операция присваивания. Множество состоит из всех элементов измерения [Дата], расположенных на уровне [Квартал] в пересечении с мерой [Paym]. Для элементов заданного пространства оператор This назначает новые значения путем суммирования подчиненных величин меры [Paym] на уровне [Месяц]. А они уже считаются корректно. Если теперь повторно выполнить процессинг куба, то сводный отчет в книге Microsoft Excel изменится — в нем появятся корректные значения для кварталов (рис. 7).
Рис. 7. Расчет значений меры [Paym]
для месяцев и кварталов
Аналогичным образом поступим с элементами измерения [Дата] на уровне [Год], а также с элементом [All] (выражение 5).
Выражение 5
scope ([Дата].[Дата].[Год].members,[Measures].[Paym]);
this = sum([Дата].[Дата].CurrentMember.Children,[Measures].[Paym]);
end scope;
scope ([Дата].[Дата].[All],[Measures].[Paym]);
this = sum([Дата].[Дата].CurrentMember.Children,[Measures].[Paym]);
end scope;
В итоге мы получим сводный отчет, корректно отображающий значения вычисляемой меры [Paym] для любого ракурса многомерного пространства (рис. 8).
Рис. 8. Расчет значений меры [Paym] для всех элементов измерения [Дата]
В статье были рассмотрены вопросы создания и развертывания на сервере пользовательских функций в составе CLR-сборок. Данные функции являются главным инструментом по ведению в OLAP-среде сложных расчетов, основанных на процедурной логике.
 |
|
