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

Содержание предыдущей серии.

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

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> 
  <RequestType>DBSCHEMA_TABLES</RequestType> 
  <Restrictions> 
    <RestrictionList> 
      <TABLE_SCHEMA>$SYSTEM</TABLE_SCHEMA> 
      <TABLE_TYPE>SCHEMA</TABLE_TYPE> 
    </RestrictionList> 
  </Restrictions> 
  <Properties> 
    <PropertyList> 
      <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 by TABLE_NAME

Скрипт 2

Рис. 2

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

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

select COLUMN_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' and TABLE_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’:

select COLUMN_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="https://schemas.xmlsoap.org/soap/envelope/"> 
  <soap:Header> 
    <xa:BeginSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis"  mustUnderstand="1"/> 
  </soap:Header> 
  <soap:Body> 
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> 
      <RequestType>MDSCHEMA_CUBES</RequestType> 
      <Restrictions/> 
      <Properties> 
        <PropertyList> 
          <Catalog>Adventure Works DW 2008R2</Catalog> 
        </PropertyList> 
      </Properties> 
    </Discover> 
  </soap:Body> 
</soap:Envelope> 
<soap:Envelope xmlns:soap="https://schemas.xmlsoap.org/soap/envelope/"> 
  <soap:Header> 
    <xa:BeginSession xmlns:xa="urn:schemas-microsoft-com:xml-analysis"  mustUnderstand="1"/> 
  </soap:Header> 
  <soap:Body> 
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> 
      <Command> 
        <Statement>create session member [Adventure Works].Measures.[Net Sales] as Measures.[Internet Sales Amount] - Measures.[Internet Total Product Cost]</Statement> 
      </Command> 
      <Properties> 
        <PropertyList> 
          <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] as Measures.[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="https://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> 
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> 
      <Command> 
        <Statement> 
          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> 
      <Properties> 
        <PropertyList> 
          <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="https://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> 
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> 
      <Command> 
        <Statement /> 
      </Command> 
      <Properties /> 
    </Execute> 
    </soap:Body>  
</soap:Envelope> 
<soap:Envelope xmlns:soap="https://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> 
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> 
      <Command> 
        <Statement /> 
      </Command> 
      <Properties /> 
    </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 xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> 
  <SessionID>87EE57CB-6A3C-47C9-95E2-F252AB109B17</SessionID> 
</Cancel>

Скрипт 13

Рис. 11

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

Автор: Алексей Шуленин