Автоматическое партицирование OLAP-кубов в SSAS 2008

В статье Партицирование OLAP-кубов в SSAS 2008 я рассказывал зачем и как партицировать кубы в SSAS. Бывают случаи, когда партицировать надо часто и руками это делать скучно. Поэтому хочется этот процесс автоматизировать.

В моем случае мне нужно партицировать в трех кубах в общей сложности 10 групп мер. Если этого не делать каждую неделю, то время процессинга за две недели возрастает с 3,5 часов до 5 с лишним.

В статье Взаимодействие с OLAP кубами (SSAS 2008) на C# я рассказывал, как работать с кубами из кода. Сначала я начал решать задачу автоматического партицирования через разработку проги, но что то сильно углубился в программирование и мне эта идея перестала нравиться, поэтому я выбрал другой путь - создание пакета SQL Server Integration Services (SSIS). Здесь я и опишу этот способ.

Идея следующая - группы мер партицируются по годам плюс есть текущая партиция, которая процесситься каждый день. Т.е., например, для продаж есть партиции за 2010 год, 2011 и current. Раз в неделю для каждой группы мер необходимо изменять партицию за текущий год (2011) и партицию current, так что бы текущая партиция содержала данные только за несколько дней. Потом процессить партицию за текущий год, что бы не было пробелов в данных. После нового года надо будет руками создавать новую годовую партицию и менять процедуру изменения партиций.

Начальное партицирование куба надо провести руками, автоматически все с нуля не создается.

Для примера возьмем, что партиции группы мер продаж формируются следующими SQL-запросами:

2010: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2010-01-01' AND sale_date < '2011-01-01'
2011: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2011-01-01' AND sale_date < '2011-05-31'
current: SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date >= '2010-05-31'

Эти запросы и надо будет менять в партициях за 2011 год и в текущей партиции.

Для начала необходимо сгенерить скрипты XMLA для изменения партиций. Я буду делать все на примере только одной группы мер. Пакет SSIS будет сделан так, что количество не будет иметь значения.

Cоздаем папку: C:\XMLAScripts. Далее, для генерации скрипта в Management Studio открываем список партиций нужной группы мер, на текущей партиции тыкаем правой кнопкой, а далее как на картинке:

В последнем меню выбираем File... и сохраняем в созданной папке под названием sales_current.xmla. Далее тоже самое повторяем для партиции текущего года и сохраняем в другой файл sales_2011.xmla.

Далее открываем файлы по-очереди и видим что-то похожее на это:

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>cubeDbSales</DatabaseID>
        <CubeID>cubeSales</CubeID>
        <MeasureGroupID>Vw Sales 1</MeasureGroupID>
        <PartitionID>Vw Sales 1</PartitionID>
    </Object>
    <ObjectDefinition>
        <Partition ...>
            <ID>Vw Sales 1</ID>
            <Name>Vw Sales</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>dbOLAP</DataSourceID>
                <QueryDefinition>SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date &gt;= '2010-05-31'</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <Source xsi:type="ProactiveCachingInheritedBinding" />
            </ProactiveCaching>
        </Partition>
    </ObjectDefinition>
</Alter>

В каждом файле заменяем дату, которая разделяет партицию текущего года и текущую партицию, в данном примере это 2011-05-31, на какую-нибудь последовательность символов. Я поменял на "###dt###", т.е. запрос в скрипте будет выглядеть вот так:

 <QueryDefinition>SELECT id, sale_date, user_id, money_sum, product_id FROM sales WHERE sale_date &gt;= '###dt###'</QueryDefinition>

Далее эта последовательность символов будет меняться на нужную дату.

В BIDS создаем Integration Services Project. Из Toolbox'а перетаскиваем Foreach Loop Container, а в него Script Task и Analysis Services Execute DDL Task. В итоге должно получиться вот так:

Далее создаем переменные:

Розовые уголки в иконках - это у меня плагин стоит, который я описываю в статье Как поменять область видимости переменной в BIDS (SSIS).

Для переменных dt и dt_str устанавливаем свойство EvaluateAsExpression = True. Для dt свойство Expression: GETDATE(). Для dt_str свойство Expression:

(DT_WSTR, 4)(YEAR(@[User::dt]))+"-"+
(LEN((DT_WSTR, 2)(MONTH(@[User::dt])))==1 ? "0"+(DT_WSTR, 2)(MONTH(@[User::dt])) : (DT_WSTR, 2)(MONTH(@[User::dt])))+"-"+
(LEN((DT_WSTR, 2)(DAY(@[User::dt])))==1 ? "0"+(DT_WSTR, 2)(DAY(@[User::dt])) : (DT_WSTR, 2)(DAY(@[User::dt])))

Такое выражение формирует из даты строку формата yyyy-MM-dd, другого пути я не нашел.

В совойствах Foreach контенера устанавливаем:

1. В разделе Collection:
1. Enumerator = Foreach File Enumerator
2. Folder = C:\XMLAScripts
3. Files = *.xmla 
2.  В разделе Variable Mappings: Variable=User:file_name, Index = 0

 В свойствах блока Script в разделе Script:

1. ReadOnlyVariables = User::dt_str,User::file_name
2. ReadWriteVariables = User::alter_code

В коде скрипта пишем (у меня SQL Server 2008, поэтому пишу на C#):

        public void Main()
        {
            string file_name = Dts.Variables["file_name"].Value.ToString();
            StreamReader sr = new StreamReader(file_name);

            string content = sr.ReadToEnd();

            sr.Close();
            sr.Dispose();

            Dts.Variables["alter_code"].Value = content.Replace("###dt###",  Dts.Variables["dt_str"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;
        }

 В свойствах блока Alter Cube в разделе DDL:

1. Connection -> создаем новое соединение с кубом
2. SourceType =Variable
3. Source = User::alter_code

На этом создание SSIS пакета закончено. Foreach контейнер выбирает из папки C:\XMLAScripts все файлы с расширением xmla, в блоке Script текущий файл читается, в нем производиться замена строки "###dt###" на сегодняшнюю дату в формате yyyy-MM-dd, а блок Alter Cube передает полученную таким образом команду OLAP-серверу. Для того, что бы добавить другие группы мер для партицирования, надо в папку C:\XMLAScripts добавить XMLA-файлы с альтерами для этих групп, в которых дата в SQL-запросе будет заменена на "###dt###".

Далее нужно создать джоб, в котором первым шагом сделать бэкап куба на всякий случай, вторым шагом запустить этот SSIS пакет, а далее отпроцессить все партиции за текущий год.