Обновление кубов OLAP методом приращений

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

Клод Сидман, SQL Server Magazine/RE

Кубы OLAP обладают возможностью хранения и организации колоссальных объемов данных, поэтому для составления отчетов они удобнее, чем стандартные реляционные базы данных. Запросы, на обработку которых реляционной базе данных требуется около 20 минут, могут предоставить результаты менее, чем за 1 минуту, если система будет обращаться к заранее вычисленным агрегированным таблицам. Однако если куб содержит данные о показателях продаж за период более года, что составит более 20 млн. записей, то добавление новых записей в фактографическую таблицу продаж и связанная с этим обработка куба данных могут занять несколько часов. Повторная обработка огромных объемов старых данных, которые ничуть не изменились, представляется крайне неэффективной, ведь в куб добавлено всего несколько записей. Соответственно, необходимо найти способ добавлять в существующий куб информацию в течение нескольких минут, для обработки только вновь поступивших записей.

Чтобы еще более усложнить задачу, потребуем, чтобы записи с итогами продаж за день добавлялись ночью в автоматическом режиме. То есть, необходимо обновлять кубы данных автоматически и методом приращений. Хотя подобная возможность является жизненно важной для реализации кубов данных OLAP в масштабах предприятия, тем не менее, она недостаточно подробно описана в документации, что порождает множество ошибок. В данной статье на примере ежедневного пополнения куба новыми записями о продажах я хочу показать, как следует применять реляционные таблицы для облегчения процесса обновления. Затем будут описаны процедуры обновления кубов методом приращений как вручную, так и программным путем, с использованием интерфейса DSO (Decision Support Objects) COM. И, наконец, я расскажу о том, как применять для этих целей DTS (Data Transformation Services).

Настройка реляционных таблиц

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

Для успешной реализации метода приращений службам OLAP понадобится сравнивать элементы фактографической таблицы с данными в многомерном кубе, чтобы исключить уже занесенные в него записи. Чтобы продемонстрировать этот метод, я использую уникальное поле приращений в фактографической таблице, по которому затем будет проведено агрегирование с применением функции MAX(). Функция MAX() позволяет написать простой запрос MDX, который возвратит наибольший идентификатор, хранящийся внутри куба. Любая запись в соответствующей реляционной таблице, значение идентификатора которой превышает полученную величину, становится кандидатом на включение в куб при следующей его обработке. Если фактографическая таблица заполняется автоматически с помощью специальных программ, которые могут присвоить уникальный номер каждой партии вводимых записей, то в качестве упомянутого выше идентификатора можно использовать номер партии записей. Чтобы следовать предлагаемой методике, необходимо убедиться в том, что записи в фактографической таблице содержат столбец, куда заносится последовательный номер, который можно хранить в кубе в качестве показателя.

Возможно, идея добавить в куб еще один показатель только для того, чтобы можно было обновлять содержимое куба методом приращений, кому-то не покажется блестящей. Но легко убедиться в том, что даже если вы не добавите показатель в куб, реляционным таблицам все равно необходимо отслеживать, какие их записи были помещены в куб, а какие - нет. Я сравнил два способа отличать вновь поступившие в фактографическую таблицу записи от тех, что уже были использованы ранее для заполнения куба, с применением только реляционных таблиц. Можно для каждой порции данных завести свою фактографическую таблицу и затем писать длинные процедуры для проверки корректности заполнения фактографических таблиц. Но тогда придется проводить полное обновление на основе индивидуальных порций. Другой способ заключается в том, чтобы вместо фактографической таблицы использовать ее представление. Оборот WHERE в определении этого представления ограничит возвращаемые строки только теми, которые еще не вводились в куб. Это можно обеспечить введением отдельной таблицы для отслеживания значения последнего идентификатора, посланного в куб. Каждый раз, когда пользователь обновляет куб, он также должен обновлять и значение идентификатора в этой таблице, чтобы оно было актуальным. При таком методе возвращаемый представлением результирующий набор данных будет зависеть от последнего значения этого идентификатора. Однако с таким подходом связана следующая проблема. До обновления значения идентификатора в служебной таблице реляционной базы данных практически невозможно удостовериться в том, что обновление куба завершено успешно. Другая проблема соответствует обратной ситуации: обновление куба проведено благополучно, а обновить идентификатор в служебной таблице не удалось. Тогда служба OLAP повторно наполнит куб уже введенными данными. Мне не удалось найти способ свернуть два разных процесса в одну транзакцию, если один процесс происходит в SQL Server, а другой - в службе OLAP. Чтобы убедиться в том, что даже прерванные транзакции не нарушат целостности как куба OLAP, так и таблиц реляционной базы данных, необходимо хранить в кубе сведения о записях, которые он содержит. Эти сведения позволят сравнить их с информацией, хранящейся в реляционной таблице. Лучший способ реализовать такой подход - использовать описанный выше идентифицирующий ключ в качестве одного из показателей многомерного куба.

Обновления куба методом приращений с применением OLAP Manager

Многомерный куб представляет собой структуру, которая содержит иерархию меньших структур, называемых секциями (partitions). В них размещаются агрегированные данные. Секции часто называют также срезами куба, так как они соответствуют параллельным подмножествам данных. К примеру, куб с данными о продажах можно разбить на секции, каждая их которых будет содержать сведения за один квартал. Но поскольку такие секции являются частями одного и того же куба, пользователю не надо учитывать эту конфигурацию при составлении своих запросов . Любой куб заключает в себе, по крайней мере, одну секцию, а может содержать десятки и сотни, если это будет способствовать оптимизации доступа к данным и их обработки. Залогом успешного секционирования куба, то есть его разбиения на множество секций, является их однородность. Это означает, что в каждой секции содержатся только те данные, которые соответствуют условию разбиения, и никакие другие. В противном случае в ответ на запрос могут быть получены некорректные данные.

Обеспечить выполнение условия, что никакие две секции не будут содержать пересекающиеся данные, можно двумя способами. Первый заключается в том, что каждой секции ставится в соответствие отдельная фактографическая таблица или представление, содержащее только те данные, которые необходимы для наполнения этой секции. Такой метод хорош в тех случаях, когда создаются новые секции для новых данных, а существующие секции по определению будут содержать архивную информацию. При этом только новые данные подвергаются обработке, необходимой для помещения в куб. Второй способ основан на создании своего фильтра для каждой секции. При этом ядро OLAP будет обрабатывать только те данные, которые должны содержаться в указанной секции, хотя всякий раз при обработке данных ядро будет обращаться к одной и той же фактографической таблице. Последний метод особенно полезен при проведении обновлений методом приращений.

В рассматриваемом примере с ежедневным обновлением сведений о продажах следует установить фильтр, который будет оставлять только те записи, чьи идентификаторы больше максимального значения идентификатора в кубе. Это значение можно увидеть в обозревателе данных куба. Поскольку в нашем примере служба OLAP применяет функцию MAX() для агрегирования поля идентификатора, то наибольшее значение получится в результате агрегирования самого верхнего уровня для всех размерностей. Этот уровень установлен для использования по умолчанию в OLAP Manager Cube Editor, как показано на экране 1.

Чтобы установить фильтр для текущей секции, выберите куб, который будете использовать, и раскройте расположенное под ним дерево. При этом вы увидите несколько папок, в том числе папку Partitions. Если щелкнуть на ее значке, то появится список секций данного куба. Щелкните правой кнопкой мыши на секции, которая по умолчанию имеет то же имя, что и сам куб. В появившемся диалоговом окне выберите Edit, затем наберите условие оборота WHERE, как показано на экране 2.

В диалоговом окне обработки куба, Process a Cube, выберите пункт обновления методом приращений, Incremental update, как показано на экране 3.

В этом случае службы OLAP добавят в секцию только те записи, которые удовлетворяют условию фильтра. Следует отметить, что если пользователь выберет полную повторную обработку или обновление при включенном фильтре, то секция будет полностью очищена от всех записей, и службы OLAP поместят в нее только записи, соответствующие фильтру. Поэтому при добавлении новых данных вручную следует обязательно убедиться в том, что после обработки куба службами OLAP вы не забыли отключить фильтр данной секции.

Автоматизация обновлений методом приращений

При нерегулярных обновлениях многомерных кубов их можно выполнять вручную. Однако часто в таких кубах хранятся сведения за каждый день и даже за каждый час. В таких случаях целесообразно автоматизировать процесс обновления. Это не так уж трудно, если разбираться в DSO и знать структуру куба. Приведу краткие сведения о DSO. Он является интерфейсом СОМ, поставляемым вместе с OLAP Manager. Теоретически доступ к DSO можно осуществить из любого языка написания сценариев, например, из VBScript, JavaScript или Perl. Но разработчики корпорации Microsoft создали DSO, используя интерфейсы VB, а этот язык требует строгого определения типов переменных. Поэтому применение распространенных языков сценариев несколько затруднено, ведь они позволяют использовать множество вызовов недокументированных методов и свойств, которые Microsoft, возможно, в дальнейшем поддерживать не будет. Поэтому я рекомендую применять VB для создания библиотек ActiveX DLL, в которых будут находиться процедуры, реализующие все необходимые для обработки кубов функции. Затем уже можно писать сценарии с обращениями в этой библиотеке DLL.

Чтобы больше узнать о базовой структуре DSO, проанализируйте приводимые ниже фрагменты кода на Visual Basic, необходимые на каждом шаге выполнения задач. Для простоты я опустил в этих примерах те части кода, которые относятся к обработке ошибок, а также все жестко задаваемые в тексте программ величины.

Объектом самого верхнего уровня является сервер, экземпляр которого создается следующим образом:

Dim dsoServer as DSO.Server

Set dsoServer = new DSO.Server

После этого сервер подсоединяется к серверу OLAP:

dsoServer.Connect('SALESSERVER')

Когда сервер будет подсоединен, откроется доступ к следующему уровню иерархии через интерфейс MDStores. Это общий интерфейс для доступа к объектам DSO следующего, более низкого уровня иерархии. Поэтому он использован для доступа к базе данных WIDGET, расположенной на сервере:

Dim dsoDb as DSO.MDStore

Set dsoDb = new  dsoServer.MDStores(`Widgets`)

Для получения доступа к кубу можно воспользоваться тем же самым интерфейсом, как это сделано ниже:

Dim dsoCube as DSO.MDStore

Set dsoCube = new  dsoDb.MDStores(`Sales`)

Теперь необходимо найти секцию куба. По умолчанию принято, что если у куба имеется только одна секция, то она носит такое же имя, как и сам куб. Поэтому в данном примере:

Dim dsoPartition as DSO.MDStore

Set dsoPartition = new   dsoCube.MDStores(`SALES`)

По аналогии с ручной процедурой обновления секции методом приращений логично было бы предположить, что теперь осталось только включить фильтр для данной секции и запустить функцию IncrementalUpdate(), как это сделано в методе, применяемом интерфейсом OLAP Manager. Однако все не так просто. DSO поставляет метод обработки объекта секции, который следует вызвать командой

DsoPartition.Process <ProcessType>

Доступные типы процессов приведены в таблице 1.

ProcessBuilkdStructure	Полностью создает заново структуру куба, но не загружает данные. Процесс применим только к кубам.
ProcessDefault	Определяет наилучший тип процесса, основываясь на изменениях структуры, которые могут произойти.
ProcessFull	Полностью создает заново структуры и заполняет их данными.
ProcessRefreshData	Загружает заново все данные, не изменяя структуру.

Но где же флажок Incremental? Его нет. OLAP Manager невольно вводит в заблуждение, представляя дело таким образом, будто обновление приращениями является одним из вариантов, наряду с полной обработкой, Full Process, и полным обновлением, Refresh. В действительности службы OLAP применяют только те типы процессов, которые приведены в таблице 1. Службы OLAP создают новую временную секцию, которая содержит введенный пользователем фильтр, и новые данные добавляются в эту секцию. Затем службы OLAP проводят слияние временной секции и исходной, что выполнимо лишь при совпадении структур обеих секций.

Для создания временной секции следует применить объект куб, как показано ниже:

Dim dsoTmpPartition as dsoCube.MDStores

Set dsoTmpPartition = dsoCube.MDStores.AddNew(`~tmpPartition`)

Обратите внимание на тильду перед названием временной секции. DSO рассматривает любую секцию, имя которой начинается с тильды, как временную, и удаляет ее, как только завершится соединение с ней. Это важный момент, так как службы OLAP будут добавлять все оставшиеся секции к исходному кубу независимо от того, какими их намеревались сделать - временными или нет. Применение тильды гарантирует, что секция исчезнет даже в случае прерывания слияния.

Слияние возможно только при совпадении структур размерностей и уровней у временной и исходной секций. Вместо того чтобы программным путем создавать одну за другой структуры, целесообразно прибегнуть к специально созданному для этого методу, а именно:

dsoPartition.Clone(dsoTmpPartition)

Этот метод гарантирует идентичность структур исходной и временной секций. Для ограничения количества данных, помещаемых в секцию, необходимо использовать фильтр, как и в ручной процедуре. Чтобы применить фильтр, следует определить наибольшее значение идентификатора в кубе. Для извлечения информации из куба используется ADO MD, как показано в листинге 1.

Dim ct As New ADOMD.Catalog

Dim cs As New ADOMD.Cellset

Dim strMdxQry As String

ct.ActiveConnection="Data Source

    SALESSERVER;Provider=msolap;"

StrMdxQry= select {[MEASURES].[ID]} on rows, &_

    {[timedata]] on columns from [SALES]

cs.Source= strMdxQry

cs.ActiveConnection=ct.ActiveConnection

cs.Open

    strHighestID=cs(0,0).FormattedValue

Выяснив значение наибольшего идентификатора в кубе, можно приступить к созданию фильтра для секции. DSO использует фильтр как оборот WHERE при выборке из фактографической таблицы, поэтому желательно придать фильтру такой вид, как показано в листинге 2.

DsoTmpPartition.SourceTableFilter="[SALESDATA].[ID] > "

&_StrHighestID

DsoTmpPartition.Process processFull

По окончании обработки секции необходимо удалить существующий фильтр до начала слияния временной и исходной секций. Иначе службы OLAP добавят временный фильтр к фильтру исходной секции, что приведет совсем не к тому результату, который ожидается. Кроме того, следует иметь в виду, что попытка слить две секции, одна из которых пуста, воспринимается как ошибка. Поэтому прежде чем давать команду слияния, обязательно надо проверить, были ли помещены во временную секцию какие-либо записи. Как это сделать, показано в листинге 3. Этот шаг завершает автоматическую обработку куба.

    If dsoTmpPartition.EstimatedSize > 0 Then

DsoTmpPartition.SourceTableFilter=""

        DsoPartition.Merge(" tmpPartition")

    End If

Применение DTS для обновления кубов методом приращений

Недавно корпорация Microsoft выпустила новую встраиваемую в DTS (Data Transformation Services) задачу, называемую OLAP Services Processing Task, которая позволяет применять обновления методом приращений к секциям куба. Позволяя контролировать прохождение задачи в графической среде, OLAP Services Processing Task предоставляет удобный способ планировать такие обновления и интегрировать их с другими задачами, выполняемыми по расписанию с привлечением фактографической таблицы. Эту встраиваемую задачу можно бесплатно загрузить с сайта Microsoft по адресу http://www.microsoft.com/sql/bizsol/DTSKit.htm.

Чтобы сформировать пакет без добавления пользовательского кода DTS, я создал таблицу в SQL Server, единственной функцией которой стало хранение последнего значения наибольшего идентификатора в кубе. Такой метод позволил создать настраиваемый фильтр для секции. Запрос SQL во время подкачки данных собирает информацию, относящуюся к наибольшему значению идентификатора, хранящегося в кубе. Этот запрос SQL ищет наибольшее значение в поле идентификатора фактографической таблицы непосредственно перед тем, как к обработке куба приступят службы OLAP. Позднее эта информация поможет фильтровать фактографическую таблицу, не меняя перед каждым обновлением условие оборота WHERE для секции. Как показано в листинге 4, такая таблица в рассматриваемом примере называется SALESID, и в ней имеется всего два поля.

Create table salesid

    ( [id] int,

    [batchdate] datetime default getdate()

    )

go

create clustered index CLX_DATE on batchdate([id])

go

Как следует из названия, поле ID содержит наибольшее значение идентификатора, найденное в кубе, а поле BATCHDATE помогает определить, какая запись была введена в куб последней. Поле BATCHDATE может послужить источником информации о том, когда службы OLAP пытались получить эти данные, что можно использовать при проведении внутренних проверок.

Необходимо создать одно соединение для куба, а другое - для базы данных, где была построена таблица SALESID. Чтобы установить соединение OLAP, откройте соединение OLE DB, выберите Microsoft OLE DB Provider для источника данных OLAP Services и введите в поле Server имя сервера OLAP, как показано на экране 4.

Значок соединения сразу же изменит форму, превратится в маленький куб (см. экран 5).

Теперь необходимо добавить еще одно соединение с базой данных SQL Server и присоединить задачу преобразования. Она запишет еще одну строку в таблицу SALESID, содержащую наибольшее на данный момент значение идентификатора, имеющееся в кубе. Во время обработки фильтр секции использует это значение, определяя, какие записи следует поместить в куб. На экране 6 показано, что в качестве источника преобразования для получения наибольшего значения идентификатора в кубе следует использовать запрос MDX.

 

Присоедините информацию размерности, основанной на времени, к полю ID (см. экран 7). В рассматриваемом случае столбец TIMEDATA содержит ячейку, которую необходимо послать в поле ID.

Если процесс обновления куба введет в куб показатель, зависящий от еще не существующей размерности, то это будет воспринято системой как ошибка, и куб не будет обрабатываться. Чтобы избежать такой ситуации, создайте задачу, которая обновит данные размерности до начала обновления секций куба. От этого шага зависит, успешно ли завершится обработка секции. Для установки задачи обработки размерности выберите значок OLAP Services Processing Task, представленный сплошным кубиком желтого цвета, как показано на экране 8.

После того как службы OLAP успешно проведут обновление размерности, можно начинать обновление секции. В рассматриваемом случае в секцию надо добавить порцию данных из фактографической таблицы, у которых значение идентификатора превышает максимальное значение, существующее в кубе. Выбрав секцию и тип обработки (обновление методом приращений, Incremental update), вы увидите поля, позволяющие изменить и фактографическую таблицу, и фильтр. В нашем примере следует изменить только фильтр, как показано на экране 9.

В отличие от фильтра, прикрепленного непосредственно к секции через OLAP Manager, этот фильтр существует только во время выполнения задачи.

На экране 10 показано исполнение пакета DTS.

Эту задачу можно выполнять и по расписанию, но при этом следует помнить, что при отсутствии записей, подлежащих обработке и пересылке в куб, часть задачи, связанная с обновлением секции, завершится ошибкой. В коде DSO, разобранном в предыдущем разделе, я показал, как избежать обработки секции при отсутствии новых записей во временной секции. Поскольку DTS при выполнении обновления методом приращений производит слияние временной и исходной секций, то при использовании DTS можно получить сообщение об ошибке. Это обусловлено тем, что DTS не контролирует наличие записей в секции. Если вы хотите избежать этой ошибки, то вставьте в пакет DTS несколько проверок, которые предотвратят запуск этой части задачи, когда в фактографической таблице нет новых записей.

Автоматизация интеграции данных

Способность многомерных кубов придавать смысл данным становится еще более ценной, если данные актуальны. При автоматизации средств интеграции данных необходимо ясно представлять себе, каким образом службы OLAP обрабатывают поступающие данные. Это особенно важно при управлении серверами, содержащими структуры данных большого объема, состоящие из множества кубов, которые регулярно обновляются.

Об авторе:

Клод Сидман (cseidman@mediaone.net) работает независимым администратором баз данных, разработчиком и преподавателем, специализирующимся на SQL Server, OLAP, PERL и ASP. Обладает сертификатами MCDBA, MCSE, MCP+I и MCT.