В статье
Партицирование 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 >= '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 >= '###dt###'</QueryDefinition>
Далее эта последовательность символов будет меняться на нужную дату.
В BIDS создаем Integration Services Project. Из Toolbox'а перетаскиваем Foreach Loop Container, а в него Script Task и Analysis Services Execute DDL Task. В итоге должно получиться вот так:
Далее создаем переменные:
Для переменных 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 контенера устанавливаем:
- В разделе Collection:
- Enumerator = Foreach File Enumerator
- Folder = C:\XMLAScripts
- Files = *.xmla
- В разделе Variable Mappings: Variable=User:file_name, Index = 0
В свойствах блока
Script в разделе
Script:
- ReadOnlyVariables = User::dt_str,User::file_name
- 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:
- Connection -> создаем новое соединение с кубом
- SourceType =Variable
- Source = User::alter_code
На этом создание SSIS пакета закончено. Foreach контейнер выбирает из папки C:\XMLAScripts все файлы с расширением xmla, в блоке Script текущий файл читается, в нем производиться замена строки "###dt###" на сегодняшнюю дату в формате yyyy-MM-dd, а блок Alter Cube передает полученную таким образом команду OLAP-серверу. Для того, что бы добавить другие группы мер для партицирования, надо в папку C:\XMLAScripts добавить XMLA-файлы с альтерами для этих групп, в которых дата в SQL-запросе будет заменена на "###dt###".
Далее нужно создать джоб, в котором первым шагом сделать бэкап куба на всякий случай, вторым шагом запустить этот SSIS пакет, а далее отпроцессить все партиции за текущий год.