Введение в SQL Server Analysis Services для разработчика. OLAP DMV ч.1.

Artikel
07/16/2010

Как отмечалось в посте SQL-запросы к измерениям и кубам, в Analysis Services в качестве языка DML поддерживается не только MDX, но и некоторая (достаточно ограниченная по возможностям) разновидность языка SQL. Объекты, к которым можно при помощи нее обращаться, выдаются схемным роусетом DBSCHEMA_TABLES, который, как и все схемные роусеты, можно посмотреть XMLA-запросом Discover – см. Скрипт 10 поста SQL-запросы к измерениям и кубам. В результатах этого запроса были обнаружены четыре с половиной типа таких объектов, три с половиной из которых были рассмотрены ранее: измерения уровня базы, измерения куба, группы мер и кубы измерений. Половинку я отвожу на кубы измерений, т.к. хотя к этому типу объектов и можно обратиться SQL-запросом, но получить в ответ ничего нельзя – см. Скрипт 9 того же поста. Нам осталось разобрать последний тип объектов, который в выдаче DBSCHEMA_TABLES имеет TABLE_SCHEMA=$SYSTEM и TABLE_TYPE=SCHEMA.

<RequestType>DBSCHEMA_TABLES</RequestType>

<TABLE_SCHEMA>$SYSTEM</TABLE_SCHEMA>

<TABLE_TYPE>SCHEMA</TABLE_TYPE>

</RestrictionList>

</Restrictions>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Discover>

Скрипт 1

Рис.1

С ним все просто. Это наши старые знакомые – схемные роусеты, т.е. то, что запрашивается XMLA-запросами Discover – см. пост Метод Discover. Сравните результат выдачи Рис.1 с результатом запроса DISCOVER_SCHEMA_ROWSETS - Рис.4 поста Метод Discover, выводящего все известные Analysis Services схемные роусеты. DBSCHEMA_TABLES является подмножеством DISCOVER_SCHEMA_ROWSETS и содержит 57 схемных роусетов из 61 известного. В него не попали DISCOVER_DATASOURCES, DISCOVER_LOCATIONS, DISCOVER_XML_METADATA, MDSCHEMA_ACTIONS.

Во первых строках поста Метод Discover, в частности, отмечалось: «Результат возвращается в виде реляционной таблицы, ну то есть в данном случае мы его будем видеть в виде XML, но идейно это таблица, а не CellSet, поэтому результаты метода Discover называются еще схемными роусетами (Schema Rowsets)». По причине табличной природы результата доступ ко всем схемным роусетам в SQL Server 2008 сделали дополнительно средствами SQL. Иными словами, практически все, что можно надискаверить методом Discover, практически каждый схемный роусет в элементе <RequestType> (57 из 61 – см.выше), получил в соответствие служебный объект из схемы $SYSTEM, который можно легко и просто запрашивать SQLным select’ом. Результатом будет ровно то же, что и в случае применения к этому схемному роусету XMLA-запроса Discover; но не в виде XML, а в виде таблицы, что удобно, т.к. экономит время на парсинг. Например, вместо Скрипта 1 можно написать

select * from $system.DBSCHEMA_TABLES where table_schema = '$SYSTEM' and TaBlE_tyPE = 'SCHEMA' order byTABLE_NAME

Скрипт 2

Рис.2

По аналогии с SQL Server эти служебные объекты схемы $SYSTEM в Analysis Services назвали Dynamic Management Views (DMV), потому что схемные роусеты содержат метаданные, а также различные параметры состояния Analysis Services, как и в SQL Server DMV.

Например, посмотреть, какие колонки имеются у «таблицы» (измерения) $Geography в «схеме» (кубе) Adventure Works (см. Рис.2 поста SQL-запросы к измерениям и кубам), можно так:

selectCOLUMN_NAME, COLUMN_GUID, COLUMN_PROPID, ORDINAL_POSITION, COLUMN_HAS_DEFAULT, COLUMN_DEFAULT, COLUMN_FLAGS, IS_NULLABLE,

DATA_TYPE, TYPE_GUID, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,

DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME,

COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME,

DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME,

[DESCRIPTION], COLUMN_OLAP_TYPE

from$system.DBSCHEMA_COLUMNS

where TABLE_CATALOG = 'Adventure Works DW 2008R2' and TABLE_SCHEMA = 'Adventure Works' andTABLE_NAME = '$Geography'

Скрипт 3

В свою очередь, можно посмотреть, какие колонки бывают у DMV, выдающего колонки:

select COLUMN_NAME from $system.DBSCHEMA_COLUMNS where TABLE_SCHEMA = '$SYSTEM' and TABLE_NAME = 'DBSCHEMA_COLUMNS'

Скрипт 4

Для «таблиц» групп мер DMV DBSCHEMA_COLUMNS неправильно показывает перечень колонок, ограничиваясь только теми, что имеют COLUMN_OLAP_TYPE = ‘MEASURE’:

selectCOLUMN_NAME, COLUMN_FLAGS, IS_NULLABLE, DATA_TYPE, COLUMN_OLAP_TYPE

from$system.DBSCHEMA_COLUMNS

where TABLE_CATALOG = 'Adventure Works DW 2008R2' and TABLE_SCHEMA = 'Adventure Works' and TABLE_NAME = 'Internet Sales'

Скрипт 5

Рис.3

В то же время мы видели (см.Рис.6 поста SQL-запросы к измерениям и кубам), что в «таблицу» Internet Sales входят еще поля для связи с измерениями: [Internet Sales].[$Promotion.Promotion], [Internet Sales].[$Sales Territory.Sales Territory Region] и др.

Для просмотра пользовательской активности используются DMV discover_connections, discover_sessions и discover_commands. Привычно посмотрев, какие колонки выдает каждое

select * from $system.DBSCHEMA_COLUMNS where TABLE_SCHEMA = '$SYSTEM' and (TABLE_NAME = 'DISCOVER_CONNECTIONS' or TABLE_NAME = 'DISCOVER_SESSIONS' or TABLE_NAME = 'DISCOVER_COMMANDS') order by TABLE_NAME

Скрипт 6

можно заметить, что в состав discover_commands входит SESSION_SPID, а в состав discover_sessions – SESSION_CONNECTION_ID, из чего хочется сделать вывод, что соединения связаны отношением 1-ко-многим с сессиями, а те, в свою очередь, таким же отношением с командами. Это не так. Как мы видели в предыдущем посте, несколько соединений могут использовать одну сессию. Вообще, соединения и сессии, как написано в BOL, связаны между собой отношением многие-ко-многим: одна сессия может разделяться несколькими соединениями, так и одно соединение может создавать несколько сессий. По классике, между сессиями и соединениями полагалась бы промежуточная таблица, через которую это отношение было бы реализовано. Или, на уровне DMV было бы логично видеть не только идентификатор соединения в discover_sessions, но и, наоборот, идентификатор сессии в discover_connections.

Как отмечалось в посте Доступ по SOAP, XMLA может работать поверх HTTP или непосредственно поверх TCP/IP. Первоначально подразумевался как раз первый случай. Как известно, HTTP – stateless протокол, а в элемент Properties XMLA-запроса всякий раз много не засунешь (см. select * from $system.discover_properties). Как быть, например, с вычисляемыми членами (созданными не в пределах запроса with ... select, а как create [session] member), локальными кубами уровня сесиии другими вычислениями? Отсюда возникла идея сессии, чтобы она персистила вычисления и другое состояние дел между запросами.

Откройте SSMS, соединитесь с сервером Analysis Services, скажите два раза New Query. Выполните в одной закладке MDX Query

select * from $system.discover_sessions order by session_start_time desc

Скрипт 7

в другой –

select * from $system.discover_connections order by connection_start_time desc

Скрипт 8

Ознакомьтесь, какие сессии и соединения присутствуют на сервере на момент начала эксперимента.

Откройте закладку XMLA-запроса (File -> Analysis Services XMLA Query) два раза. Это будут еще два соединения с Analysis Services, на которых будет ставиться эксперимент. На первых двух (условно говоря, MDXовских) мы будем наблюдать за его ходом.

Установите конфигурационные настройки MinIdleSessionTimeout и IdleOrphanSessionTimeout сервера AS в 0 (см. пред.пост), чтобы запретить ему прибирать неактивные сессии по собственному усмотрению.

На первой XMLA-закладке напишите два следующих XMLA-запроса в их полной SOAPовской форме (см. пост MDX и XMLA \ Скрипт 1). Каждый запрос открывает собственную сессию. В одной выполняется Discover-запрос схемного роусета MDSCHEMA_CUBES, во второй создается вычисляемый член масштаба сессии. Полная форма с конвертиком каждому из запросов требуется, потому что управление открытием, закрытием и назначением сессий Analysis Services происходят в заголовке SOAPовской формы, т.е. вне <soap:Body>. Выполните поочередно каждый из этих запросов, выделив его и нажав кнопку ! Execute.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Header>

<xa:BeginSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis" mustUnderstand="1"/>

</soap:Header>

<soap:Body>

<RequestType>MDSCHEMA_CUBES</RequestType>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Discover>

</soap:Body>

</soap:Envelope>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Header>

<xa:BeginSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis" mustUnderstand="1"/>

</soap:Header>

<soap:Body>

<Statement>create session member [Adventure Works].Measures.[Net Sales] as Measures.[Internet Sales Amount] - Measures.[Internet Total Product Cost]</Statement>

</Command>

<Catalog>Adventure Works DW 2008R2</Catalog>

</PropertyList>

</Properties>

</Execute>

</soap:Body>

</soap:Envelope>

Скрипт 9

В результатах каждого запроса AS пришлет гуид сессии, которую он ему назначил.

Рис.4

Запомните гуиды обеих сессий (в моем случае это 78D7E947-636E-40A3-A7C1-F64F03D2CC4D и 482124D2-4CD8-4D4E-832C-EDD3A2A477E4), перейдите в закладку MDX-запроса с discover_sessions (Скрипт 7), перевыполните его и увидьте две новые сессии в результатах:

Рис.5

Судя по разным значениям в колонке SESSION_CONNECTION_ID, по-видимому, SSMS открывал новое соединение под каждый XMLA-запрос несмотря на то, что они выполнялись с одной закладки. И сразу закрывал после выполнения, потому что если сейчас перейти в закладку MDXQuery2, где лежит выборка с discover_connections, и освежить ее, то соединений с идентификаторами 43 и 46 в результатах не найдется. То есть фокус удался, но не до конца.

Ладно, давайте посмотрим обратный фокус: с другого соединения законнектимся в существующую сессию. Когда CREATE MEMBER и использующий его запрос делаются из одной MDX-закладки, все работает, потому что это автоматически означает одну сессию:

create member [Adventure Works].Measures.[Net Sales] asMeasures.[Internet Sales Amount] - Measures.[Internet Total Product Cost]

select Product.[Product Categories].Category.Members on columns,

Customer.[Customer Geography].Country.Members on rows

from [Adventure Works] where

(Date.Calendar.[Calendar Year].[CY 2008], Measures.[Net Sales])

Скрипт 10

Рис.6

С другой MDX-закладки запрос не поймет этот вычисляемый член, потому что он был определен в масштабах иной сессии. То же и с XMLA– если специально не оговорить сессию в заголовке запроса, под него будет открыта новая сессия, в которой, понятно, Measures.[Net Sales] не значится.

Рис.7

Чтобы воспользоваться вычисляемым членом Measures.[Net Sales], надо коннектиться строго в сессию 482124D2-4CD8-4D4E-832C-EDD3A2A477E4, в которой он был создан (Скрипт 9, Рис.4):

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Header>

<xa:EndSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis" mustUnderstand="1" SessionId="482124D2-4CD8-4D4E-832C-EDD3A2A477E4"/>

</soap:Header>

<soap:Body>

select Product.[Product Categories].Category.Members on columns,

Customer.[Customer Geography].Country.Members on rows

from [Adventure Works] where

(Date.Calendar.[Calendar Year].[CY 2008], Measures.[Net Sales])

</Statement>

</Command>

<Catalog>Adventure Works DW 2008R2</Catalog>

<Format>Multidimensional</Format>

<AxisFormat>TupleFormat</AxisFormat>

</PropertyList>

</Properties>

</Execute>

</soap:Body>

</soap:Envelope>

Скрипт 11

Тогда запрос в <Statement>успешно выполняется:

Рис.8

В discover_sessions видно, что новых сессий не прибавилось. Этот фокус удался на славу J. Мы молодцы.

Теперь остается закрыть за собой сессии, т.к. в начале представления были изменены конфигурационные настройки, и AS после нас убирать мусор не будет. Делается это при помощи следующего скрипта:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Header>

<xa:EndSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis" mustUnderstand="1" SessionId="482124D2-4CD8-4D4E-832C-EDD3A2A477E4"/>

</soap:Header>

<soap:Body>

</Command>

</Execute>

</soap:Body>

</soap:Envelope>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Header>

<xa:EndSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis" mustUnderstand="1" SessionId="78D7E947-636E-40A3-A7C1-F64F03D2CC4D"/>

</soap:Header>

<soap:Body>

</Command>

</Execute>

</soap:Body>

</soap:Envelope>

Скрипт12

Рис.9

Выглядит немного громоздко из-за того, что несколько EndSession сразу в одном заголовке (Header) сделать нельзя. Далее, заголовок автоматически тянет за собой <soap:Body>, в котором обязательно должно стоять Discover, либо Execute и, в случае последнего, под ним обязательно должна находиться <Command>, под ней - <Statement />, и только ему уже допускается быть пустым. Как бы то ни было, все работает. Сравните Рис.5 с тем же запросом сейчас:

Рис.10

Сессии действительно закрылись, как показывает discover_sessions. В контексте базы Adventure Works 2008 R2 осталась болтаться только сессия с гуидом 87EE57CB-6A3C-47C9-95E2-F252AB109B17 и ProcessID = 3717. Это сессия, которая образовалась при иллюстрации Рис.6. Прихлопнем ее с помощью команды Cancel – аналога SQL Serverной команды KILL.

</Cancel>

Скрипт 13

Рис.11

Команда Cancel применяется, когда штатные способы закрытия сессии не срабатывают, например, завис запрос.

Алексей Шуленин

Freigeben über

Введение в SQL Server Analysis Services для разработчика. OLAP DMV ч.1.

Zusätzliche Ressourcen