1С: Предприятие. Консолидированная отчетность в корпорации.

Распределенные кластеры MS SQL 2000. Быстрый дифференцированный OLAP.

О чем данная статья?  
Постановка задачи для кластеров и OLAP
Альтернативные варианты решения: репликация, Real-time OLAP
В чем преимущества кластера MS SQL 2000 и дифференцированного OLAP как DSS-решения?
Шаг 1. Создание view для доступа к данным к продажам
Шаг 2. Создание дифференцированного Data Warehouse по данным продаж в кластере
Шаг 3. Загрузка дифференцированного Data Warehouse
Шаг 4. Создаем кластер MS SQL 2000 и убеждаемся, что он работает производительно
Шаг 5. Создаем OLAP-куб поверх кластера MS SQL 2000
Шаг 6. Выгружаем данные в дифференцированный OLAP-куб
Шаг 7. Просматриваем OLAP-данные через MS Excel 2000
Заключительные рекомендации и выводы

О чем данная статья?

Данная статья написана по мотивам внедрения в компании Сатурн продукта "Бизнес Сканнер" (OLAP-решения для Microsoft Analysis Services) . Статья демонстрирует принципы построения распределенных OLAP-систем в корпорациях.

Статья должна быть полезна всем разработчикам, которые заняты проблемой консолидированной отчетности средствами MS SQL 2000, в частности его OLAP-сервисами. Статья посвящена использованию кластеров и дифференцированного OLAP из Microsoft SQL Server 2000 для повышения быстродействия и снижения трудоемкости разработки систем приятия решений (Decision Support System, DSS). В качестве примера такой системы мы будем рассматривать 1С: Предприятие для SQL и OLAP-отчетность по данным в ней. Мы рассмотрим вариант со следующими параметрами быстродействия: время построения произвольного OLAP-отчета в Excel 2-3 сек., задержка по доставке данных из филиалов (других баз) не более 10 мин.

Постановка задачи для кластеров и OLAP

Рассмотрим реальные примеры, где решение с подобными характеристиками необходимо.

Агрессивная торговая компания, имеющая несколько филиалов. Объем операций в каждом филиале велик, поэтому там установлено 1С: Предприятие для SQL. Все филиалы объединены в Intranet-сеть компании. Требуется получать консолидированную отчетность в центральном офисе, с запозданием не более 10 мин и временем построения не более 5 сек. Такие требования связанны со следующей типовой ситуацией. Компания постоянно предлагает клиентам новые товары и новые системы скидок, ставка делается на большие объемы продаж в ущерб рентабельности отдельных сделок. Соответственно сейл-менеджеры могут по ошибке начать торговать в убыток, каждая такая ошибка может достигать $400 на операцию, за день по фирме убыток может достигнуть $5000.  Другой аспект, быстрое выявление низких продаж нового продукта по филиалу позволяет выявить недостаточную обеспеченность раздаточными рекламными материалами. С учетом того, что первый день продаж ожидаемого рынком продукта  имеет характер ажиотажного спроса, то доставка необходимых материалов в  тот же день помогает не потерять $20000-$30000 сделок.

Многопрофильная компания. Компания занимается полиграфической деятельностью и имеет сразу несколько направлений, чтобы предоставить клиентам комплексные услуги. В результате компания хоть и территориально находится в одном офисе, но использует сразу несколько специализированных конфигураций 1С: Предприятия от разных поставщиков. Хотелось бы также иметь консолидированную отчетность по компании с запозданием не более 10 мин, чтобы иметь возможность оперативно реагировать на возникающие проблемы.

В обоих случаях менеджеры предпочитают Excel как средство анализа, причем во второй компании директор использует notebook фирмы Apple с Microsoft Office и 1С: Предприятие само по себе у него не запускается.

Альтернативные варианты решения: репликация, Real-time OLAP

Репликация. Можно сделать ставку на быструю репликацию во время работы филиалов. Опыт показывает, что можно добиться синхронизации данных с запозданием 10 мин для 5 филиалов с 50 рабочими местами и суммарным объемом продаж $1 млн. в месяц. Данное решение возможно, но весьма трудоемко и дорого.

Real-time OLAP. Можно использовать Real-time OLAP для построения OLAP-отчетов в Excel в реальном времени. Однако, Real-time OLAP запускается только поверх физических таблиц создавая indexed view. К самим таблицам предъявляется целый набор требований (отсутствие null-значений, ANSI-установки и др.). В нашем случае доступ к данным в таблицах 1С желательно осуществлять через view, кроме того данные носят распределенный характер.

В чем преимущества кластера MS SQL 2000 и дифференцированного OLAP как DSS-решения?

Кластер MS SQL 2000

Снижение трудоемкости. Кластер MS SQL 2000 позволяет работать с данными распределенными по серверам филиалов, так как будто они образуют одну базу данных. Таким образом, разработчик пишет одну версию кода сразу для всей распределенной системы, не закладывая условий на специфическое взаимодействие с конкретными серверами. Очевидно, что это значительно снижает трудоемкость разработки и повышает надежность системы. Например, добавление нового сервера филиала не приводит к переписыванию кода в нескольких местах.

Повышение быстродействия. MS SQL 2000 в зависимости от ваших запросов автоматически вычисляет на каком сервере лежат нужные данные и обращается именно к нему. Это позволяет достичь очень значительного повышения быстродействия системы. Именно кластерные решения MS SQL 2000 обошли по производительности решения на базе Oracle по тестам TPC.

Дифференцированный OLAP

Снижение трудоемкости. По данным из кластера MS SQL 2000 можно строить отчеты различными средствами, например, используя ADO и Crystal Report. Тем не менее, на мой взгляд самым легкими в запуске и эксплуатации системами отчетности являются OLAP-решения, на базе Microsoft Analytical Services и Microsoft Excel 2000 (не путать с desktop-решениями имитирующими OLAP на базе Microsoft Web Office Components и др. клиентских решений).

Повышение производительности. OLAP-системы чрезвычайно быстродействующие (предельное время отклика 5 сек регламентировано "Кодовским стандартом"). Однако OLAP-системы требуют продолжительной "загрузки" данных из OLTP-системы, которое обычно делается ночью. Исключение составляет Real-time OLAP и Incremental OLAP. Система Real-time OLAP использует индексированные view, что позволяет хранить и обновлять необходимые итоги в реальном времени. Как мы уже отмечали, Real-time OLAP имеет ряд ограничений и, как правило, его можно "надеть" на систему только при разработке ее заранее подумать о его использовании. Дифференцированный OLAP позволяет не производить полную выгрузку данных, а обновить OLAP-куб только новыми данными. Поэтому дифференцированный OLAP можно обновлять во время работы пользователей с небольшой задержкой в актуальности (около 10 мин. и менее).

Шаг 1. Создание view для доступа к данным к продажам

Рассмотрим пошаговый пример создания DSS-решения на базе кластера MS SQL 2000 и дифференцированного OLAP. На первом шаге нам нужно создать view для доступа к данным о продажах. Примерно следующей структуры:

create view Sales

                .. as id, -- ключ операции, 

.. as Date, -- дата операции,

                .. as An1, .. as An2, .. as An3, .. as An4, .. as An5 -- аналитика операциии (коды справочников),  

                .. as S, -- сумма в учетной валюте

                .. as V, -- сумма в валюте операции

                .. as I -- количество

from …. таблицы_1С

Отметим, наше решение пригодно и для консолидации данных из систем разных поставщиков. Например, если компания использует в части филиалов складскую систему своего изготовления, а другая часть делает ставку на 1С: Торговлю мы можем в обоих случаях с помощью view извлечь информацию в единообразном виде.

Рекомендация. Рекомендуем возводить OLAP-системы поверх учета построенного на плане счетов, а не на оперативных регистрах. Сравнение данных подходов выходит за рамки данной статьи, но обратите внимание на данный момент. Не вдаваясь в подробности приведем примеры view построенные по коммерческому плану счетов в 1С.

CREATE view Passes as
select convert(datetime, convert(varchar(8),DATE_TIME_DOCID)) as Date, A1.SCHKOD as Дебет, A2.SCHKOD as Кредит, 
SP6215 as Описание, SUM_ as Сумма, CURSUM as [ВалСумма], AMOUNT as [Количество], 
CURRID, DTSC0, DTSC1, DTSC2, KTSC0, KTSC1, KTSC2 
from _1SENTRY P, _1SACCS A1, _1SACCS A2
where 
P.ACCDTID=A1.ID and 
P.ACCKTID=A2.ID

CREATE view Sales as
select Date, Описание, Сумма, ВалСумма, Количество
CURRID, DTSC0, DTSC1, DTSC2, KTSC0, KTSC1, KTSC2
from Passes P
where Кредит='У62.' and DTSC0<>' 3 '

Шаг 2. Создание дифференцированного Data Warehouse по данным продаж в кластере.

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

1)       Data Warehouse должен быть не простым, а дифференцированным, т.е. показывать только новые операции после последней выгрузки в OLAP.

2)       Data Warehouse должен быть в кластере.

На данном шаге мы не разрешим обе проблемы полностью. Пока ограничимся правильным созданием таблицы для Data Warehouse. Обратите внимание, на  поле BaseId. Именно благодаря нему мы сможем создать кластер. По check-условию оптимизатор MS SQL 2000 сможет определить, в какой из таблиц в кластере находятся данные физически. Условие вхождение BaseId в primary key, также обязательно, т.к. позволяет MS SQL 2000 выбирать данные сразу по кластерному индексу, в противном случае построить кластер не удастся.

CREATE TABLE SalesX (

                BaseId varchar (255) CHECK (BaseId = 'Base1'), -- Идентификатор базы в кластере (ЦФУ)

                id varchar (255), -- ключ операции

                Date datetime, -- дата операции

                An1 varchar (255), -- Аналитика, уровень 1

                An2 varchar (255), -- Аналитика, уровень 2

                An3 varchar (255), -- Аналитика, уровень 3

                An4 varchar (255), -- Аналитика, уровень 4

                An5 varchar (255), -- Аналитика, уровень 5

                S  money, -- Сумма в учетной валюте

                V money, -- Сумма в валюте операции

                I float, -- количество

              primary key (id, BaseId) -- составной ключ для кластера

)

Что выбрать в качестве поля BaseId? Это должно быть поле имеющее проблемный смысл, т.е. не являющееся суррогатным ключом. В нашем случае наиболее логично использовать BaseId как идентификатор Центра Финансового Учета (ЦФУ).

Шаг 3. Загрузка дифференцированного Data Warehouse.

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

CREATE TABLE RegSales (

                id  varchar (255) primary key,

)

Теперь мы можем написать процедуру выгрузки в дифференцированный Data Warehouse.

create procedure GetSalesX

as

insert SalesX(BaseId, id, Date, An1, An2, An3, An4, An5, S, V, I)

select 'Base1',id, Date, An1, An2, An3, An4, An5, S, V, I

from Sales S where not exists(select * from RegSales R where R.id=S.id)

insert RegSales(id) select id from SalesX

GO

Для того чтобы выгрузка автоматически происходила раз в 10 минут, требуется создать Package с помощью Data Transformation Services (DTS) и поставить его на запуск по расписанию SQL Server Agent. Все это не сложно, т.к. Enterprise Manager предоставляет удобный набор визуальных мастеров для данных операций.

Шаг 4. Создаем кластер MS SQL 2000 и убеждаемся, что он работает производительно

Проверяем, что видим через Intranet все удаленные сервера. Добавляем их в Linked Servers в Enterprise Manager. Выбираем или создаем базу в центральном офисе, через которую мы будем видеть весь кластер. В данной базе создаем distributed view, которая как единую таблицу будет показывать Data Warehouse всех удаленных филиалов.

CREATE VIEW SalesX AS

   SELECT * from Server1.Cluster1.dbo.SalesX

UNION ALL

   SELECT * from Server2.Cluster2.dbo.SalesX

Проверим, что кластер получился и работает производительно. Сделаем в SQL Query Analyzer запрос select top 10 * from SalesX. Посмотрим Query Plan (Ctrl+L). Видно, что запрос привел к обращению ко всем удаленным серверам кластера.

Теперь посмотрим как сработает такой запрос: select top 3 * from SalesX where BaseId='Base1'

Как мы видим Query Plan выдает, что обращение было сделано только к серверу Server1. Если обращение все равно было ко всем серверам, это значит, что где-то была сделана ошибка.

Замечание. SalesX является updateable distributed view, т.е. к ней можно применять операции insert, delete, update. Они будут также оптимизироваться. В нашем случае такая возможность не нужна. Однако в OLAP-задачах построенных на write-enable OLAP-кубах (кубы с возможностью записью данных в базу обратно) такая возможность может быть полезна, если мы будем далее создавать систему планирования/бюджетирования продаж. В этом случае OLAP-куб служит не только средством просмотра фактических результатов, но средством ввода плановых данных.

Шаг 5. Создаем OLAP-куб поверх кластера MS SQL 2000.

Запускаем Analysis Manager, создаем новую аналитическую базу. В данной базе создаем OLAP-куб, в который как fact table выбираем созданную нами distributed view SalesX.

Замечания.

1)       В данном описании мы не рассматриваем отдельно работу со справочниками (Контрагенты, Товары, Валюты и др.). Отметим только общие и важные моменты. Для данных справочников также следует создать distributed view, во многих случаях удается его построить сразу на таблицах физического хранения данных, добавив туда поле BaseId. Если это нельзя сделать, тогда создаем таблицы аналогичные SalesX и делаем загрузку данных по расписанию. Если вы будете монтировать кластер поверх таблиц 1С: Предприятия обратите внимание на необходимость создания instead of триггеров для заполнения полей BaseId, т.к. 1С конечно в своих insert не будет подавать значение для данного поля.

2)       Важная рекомендация: оформляйте все измерения куба в виде Shared Dimension, это значительно убыстрит систему.

3)       Обратите внимание на создание Time Dimension. Решение "в лоб" добавить SalesX.Date как измерение времени может привести к неоправданному сканированию таблицы SalesX. Возможно, лучше создать таблицу типа Calendar(Date datetime primary key) и заполнить ее один всеми значениями дат и именно данную таблицу использовать как Time Dimension.

4)       Не забываем настроить права доступа к OLAP-кубу как ко всему, так и к отдельным размерностям (см. Cube Roles). Отметим, что перед этим вам нужно убедится, что все пользователи имеют NT-логины и возможность использования Trusted Security. Настройка прав доступа для консолидированных кубов по размерностям обязательна, т.к. такие кубы обычно содержат массу приватной информации.

5)       Используйте MOLAP-хранение данных. Они значительно быстрее работает, в случае кластера это может означать в 100 раз быстрее.

Шаг 6. Выгружаем данные в дифференцированный OLAP-куб.

В службе DTS создаем Package, который содержит 2 последовательных действия. Сначала запускаем обновление куба как incremental update. После успешного завершения данной задачи (on-success) удаляем данные из дифференцированного Data Warehouse кластера, чтобы не обработать данные дважды. Для этого запускаем Execute SQL Task с командой delete SalesX.

Шаг 7. Просматриваем OLAP-данные через MS Excel 2000.

Выполняем следующую последовательность действий:

1)       Запускаем в Excel мастер сводных таблиц.

2)       Указываем, что данные во внешнем источнике.

3)       Переходим на закладку OLAP и выбираем "новый".

4)       Указываем, что используем OLE DB Provider for OLAP 8.0.

5)       Указываем, что берем данные с OLAP-сервера и набираем его сетевое имя.

6)       Добавляем OLAP-куб как сводную таблицу на лист MS Excel 2000, настраиваем его размерности по умолчанию.

7)       Сохраняем файл Excel и рассылаем пользователям.

Рекомендация. Используя запись макросов MS Excel, рекомендуем сохранить несколько типовых настроек OLAP-куба.

Заключительные рекомендации и выводы

Что можно доделать…

Обработка редактирования. Для упрощения задачи мы не рассматривали вопрос как о том, как обрабатывать редактирование данных пользователями (мы добавляли в Data Warehouse новые данные, но не редакцию). Статистика показывает, что 80% накладных вводятся с "первого раза", поэтому для оперативного анализа возможно редакция и не существенна.  Однако если это критично сделать обработку редактирования не сложно. Нужно на таблицу накладных повесить триггер, отслеживающий update и delete, зарегистрировав событие в RegSales. После этого нужно сделать в SalesX сторную запись для отката старого значения, и добавить запись с новым значением по существующему алгоритму.

Полное процессирование куба. Применяете вы упрощенный механизм или нет, вам все равно нужно иметь процедуру полного обновления данных в кубе, например на случай вашей ошибке в дифференцированной выгрузке. Самое простое решение сделать это ночным процессом по такой схеме. Сотрите все записи из RegSales, после этого SalesX загрузится всеми данными. Затем запустите на OLAP-кубе full process.

Data Mining как удобная навигация по "факторам влияния" на продажи. Не вдаваясь подробно в тематику Data Mining (этому посвящена отдельная статья), можно отметить, что чрезвычайно полезно создать модель Microsoft Decision Tree по факторам влияния на продажи и добавить полученное дерево в куб. Это очень удобно даже с точки зрения навигации при большом количестве аналитических измерений. DM-измерение в кубе будет проводником по важным комбинациям аналитик.

Выводы

Простота реализации на базе системной интеграции. Не смотря на вовлечение в решение большого количества технологий, само построение системы было сравнительно простым, суммарный объем кода на Transact SQL не превышает 100 строк.  Это достигнуто благодаря тому, что каждая технология является уже готовым решением локальной проблемы. Кластер решает проблему интеграции отдельных баз и производительности комплекса из них. OLAP решает проблему быстрой визуализации данных в виде отчета и т.д. Все это отражение того, что мы живем в эпоху системной интеграции, когда на первый план выходит не программирование, а удачная интеграция стандартных технологических решений.

Богатые возможности Microsoft Analysis Services в сравнении с desktop-решениями. После того как Microsoft сделал доступным серверные средства OLAP в Microsoft SQL Server 2000 Standard Edition (поставляется вместе с 1С: Предприятием для SQL), в большинстве случаев будет неразумна покупка за деньги desktop-решения имитирующего OLAP. Работа поверх кластера, дифференцированное обновление, иерархические измерения, безопасность, Data Mining-измерения в OLAP и многое другое доступно уже сейчас в составе MS SQL 2000. Как OLAP-клиент вполне подходит MS Excel 2000. При решении купить OLAP-систему, обратите внимание на то, как она загружает данные в куб, загрузка должна делаться сразу из таблиц через view, в противном случае выгрузка будет работать очень медленно и модифицировать ее будет очень сложно.

Высокое быстродействие и гибкость сервисов MS SQL 2000. Даже построив описанную систему для опытной эксплуатации, вы сможете убедиться в высокой производительности и гибкости кластеров MS SQL 2000 и его OLAP-сервисов.

Владимир Иванов