Введение в SQL Server Analysis Services для разработчика. Реляционное представление селлсета.
Как написано в Википедии, XMLA состоит из 2-х SOAPовских методов: Execute и Discover. Ранее в этой серии мы оперировали с примерами исключительно на Execute. Мы разобрали, что Execute состоит из трех дочерних элементов: Command, Properties и Parameters. Команда – это то, что пишется на закладке MDX Query в SSMS, а также в CommandText или CommandStream объекта AdomdCommand в ADOMD.NET. Команда может быть MDX-запросом; резервным копированием; процессингом; созданием, изменением, удалением многомерного объекта и т.д. (см. http://msdn.microsoft.com/ru-ru/library/ms187139(SQL.105).aspx). Использование параметра в запросе было продемонстрировано выше. Доступные свойства перечислены здесь. Они могут использоваться для задания БД, по которой выполняется запрос, для задания формата вывода результата и т.д. Например, на Рис.1 в комбобоксе выбора текущей БД в SSMS значится Test; тем не менее запрос выполняется без возражений, потому что среди его Properties оговорен Catalog = Adventure Works DW 2008R2.
<Execute xmlns='urn:schemas-microsoft-com:xml-analysis'>
<Command>
<Statement>
select Product.Category.Members on 0
, Descendants([Date].Calendar.[Calendar Year].[CY 2008], Month) on 1
, Geography.Geography.City.Members on 2
from [Adventure Works]
where Measures.[Internet Sales Amount]
</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>Adventure Works DW 2008R2</Catalog>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute>
Рис.1
Кстати говоря, свойство Format, насколько я понимаю, влияет только на вид упаковки передаваемого результата в XML. Оба формата могут быть с одинаковой легкостью засунуты как в CellSet, так и в Rowset. Рассмотрим запрос из предыдущего примера (эл-т Statement), для которого я отбросил 3-ю ось в случае окна MDX Query в SSMS, т.к. там умеют отображаться только двумерные селлсеты. Экран у нас покамест плоский, а рисовать проекции или послойные срезы SSMS не обучена и, когда встречает больше, чем две оси, жалуется, что «Results cannot be displayed for cellsets with more than two axes».
select Product.Category.Members on 0
, Descendants([Date].Calendar.[Calendar Year].[CY 2008], Month) on 1
from [Adventure Works]
where Measures.[Internet Sales Amount]
Рис.2
Ну представьте себе для наглядности еще дополнительно третью ось перпендикулярно плоскости рисунка, на которой отложены города. Как-то так.
Как мы только что видели на Рис.1, в окне XMLA-запроса в SSMS допускается присутствие > 2 осей. Аналогично, не проблема получить более, чем двумерный селлсет из пользовательского приложения. Ведь задача ADOMD.NET нам его только вернуть, а уж как мы его собираемся рисовать – это наша забота.
using System;
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://w7x86sql08r2/msolap/msmdpump.dll;" +
"User ID=w7x86sql08r2\\Administrator;Password=L10nKing");
cnn.Open();
AdomdCommand cmd = new AdomdCommand(
@"select Product.Category.Members on 0
, Descendants([Date].Calendar.[Calendar Year].[CY 2008], Month) on 1
, Geography.Geography.City.Members on 2
from [Adventure Works]
where Measures.[Internet Sales Amount]", cnn);
cmd.Properties.Add("Catalog", "Adventure Works DW 2008R2");
cmd.Properties.Add("Format", "Multidimensional");
CellSet resMultidim = cmd.ExecuteCellSet();
foreach (Axis a in resMultidim.Axes)
{
Debug.WriteLine("Ось " + a.Name);
foreach (Position p in a.Positions)
{
Debug.Write(String.Format("Позиция №{0}. Мемберы: ", p.Ordinal));
foreach (Member m in p.Members) Debug.Write(String.Format("{0}; ", m.Caption));
Debug.WriteLine("");
}
Debug.WriteLine("");
}
AdomdDataReader resTabular = cmd.ExecuteReader();
DataTable t = resTabular.GetSchemaTable();
/* SchemaTable – это таблица, записями к-й являются колонки рез-та со своими атрибутами */
Debug.WriteLine("");
foreach (DataRow r in t.Rows)
{
Debug.WriteLine("");
foreach (DataColumn c in t.Columns)
Debug.Write(String.Format("{0} = {1}; ", c.ColumnName, r[c]));
}
cnn.Close();
}
}
Рис.3
Обратите внимание, что что бы мы ни написали в cmd.Properties.Add("Format", "..."): Multidimensional или Tabular, - это влияет только на способ сериализации результата при передаче. Multidimensional формат прекрасно загоняется как в многомерную форму в результате выполнения метода ExecuteCellSet(), так и в табличную выполнением метода ExecuteReader(). Аналогично, можно поставить в свойствах формат = Tabular, который также прекрасно выполняется обоими методами и загоняется, соответственно, как в многомерный, так и в табличный результаты. В первой выдаче я постарался отобразить структуру многомерного селлсета. Она совершенно незамысловата: три оси, как заказывали, и члены расписанных в запросе множеств по каждой оси.
Ось Axis0
Позиция №0. Мемберы: All Products;
Позиция №1. Мемберы: Accessories;
Позиция №2. Мемберы: Bikes;
Позиция №3. Мемберы: Clothing;
Позиция №4. Мемберы: Components;
Ось Axis1
Позиция №0. Мемберы: January 2008;
Позиция №1. Мемберы: February 2008;
Позиция №2. Мемберы: March 2008;
Позиция №3. Мемберы: April 2008;
Позиция №4. Мемберы: May 2008;
Позиция №5. Мемберы: June 2008;
Позиция №6. Мемберы: July 2008;
Позиция №7. Мемберы: August 2008;
Ось Axis2
Позиция №0. Мемберы: Alexandria;
Позиция №1. Мемберы: Coffs Harbour;
Позиция №2. Мемберы: Darlinghurst;
Позиция №3. Мемберы: Goulburn;
Позиция №4. Мемберы: Lane Cove;
Позиция №5. Мемберы: Lavender Bay;
...
Позиция №586. Мемберы: Rock Springs;
Скрипт 1
Хитрее выглядит реляционное представление подкубика. На выходе получается здоровенная двумерная сводная таблица, структуру которой можно видеть из второй выдачи на Рис.3.
ColumnName = [Geography].[Geography].[Country].[MEMBER_CAPTION]; ColumnOrdinal = 0; Col
ColumnName = [Geography].[Geography].[State-Province].[MEMBER_CAPTION]; ColumnOrdinal =
ColumnName = [Geography].[Geography].[City].[MEMBER_CAPTION]; ColumnOrdinal = 2; Column
ColumnName = [Date].[Calendar].[Calendar Year].[MEMBER_CAPTION]; ColumnOrdinal = 3; Col
ColumnName = [Date].[Calendar].[Calendar Semester].[MEMBER_CAPTION]; ColumnOrdinal = 4;
ColumnName = [Date].[Calendar].[Calendar Quarter].[MEMBER_CAPTION]; ColumnOrdinal = 5;
ColumnName = [Date].[Calendar].[Month].[MEMBER_CAPTION]; ColumnOrdinal = 6; ColumnSize
ColumnName = [Product].[Category].[All Products]; ColumnOrdinal = 7; ColumnSize = 0; Nu
ColumnName = [Product].[Category].&[4]; ColumnOrdinal = 8; ColumnSize = 0; NumericPreci
ColumnName = [Product].[Category].&[1]; ColumnOrdinal = 9; ColumnSize = 0; NumericPreci
ColumnName = [Product].[Category].&[3]; ColumnOrdinal = 10; ColumnSize = 0; NumericPrec
ColumnName = [Product].[Category].&[2]; ColumnOrdinal = 11; ColumnSize = 0; NumericPrec
Скрипт 2
Изо всех осей, кроме оси 0, она делает декартово произведение, которое в плоском виде пускает по оси Y результирующей таблицы. Перемножает оси она, судя по всему, в обратном порядке, т.е. не 1-ю на 2-ю на ... на n-ю, а n-ю на n-1-ю на ... По оси Х мы видим сначала заголовки этих колонок. За ними по оси Х следуют члены множества, заданного в запросе по оси 0 – {[Product].[Category].[All Products], [Product].[Category].&[4], [Product].[Category].&[1], ColumnName = [Product].[Category].&[3]}. Амперсендом, если кто забыл, обозначаются индексы членов. Обычно индексы она берет, когда имена неуникальны. Колонки оси Y, если можно так выразиться, образуют сумму уровней иерархий членов множеств вдоль приходящихся на нее (ось Y) осей запроса. Любопытно, что, например, несмотря на то, что из иерархии географии мы заказывали только город, автоматически подтянулись вышележащие уровни иерархии: штат/провинция и страна. Аналогично с иерархией времени. Ось Y можно представить себе программно в виде вложенного тройного цикла. Простоты ради будем считать, что на одну ось запроса у нас приходится ровно одно измерение. (Ситуацию, когда на оси запроса crossjoin можно домыслить опосля). Сначала идет цикл по всем измерениям, приходящимся на оси запроса: от n-й до 1-й. Внутри каждого измерения идет цикл по уровням иерархии от самого высокого до уровня членов множества по оси запроса. Внутри каждого уровня идет цикл по членам (значениям) атрибута, образующего этот уровень. Кароче. Какая колонка по оси Y сводной таблицы выступает там у нас первой? Страна. Берем первую страну из измерения География. Допустим, Албания. Вторая колонка – штат/провинция. Албания состоит из 12 префектур. Кто не знает – срочно учить олбанский. Остальные берут первую провинцию – Берат. Город. Первый город в этой провинции, очевидно, ее столица. Она тоже называется Берат. Переходим к измерению Время. Его множество по оси запроса 1 содержит все месяцы 2004 г. и имеет уровни иерархии (это задано в иерархии Календарь так же, как уровни иерархии Географии были заданы в иерархии География – второе [Geography] после точки) Год, Семестр, Квартал, Месяц. Берем 2004 г., 1-й семестр, 1-й квартал, январь. Расписываем далее в строке значения меры Internet Sales Amount (это то, что стояло в условии where запроса, иначе бы взялась та мера, которая является в кубе мерой по умолчанию) по графам Все продукты, Примочки, Велики, Шмотки, Запчасти. Если кто не забыл, это множество из измерения Продуктов, которое лежало у нас по 0-й оси запроса и потому плавно переместилось в колонки (ось Х) сводной таблицы результата. Ффу, первая строка готова. Вторая строка будет выглядеть так: Албания, Берат, Берат, 2004-й год, 1-й семестр, 1-й квартал, февраль, значения Internet Sales Amount для этих точек в географии и времени, а также отдельно для Всех продуктов, Примочек, Великов, см. выше. Продуктовые колонки одни и те же тянутся сверху донизу. Третья строчка – март и т.д. Месяц пробегает весь диапазон имеющихся значений, после чего квартал меняется на второй и месяц побежал сызнова. Когда шестеренка кварталов совершит полный оборот, сдвинется на один зубчик шестерня семестров, он станет 2-й. И т.д. вплоть до самой большой шестерни «Страна». Примерно так образно выглядит трактовка декартова произведения.
Алексей Шуленин