Введение в SQL Server Analysis Services для разработчика. SQL-запросы к измерениям и кубам.
Имеется в виду не создание прилинкованного сервера к Analysis Services со стороны SQL Server. Мы остаемся строго в рамках Analysis Services и никуда за них не выходим. Просто MDX является не единственным языком, на котором можно обращаться к Analysis Services. Наряду с ним поддерживаются DMX (Data Mining eXtensions) и некоторое подмножество, скорее, диалект SQL. Этот диалект воспринимается в элементе Statement элемента Command XMLA-метода Execute наравне с обычным MDX.
Однажды после выхода SQL Server 2005 Крис Вебб делал локальный кубик и от нечего делать наблюдал этот процесс в профайлере. Локальный кубик – это некоторая область большого серверного куба, которая персистится в файл. С этим файлом затем можно соединяться из клиентского приложения и работать, как со взрослыми кубами. Примерная аналогия в реляционном мире – база мобильного SQL Server (Compact Edition). Она тоже самодостаточна при деплойменте и там тоже строка соединения представляет из себя просто путь к sdf. Мобильный SQL Server используется в основном в сценариях синхронизации и кэширования. Аналогично, основным назначением локальных кубов являются задачи обеспечения локального кэша. Сделайте своему начальству локальный кубик по верхним этажам наиболее интересных измерений. Пусть оно его крутит себе в Excele и развлекается, пока летит в самолете. Как говорится, чем бы начальство ни тешилось...
Локальный кубик делается в MDX командой CREATE GLOBAL CUBE. Для тех, кто решил, что это юмор такой, я еще раз повторяю, что MDX (равно как и XMLA) – очень логичный язык, и если локальный куб в нем обзывается глобальным, значит, так надо. Значит, бывают более локальные локальные кубы – см. CREATE SESSION CUBE. Сессионный локальный куб – это как временная таблица с одной решеткой #. Впрочем, мы отвлеклись. Стало быть, делал он этот самый локальный кубик, скажем, по примеру из BOL:
CREATE GLOBAL CUBE [LocalReseller]
Storage 'C:\LocalAWReseller1.cub'
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Reseller Sales Amount],
DIMENSION [Adventure Works].[Reseller],
DIMENSION [Adventure Works].[Date]
)
Скрипт 1
и посматривал между тем в профайлер. А там, оказалось, ужас, чего творится:
Рис.1
Обратите внимание на странные строчки с EventSubclass = 2 – SQLQuery. Заглянув в колонку TextData, можно убедиться, что в качестве Query действительно выступает никакой не MDX, а вовсе даже SQL, только немного странный:
SELECT DISTINCT
KEY ( [Adventure Works].[$Reseller].[Bank Name],0 ) AS [eller0_0],
NAME ( [Adventure Works].[$Reseller].[Bank Name] ) AS [eller0_1]
FROM [Adventure Works].[$Reseller]
Скрипт 2
Эге, подумал Крис. Нетрудно догадаться, что прочитать измерение можно командой select * from [Имя куба].[$Имя измерения], например,
select * from [Adventure Works].[$Geography]
Скрипт 3
Рис.2
Это не есть MDX-запрос, тем не менее на закладке MDX Query в SSMS он нормально воспринимается, следовательно, может использоваться в качестве <Statement> в XMLA-запросе (см. рис.5) и в качестве CommandText/CommandStream объекта AdomdCommand.
Под звездочкой понимается вывод всех «полей», т.е. атрибутов измерения. Вот они для измерения Географии в многомерном редакторе BIDS:
Рис.3
Рис.4
Следует отметить, что читается именно измерение, а не его реляционная таблица, и именно атрибуты, а не соответствующие им поля. Во-первых, измерение может состоять из совокупности связанных таблиц, при этом в запросе мы указываем измерение, а не какую-либо таблицу. Во-вторых, некоторым полям не соответствуют никакие атрибуты. В этом случае в выдаче Рис.2 мы их просто не видим.
Атрибуты можно перечислять в явном виде в качестве полей запроса и задавать им псевдонимы, как в каноническом SQL:
select Country as [Страна], [State-Province] as [Штат],
City as [Город], [Geography Key] as [Ключ]
from [Adventure Works].[$Geography]
Скрипт 4
Рис.5
Измерение выводится в виде большой плоской таблицы, структура которой подобна структуре, которую я расписывал очень подробно и образно в посте Реляционное представление селлсета на примере зубчатой передачи. Здесь принцип тот же. Самая маленькая шестеренка – это ключевой атрибут измерения. Если между атрибутами не определено больше никаких зависимостей, то шестерни, соответствующие остальным атрибутам, соприкасаются непосредственно с шестеренкой ключевого атрибута. В нашем случае между атрибутами измерения Geography определены зависимости – см. Рис.5. В соответствии с ними шестеренка Geography Key соприкасается с более крупной шестеренкой Postal Code. Та, в свою очередь, с еще более крупной шестеренкой City. Та – со State-Province. И та, наконец, с самой большой шестерней Country. Процесс вращения шестеренок в табличном виде мы видим на Рис.2 и 5.
Аналогично производится чтение из группы мер: select * from [Имя куба].[Имя группы мер], только здесь значок доллара не ставится. Пример чтения группы мер Internet Sales:
select * from [Adventure Works].[Internet Sales]
Скрипт 5
Рис.6
Это практически эквивалентно чтению таблицы фактов, соответствующей данной группе мер (для простоты рассматриваем случай, когда группе мер соответствует одна таблица фактов). Вот в целях сравнения результат чтения из реляционной таблицы FactInternetSales (по соображениям наглядности приводятся не все поля).
select PromotionKey, SalesTerritoryKey, CurrencyKey, CustomerKey, ProductKey, OrderDateKey, ShipDateKey, DueDateKey,
OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost
from AdventureWorksDW.[dbo].FactInternetSales
Скрипт 6
Рис.7
Еще раз: на Рис.7 мы видим результаты запроса к реляционному движку в отличие от Рис.6 и предшествующих запросов, которые адресовались Analysis Services. Сравнивая Рис.6 с Рис.7, можно видеть, что в рез-тах запроса Скрипт 5 слева идут колонки ключевых атрибутов участвующих в данной группе мер измерений; при этом в в них вместо ключа отображается имя атрибута. Например, на Рис.7 в колонке SalesTerritoryKey стоят числа 6, 7, 1, 4, ... Это ключи атрибута Sales Territory Region (см. Рис.8). На Рис.6 в соответствующей колонке [Internet Sales].[$Sales Territory.Sales Territory Region] мы видим соответствующие им имена того же атрибута: Canada, France, ... Как можно видеть из того же Рис.8, ключи атрибута Sales Territory Region берутся из колонки SalesTerritoryKey реляционной таблицы Sales Territory, имена – из колонки SalesTerritoryRegion той же таблицы.
Рис.8
Название колонки образуется из названия группы мер, измерения (с долларовым префиксом) и названия атрибута измерения: [Internet Sales].[$Sales Territory.Sales Territory Region]. Далее справа на Рис.6 (не поместилось) идут численные столбцы, соответствующие мерам данной группы мер. Их названия имеют вид [Группа мер].[Имя меры], например, [Internet Sales].[Internet Sales Amount], [Internet Sales].[Internet Order Quantity] и т.д.
Измерение Sales Territory является одновременно измерением, принадлежащим кубу Adventure Works, и измерением уровня базы (как и участвовавшее в Скрипте 3 измерение Geography). Обратимся еще раз к Рис.6. Измерения уровня базы представлены в Object Explorer (левая панель SSMS). Они существуют безотносительно какого-либо куба. Как написано в документации, «Измерение, существующее независимо от куба, называется измерением базы данных, а экземпляр измерения базы данных в кубе называется измерением куба.» На средней вертикальной панели, где показаны метаданные куба Adventure Works, можно видеть измерения, относящиеся непосредственно к этому кубу. Очевидно, что не все измерения уровня базы обязаны входить в тот или иной куб. Обратно, одно измерение уровня базы может иметь несколько реализаций внутри куба. Например, таблица фактов FactInternetSales в кубе Adventure Works имеет три датных колонки (OrderDateKey, DueDateKey и ShipDateKey), каждой из которых она связана с таблицей DimDate, на основе которой построено измерение уровня базы Date. Соответственно, внутри куба мы видим три измерения, представляющих собой реализации измерения Date уровня базы – Date, Delivery Date и Ship Date. Как запрашивать измерение куба, мы уже знаем – Скрипт 4. К измерению уровня базы нужно обращаться по имени [$Имя измерения].[$Имя измерения]. Например, так запрашивается измерение Date уровня базы:
select * from [$Date].[$Date]
Скрипт 7
Рис.9
А так – его реализации внутри куба Adventure Works:
select * from [Adventure Works].[$Date]
select * from [Adventure Works].[$Delivery Date]
select * from [Adventure Works].[$Ship Date]
Скрипт 8
Еще одной разновидностью объектов AS выступают кубы измерений (dimension cubes), про которые упоминалось в конце поста Ограничения Discover и давалась ссылка на Мошин блог. Как отмечает Моша, чтобы иметь возможность средствами MDX запрашивать сугубо измерения, то есть ставить измерение после FROM, например,
SELECT Head([Product].[Product].[Product].Members, 100) ON 0 FROM $Product
Рис.10
предусмотрены служебные объекты под названием кубы измерений – те самые, которые получаются из схемного роусета MDSCHEMA_CUBES с ограничением CUBE_SOURCE = 2. Такие кубы не видны с клиента (как кубы) и состоят из одного измерения (того самого, которое запрашиваем) и единственной меры, название которой совпадает с именем измерения и которая, по определению, всюду равна NULL. Теоретически до них также можно доступиться средствами разбираемого в настоящем посте диалекта SQL, поддерживаемого в рамках MDX:
select * from [$Product].[Dim ProductMG]
Скрипт 9
Однако запросы против таких объектов ничего не возвращают, хотя и на синтаксис не ругаются.
Посмотреть все таблицы, которые доступны в базе средствами SQLеподобных запросов из MDX, можно при помощи схемного роусета DBSCHEMA_TABLES, например, в следующем Discover-запросе:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DBSCHEMA_TABLES</RequestType>
<Restrictions />
<Properties>
<PropertyList>
<Catalog>Adventure Works DW 2008R2</Catalog>
</PropertyList>
</Properties>
</Discover>
Скрипт 10
Рис.11
Просматривая результаты, можно видеть все четыре перечисленных в данном посте типа OLAPовских таблиц: измерения куба (Скрипты 3, 4, 8), группы мер куба (Скрипт 5), измерения уровня базы (Скрипт 7), кубы измерений (Скрипт 9). Отдельный большой блок составляют появившиеся в 2008-м DMV (схема $SYSTEM). Это наши знакомые схемные роусеты. До них можно доступаться не только методом Discover, но и SQLным запросом. Мы их рассмотрим, предположительно, через пост, а пока – перемена.
На время нашего перерыва у меня будет просьба к тем читателям,у кого стоит SQL Server 2008 R2 Analysis Services. Сделайте, пожалуйста, (на тестовой конфигурации) локальный куб по образу Скрипт 1, добавив в него измерение Product, т.е.
CREATE GLOBAL CUBE [LocalReseller]
Storage 'C:\LocalAWReseller1.cub'
FROM [Adventure Works]
(
MEASURE [Adventure Works].[Reseller Sales Amount],
DIMENSION [Adventure Works].[Reseller],
DIMENSION [Adventure Works].[Date],
DIMENSION [Adventure Works].[Product]
)
и если выскочит ошибка, дайте знать в комментариях. Спасибо.
Алексей Шуленин