Програмування запитів MDX – ЧАСТИНА 2

Ще одним обмеженням на створення множин для запитів 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 році

•&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Descendants([Date][Calendar][Calendar

Year] & [2003], 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] &amp[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]&amp[Germany],

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

[Date][Calendar Year][Calendar Year]£[2004]})

виглядає як кортеж, однак MDX неявно перетворює перший член (клієнти з Німеччини) в безліч, а потім згенерує перехресне твір, повертаючи безліч з двох кортежів

Використання SQL Server Management Studio

Імена обєктів в кубі можуть бути досить довгими, що ускладнює їх безпомилковий ввід На щастя, утиліта SQL Server Management Studio пропонує зручний графічний інтерфейс, в якому імена функцій MDX і обєктів можна вставляти методом перетягування Почнемо з відкриття нового запиту MDX служби аналізу і виберемо на панелі інструментів відповідну базу даних служби аналізу, а також куб призначення у верхньому лівому кутку вікна запиту Вкладка Metadata (рис 452) автоматично наповниться усіма заходами, вимірами та іншими обєктами даного куба Після цього запити MDX можна створювати, перетягуючи обєкти на панель сценарію або переключившись у вкладку Functions, таким же чином перетягувати визначення функцій

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

інформація \

Рис 452 Вкладка Metadata утиліти SQL Server Management Studio

Розробник куба може при бажанні згрупувати ієрархії вимірювання в папки, також показані на рис 452 Папки є зручним засобом організації довгих списків ієрархій і абсолютно не надають впливу на структуру куба і методику написання запитів 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] &amp[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] )&gt005

)

SET [TimeFrame] AS LastPeriods(9,[Date][Calendar][Month]&amp[2004]&amp[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]*105 – [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] &amp[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] &amp[2004]&amp[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 року потрібно або послатися на рівень [АН] календарного року, або, якщо розробник куба придушив цей рівень, – на член [2004] ієрархії року Визначень членів за замовчуванням зазвичай уникають, проте в деяких ситуаціях вони можуть виявитися корисними У такому випадку при створенні запитів слід враховувати всі ієрархії, які мають членів за замовчуванням

■ Автоматична перевірка існування і наявність значення Приміщення безлічі {[Date] [Calendar Year] [Calendar Year] Members * [Date] [Calendar] [Month] Members} на вісь запиту призведе до перерахування 2001 зі своїми місяцями, 2002 года зі своїми місяцями і тд Питається, чому ж у результаті перехресного обєднання не було отримано даний перехресне твір множин Справа в тому, що служба аналізу автоматично визначає, які члени ієрархій співіснують, і повертає тільки прийнятні комбінації Цей режим роботи називають автоматичною перевіркою існування він функціонує тільки з ієрархіями одного виміру Параметр NON EMPTY використовується для того, щоб далі обмежити безлічі тільки тими комбінаціями, які мають відповідні значення заходів

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*