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

Выполнение произвольного XMLA средствами ADOMD.NET является заманчивым, но, увы, недостижимым решением несмотря на то, что в Интернете можно наткнуться на людей, утверждающих, что им удалось его достичь теми или иными средствами. Кое-где мимоходом упоминается (не буду показывать пальцем на MSDNовский блог одного уважаемого товарища), что все, что для этого нужно - это засунуть текст XMLA-запроса не в CommandText, а в CommandStream объекта AdomdCommand и выполнить ExecuteXmlReader(). К сожалению, это фикция. Документация на ADOMD обложила разработчиков достаточно плотно, не оставив в том числе лазейки в виде CommandStream. В русской MSDN Library на эту тему, кстати, на чистом русском языке говорится: "The AdomdCommand assumes that the System.IO.Stream contains an XML for Analysis compliant command (that is, a command that can be framed by the <Command> tag within an XML for Analysis request)". Выделено мной. Иными словами, что CommandStream, как и CommandText, изо всего текста XMLA-запроса воспринимает только начинку элемента Command и сам за сценой заворачивает ее в полагающиеся родительские элементы. Попытка засунуть в него полноценный XMLA, например, с параметрами в виде элемента <Parameters> - см. Скрипт 1 предыдущего поста - приводит к той же ошибке, что и в случае CommandText - см. Скрипт 4 позапредыдущего поста.

using System.Data;

using System.IO;

using System.Xml;

using System.Diagnostics;

using Microsoft.AnalysisServices.AdomdClient;

using System.Text;

class Program

{

    static void Main(string[] args)

    {

        AdomdConnection cnn = new AdomdConnection(@"Data Source=http://192.168.0.136/msolap/msmdpump.dll; User ID=192.168.0.136\\Administrator;Password=Abra_Chupakabra");

        cnn.Open();

        string xmla = @"<Execute xmlns='urn:schemas-microsoft-com:xml-analysis'>

                           <Command>

                               <Statement>

                                   select [Measures].members on 0,

                                   Filter(Customer.[Customer Geography].Country.members,

                                   Customer.[Customer Geography].CurrentMember.Name = @CountryName) on 1

                                   from [Adventure Works]

                               </Statement>

                           </Command>

                           <Properties>

                               <PropertyList>

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

                               </PropertyList>

                           </Properties>

                           <Parameters>

                               <Parameter>

                                   <Name>CountryName</Name>

                                   <Value>'United Kingdom'</Value>

                               </Parameter>

                           </Parameters>

                      </Execute>";

        AdomdCommand cmd = new AdomdCommand();

        cmd.Connection = cnn;

        cmd.CommandStream = new MemoryStream(Encoding.UTF8.GetBytes(xmla));

        XmlReader res = cmd.ExecuteXmlReader();

        res.MoveToContent(); res.Read();

        Debug.WriteLine(res.ReadOuterXml());

        res.Close();

        cnn.Close();

    }

}

Скрипт 1

Рис.1

Чтобы этот код заработал, запрос в CommandStream надо переписать подобно Скрипту 2 из предыдущего поста, взяв только начинку элемента Command и засунув элементы Properties и Parameters в соответствующие свойства объекта AdomdCommand. Тэг <Statement> также можно опускать, как мы помним. Если внутри CommandText (или, соответственно, CommandStream) нет никаких тэгов, ADO MD по умолчанию оборачивает содержимое тэгом <Statement>.

static void Main(string[] args)

    {

       AdomdConnection cnn = new AdomdConnection("Data Source=http://192.168.0.136/msolap/msmdpump.dll;" +

                                                 "User ID=192.168.0.136\\Administrator;Password=Abra_Chupakabra");

       cnn.Open();

       string xmla = @"<Statement>

                          select [Measures].members on 0,

                                 Filter(Customer.[Customer Geography].Country.members,

                                        Customer.[Customer Geography].CurrentMember.Name = @CountryName) on 1

                          from [Adventure Works]

                      </Statement>";

       AdomdCommand cmd = new AdomdCommand();

       cmd.Connection = cnn;

       cmd.CommandStream = new MemoryStream(Encoding.UTF8.GetBytes(xmla));

       cmd.Properties.Add("Catalog", "Adventure Works DW 2008R2");

       cmd.Parameters.Add(new AdomdParameter("CountryName", "United Kingdom"));

       XmlReader res = cmd.ExecuteXmlReader();

       res.MoveToContent(); res.Read();

       string s = res.ReadOuterXml();

       res.Close(); cnn.Close();

    }

Скрипт 2

Рис.2

Метод ExecuteXmlReader возвращает результат в виде XML, как если бы мы выполняли XMLA-запрос из SSMS.

Передача текста команды в свойстве CommandText или CommandStream никак не влияет на формат передачи результата. Если результатом команды является селлсет, его можно получить как в виде XML при помощи метода ExecuteXmlReader, как мы только что видели в Скрипте 2, так и в виде объекта CellSet при помощи метода ExecuteCellSet:

static void Main(string[] args)

    {

       AdomdConnection cnn = new AdomdConnection("Data Source=http://192.168.0.136/msolap/msmdpump.dll;" +

                                                 "User ID=192.168.0.136\\Administrator; Password=Abra_Chupakabra");

       cnn.Open();

       string xmla = @"<Statement>

                          select [Measures].members on 0,

                                 Filter(Customer.[Customer Geography].Country.members,

                                        Customer.[Customer Geography].CurrentMember.Name = @CountryName) on 1

                          from [Adventure Works]

                      </Statement>";

       AdomdCommand cmd = new AdomdCommand();

       cmd.Connection = cnn;

       cmd.CommandStream = new MemoryStream(Encoding.UTF8.GetBytes(xmla));

       cmd.Properties.Add("Catalog", "Adventure Works DW 2008R2");

       cmd.Parameters.Add(new AdomdParameter("CountryName", "United Kingdom"));

       CellSet res = cmd.ExecuteCellSet();

       for (int i = 0; i < res.Axes[0].Positions.Count; i++)

       {

           Debug.WriteLine("");

           for (int j = 0; j < res.Axes[1].Positions.Count; j++)

               Debug.Write(res.Cells[i, j].FormattedValue);

       }

       cnn.Close();

    }

Скрипт 3

Я скомбинировал в этом примере первую половину из Скрипта 2, а вторую, которая CellSet, - из Скрипта 2 предыдущего поста. Натурально, получается то же самое, что и на рис.3 предыдущего поста.

Если команда не является запросом, возвращающим кусок кубика, очевидно, что бестолку просить ее при этом возвратить селлсет - будет ошибка. Например, если в предыдущий скрипт вместо селекта подставить XMLA-команду процессинга

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Parallel>

    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">

      <Object>

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

        <DimensionID>Dim Product</DimensionID>

      </Object>

      <Type>ProcessUpdate</Type>

    </Process>

  </Parallel>

</Batch>

Скрипт 4

при попытке возвратить результат случится ошибка:

Pис.3

В то же время если Скрипт 4 в качестве текста команды подставить в Скрипт 2, то при помощи метода ExecuteXmlReader этот запрос нормально выполняется, возвращая результат

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />:

Рис.4

Это то, что появляется в SSMS в панели Results. Есть еще интересный вопрос, как поймать то, что появляется в панели Messages, но я пока не буду на него отвлекаться, т.к. делается это нетривиально и совершенно не так, как в SQL Server.
К слову сказать, <Batch> (а также <Process>) является такой же командой XMLA, то есть может стоять под элементом <Command>, как и <Statement> - см. http://msdn.microsoft.com/en-us/library/ms187139.aspx, следовательно, может выступать в качестве AdomdCommand.

Возникает вопрос: зачем параллельно к CommandText'у потребовалось иметь еще свойство CommandStream, если идейно у них одинаковые ограничения? По-видимому, CommandStream предполагается использовать, если содержимое <Command> достаточно велико и хранится в файле. В особенности этим отличаются DDL-команды. Зайдите в SSMS -> Object Explorer, кликните правой кнопкой по кубу Adventure Works и скажите Script Cube As -> Create.

Рис.5

Не повторяйте это на нагруженной машине. Процесс генерации скрипта целиком по кубу достаточно задумчивый. На его выходе рождается здоровый файл XML. Понятно, что если его нужно накатить из пользовательского приложения где-нибудь на другой машине, чтобы воспроизвести по структуре кубик Adventure Works, лучше выполнять его через CommandStream.

    static void Main(string[] args)

    {

       AdomdConnection cnn = new AdomdConnection("Data Source=http://192.168.0.136/msolap/msmdpump.dll;" +

                                                 "User ID=192.168.0.136\\Administrator; Password=Abra_Chupakabra");

       cnn.Open();

       AdomdCommand cmd = new AdomdCommand();

       cmd.Connection = cnn;

       cmd.CommandStream = new FileStream(@"c:\Temp\AdventureWorks.xmla", FileMode.Open, FileAccess.Read);

       XmlReader res = cmd.ExecuteXmlReader();

       res.MoveToContent(); res.Read();

       Debug.WriteLine(res.ReadOuterXml());

       res.Close(); cnn.Close();

    }

Скрипт 5

Продолжение следует.