Как и всегда при работе с кубами начнем с таблицы фактов, в которой хранятся меры и есть измерения. Измерение времени является фундаментальным. Однако при большом объеме данных удобно хранить их в реляционной базе данных не на каждый день, а только за те дни, когда происходят изменения в значении меры. Это позволяет избежать дублирования большого объема идентичных значений меры и намного уменьшает объем хранимых данных. Рассмотрим такой пример реляционных таблиц.
if object_id ( 'dbo.Dates', 'U' ) is null
begin
create table dbo.Dates
(
dtDate date not null,
constraint PK_Dates_dtDate primary key clustered ( dtDate asc ) on [primary]
) on [primary]
end
go
if object_id ( 'dbo.AttrData', 'U' ) is null
begin
create table dbo.AttrData
(
iAttrId int not null,
constraint PK_AttrData_iAttrid primary key clustered ( iAttrId asc ) on [primary]
) on [primary]
end
go
if object_id ( 'dbo.Data', 'U' ) is null
begin
create table dbo.Data
(
iDataId int not null,
iAttrId int not null,
constraint PK_Data_iDataId primary key clustered ( iDataId asc ) on [primary],
constraint FK_Data_iAttrId foreign key ( iAttrId ) references dbo.AttrData ( iAttrId )
on update cascade on delete no action
) on [primary]
end
go
if object_id ( 'dbo.FactStore', 'U' ) is null
begin
create table dbo.FactStore
(
iDataId int not null,
dtDate date not null,
dtDateNext date not null,
fSum float not null,
constraint PK_FactStore_dtDate_dtDateNext_iDataId primary key clustered
( dtDate asc, dtDateNext desc, iDataId asc ) on [primary],
constraint AK_FactStore_iDataId_dtDate unique nonclustered
( iDataId asc, dtDate asc ) on [primary],
constraint FK_FactStore_iDataId foreign key ( iDataId ) references dbo.Data ( iDataId )
on update cascade on delete no action,
constraint FK_FactStore_dtDate foreign key ( dtDate ) references dbo.Dates ( dtDate )
on update no action on delete no action,
constraint FK_FactStore_dtDateNext foreign key ( dtDateNext ) references dbo.Dates ( dtDate )
on update no action on delete no action
) on [primary]
end
Таблица dbo.FactsStore это таблица фактов, dbo.Data - сущности, dbo.AttrData и dbo.Dates - измерения. Создадим вспомогательную таблицу для возможности хранения данных в кубе только на уровне атрибутов iDataId без хранения самих iDataId:
if object_id ( 'dbo.Facts', 'U' ) is null
begin
create table dbo.Facts
(
dtDate date not null,
dtDateNext date not null,
fSum float not null,
iAttrId int not null,
constraint PK_Facts_dtDate_dtDateNext_iAttrId primary key clustered
( dtDate asc, dtDateNext desc, iAttrId asc ) on [primary],
constraint AK_Facts_iAttrId_dtDate unique nonclustered ( iAttrId asc, dtDate asc ) on [primary],
constraint FK_Facts_iAttrId foreign key ( iAttrId ) references dbo.AttrData ( iAttrId )
on update cascade on delete no action,
constraint FK_Facts_dtDate foreign key ( dtDate ) references dbo.Dates ( dtDate )
on update no action on delete no action,
constraint FK_Facts_dtDateNext foreign key ( dtDateNext ) references dbo.Dates ( dtDate )
on update no action on delete no action
) on [primary]
end
go
Создадим многомерную базу данных на основе этой реляционной схемы. Надо отметить, что для простоты в этом примере помимо измерения времени есть только одно измерение. Однако в реальности измерений может быть намного больше: десятки и сотни. Поэтому в целях уменьшения объема хранимых ячеек в кубе можно хранить данные так же как и в таблице фактов dbo.FactStore, то есть не на каждый день, а только за даты изменения мер. Ниже мы также посмотрим какие есть подводные камни на этом пути и как их обойти.
В среде Visual Studio создадим новый проект типа Analysis Services Multidimensional and Data Mining Project. Затем требуется выполнить такие тривиальные операции как создание источника данных и представления источника данных. После этого все готово для создания измерений. В измерениях можно и нужно создавать иерархии, но это не является главной темой статьи, поэтому таблицы измерений простые, содержат всего по одному атрибуту, в связи с чем пользовательские иерархии создавать не будем. Для удобства имена измерений и атрибутов оставим такими же как и в реляционной базе данных.
Создадим куб Facts на основе таблицы dbo.Facts. В нем будет одна группа мер с одной мерой fSum. Измерения времени в кубе назовем Dates и DatesNext. Приступим к разработке запросов. Для получения суммы fSum в группировке по iAttrId на определенный день (например, на 2017-05-12) в реляционной базе данных используется такой запрос:
select data.iAttrId, fSum = sum ( facts.fSum )
from
dbo.FactStore facts
inner join
dbo.Data data on facts.iDataId = data.iDataId
where '2017-05-12' between facts.dtDate and facts.dtDateNext
group by data.iAttrId
Для того чтобы переписать этот запрос на языке MDX, вспомним, что в MDX есть операция пересечения множеств. То есть нужно взять измерение даты начала от начальной точки до интересующей даты и пересечь это множество с частью измерения даты окончания от даты отчета до конечной точки. Можно сделать это таким образом:
select
Measures.fSum on columns,
non empty AttrData.iAttrId.iAttrId on rows
from Facts
where
(
{ Dates.dtDate.dtDate.[yyyy-mm-dd_min] : Dates.dtDate.dtDate.[2017-05-12] },
{ DatesNext.dtDate.dtDate.[2017-05-12] : DatesNext.dtDate.dtDate.[yyyy-mm-dd_max] }
)
В запрос вместо символов yyyy-mm-dd_min, yyyy-mm-dd_max нужно подставить минимальную и максимальную дату во временном измерении. Здесь мы натыкаемся на проблему, которая состоит в том, что при большом числе ячеек в кубе и большом числе дней операция пересечения временных измерений начинает работать очень медленно. Выход из этой ситуации можно найти, применяя секционирование. Чтобы облегчить операцию поиска пересечений, было бы хорошо работать не с самого начала измерения Dates, а с некоторой не очень далекой точки, и не до самого конца измерения DatesNext, а до определенной концевой не очень далекой точки. Достичь этого можно следующим образом. Представим себе, что есть некоторый интервал, например, месяц. И за каждый такой месяц физически есть своя история изменения меры fSum для сущностей iDataId. То есть на каждую дату начала секции (месяца) в таблице хранятся состояния fSum для всех имеющихся в этот день сущностей. Если есть iDataId, у которого fSum не меняется с 20.05 по 05.06, то в таблице dbo.FactStore хранится не одна, а 2 строки. У первой будет дата окончания 31.05, у второй дата начала 01.06. С одной стороны это создает некоторую избыточность. Но с другой стороны облегчает и работу последующих запросов mdx и работу с самой реляционной таблицей: можно работать с месяцами как с различными секциями одной секционированной таблицы, что облегчает перезагрузку данных за периоды, уменьшает уровень блокировок. Данный подход является компромиссом между хранением данных на каждый день и хранением за периоды без секционирования. Наличие срезов на начало секций создает определенную избыточность, однако при не очень коротких секциях сокращение объема за счет хранения данных в пределах секции за период очень значительно по сравнению с хранением атрибутов на каждый день. Секционируя таблицу фактов, конечно, нужно секционировать и куб.
Для реализации этого подхода на стороне Analysis Services необходимо наполнить таблицу dbo.Facts. Делать это лучше посекционно. Код ниже показывает пример того как это можно сделать для одного месяца (05.2017).
if object_id ( 'tempdb..#Buf', 'U' ) is not null
begin
drop table #Buf
end
select dates.dtDate, data.iAttrid, fSum = sum ( fSum )
from
dbo.FactStore facts
inner join
dbo.Dates dates on dates.dtDate between facts.dtDate and facts.dtDateNext
inner join
dbo.Data data on data.iDataId = facts.iDataId
where facts.dtDate between '2017-05-01' and '2017-05-31'
group by dates.dtDate, data.iAttrId
create unique clustered index IX_iAttrId_dtDate on #Buf ( iAttrId asc, dtDate asc ) on [primary]
if object_id ( 'tempdb..#BufDiff', 'U' ) is not null
begin
drop table #BufDiff
end
select iAttrId, dtDate, fSum
into #BufDiff
from
(
select
iAttrId,
dtDate,
fSum,
dtDate_Prev = lag ( dtDate ) over ( partition by iAttrId order by dtDate asc ),
fSum_Prev = lag ( fSum ) over ( partition by iAttrId order by dtDate asc )
) data
( dtDate <> dateadd ( day, 1, dtDate_Prev ) or dtDate_Prev is null )
or
( fSum <> fSum_Prev or fSum_Prev is null )
create unique clustered index IX_iAttrId_dtDate on #BufDiff ( iAttrId asc, dtDate asc ) on [primary]
if object_id ( 'tempdb..#BufEnds', 'U' ) is not null
begin
drop table #BufEnds
end
select iAttrId, dtDate
into #BufEnds
from #Buf curr
where not exists
(
select *
from #Buf nxt
where
curr.dtDate = dateadd ( day, -1, nxt.dtDate )
and
curr.iAttrId = nxt.iAttrId
)
create unique clustered index IX_iAttrId_dtDate on #BufEnds ( iAttrId asc, dtDate asc ) on [primary]
if object_id ( 'tempdb..#BufHist', 'U' ) is not null
begin
drop table #BufHist
end
select buf.iAttrId, buf.dtDate, buf.fSum, dates.dtDateNext
into #BufHist
from
#BufDiff buf
inner join
(
select iAttrId, dtDate,
dtDateNext = isnull
(
dateadd ( day, -1, lead ( dtDate ) over ( partition by iAttrId order by dtDate asc ) ),
'9999-12-31'
)
) dates on
buf.iAttrId = dates.iAttrId and
buf.dtDate = dates.dtDate
create unique clustered index IX_iAttrId_dtDate_dtDateNext on #BufHist ( iAttrId asc, dtDate asc, dtDateNext desc ) on [primary]
update hist with ( tablock )
set hist.dtDateNext = ends.dtDate
from
#BufHist hist
inner join
#BufEnds ends on
hist.iAttrId = ends.iAttrId and
ends.dtDate between hist.dtDate and dateadd ( day, -1, hist.dtDateNext )
/* вместо delete лучше секцинировать dbo.Facts и использовать switch partition (до ms sql 2016 для этого требуется вспомогательная буферная таблица) */
delete dbo.Facts with ( tablock )
where dtDate between '2017-05-01' and '2017-05-31'
insert into dbo.Facts ( iAttrId, dtDate, dtDateNext, fSum )
select iAttrId, dtDate, dtDateNext, fSum
from #BufHist
В результате запрос на T-SQL перепишется так:
select data.iAttrId, fSum = sum ( facts.fSum )
from
dbo.FactStore facts
inner join
dbo.Data data on facts.iDataId = data.iDataId
where
dtDate between '2017-05-01' and '2017-05-31' and
'2017-05-12' between facts.dtDate and facts.dtDateNext
group by data.iAttrId
, а запрос на MDX будет выглядеть так:
select
Measures.fSum on columns,
AttrData.iAttrId.iAttrId on rows
from Facts
where
(
{ Dates.dtDate.dtDate.[2017-05-01] : Dates.dtDate.dtDate.[2017-05-12] },
{ DatesNext.dtDate.dtDate.[2017-05-12] : DatesNext.dtDate.dtDate.[2017-05-31] }
)
Продолжим разработку запросов. Очень часто данные надо агрегировать не для одного дня, а для многих дней из определенного периода. Например, если требуется получить данные в группировке по дню и iAttrId за 05.2017, то на T-SQL можно написать такой запрос:
select data.iAttrId, dates.dtDate, fSum = sum ( facts.fSum )
from
dbo.FactStore facts
inner join
dbo.Data data on facts.iDataId = data.iDataId
inner join
dbo.Dates dates on dates.dtDate between facts.dtDate and facts.dtDateNext
where
dates.dtDate between '2017-05-01' and '2017-05-31' and
facts.dtDate between '2017-05-01' and '2017-05-31'
group by data.iAttrId, dates.dtDate
Чтобы переписать этот запрос на MDX напишем для начала такую заготовку:
select
Measures.fSum on columns,
non empty
(
AttrData.iAttrId.iAttrId
*
(
{ Dates.dtDate.dtDate.[2017-05-01] : Dates.dtDate.dtDate.[2017-05-31] },
{ DatesNext.dtDate.dtDate }
)
) on rows
from Facts
Данный запрос предоставляет нужные значения атрибутов, но не на каждый день периода, а только за те дни, когда для атрибута менялась мера. Честно говоря, не уверен, что средствами mdx, можно переписать запрос так, чтобы он из одной ячейки, соответствующей периоду в 5 дней, смог вернуть 5 ячеек на каждый день внутри периода. Предлагаю решить эту задачу, используя CLR-сборку, содержащую метод, который принимает на вход и возвращает многомерное множество. В коде предполагается, что во входном множестве первая координата это дата начала, а вторая - дата окончания. Исходный код выглядит следующим образом:
using System;
using System.Data;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;
namespace Tasks
{
public Set ConvertPeriods(Set InputSet)
{
SetBuilder SetB = new SetBuilder();
foreach (Microsoft.AnalysisServices.AdomdServer.Tuple tp in InputSet.Tuples)
{
System.DateTime DateStart = System.DateTime.Parse(tp.Members[0].Name),
DateEnd = System.DateTime.Parse(tp.Members[1].Name);
for (int i = 0; i < (DateEnd - DateStart).TotalDays + 1; i++)
{
Expression exp = new Expression();
System.DateTime dt = DateStart.AddDays(i);
exp.ExpressionText = "Dates.dtDate.dtDate.[" + dt.Year.ToString() + "-" +
(dt.Month.ToString().Length == 1 ? "0" : "") + dt.Month.ToString() + "-" +
(dt.Day.ToString().Length == 1 ? "0" : "") + dt.Day.ToString() + "]";
Member mem = exp.CalculateMdxObject(null).ToMember();
TupleBuilder tpBuild = new TupleBuilder();
tpBuild.Add(mem);
for (int h = 2; h < tp.Members.Count; h++)
{
tpBuild.Add(tp.Members[h]);
}
SetB.Add(tpBuild.ToTuple());
}
}
}
}
Используя Management Studio или XML/A, можно развернуть сборку в многомерной базе данных Analysis Services. Пусть она называется MdTasks. Тогда запрос переписывается в таком виде:
select
Measures.fSum on columns,
non empty MdTasks.ConvertPeriods
(
nonempty
(
(
{ Dates.dtDate.dtDate.[2017-05-01] : Dates.dtDate.dtDate.[2017-05-31] },
{ DatesNext.dtDate.dtDate }
)
*
AttrData.iAttrId.iAttrId
)
) on rows
from Facts
Множество, передаваемое clr-функции, обернуто в функцию nonempty для ускорения работы метода. Теперь и на стороне MDX можно получать агрегаты на каждый день из кубов, хранящих данные в экономном формате за периоды изменения мер!
