Введение в SQL Server Analysis Services для разработчика. MDX и XMLA.
Предлагаю на время отвлечься от Database Engine и поговорить для разнообразия про Analysis Services. Примерно год назад я подготовил цикл заметок, объединенных общей темой "Введение в SQL Server Analysis Services для разработчика", который публиковался на форуме Российского сообщества пользователей SQL Server, но в связи с утратой сайта в настоящий момент нигде не опубликован. Я извлек этот материал из чулана, сдул пыль, подновил и на правах автора выдал самому себе любезное разрешение опубликовать его в блоге для ISV. Итак, для начала прозвучит такая легкая импровизация, я бы сказал, фантазия, на тему MDX и XMLA. У рояля то же.
MDX – это язык запросов к серверу Analysis Services. Для аналитического сервера MDX – примерно то же, что SQL для реляционного. MDX расшифровывается как MultiDimensional eXpressions, иногда eXtensions; предполагается, что расширения к классическому SQL. Наиболее популярно про MDX можно прочитать здесь , а наиболее точно здесь , потому что Михаил Пасуманский является автором не только оной книги, но и самого языка. В 1990 г. он переместился из питерского Политеха в Израиль, где занялся написанием аналитического приложения под названием «Панорама», заключавшего в себе аналитический сервер, аналитический клиент и средство их общения между собой, тоже как ни странно аналитическое. Как написано во введении к книжке «Microsoft SQL Server 2005 Analysis Services», в 1995 г. они выпустили первую версию, а еще через год OLAP-технологию фирмы Panorama Software вместе с ее ключевыми разработчиками удачно прикупила Microsoft, которой как раз позарез требовалось нечто подобное для грядущего SQL Server 7.0. Клиентскую часть «Панорамы» было признано необходимым убрать, а для серверной – формализовать и стандартизировать протоколы обмена данными. Так появилась на свет спецификация OLE DB for OLAP (ODBO) и язык MDX, как ее составная часть. Михаил к тому времени уже звался Моша, что, по-видимому, означает большая мошка, или Big Bug J. «Годы жизни в Израиле не прошли для него даром: теперь он предпочитает называться Мошей, что нормальным еврейским именем не является, но представляет вполне оригинальную компиляцию имен Миша и Моше» - http://home.sprintmail.com/~fva/pink_elephant/win/11.htm.
Несмотря на всенародную поддержку со стороны SAS, SAP, NCR, Microstrategy и др. формально ODBO оставался проприетарным майкрософтовским протоколом, и в 2001-м ему на смену пришла спецификация XML for Analysis (XMLA), основанная на открытых стандартах HTTP, XML, SOAP. Хотя SOAP стандартно реализуется через заголовки HTTP (и AS можно сконфигурить на работу по HTTP) в сценариях локальной работы XMLA происходит непосредственно поверх TCP/IP. На тему SOAP напрямую по TCP/IP в стандартах ничего не говорится, поэтому как оно выглядит и что передается в этом случае по веревке, разбирается в блоге у Моши в. Текущей версией XMLA является 1.1 (http://www.xmlforanalysis.com/xmla1.1.doc), принятая в ноябре 2002 г. на совете в составе Microsoft, SAS и Hyperion. Анонс по этому случаю опубликован здесь . Версия 1.1 используется всеми значимыми OLAP-вендорами. включая Oracle и IBM, дольше всех воротившими нос из-за подозрений, что несмотря на независимость стандарта XMLA он играет на руку Microsoft. Однако по факту IBM DB2 OLAP Server есть то же самое, что Hyperion Essbase, с точностью до ребрэндинга, а 22 мая 2008 г. Hyperion вообще был куплен ораклом со всем, что в нем было, включая поддержку XMLA, и вошел в состав ораклового Business Intelligence product suite. Так что теперь наши лучшие друзья волей неволей тоже поддерживают XMLA.
С 2003 г. никаких модификаций и усовершенствований в XMLA не наблюдалось. Из-за этого отдельные оппортунистические элементы стали поговаривать, что пациент, скорее, мертв, чем жив . Моша пылко возразил в своем блоге, что раз не вносилось, значит, не нужно. Значит, по крайней мере, позарез еще не приспичило. Значит, того, что имеется, пока хватает. А все вместе это значит, что они в свое время очень хорошо и дальновидно проработали стандарт. Короче, Моша молодец, а XMLA рулез и живее всех живых.
XMLA базируется на двух SOAP-методах: Discover и Execute. Первый используется для запроса метаданных, второй - для выполнения запросов. Таким образом, когда вы делаете MDX-запрос из SQL Server Management Studio (SSMS)
Рис.1
или выполняете AdomdCommand из приложения
Рис.2
на сервер AS улетает что-нибудь типа
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>SELECT Measures.MEMBERS ON COLUMNS FROM [Adventure Works] </Statement>
</Command>
<Properties>
<PropertyList>
<DataSourceInfo/>
<Catalog>Adventure Works DW 2008R2</Catalog>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute>
</soap:Body>
</soap:Envelope>
Скрипт 1
Откройте SSMS и скажите новый запрос. Выберите XMLA Query. Вставьте скрипт 1 в окно запроса и выполните его.
Рис.3
Результат XMLA-запроса возвращается в виде XML Row- или Cellset'a. В данном случае мы обращались к кубику, соответственно, получили в ответ его кусок, т.е. набор ячеек.
рис.4
Вначале идет XSD-описание результата
<xs:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:mddataset" elementFormDefault="qualified" xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla">
<!-- The schema is defined in the publicly available documentation for MSXLA (Microsoft Extension to XMLA Schema)-->
<xs:import namespace="http://schemas.microsoft.com/analysisservices/2003/xmla" />
<xs:complexType name="MemberType">
<xs:sequence>
<xs:any namespace="##targetNamespace" minOccurs="0" maxOccurs="unbounded" processContents="skip" />
</xs:sequence>
<xs:attribute name="Hierarchy" type="xs:string" />
</xs:complexType>
...
Затем описание осей
<AxesInfo>
<AxisInfo name="Axis0">
<HierarchyInfo name="[Measures]">
<UName name="[Measures].[MEMBER_UNIQUE_NAME]" type="xsd:string" />
<Caption name="[Measures].[MEMBER_CAPTION]" type="xsd:string" />
<LName name="[Measures].[LEVEL_UNIQUE_NAME]" type="xsd:string" />
<LNum name="[Measures].[LEVEL_NUMBER]" type="xsd:int" />
<DisplayInfo name="[Measures].[DISPLAY_INFO]" type="xsd:unsignedInt" />
</HierarchyInfo>
</AxisInfo>
...
Затем структура селлсета
<Axes>
<Axis name="Axis0">
<Tuples>
<Tuple>
<Member Hierarchy="[Measures]">
<UName>[Measures].[Internet Sales Amount]</UName>
<Caption>Internet Sales Amount</Caption>
<LName>[Measures].[MeasuresLevel]</LName>
<LNum>0</LNum>
<DisplayInfo>0</DisplayInfo>
</Member>
</Tuple>
<Tuple>
<Member Hierarchy="[Measures]">
<UName>[Measures].[Internet Order Quantity]</UName>
<Caption>Internet Order Quantity</Caption>
<LName>[Measures].[MeasuresLevel]</LName>
<LNum>0</LNum>
<DisplayInfo>131072</DisplayInfo>
</Member>
</Tuple>
...
Затем данные
<CellData>
<Cell CellOrdinal="0">
<Value xsi:type="xsd:decimal">29358677.2207</Value>
<FmtValue>29,358,677.22$</FmtValue>
</Cell>
<Cell CellOrdinal="1">
<Value xsi:type="xsd:int">60398</Value>
<FmtValue>60,398</FmtValue>
</Cell>
...
Упаковку запроса в soap:Envelope можно опускать:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>SELECT Measures.MEMBERS ON COLUMNS FROM [Adventure Works]</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>Adventure Works DW 2008R2</Catalog>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute>
Алексей Шуленин