Введение в SQL Server Analysis Services для разработчика. Особенности диалекта SQL в MDX.
В прошлом посте было рассказано о том, что измерения и группы мер в Analysis Services можно запрашивать не только с помощью MDXовского SELECT, но и с помощью обычного, если можно так выразиться, SQLного SELECT-запроса. Соответствующий диалект SQL понимается Analysis Services и может быть использован, например, из ADOMD.NET-приложения аналогично MDX. В данном посте мы попытаемся нащупать правила и очертить возможности этого диалекта.
Первое, что можно увидеть по результатам запроса Рис.11 предыдущего поста, - что SSAS, подобно классической СУБД, обладает понятиями TABLE_CATALOG (база данных), TABLE_SCHEMA и TABLE_NAME. В предыдущем посте мы использовали только имя схемы и имя таблицы. При желании можно было использовать полную нотацию, однако указание имени базы в имени таблицы, по большому счету, ни на что не влияет. Если мы находимся в контексте нужной базы, его указывать, понятно, необязательно, а если нет, то все равно не поможет. Пример: полная нотация с указанием имени базы не помогает. Требуется явно установить соединение SSMS с нужной базой.
Рис.1
В списке select возможно указание конкретных полей и их элиасование. Например, чтобы максимально уподобить запрос Рис.6 результатам реляционного запроса Рис.7 предыдущего поста, можно написать
select [$Promotion.Promotion] as PromotionKey, [$Sales Territory.Sales Territory Region] as SalesTerritoryKey,
[$Source Currency.Source Currency Code] as CurrencyKey, [$Customer.Customer] as CustomerKey,
[$Product.Product] as ProductKey, [$Date.Date] as OrderDateKey, [$Ship Date.Date] as ShipDateKey,
[$Delivery Date.Date] as DueDateKey,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
Скрипт 1
Рис.2
Можно даже еще максимальней, чтобы вместо имен атрибутов выдавались ключи:
select KEY([$Promotion.Promotion]) as PromotionKey, KEY([$Sales Territory.Sales Territory Region]) as SalesTerritoryKey,
KEY([$Source Currency.Source Currency Code]) as CurrencyKey, KEY([$Customer.Customer]) as CustomerKey,
KEY([$Product.Product]) as ProductKey, KEY([$Date.Date]) as OrderDateKey, KEY([$Ship Date.Date]) as ShipDateKey,
KEY([$Delivery Date.Date]) as DueDateKey,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
Скрипт 2
Рис.3
Как нетрудно догадаться, KEY() – это функция, которая возвращает ключевое значение атрибута. Из подсмотренного в профайлере Скрипта 2 предыдущего поста видно, что у этой функции может быть второй аргумент – число. Выполнив этот скрипт, можно видеть, что второй аргумент – это смещение по цепочке связанных атрибутов. Например, KEY(от почтового индекса 01071, 1) – это ключ его родительского атрибута – города Dresden. KEY(от почтового индекса 01071, 2) – это ключ его дедушки, земли Hessen, и т.д. Для превращения ключа обратно в имя используется функция NAME().
Список функций, поддерживаемых в MDXовском SQL, можно посмотреть в файле %ProgramFiles%\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\Cartridges\as90.xsl. В этой папке лежат xslи, содержащие инструкции по генерации SQL к разным источникам данных во время процессинга куба: к сайбейзу (то есть уже сапу), ораклу, дибиту, даже к информиксу. А также к сиквелу разных версий и AS:
Рис.4
Вообще замечательный xsl. Там, например, при желании еще можно найти такие строчки
<xsl:template match="/">
<xsl:element name="Statement">
...
<!-- Select statement -->
<xsl:template match="Select">
...
из которых в общих чертах становится понятна структура поддерживаемого оператора SELECT. Как мы уже видели (Скрипт 2 предыдущего поста), поддерживается DISTINCT. Также поддерживается условие фильтрации при помощи предиката WHERE:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, [$Date.Date] as OrderDate, [$Ship Date.Date] as ShipDate,
[$Delivery Date.Date] as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia'
Скрипт 3
Рис.5
Однако в предикате WHERE из операндов в условиях поддерживается только =. Попытка использовать, допустим, > не удается:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, [$Date.Date] as OrderDate, [$Ship Date.Date] as ShipDate,
[$Delivery Date.Date] as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [Internet Unit Price] > 3000
--------------------------------------------------------------------------------
Executing the query ...
Query (8, 67) Errors from the SQL query module: There is an unexpected operator in the WHERE clause. Only the Equals operator ( = ) is supported.
Execution complete
Скрипт 4
Условия в WHERE можно соединять между собой только с помощью AND. Логический оператор OR, разработчики SSAS как-то упустили за ненадобностью, поэтому, например, этот запрос работает:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, [$Date.Date] as OrderDate, [$Ship Date.Date] as ShipDate,
[$Delivery Date.Date] as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
Скрипт 5
а этот уже нет:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, [$Date.Date] as OrderDate, [$Ship Date.Date] as ShipDate,
[$Delivery Date.Date] as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' OR [$Product.Product] = 'Road-150 Red, 44'
--------------------------------------------------------------------------------
Executing the query ...
Query (129, 68) Parser: The syntax for '[Sales Territory]' is incorrect.
Execution complete
Скрипт 6
А на оператор NOT они вообще забили и даже ошибку не стали обрабатывать:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, [$Date.Date] as OrderDate, [$Ship Date.Date] as ShipDate,
[$Delivery Date.Date] as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' AND NOT [$Product.Product] = 'Road-150 Red, 44'
--------------------------------------------------------------------------------
Executing the query ...
Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 220, function 'DMXNodeSelect::InitFromASTSelect').
Execution complete
Скрипт 7
Поддерживается также сортировка (предикат ORDER BY) и также довольно странно. Сам по себе этот предикат применяться не может, только за компанию с GROUP BY:
select [$Promotion.Promotion] as Promotion, [$Sales Territory.Sales Territory Region] as SalesTerritory,
[$Source Currency.Source Currency Code] as Currency, [$Customer.Customer] as Customer,
[$Product.Product] as Product, KEY([$Date.Date]) as OrderDate, KEY([$Ship Date.Date]) as ShipDate,
KEY([$Delivery Date.Date]) as DueDate,
[Internet Order Quantity] as OrderQuantity, [Internet Unit Price] as UnitPrice,
[Internet Extended Amount] as ExtendedAmount
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
order by OrderDate
--------------------------------------------------------------------------------
Executing the query ...
Errors from the SQL query module: An ORDER BY clause cannot be used without a GROUP BY clause.
Execution complete
Скрипт 8
словом, как-то так:
select [$Customer.Customer] as Customer, SUM([Internet Order Quantity]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer]
order by [$Customer.Customer]
Скрипт 9
Если в простых запросах (без джойнов) в дополнение к GROUP BY используется ORDER BY, выражения в этих предикатах должны быть одинаковы. В противном случае:
select [$Customer.Customer] as Customer, SUM([Internet Order Quantity]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer]
order by OrderDate
--------------------------------------------------------------------------------
Executing the query ...
Internal error: An unexpected exception occurred.
Execution complete
Скрипт 10
В простых запросах композитные выражения в ORDER BY (в отличие от GROUP BY) не поддерживаются:
select [$Customer.Customer] as Customer, KEY([$Date.Date]) as OrderDate, SUM([Internet Order Quantity]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer], OrderDate
order by [$Customer.Customer], OrderDate
--------------------------------------------------------------------------------
Executing the query ...
Errors from the SQL query module: Multiple ORDER BY conditions are not supported.
Execution complete
Скрипт 11
Коль скоро появился GROUP BY, напрашиваются агрегатные функции. С ними тоже не все понятно. В функцию SUM() не удается поместить выражение:
select [$Customer.Customer] as Customer, KEY([$Date.Date]) as OrderDate, SUM([Internet Order Quantity] * [Internet Unit Price]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer]
order by [$Customer.Customer]
--------------------------------------------------------------------------------
Executing the query ...
Query (1, 78) Errors from the SQL query module: The column contains an expression that is not valid.
Execution complete
Скрипт 12
хотя, допустим, SUM([Internet Order Quantity]) работает.
Как использовать агрегатную функцию COUNT(), я так и не врубился:
select [$Customer.Customer] as Customer, COUNT([$Date.Date]), SUM([Internet Order Quantity]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer]
order by [$Customer.Customer]
--------------------------------------------------------------------------------
Executing the query ...
Errors from the SQL query module: The 'Internet Total Product Cost' measure cannot be aggregated with the Count function.
Execution complete
Скрипт 13
Похоже, агрегатные функции можно применять только к мерам и только ту, которая определена для данной меры в кубе. Кстати, как можно видеть на Рис.4, существует еще агрегатная функция AGGREGATE().
select [$Customer.Customer] as Customer, AGGREGATE([$Date.Date]), AGGREGATE([Internet Order Quantity]) as Quantity
from [Adventure Works].[Internet Sales]
where [$Sales Territory.Sales Territory Region] = 'Australia' and [$Product.Product] = 'Road-150 Red, 44'
group by [$Customer.Customer]
order by [$Customer.Customer]
Скрипт 14
По ощущениям, будучи применена к мерам, она означает брать агрегатную функцию, определенную в кубе для данной меры:
Рис.6
а к измерениям – просто СOUNT.
Также на Рис.4 можно видеть, что диалект SQL поддерживает предикат NATURAL JOIN. Больше никаких джойнов не просматривается. Видимо, они там без надобности, поскольку основная задача джойнов с точки зрения OLAP – сджойнить измерение с группой мер. Джойнить измерения между собой особого смысла нет, т.к. они чаще всего ортогональны. (Еще раз – речь идет о джойне готовых многомерных объектов, а не составляющих их реляционных таблиц, допустим, в луче снежинки). Foreign key колонки в группе мер автоматически называются так же, как ключевой атрибут измерения (см., напр., Рис.6 предыдущего поста) , так что натурального джойна вполне хватает. Давайте приджойним к группе мер Internet Sales измерение Customers:
select * from [Adventure Works].[Internet Sales] natural join [Adventure Works].[$Customer]
Скрипт 15
Можно сгруппировать значения мер по атрибутам этого измерения.
select [$Customer].Education as Education, AGGREGATE([Internet Sales].[Internet Sales Amount]) as [Internet Sales Amount]
from [Adventure Works].[Internet Sales] natural join [Adventure Works].[$Customer]
group by [$Customer].Education
Скрипт 16
Рис.7
Можно усложнить пример, сгруппировав несколько мер по разным атрибутам, добавив условия фильтрации и сортировки:
select [$Customer].Education, [$Customer].[Marital Status], [$Customer].[Gender],
AGGREGATE([Internet Sales].[Internet Sales Amount]) as [Internet Sales Amount],
AGGREGATE([Internet Sales].[Internet Order Quantity])
from [Adventure Works].[Internet Sales] natural join [Adventure Works].[$Customer]
where [$Customer].[Gender] = 'MALE'
group by [$Customer].Education, [$Customer].[Marital Status], [$Customer].[Gender]
order by [$Customer].Education, [$Customer].[Marital Status]
Скрипт 17
Рис.8
Обратите внимание, что в случае NATURAL JOIN выражения, по которым выполняется группирование и сортировка, преспокойно могут различаться (ср. со Скриптом 10), а также допускаются композитные выражения в ORDER BY (ср. со Скриптом 11). Какой занятный язык! (с) «Собака на сене», граф Лодовико. По-видимому, без прилинкованного сервера все-таки не обойтись. Начхалдосо недурносо. Пошли баши.
Алексей Шуленин