Введение в 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
.png)
Рис. 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
.png)
Рис. 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="https://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="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="https://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
при попытке возвратить результат случится ошибка:
.png)
Pис. 3
В то же время если Скрипт 4 в качестве текста команды подставить в Скрипт 2, то при помощи метода ExecuteXmlReader этот запрос нормально выполняется, возвращая результат
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />:
.png)
Рис. 4
Это то, что появляется в SSMS в панели Results. Есть еще интересный вопрос, как поймать то, что появляется в панели Messages, но я пока не буду на него отвлекаться, т.к. делается это нетривиально и совершенно не так, как в SQL Server.
К слову сказать, <Batch> (а также <Process>) является такой же командой XMLA, то есть может стоять под элементом <Command>, как и <Statement> - см. https://msdn.microsoft.com/en-us/library/ms187139.aspx, следовательно, может выступать в качестве AdomdCommand.
Возникает вопрос: зачем параллельно к CommandText'у потребовалось иметь еще свойство CommandStream, если идейно у них одинаковые ограничения? По-видимому, CommandStream предполагается использовать, если содержимое <Command> достаточно велико и хранится в файле. В особенности этим отличаются DDL-команды. Зайдите в SSMS -> Object Explorer, кликните правой кнопкой по кубу Adventure Works и скажите Script Cube As -> Create.
.png)
Рис. 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
Продолжение следует.
Автор: Алексей Шуленин