Настройка характеристик показа данных

Рас Уитни, SQL Server Magazine

Свойства ячеек можно использовать для выделения в аналитических приложениях исключений из общей картины.

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

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

Вычисляемые ячейки

Технология вычисляемых ячеек базируется на использовании специальных формул. Такая формула определяет значение ячейки куба; условие, которому должна удовлетворять ячейка, чтобы формула была к ней применена; область куба, к которой должна относиться вычисляемая ячейка. Определение вычисляемой ячейки также может включать информацию о свойствах ячейки. Как и формула, указанная в вычисляемой ячейке, информация о свойствах ячейки будет применяться к конкретной ячейке только в том случае, если эта ячейка удовлетворяет условию, заданному для вычисляемых ячеек. Информация о свойствах ячейки может включать строку числового формата, цвет шрифта, цвет фона, название шрифта, размер шрифта, видоизменение шрифта (то есть полужирный, курсив, подчеркнутый или перечеркнутый). Для создания вычисляемых ячеек на сервере можно воспользоваться Analysis Manager, а для их определения на клиентском компьютере следует применять запросы MDX.

Работа с вычисляемыми ячейками в Analysis Manager. Входящий в состав Analysis Manager редактор куба Cube Editor содержит мастер вычисляемых ячеек, Calculated Cells Wizard. Этот мастер помогает формировать определения вычисляемых ячеек. В качестве примера попробуем создать вычисляемую ячейку, которая должна выделять все непотребительские товары, объемы продаж которых превышают 12 000 единиц. Запустим Analysis Manager, щелкнем правой кнопкой мыши на кубе учебной многомерной базы данных FoodMart 2000 Sales, и в появившемся контекстном меню выберем пункт Edit, чтобы запустить редактор куба Cube Editor. В окне редактора Cube Editor раскроем пункт меню Insert и выберем в нем пункт вычисляемых ячеек, Calculated Cells. Это автоматически приведет к запуску мастера Calculated Cells Wizard.

Сначала мастер определяет область куба (подкуб), в котором будет действовать формула для вычисляемых ячеек. Чтобы выбрать члены, соответствующие непотребительским товарам, выберем в расположенном слева списке размерность товаров Product. Затем в правой части выберем тип множества членов, Members Set, соответствующий отдельным товарам, то есть A single member. После этого раскроем дерево размерности товаров All Products до второго уровня и выберем непотребительские товары, Non-Consumable, как показано на рисунке 1.

Теперь нажмем кнопку Next, и мастер высветит подсказку для ввода условия применения формулы (это необязательный шаг). Чтобы выделение ячейки происходило только в тех случаях, когда количество проданных единиц товара превышает 12 000 штук, следует выбрать пункт Meet the following condition, соответствующий наложению условия на ячейку, и ввести формулу условия:

CalculationPassValue((Product.CurrentMember, [Unit

 Sales]), 0 ) > 12000

Возможно, приведенная выше формула условия покажется читателям сложной. Это обусловлено тем, что она включает ссылку на саму себя. Формула условия вычисляемой ячейки проверяет значение текущей ячейки, а эта ячейка, в свою очередь, ссылается на формулу вычисляемой ячейки. Чтобы не попасть в бесконечный цикл вычисления значения одной и той же ячейки, я воспользовался функцией CalculationPassValue(). Эта функция позволяет определить содержащееся в ячейке значение до того, как будет применена формула вычисляемой ячейки. Такой подход оказывается эффективным в силу того, что формулы MDX вычисляются за несколько итераций. Поскольку Analysis Services рассчитывает значение вычисляемых ячеек после 0 итерации, можно прекратить вычисления по окончании 0 итерации. Тем самым предотвращается зацикливание формулы, порождаемое ссылкой на саму себя. Второй параметр функции CalculationPassValue() как раз и представляет собой номер итерации, после которой следует прекратить вычисление выражения MDX.

После того как будет введена формула условия и нажата кнопка Next, мастер подсказывает, что пора ввести формулу расчета. Эта формула определяет, какое значение будет содержаться в ячейке, после того как Analysis Services закончит расчет вычисляемой ячейки. Если, к примеру, вы не хотите менять значение ячейки, просто введите адрес текущей ячейки. При этом снова следует воспользоваться функцией CalculationPassValue() для предотвращения зацикливания. Расчетная формула выглядит следующим образом:

CalculationPassValue(Product.CurrentMember, 0 )
 

Нажмите Next для перехода к следующему действию и введите название вновь созданной вычисляемой ячейки. Я назвал ее <Выделение непотребительских товаров>, NonConsumable Highlight. Теперь можно нажать кнопку окончания Finish. К этому моменту у нас имеется вычисляемая ячейка, у которой пока отсутствует критерий вывода.

Чтобы определить критерий вывода в свойствах вычисляемой ячейки, выберите в расположенном слева дереве в окне редактора куба Cube Editor вновь созданную вычисляемую ячейку. Раскройте секцию свойств Properties в левом нижнем углу, а затем выберите закладку Advanced, как показано на рисунке 2.

Цвет шрифта ForeColor и цвет фона BackColor задается в палитре RGB. При этом каждый базовый тон (красный, зеленый, синий) задается коэффициентом, значения которого могут быть от 0 до 255. Коэффициент 0 соответствует самому светлому оттенку цвета, а 255 - самому темному. Следующая формула позволяет создать любую комбинацию цветов:

Color Value = (Blue * 256 * 256) + (Green * 256) + (Red)
 

К примеру, если вам нужен ярко-красный цвет, используйте для красного (red) тона коэффициент 255, а для синего (blue) и зеленого (green) тонов коэффициент 0. В результате получится значение 255. Формула для ярко-зеленого цвета выглядит так:

Color Value = (0 * 256 * 256) + (255 * 256) + (0) = 65280
 

Для свойства FontFlags, которое задает видоизменение шрифта, можно использовать следующий набор значений: 1 - для полужирного написания, 2 - для курсива, 4 - для подчеркивания и 8 - для зачеркивания. Эти свойства можно комбинировать, складывая нужные значения. К примеру, поскольку значение 3 равно 1 плюс 2, это соответствует полужирному курсиву.

Свойство Format String имеет более сложную структуру. Его потенциальные значения не относятся ни к числовому, ни к символьному, ни к временному типу данных. Для числовых величин необходимо присвоить свойству Format String от одного до четырех значений, разделенных двоеточием. Эти четыре значения определяют формат представления соответственно положительных, нулевых и отрицательных чисел, а также неопределенных величин. Если ввести не все четыре параметра, а меньше, то каждый введенный параметр будет относиться к нескольким категориям чисел. К примеру, если задать два параметра, то первый из них будет относиться к положительным числам и нулю, а второй параметр - к отрицательным числам. Неопределенные значения будут обрабатываться по умолчанию, то есть им будет соответствовать пустая строка. Ниже приведен пример назначения двух параметров в свойстве Format String:

$#,##0;($#,##0)

В данном примере для представления отрицательных чисел использованы круглые скобки, а не знак минус перед абсолютным значением числа. Подробную информацию обо всех значениях параметров свойства Format String можно найти в сети в книгах SQL Server Books Online (BOL). Раздел, посвященный использованию свойств ячеек, называется "Using Cell Properties."

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

WITH CELL CALCULATION Sample FOR `({[Product],[Product Family],&[Food]})`

AS `CalculationPassValue(Product.CurrentMember, 

0 )`,

CONDITION = `CalculationPassValue((Product

   .CurrentMember,[Unit Sales]), 0 ) > 12000`,

FORE_COLOR = `255 * 256`

SELECT [Product].[Product Family].Members on

Columns FROM Sales

CELL PROPERTIES VALUE, FORMATTED_VALUE,

 FORE_COLOR, BACK_COLOR

В этом примере зеленым цветом будут отмечены ячейки, содержащие сведения о пищевых продуктах, для которых объем продаж превышает 12 000 единиц. Значения для непотребительских товаров будут показаны синим цветом. Этот цвет был задан при определении вычисляемой ячейки средствами Analysis Manager. Значения, относящиеся к напиткам, будут показаны красным цветом (я поясню это ниже).

Последний раздел запроса MDX, начинающийся с ключевых слов CELL PROPERTIES, указывает, какие свойства ячеек будут использоваться в представлении результатов обработки запроса. При использовании учебного приложения MDX или же любого другого приложения OLAP необходимо включать в запрос MDX ключевые слова CELL PROPERTIES, если вы хотите, чтобы результаты обработки этого запроса были показаны заданным цветом и шрифтом. Свойство Format String будет выполняться независимо от того, включены ли в запрос ключевые слова CELL PROPERTIES. Это обусловлено тем, что при выводе результатов обработки запроса на экран дисплея по умолчанию используются значения VALUE, FORMATTED_VALUE, а также ORDINAL. VALUE и FORMATTED_VALUE для представления соответственно числового и символьного содержания ячеек. Значение FORMATTED_VALUE используется свойством Format String независимо от того, было ли это указано в запросе. Значение порядкового номера ORDINAL представляет собой уникальный индекс ячейки в наборе данных, который содержит результаты обработки запроса.

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

Вычисляемые члены

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

WITH MEMBER [Measures].[Unit Sales Highlight] as

`[Unit Sales]`,

FORE_COLOR = `iif([Unit Sales] > 12000, 255 * 256,

 iif([Unit Sales] > 5000, 255, 0))`,

FONT_FLAGS = `iif([Unit Sales] > 12000, 2,

 iif([Unit Sales] < 5000, 1, 0))`

SELECT {[Measures].[Unit Sales Highlight]} on

 COLUMNS,

[Drink],Children on ROWS

FROM Sales

CELL PROPERTIES VALUE, FORMATTED_VALUE,

 

 FORE_COLOR, FONT_FLAGS

 

 

 

Для выделения высоких и низких значений вместо показателя объема продаж Unit Sales использованы вычисляемые члены. Значения, превышающие 12 000, вычисляемый член показывает зеленым курсивом, а значения до 5 000 вычисляемый член показывает полужирным шрифтом красного цвета. Я анализировал различия в использовании вычисляемых членов и вычисляемых ячеек в статье "Сила вычисляемых ячеек" [опубликованной в 3 номере журнала SQL Server Magazine Online - прим.ред.]. Здесь важно отметить, что вычисляемые ячейки поддерживаются только версией SQL Server 2000 Enterprise Edition, в то время как вычисляемые члены поддерживаются всеми версиями SQL Server 2000.

Вычисляемые члены можно задать на сервере через Analysis Manager, а на клиентской машине - с помощью запросов MDX. Один метод состоит в том, чтобы сначала воспользоваться средствами Analysis Manager для определения показателя, значение которого будет выделяться разными цветами в зависимости от его величины. После этого следует скрыть показатель, не предусматривающий выделения исключений (Unit Sales). Теперь клиентские приложения смогут выбрать только показатель с выделением значений. Другой метод заключается в том, чтобы при помощи Analysis Manager сделать новый вычисляемый член используемым по умолчанию в кубе (эта возможность является новой для Analysis Services).

Возможности членов, заданных пользователем

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

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

Чтобы воспользоваться задаваемыми пользователем членами для определения свойств ячеек, необходимо сделать следующее:

• Включить задаваемые пользователем члены Custom Members в размерность или в указанный уровень размерности.
• Включить параметры задаваемых пользователем членов Custom Member Options для выбранной размерности или ее уровня.
• Заполнить параметры члена в таблице размерности.
• Повторно обработать размерность.
• Повторно обработать куб.

Только у задаваемых пользователем членов могут быть параметры пользовательских членов. Задаваемые пользователем члены представляют собой члены размерности, которые используют для определения значений членов столбец таблицы размерности, содержащий формулы MDX. Чтобы активизировать задаваемые пользователем члены, следует запустить Analysis Manager и раскрыть дерево для просмотра общих размерностей многомерной базы данных FoodMart 2000. Щелкните правой кнопкой мыши на размерности товаров Product и выберите из контекстного меню пункт Edit. При этом автоматически запустится редактор размерностей Dimension Editor. В редакторе Dimension Editor выберите в дереве семейство товаров Product Family, а затем раскройте секцию свойств Properties, которая расположена слева внизу. Щелкните по закладке Advanced и прокрутите вниз перечень свойств до тех пор, пока не дойдете до свойства с меткой Custom Members. Щелкните по эллипсу, который находится рядом со значением свойства задаваемого пользователем члена Custom Member. На экране появится диалоговое окно Define Custom Member Column, предназначенное для определения столбца задаваемого пользователем члена. Это окно показано на рисунке 3.

Теперь можно активизировать задаваемые пользователем члены и создать для их хранения новый столбец в таблице размерности товаров. Введите в качестве названия столбца FamilyCustomMembers и нажмите OK.

После того как будут установлены пользовательские члены, можно приступить к активизации их параметров. Для этого следует щелкнуть по эллипсу рядом со значением свойства задаваемого пользователем члена. При этом появится диалоговое окно Define Custom Member Options, показанное на рисунке 4.

Это диалоговое окно, предназначенное для определения параметров, позволяет активизировать параметры пользовательских членов и создать еще один столбец в таблице размерности. Этот столбец будет содержать пары, состоящие из названия свойства ячейки и его значения. Я назвал этот столбец FamilyMemberOptions.

Теперь сохраните все внесенные изменения и запустите Microsoft Access, чтобы просмотреть многомерную базу данных FoodMart 2000. Напомню, что файл FoodMart 2000.mdb находится в каталоге \program files\microsoft analysis services\samples. После этого запустите следующий запрос, чтобы определить свойства ячейки для членов размерности, относящихся к семейству напитков Drink:

UPDATE product_class

SET product_class.FamilyMemberOptions =

 "FORE_COLOR=`255`",

 product_class.FamilyCustomMembers =

  "Aggregate(Product.CurrentMember.Children)"

WHERE (((product_class.product_family)="Drink"));

После этого запустите Analysis Manager заново и проведите повторную обработку размерности товаров и куба продаж Sales. Теперь в окне просмотра данных Analysis Manager в ячейках, содержащих информацию о продаже товаров из семейства напитков, значения будут показаны красным цветом.

Мощный инструмент

При разработке аналитических приложений целесообразно воспользоваться возможностями, предоставляемыми свойствами ячеек. Иными словами, при генерации запроса MDX аналитическим приложением следует запросить и свойства ячейки и извлечь их вместе с результатами обработки запроса. В листинге 1 приведена программа, написанная на Visual Basic (VB), которая демонстрирует, как средствами ADO запрашивать и извлекать свойства ячеек.

Запрос и извлечение свойств ячеек.

Private Sub Form_Load()

  ` Открыть соединение с  OLAP Services на локальном компьютере

  Dim gConnection As Connection

  Set gConnection = New Connection

  gConnection.Open "Data Source=LocalHost;Provider=MSOLAP"

  ` Убедиться в том, что работаем с базой данных FoodMart

  If gConnection.DefaultDatabase <> "FoodMart" Then

    gConnection.DefaultDatabase = "FoodMart 2000"

  End If

  ` Объявить переменные (набор ячеек) для хранения результатов запроса

  Dim oCellSet As Cellset

  ` Запустить простой одномерный запрос MDX

  Set oCellSet = New Cellset

  oCellSet.Open "WITH CELL CALCULATION Fred FOR `({[Product].[Product Family].&[Food]})` " & _

    "AS `CalculationPassValue(Product.CurrentMember, 0 )`, " & _

    "CONDITION = `CalculationPassValue(Product.CurrentMember, 0 ) > 12000`, " & _

    "FORE_COLOR = `255 * 256` " & _

    "SELECT [Product].[Product Family].Members on COLUMNS " & _

    "FROM Sales " & _

    "CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR", _

    gConnection

  ` Объявить переменные, необходимые для показа результатов

  Dim oAxis As Axis

  Dim nDim As Integer

  Dim oMember As Member

  Dim nCell As Integer

  Dim oCell As Cell

  Dim oProperty As Property

  Dim oPosition As Position

  ` Организовать цикл просмотра ячеек по столбцам результатов,

  ` выступающим в роли осей

  For nCell = 0 To oCellSet.Axes(0).Positions.Count - 1

    ` Получить ссылку на ячейку

    Set oCell = oCellSet(nCell)

    ` Получить ссылку на объект, позиционирующий ось

    Set oPosition = oCellSet.Axes(0).Positions(nCell)

    ` Вывести порядковый номер ячейки, имя члена и число свойств для этой позиции

    Debug.Print "Cell(" & oCell.Ordinal & ") " & oPosition.Members(0).Name & "=" & oCell.FormattedValue

& " has " & oCell.Properties.Count & " Properties"

    ` Вывести в цикле свойства ячейки для этой позиции и их значения

    For Each oProperty In oCell.Properties

      Debug.Print "Property " & oProperty.Name & " = " & oProperty.Value

    Next

  Next nCell

  Set oCellSet = Nothing

  Set gConnection = Nothing

End Sub

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

Об авторе

Рас Уитни возглавляет исследовательское отделение в компании Knosys, руководит разработкой клиентского инструментария OLAP и входит в совет директоров компаний Knosys и Distributed Database Consulting (DDBC).