Главная » Microsoft SQL Server, Базы данных » Программирование запросов MDX – ЧАСТЬ 2

0

Еще одним ограничением на создание множеств для запросов MDX является то, что каждая иерархия может указываться только для одной оси или определения среза. Если иерархия Calendar Year (календарный год) явно указана в определении строки, то она не может снова появиться в срезе. Это ограничение применяется в пределах исключительно одной иерархии. Если некоторая другая иерархия также содержит календарный год (например, иерархия Calendar в базе Adventure Works), то она может быть указана для одной оси, в то время как иерархия Calndar Year — для другой.

Меры

Меры являются значениями, для представления которых создавался куб. Все они доступны запросам MDX как члены всегда присутствующего измерения Measures. Это измерение не имеет иерархий или уровней, поэтому ссылка на любую меру выполняется непосредственно из уровня измерения: [Measures] . [имя_меры]. Если в запросе явно не указано ни одной меры, то используется мера, принятая в кубе по умолчанию.

Генерация множеств из функций

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

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

SELECT

{[Measures].[Internet Sales Amount],

[Measures].[Internet Total Product Cost]} ON COLUMNS,

{ } ON ROWS

FROM [Adventure Works]

?               .Members. Эта функция перечисляет всех членов либо иерархии, либо уровня. При использовании с уровнем будут перечислены все его члены (например, [Date] . [Calendar] . [Month] .Members вернет все календарные месяцы). При использовании с иерархиями будут перечислены все члены каждого из уровней (например, [Date] . [Calendar] .Members вернет все годы, семестры, кварталы, месяцы и дни).

?               .Children. Перечисление всех потомков данного члена (например, [Date] . [Calendar] . [Calendar Quarter] & [2002] & [1] .Children вернет все месяцы первого квартала 2002 года).

?               Descendants (начало [, глубина [, флаг] ] ). Получение списка всех потомков, их потомков и т.д. некоторого члена или набора членов. В качестве аргумента начало укажите члена или набор членов, глубина — это либо имя конкретного уровня, либо количество уровней ниже члена начало. По умолчанию, если аргумент глубина явно определен, перечисляются только наследники данного уровня. Аргумент флаг может скорректировать этот режим работы, позволяя отображать уровни ниже и выше заданного, а также самого заданного уровня в любой комбинации. Этот флаг может принимать следующие значения: SELF, AFTER, BEFORE, BEFORE_AND_AFTER, SELF_AND_AFTER, SELF_AND_BEFORE, SELF_BEFORE_AFTER. Приведем несколько примеров.

•           Descendants ( [Date] . [Calendar] . [Calendar Year] . & [2003] ). Перечисляет год, семестры, кварталы, месяцы и дни в 2003 году.

•           Descendants([Date].[Calendar].[Calendar Year]. &[2003], [Date]. [Calendar] . [Month] ). Перечисляет месяцы в 2003 году.

•           Descendants([Date].[Calendar].[Calendar

Year] . & [2 003 ] , 3 , SELF_AND_AFTER). Перечисляет месяцы и дни в 2003 году.

?               LastPeriods {п, член). Возвращает п периодов, заканчивающихся членом член (например, LastPeriods(12, [Date] . [Calendar] . [Month] .&[2004]&[6] вернет месяцы с июля 2003 года по июнь 2004 года). Если значение п отрицательно, будут возвращены будущие периоды, начиная с член.

?               TopCount (множество, п [, числовое_выражение] ). Возвращает первые п элементов множества, отсортированные по числовому_выражению (например, TopCount([Date] . [Calendar] . [Month] .Members,5, [Measures] . [Internet Sales Amount] ) вернет первые пять по объему продаж Интернет-месяцев). Стоит ознакомиться также и с аналогичными функциями: BottomCount, TopPercent и BottomPercent.

В отличие от функции TopCount и ее “побратимов” большинство функций, работающих с множествами, не предполагают выполнения сортировки — они возвращают члены в том порядке, в котором они хранятся в кубе. Для сортировки можно воспользоваться специальной функцией Order. Ее синтаксис следующий: Order {множество, sort_by [ , { ASC | DESC | В AS С | BDESC } ] ). Укажите множество, которое следует сортировать, и, по желанию, порядок сортировки. Параметры AS С и DESC указывают на сортировку элементов в пределах уровней иерархии. Например, сортировка месяцев в иерархии Calendar базы Adventure-

Works с использованием одного из этих параметров приведет к упорядочению месяцев в пределах квартала (следующий уровень иерархии), но не в пространстве всего множества. Параметры В AS С и BDESC позволяют выполнить сортировку без учета границ, устанавливаемых родителями члена.

Сгенерированные множества часто содержат членов, для которых недоступны данные мер. Эти члены могут быть подавлены путем дополнения определения оси спереди ключом NON EMPTY. В следующем примере будут извлечены объемы продаж в разрезе торговых представителей по месяцам 2004 года. Ключ NON EMPTY здесь использован для заголовков столбцов, поскольку для некоторых месяцев 2004 года куб вообще не содержит данных. Также этот ключ может оказаться полезным и для определений строк, поскольку не каждый сотрудник является торговым представителем. В приведенном примере использована функция Order для упорядочения торговых производителей по их объемам продаж в 2004 году. Обратите внимание на то, что sort_by является кортежем, определяющим объемы продаж в 2004 году. Если опустить кортеж [Date] . [Calendar] . [Calendar Year] . &[2004], то сортировка проводилась бы по объемам продаж в пределах всего периода накопления данных. SELECT

NON EMPTY {Descendants([Date].[Calendar].[Calendar Year].?[2004], 3)} ON COLUMNS,

NON EMPTY {

Order(

[Employee] . [Employee] .Members,

( [Date] . [Calendar] . [Calendar Year] .&[2004],

[Measures].[Reseller Sales Amount]),

BDESC

)

} ON ROWS

FROM [Adventure Works]

WHERE ([Measures].[Reseller Sales Amount])

Результат приведенного запроса будет следующим:

January 2004

February 2004

June 2004

All Employees

$1662547,32

$2700766,80

$3415479,07

Linda С. Mitchell

$117697,41

$497155,98

$282711,04

Jae В. Park

$219443,93

$205602,75

$439784,05

Stephen В. Jiang

$70815,36

(null)

$37652,92

Amy E. Alberts

$323,99

$42041,96

(null)

Syed E. Abbas

$3936,02

$1376,99

$4197,11

Все эти сгенерированные множества содержат единственную иерархию. Спрашивается, как сгенерировано множество заголовков? Функция Cross join генерирует перекрестное произведение любого количества множеств, в результате создавая одно большое множество с кортежами, составленными из всевозможных комбинаций множеств источника. Например, Crossjoin( [Product] . [Product Line] . [Product Line] .Members, [Sales Territory] . [Sales Territory Country] . [Sales Territory Country] .Members) будет содержать два уровня заголовков, перечисляющих товарную линию (Product Line) и страну продаж (Sales Territory Country). В качестве альтернативы для генерации перекрестного произведения между множествами можно использовать оператор перекрестного объединения (*). Заключение списка множеств, разделенных запятыми, в скобки даст тот же эффект. Однако эта конструкция в некоторых контекстах может выглядеть странно. Например, конструкция

([Customer].[Country].[Country].&[Germany],

{[Date].[Calendar Year].[Calendar Year].&[2003],

[Date].[Calendar Year].[Calendar Year].?[2004]})

выглядит как кортеж, однако MDX неявно преобразует первый член (клиенты из Германии) в множество, а затем сгенерирует перекрестное произведение, возвращая множество из двух кортежей.

Использование SQL Server Management Studio

Имена объектов в кубе могут быть достаточно длинными, что усложняет их безошибочный ввод. К счастью, утилита SQL Server Management Studio предлагает удобный графический интерфейс, в котором имена функций MDX и объектов можно вставлять методом перетаскивания. Начнем с открытия нового запроса MDX службы анализа и выберем на панели инструментов соответствующую базу данных службы анализа, а также куб назначения в верхнем левом углу окна запроса. Вкладка Metadata (рис. 45.2) автоматически наполнится всеми мерами, измерениями и прочими объектами данного куба. После этого запросы MDX можно создавать, перетаскивая объекты на панель сценария или переключившись во вкладку Functions, таким же образом перетаскивать определения функций.

гДополнителбная Более подробно об утилите SQL Server Management Studio см. в главе 6.

информация \

Рис. 45.2. Вкладка Metadata утилиты SQL Server Management Studio

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

Расширенные запросы select

Помимо базового процесса генерации таблиц, описанного ранее в этой главе, синтаксис инструкции SELECT, показанный ниже, также включает часто используемые функции.

[ WITH <вычисление \ множество [ , <вычисление \ множество . . . ] ] SELECT [ <множество> on О

[ , <множество> on 1 … ] ]

FROM <куб> | <подкуб>

[ WHERE ( <множество> ) ]

Инструкция SELECT может возвращать от 0 до 128 осей, где первые пять имеют следующие псевдонимы: ROWS, COLUMNS, PAGES, SECTIONS и CHAPTERS. В качестве альтернативы оси могут именоваться и с указанием порядковых номеров: AXIS(0),AXIS(1) и т.д.

По мере усложнения запросов пропорционально возрастает необходимость их ясности и документирования. Разбивайте длинные запросы на несколько строк и используйте отступы, чтобы визуально организовать вложенные аргументы. Используйте комментарии о назначении определенных элементов запроса, отделяя их двумя тире (–) или двумя косыми чертами (//) в конце строки. Можно также использовать и многострочные комментарии, применяя следующий синтаксис: /* комментарий */.

Подкубы

Подкуб определяется в предложении FROM, где другая инструкция SELECT с именем другого куба заключается в скобки. Этот механизм работает практически так же, как и управляемые таблицы в SQL, за исключением того, что управляемые таблицы включают только явно идентифицированные столбцы, а подкуб содержит все иерархии результата, за исключением имеющих ограниченное членство. В следующем примере создается подкуб, содержащий десять лучших товаров и пять месяцев с наиболее высокими объемами продаж (в расчет при этом принимаются все продажи в регионе США). После этого количества заказов суммируются по дням недели и подкатегориям: SELECT

{ [Date] . [Day Name] .Members} on Columns,

{ [Product] . [Subcategory] . [Subcategory] .Members} ON ROWS FROM (SELECT

{TOPCOUNT([Product].[Model Name].[Model Name].Members, 10, [Measures].[Internet Sales Amount])} ON COLUMNS,

{TOPCOUNT([Date].[Calendar].[Month], 5,

[Measures].[Internet Sales Amount])} ON ROWS FROM [Adventure Works]

WHERE ([Customer].[Country]. &[United States]))

WHERE ([Measures].[Internet Order Count])

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

Предложение with

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

Множества и вычисления также могут быть определены в составе куба (подробно об этом — в следующем разделе “Сценарии MDX”). Если какой-либо элемент будет использоваться для чего-то большего, нежели подмножество запроса, создавайте его как часть куба. Это сделает его доступным глобально и централизованно настраиваемым.

В предыдущих версиях MDX требовалось, чтобы все элементы with заключались в одиночные кавычки (например, WITH [MySet] AS ‘определение’). Теперь эти кавычки не являются обязательными и в приводимых ниже примерах будут опущены.

Множества

Добавьте в предложение WITH именованное множество, используя синтаксис имя_ множества AS определение, где имя_множества — любой допустимый идентификатор, а определение определяет множество, допустимое для использования в оси или предложении WHERE. В следующем примере моделируются три множества для исследования девятимесячных трендов товаров, имеющих более чем 5%-ный рост объемов продаж в 2004 году: WITH

SET [ProductList] AS

Filter( [Product].[Product].[Product].Members,

([Date] . [Calendar Year] .?[2004] ,

[Measures] . [Internet Ratio to All Products] )>0.05

)

SET [TimeFrame] AS LastPeriods(9,[Date].[Calendar].[Month].&[2004]&[6])

SET [MeasureList] AS {

[Measures].[Internet Order Count],

[Measures].[Internet Sales Amount]

}

SELECT

{[MeasureList]*[ProductList]} ON COLUMNS,

{[TimeFrame]} ON ROWS FROM [Adventure Works]

Данный запрос дает следующий результат:

Internet Order … Count

Internet Order Count

Internet Sales … Amount

Internet Sales Amount

Mountain-200 Silver, 38

Mountain-200 Black, 46

Mountain-200 Silver, 38

Mountain-200 Black, 46

Octover 2003

29

29

$67279,71

$66554,71

November 2003

28

31

$64959,72

$71144,69

December 2003

32

42

$74239,68

$96389,58

January 2004

28

36

$64959,72

$82619,64

February 2004

36

34

$83519,64

$78029,66

March 2004

35

33

$81199,65

$75734,67

April 2004

45

34

$104399,55

$78029,66

May 2004

48

50

$111359,52

$114749,50

June 2004

62

44

$143893,38

$100979,56

В приведенном примере использовалась функция Filter, которая ограничивает множество товаров только теми, которые имели рост объема продаж больше 5%. Эта функция имеет следующий общий синтаксис: Filter (множество, условие).

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

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

Несмотря на то что синтаксис вычисляемых членов аналогичен синтаксису множеств (имя_члена AS определение), имя члена должно подходить существующей иерархии, как показано в следующем примере:

WITH

MEMBER [Measures].[GPM After 5% Increase] AS ( [Measures].[Internet Sales Amount]*1.05 – [Measures].[Internet Total Product Cost] ) /

[Measures].[Internet Sales Amount], FORMAT_STRING = ‘Percent1 MEMBER [Product].[Subcategory].[Total] AS [Product].[Subcategory].[All Products]

SELECT

{[Measures].[Internet Gross Profit Margin],

[Measures].[GPM After 5% Increase]} ON 0,

NON EMPTY{ [Product] . [Subcategory] . [Subcategory] .Members,

[Product].[Subcategory].[Total]} ON 1 FROM [Adventure Works]

WHERE ( [Date] . [Calendar] . [Calendar Year] .?[2004])

Данный запрос даст следующий результат:

Internet Gross Profit Margin

GPM after 5% Increase

Bike Racks

62,60%

67,60%

Bike Stands

62,60%

67,60%

Bottles and Cages

62,60%

67,60%

Touring Bikes

37,84%

42,84%

Vests

62,60%

67,60%

Total

41,45%

46,45%

В приведенном примере исследовался текущий рост прибыли и рост прибыли “что, если” по подкатегориям товаров, включая общий объем по всем подкатегориям. Обратите внимание на то, что для согласования с другими иерархиями, использующимися на оси запроса, созданы специальные имена. Необязательный модификатор FORMAT_STRING устанавливает формат отображения вычисляемых элементов. Куб источника содержит форматы по умолчанию для каждой меры, в то же время меры, созданные вычислениями, вероятнее всего, потребуют особого форматирования. Итог [Product] . [Subcategory] . [Total], подобно другим окончательным и промежуточным итогам, может быть основан на родительском члене (в данном случае это уровень [А11 ]) для обеспечения соответствующего значения:

WITH

SET [Top2 0ProductList] AS

TOPCOUNT([Product].[Product].[Product].Members,

20,

([Date] . [Calendar] . [Calendar Year] .&[2004] ,

[Measures].[Internet Order Count]))

SET [NotTop2 0ProductList] AS Order(

Filter(

{[Product].[Product].[Product].Members – [Top20ProductList] }, NOT IsEmpty([Measures] . [Internet Order Count] )),

[Measures].[Internet Order Count],BDESC)

MEMBER [Measures].[Average Top2 0ProductList Order Count] AS AVG([Top20ProductList],[Measures].[Internet Order Count]) MEMBER [Measures].[Difference from Top2 0 Products] AS [Measures].[Internet Order Count] – [Measures].[Average Top2 0ProductList Order Count]

MEMBER [Product].[Product].[Top 2 0 Products] AS AVG([Top2 0ProductList])

SELECT

{[Measures].[Internet Order Count],

[Measures].[Difference from Top2 0 Products] } ON COLUMNS,

{[Product].[Product].[Top 2 0 Products],

[NotTop2 0ProductList]} ON ROWS FROM [Adventure Works]

WHERE ( [Date] . [Calendar] . [Month] .&[2004]&[6])

Будет получен следующий результат:

Internet Order Count

Difference from Top20 Products

Тор20 Products

176

0

Hydration Pack – 70 oz

76

-100

Mountain-200 Silver, 38

62

-114

Touring-3000 Yellow, 54

4

-172

Touring 3000 Yellow, 58

4

-172

Mountain-500 Black, 40

2

-174

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

?               Тор2 OProductList. Создает список двадцати самых ходовых по количеству заказов товаров за весь 2004 год.

?               NotTop2OProductList. Создает список всех товаров, не вошедших в двадцатку самых ходовых. Оператор “исключение” (~) используется для удаления двадцати самых продаваемых товаров из общего списка. Этот список фильтруется для исключения также пустых членов, а затем упорядочивается согласно уменьшению количества заказов.

?               Average Тор2OProductList Order Count. Вычисление среднего количества заказов среди двадцати самых ходовых товаров. Аналогичные итоговые функции типа SUM, MIN, МАХ и MEDIAN используют один и тот же синтаксис: AVG (множество [, числовое_выражение] ). На практике это вычисление, скорее всего, было бы реализовано в составе другого вычисления; здесь же оно было включено для демонстрации зависимости одного вычисления от другого.

?               Difference from Тор2 0 Products. Разность между количеством заказов конкретного товара и средним значением верхней двадцатки.

?               Тор 2 0 Products. Создается как часть иерархии товаров для получения строки, отображающей среднее значение верхней двадцатки. Так как эта строка должна отображаться для массы мер, числовое_выражение опущено — оно будет вычислено в контексте отображаемой ячейки.

Параметры измерений

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

?               MdxMissingMemberMode. Этот параметр измерения, для которого установлено значение true, позволяет запросу игнорировать некорректные члены, являющиеся частью запроса, не генерируя ошибку. Например, если ось определена как { [Product] . [Product] . [Mountain-100 Silver, 38] , [Product] . [Product] . [Banana]}, a Banana не является корректным названием товара, ошибка не будет сгенерирована. Вместо этого в результате будут перечислены горные велосипеды, а совсем не фрукты. Если установить для этого параметра значение false, то для ошибочных имен членов будут генерироваться ошибки. Сценарии MDX (вычисления, описанные в определении куба) всегда вызывают ошибку для отсутствующих членов, независимо от установок данного параметра.

?               IgnoreUnrelatedDimensions. Если равно true, это свойство группы мер заставляет MDX игнорировать измерения, не связанные с опрашиваемой группой мер. Например, измерение для сотрудников в базе Ad venture Works никак не связано с мерами для Интернета, поскольку торговые представители не участвуют в Интернет-продажах. По этой причине в результатах запроса

SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,

{[Employee].[Employee].[Employee].Members} ON ROWS FROM [Adventure Works]

будут перечислены все сотрудники, и для каждого из них будет указан общий объем Интернет-продаж. Это является следствием удовлетворения следующим требованиям: перечислить всех сотрудников и игнорировать несвязанные измерения при подсчете Интернет-продаж. Альтернативное значение этого параметра (false) приведет к тому, что все сотрудники также будут перечислены, но их объем продаж будет иметь пустое значение null. По умолчанию для этого параметра установлено значение true, и это решение является более гибким; в то же время следует быть особо внимательным при написании запросов MDX.

?               Если для указанной иерархии был определен член по умолчанию, результаты будут ограничены только связанными с ним значениями, если в запросе явно не указано другое значение этой иерархии. Например, если членом по умолчанию для года является [Date] . [Calendar] . [Calendar Year] . & [2003], то ссылка в запросе на [Date] . [Calendar] . [Month] . & [2004] & [б] без указания иерархии календарного кода приведет к отсутствию результатов запроса. Чтобы извлечь данные для июня 2004 года, нужно либо сослаться на уровень [АН] календарного года, либо, если разработчик куба подавил этот уровень, — на член [2 004] иерархии года. Определений членов по умолчанию обычно избегают, однако в некоторых ситуациях они могут оказаться полезными. В таком случае при создании запросов следует учитывать все иерархии, которые имеют членов по умолчанию.

?               Автоматическая проверка существования и наличие значения. Помещение множества { [Date] . [Calendar Year] . [Calendar Year] .Members * [Date] . [Calendar] . [Month] .Members} на ось запроса приведет к перечислению 2001 года со своими месяцами, 2002 года со своими месяцами и т.д. Спрашивается, почему же в результате перекрестного объединения не было получено настоящее перекрестное произведение множеств? Дело в том, что служба анализа автоматически определяет, какие члены иерархий сосуществуют, и возвращает только приемлемые комбинации. Этот режим работы называют автоматической проверкой существования; он функционирует только с иерархиями одного измерения. Параметр NON EMPTY используется для того, чтобы далее ограничить множества только теми комбинациями, которые имеют соответствующие значения мер.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии