Блеск и нищета сводных таблиц
Часть 3
Обратная запись в Microsoft Excel
Настройка обратной записи в аналитическом кубе
В части 2 данной статьи была затронута тема доработки сводных таблиц посредством создания собственных запросов к многомерным базам данных из среды Microsoft Excel. Пользовательские MDX-запросы позволяют значительно расширить стандартные возможности сводных таблиц. В ряде случаев с их помощью можно заметно снизить трудоемкость прикладных задач.
Собственные MDX-запросы — не единственный способ просто и быстро повысить эффективность работы со сводными таблицами. Другая операция, которая обычно волнует пользователей, — это запись данных из сводной таблицы напрямую в источник данных, так называемая Write Back. К сожалению, обратная запись не входит в состав штатной функциональности сводных таблиц. Но пользователю, интенсивно с ними работающему, она часто бывает необходима. Как известно, спрос рождает предложение. Поэтому сегодня в Интернете не составляет труда найти всевозможные надстройки (Add-In) к программе Excel, реализующие функциональность обратной записи. Большинство из них бесплатны, имеют открытые исходные тексты, их установка на компьютер является делом нескольких минут. Сложности возникают, если требуется адаптировать программу под собственные нужды и бизнес-правила, так как в этом случае необходимо знать принципы ее работы.
Microsoft Excel является основной, а во многих случаях и единственно доступной для большинства пользователей программой бизнес-анализа. Поэтому краткий рассказ о механизмах и технологиях, используемых при выполнении операции Write Back, будет интересен широкому кругу читателей.
Несколько слов об OLE DB
Базовым интерфейсом для доступа к внешним данным в Microsoft Excel являются поставщики данных OLE DB. Прикладную ценность OLE DB проще всего выразить одной фразой: если какойнибудь источник данных поддерживает технологию OLE DB (существует OLE DB-провайдер), то из него всегда можно получить данные в виде плоской таблицы. Принципиальным здесь является тезис о произвольности внутренней структуры источника данных, иными словами — это необязательно должна быть реляционная база данных. Аналитические базы, в частности базы данных Microsoft Analysis Server, поддерживают OLE DB. Благодаря этому обстоятельству не составляет труда получить многомерные данные в формате обычных таблиц.
Microsoft Excel обращается к серверу Microsoft Analysis Server посредством OLE DB-провайдера версии MSOLAP.4. В предыдущей статье цикла было показано, как следует конфигурировать провайдер, чтобы в итоге получить возможность посылать запросы из среды Microsoft Excel напрямую в аналитический сервер.
Продемонстрируем, каким образом можно настроить доступ к аналитическим серверам из реляционных сред. В качестве примера возьмем СУБД Microsoft SQL Server. Здесь подключение к внешним источникам настраивается при помощи объекта Linked Server. Создадим новый присоединенный сервер с такими параметрами, как на рис. 1.
Рис. 1. Создание присоединенного сервера
В поле Product name следует указать подходящую версию OLE DB-провайдера. В нашем случае — это всё тот же MSOLAP.4, в поле Data source — название аналитического сервера, а поле Catalog — имя базы на выбранном сервере, из которой требуется получить информацию.
После этого при помощи инструкции OPENQUERY можно составлять запрос на выборку из аналитической базы (рис. 2).
Рис. 2. Запрос на выборку из аналитической базы данных
На рис. 2 показан запрос данных по продажам за последние два месяца с детализацией по подразделениям. Как видно из того же рисунка, выходные данные преобразованы OLE DB-провайдером в формат плос-кой таблицы — все атрибуты выведены в заголовки.
Провайдер OLE DB предоставляет пользователю набор стандартных интерфейсов для обращения к источнику данных. В частности, при помощи OLE DB на OLAP-источник можно послать любую команду из класса Data Manipulation Statement. Однако штатное подключение Microsoft Excel, использующее OLE DB, поддерживает единственную команду — Select.
Обычно ограничения подобного рода достаточно просто обходятся путем вызова необходимых интерфейсов в среде Visual Basic. К сожалению, в случае с OLE DB данная методика не работает, так как классы OLE DB, будучи универсальными объектами, не поддерживают автоматизацию (Automation).
Для обращения к OLE DB из языков, работающих по технологии «позднего связывания» (в частности, Visual Basic), приходится использовать COM-надстройки к провайдеру OLE DB — ADO DB либо ADO MD. Указанные надстройки содержат класс Command, позволяющий определить произвольную команду, выполняющуюся по отношению к источнику данных, и делегировать ее OLE DB для последующего исполнения.
Обратная запись в Microsoft Excel
Вернемся к нашей проблеме. Операция обратной записи Write Back представляет собой обновление аналитического куба. Поэтому для ее выполнения достаточно иметь возможность послать на сервер команду Update cube с указанием условий отбора ячеек, подлежащих изменению.
Будем считать, что в Microsoft Excel уже имеется подключение к OLAP-источнику с именем OLAP_Connection. Запускаем редактор Visual Basic и в разделе Tools → References подключаем к проекту библиотеку Microsoft ActiveX Data Objects (использовалась последняя ее версия — 2.8). Такая операция добавляет библиотеку классов ADODB.
Создаем новый модуль, а в нем — процедуру с именем Cube_Update. Объявляем и создаем в процедуре новые переменные:
Dim AdoCmd As ADODB.Command
Dim Conn As ADODB.Connection
Set AdoCmd = New ADODB.Command
Set Conn = New ADODB.Connection
Экземпляр класса ADODB.Command позволяет составить команду, которая будет делегирована провайдеру OLE DB, а класс ADODB.Connection предназначен для определения параметров этого подключения.
Присвоим свойству ConnectionString класса ADODB.Connection значения параметров уже существующего OLE DB-подключения:
Conn.ConnectionString = ActiveWorkbook.Connections(«OLAP_Connection»).OLEDBConnection.ADOConnection
Теперь подключение можно открыть и указать его в качестве исходного для объекта ADODB.Command:
Conn.Open
AdoCmd.ActiveConnection = Conn
Предположим, что книга Microsoft Excel содержит именованный диапазон Update_Command, в котором составлена необходимая команда на обновление куба. Эту команду следует передать объекту ADODB.Command, после чего дать указание на ее исполнение:
Conn.BeginTrans
AdoCmd.CommandText = Range(«Update_Command»).Value
AdoCmd.Execute
Conn.CommitTrans
Команду на обновление необходимо выполнять в транзакции (Conn.BeginTrans — Conn.CommitTrans).
Наконец, после выполнения всех действий подключение следует закрыть:
Conn.Close
Как видите, вся процедура обратной записи укладывается в десяток очень простых команд. Прежде чем приступить к ее практической реализации, осталось настроить аналитическую базу таким образом, чтобы она адекватно реагировала на подобную активность клиентского приложения.
Настройка обратной записи в аналитическом кубе
Механизм обратной записи в аналитических базах может выполняться в двух режимах: Temporary writeback и Permanent writeback.
В первом случае все изменения хранятся в кэше обратной записи, поэтому они доступны только в пределах одной сессии для пользователя, открывшего данный сеанс работы с отчетом. После закрытия сеанса все значения из кэша удаляются, а куб возвращается в свое первоначальное состояние. Такой режим работы часто называют анализом «Что если» (What if analysis), поскольку он позволяет провести анализ предметной области при изменении различных условий, сохраняя при этом исходные данные.
Второй режим носит название режима постоянной записи (Permanent writeback). При его использовании изменения физически сохраняются на сервере, что делает их доступными для последующего просмотра другими пользователям системы.
Изменения хранятся в специальном формате — дельта-значение. Дельтазначение вычисляется как разность между конечным и исходным состояниями ячейки. Иными словами, если сначала в ячейке хранилось значение «100», которое потом заменили на «80», то такая операция будет записана в виде дельта-значения «–20». Дельта-значения должны накапливаться в специальной секции куба — секции обратной записи (Writeback Partition). При формировании куба такая секция не создается по умолчанию, поэтому ее следует добавить в куб в ручном режиме.
Собственно, добавление секции производится довольно просто. В редакторе кубов проекта Business Intelligence Studio нужно совершить переход на вкладку Partitions и выбрать на ней группу мер, в которую планируется заносить изменения.
Затем нажатием на правую клавишу мыши для этой группы мер следует вызвать контекстное меню и выбрать в нем пункт Writeback Settings. Наконец, в появившемся диалоговом окне нужно выбрать источник данных и указать имя таблицы, которую Microsoft Analysis Server будет использовать для хранения дельта-значений. После выполнения обработки группы мер для нее включается режим постоянной обратной записи.
Заключение
Чтобы обеспечить работоспособность предложенной схемы, осталось решить последнюю и, возможно, главную проблему. Мы всё время неявно полагали, что в нашем распоряжении уже имеется готовый текст команды на обновление куба. Соответственно все действия, описанные в статье, были направлены лишь на то, чтобы донести эту команду до аналитического сервера и обеспечить последующее «понимание и исполнение» с его стороны. Однако составление любого MDX-запроса довольно трудоемко. Если же запрос должен меняться в зависимости от контекста исполнения (текущего ракурса куба), то задача значительно усложняется.
Когда в наличии имеется отчет в форме сводной таблицы, то получить из него адрес любой ячейки в многомерном пространстве можно посредством функции Получить.Данные.Сводной.Таблицы() (GetPivotData). К сожалению, такая координата совершенно не подходит на роль аргумента в MDX-запросе. Поэтому более перспективным видится подход, при котором отчет создается путем разверток измерений. Для этого необходимо знать принцип действия функций КУБ(), чему и будет посвящена следующая статья.
 |
|
