Служба Analysis Services в SQL Server 2005 подверглась коренной переработке, и, на наш взгляд, в настоящее время это самая мощная платформа бизнес-аналитики в мире. Многочисленные функции и встроенные усовершенствования Analysis Services 2005 обеспечивают автоматическое, интеллектуальное решение многих типичных задач бизнеса. Не нужно быть специалистом в программировании OLAP или многоразмерных выражений Multidimensional Expressions (MDX), чтобы строить полнофункциональные аналитические приложения.

Последние усовершенствования Analysis Services 2005 в области временного анализа позволяют быстро выполнять расчеты, учитывающие временные факторы. Для начала рассмотрим, каким образом новый мастер Business Intelligence Wizard облегчает временной анализ. Затем будут даны ответы на вопросы «как» и «почему», касающиеся внутренних механизмов мастера. И наконец, мы проанализируем методы моделирования, используемые во временном анализе, и исходный текст, генерируемый мастером, чтобы показать оптимальные приемы моделирования как метаданных, так и MDX. Данная статья представляет собой лишь краткий обзор, поэтому содержащаяся в ней информация будет полезна в первую очередь специалистам, знакомым с SQL Server 2000 Analysis Services.

Временной анализ

Как правило, предприятиям требуется проанализировать временной ряд для данной точки в определенном наборе временных интервалов. Analysis Services позволяет выполнять вычисления на основе временных показателей - рассчитывать значения конкретной величины в течение времени, например в периоды до конкретной даты и различные формы скользящего среднего. Существует много методов моделирования этой задачи, но каждый из них требует подготовки потенциально сложного исходного текста MDX и принятия важных решений о моделировании метаданных. Последствия неоптимального решения могут быть серьезными; например, модель может оказаться значительно менее дружественной (из-за чрезмерного увеличения размерности показателей), а расчеты могут производиться слишком медленно (из-за избыточных вычислений на этапе выполнения).

В настоящее время составить удачную модель несложно. Благодаря ряду улучшений мастера Business Intelligence Wizard можно расширить и углубить модель. Для организации временных расчетов с помощью Analysis Services 2005 достаточно просто задействовать аналитические возможности Business Intelligence Wizard.

Использование мастера BI Wizard

Для временного анализа конкретного куба следует запустить Business Intelligence Wizard из контекстного меню Business Intelligence Developer Studio в Solution Explorer для размерности (dimension) или куба разработки Analysis Services 2005. На экране Choose Enhancement мастера (экран 1) перечислены усовершенствования, применимые к текущему объекту.

Экран 1. Окно Choose Enhancement мастера Business Intelligence Wizard

В большинстве случаев операции временного анализа вводятся на поздних стадиях цикла разработки, после того как будет завершена работа над базовой структурой метаданных и вычислениями, не связанными со временем. Важно завершить структуру размерности дат (или времени) базы данных до добавления временной аналитики, так как в будущем на ее основе будут строиться вычисления, генерируемые мастером. Также важно принять решение о вводе в целевой куб одного или нескольких экземпляров (ролей) размерности даты (например, Ship Date - дата отгрузки, Order Date - дата заказа), так как мастер изменяет структуру этих ролей. А поскольку временная аналитика может применяться как к физическим, так и к расчетным показателям, необходимо завершить проектирование размерности показателей (в том числе расчетных показателей), чтобы иметь полный набор параметров при запуске мастера.

Перед применением временной аналитики необходимо выбрать целевую иерархию и генерируемые расчеты. На экране Choose Target Hierarchy and Calculations (экран 2) можно задать иерархию размерностей куба для анализа с использованием временного ряда и временных расчетов. Как правило, назначается пользователь или многоуровневая иерархия в размерности, чтобы обеспечить анализ на различных временных уровнях (например, Years - годы, Semesters - семестры, Quarters - кварталы). Сначала выбирается иерархия, а потом вычисления, которые должен произвести мастер. Важно обратить внимание на то, что используемый мастером шаблон вычислений - полностью расширяемый, поэтому его легко настроить в соответствии с нуждами любого потребителя, местности или отрасли.

Экран 2. Окно Choose Target Hierarchy and Calculations

Первое решение - выбор иерархии для использования в качестве основы для анализа временных расчетов - заслуживает подробного объяснения, так как существенно отличается от модели Analysis Services 2000. В Analysis Services 2000 каждая размерность содержит единственную иерархию; в Analysis Services 2005 одна размерность может содержать (и чаще всего содержит) много иерархий.

Поскольку мы имеем дело со временем, велика вероятность, что целевой куб будет иметь более одной роли. Каждая роль основывается на одной размерности базы данных, но несет уникальное значение через различное отношение внешнего ключа к таблице фактов. Например, размерность базы данных Date может выполнять различные роли (Ship Date, Bill Date, Order Date) в контексте одного куба. Размерность может иметь (и часто имеет) несколько иерархий, охватывающих различные типы календарей, например Fiscal (финансовый), Reporting (отчетный), Manufacturing (производственный) и ISO 8601. В таких случаях следует запустить мастер несколько раз, чтобы охватить несколько иерархий в различных ролях размерности. В Analysis Services 2005 мастер Dimension Wizard может за один проход автоматически генерировать размерность, которая будет содержать любые или все перечисленные типы календарей.

Затем определяется диапазон вычислений (экран 3) с указанием показателей, к которым следует применить вычисления. Мастер составляет список всех показателей куба (как физических, так и расчетных). При принятии решения о показателях следует учитывать как логическое соответствие, так и ожидания пользователей из сферы бизнеса. Выбрав показатели, нужно щелкнуть на кнопке Next, чтобы просмотреть результат выбора, а затем щелкнуть Finish, чтобы применить изменения. После этого можно исследовать куб, чтобы убедиться в его расширенных аналитических возможностях.

Экран 3. Задание диапазона вычислений с указанием нужных показателей

Изменения в структуре базы данных и метаданных

Теперь обратимся к внутренним механизмам и ответим на вопросы «как» и «почему» в области функционирования Business Intelligence Wizard, рассмотрим базовые структурные и невычислительные изменения метаданных, соответствующие различным этапам работы мастера.

Первым делом мастер генерирует новый именованный расчет (named calculation) в таблице временной размерности в Data Source View (DSV). Именованный расчет - это вычисляемый столбец, расположенный исключительно внутри DSV; в базовый источник данных никаких изменений не записывается. Именованный расчет служит источником для вычисленной иерархии атрибутов. Вычисленный атрибут содержит одно невычисляемое значение (на основе именованного расчета) в дополнение ко всем выбранным пользователем вычислениям. Мастер использует константу в качестве основы для вычислений, применяя ее ко всем строкам в таблице размерностей. Этот столбец возвращает натуральные значения в качестве выбираемой по умолчанию величины и для сравнения с расчетными представлениями.

Построив столбец, мастер создает атрибут размерности, который указывает на вычисленный столбец. Поскольку иерархия данного атрибута содержит в основном вычисления, логически агрегировать эти члены нельзя. Чтобы отметить иерархию как неаддитивную, блокируется системно-генерируемый уровень All. Свойство IsAggregatable атрибута размерности управляет существованием уровня All; поскольку нам не нужно агрегировать членов в вычисленную иерархию, ему присвоено значение False.

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

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

После создания атрибута базы данных мастер анализирует структуру куба, определяя наличие нескольких ролей для размерности целевого куба. Как отмечалось ранее, в каждом проходе мастер обрабатывает одну размерность куба, и в результате появляется один расчетный атрибут. Поскольку вычисления в контексте нецелевых ролей не используются, расчетный атрибут блокируется для ролей, к которым он не применяется.

Моделирование вычислений

После того как сформирована необходимая структура куба и размерностей, можно составить собственно вычисления. В зависимости от местонахождения куба, каждый временной расчет может существовать в одном из трех состояний.

• Состояние 1. Вычисление производится над текущей координатой и имеется достаточно данных для расчета. В этих случаях применяется само выражение.
• Состояние 2. Не существует достаточных данных для вычислений. Например, выполняется сравнение периодов, и текущий период - первый в иерархии (типичный случай, когда запрошено сравнение период/период для первого периода доступных данных в кубе). Поскольку данных недостаточно, вычисления явно возвращают нулевое значение.
• Состояние 3. Вычисление неприменимо к текущей координате. Например, если текущая координата относится к Year to Date для члена All Years или Month to Date при просмотре данных за текущий год. В этих случаях вычисления, генерируемые мастером, просто возвращают строку NA или ее локализованный эквивалент.

Чтобы выяснить, к какому состоянию относится текущая координата, обычно используется функция IIF (в Analysis Services 2000); также можно использовать оператор CASE (новшество Analysis Services 2005). Однако данный подход приводит к динамическим проверкам в ходе оценки каждой ячейки на этапе выполнения, и результирующие выражения MDX быстро становятся громоздкими, а сложность отладки повышается по мере увеличения числа вложений.

В мастере Business Intelligence Wizard реализован гораздо более эффективный и изящный подход. Мастер использует новые синтаксические конструкции сценариев MDX для определения конкретных диапазонов, к которым применяются вычисления. Analysis Services оценивают операторы SCOPE статистически (один раз) при выполнении сценария MDX, поэтому данный метод исключает необязательные проверки ячеек на этапе выполнения.

Для иллюстрации этого подхода предположим, что пользователь запросил три вычисления: Year to Date, Year Over Year Growth и Twelve Month Moving Average. В листингах 1-3 показаны фрагменты сценария MDX, генерируемого мастером для этих вычислений.

Листинг 1 строит расчетных членов. Мастер присваивает значение NA учетной записи для состояния 3, к текущей координате вычисление не применяется. Специалисты, знакомые с MDX, могут отметить новый, упрощенный синтаксис оператора CREATE.

На следующем этапе (листинг 2) устанавливается диапазон вычислений для выбранных пользователем показателей. Все показатели, не попавшие в оператор SCOPE, сохраняют значение NA, так как пользователь указал, что выбранные временные аналитические вычисления неприменимы.

После того как будет указан диапазон для корректных показателей, можно назначить более значимые выражения для расчетных членов. В листинге 3 показан пример присвоений MDX, соответствующих выбранным вычислениям. Присвоения, важнейшее усовершенствование MDX в Analysis Services 2005, позволяют применять выражения MDX к существующим ячейкам в пространстве куба; результирующие значения затем агрегируются так же, как это делается при отсутствии выражений.

Полезные приемы

Рассмотрим исходный текст MDX, генерируемый мастером, и ряд специальных приемов, которые можно задействовать при подготовке собственных вычислений в Analysis Services 2005.

Как показано в листинге 3, удобно использовать иерархии атрибутов в левой стороне операции присваивания. В Analysis Services 2005 пространство куба целиком определяется атрибутами, поэтому пространство, к которому применяются вычисления, лучше всего описать иерархиями входящих в него атрибутов. И наоборот, указание диапазона для определяемых пользователем иерархий может привести к чрезмерному ограничению области вычислений. Вычисление Year to Date иллюстрирует оптимальный метод через использование иерархии атрибута Fiscal Year, которая исключает член All (где вычисление неприменимо). Данный подход охватывает все остальные атрибуты в иерархии, независимо от того, являются ли они членами All.

Следует отметить, что в правой стороне операции приравнивания используются многоуровневые пользовательские иерархии (а не иерархии атрибутов), что позволяет применить такие иерархически ориентированные функции MDX, как ParallelPeriod и PeriodsToDate.

Еще один удобный прием - задействовать функцию Aggregate (а не Sum) в вычислении Year to Date. В Analysis Services 2005 функция Aggregate применима для обработки неаддитивных разнородных счетных и полуаддитивных показателей, а также других неаддитивных величин, таких как размерности «многие ко многим» и выражения с вычислениями. Функцию Aggregate можно применять даже поверх некоторых расчетных показателей, таких как отношения.

Изучив внутренние механизмы мастера Business Intelligence Wizard, можно развернуть модифицированный куб и взглянуть на него с точки зрения конечного пользователя.

Временной анализ - это просто

Как мы видим из данного примера, усовершенствованная процедура временного анализа, SQL Server 2005 Analysis Services, со множеством функций и встроенных улучшений, обеспечивает автоматическое, интеллектуальное решение типичных задач в сфере бизнеса. Благодаря этим функциям и значительно усовершенствованным графическим и аналитическим возможностям Analysis Services 2005 позволяет быстро развертывать (с быстрой окупаемостью затрат) BI-приложения, значительно превосходящие по своим возможностям прежние инструменты.