За последние годы появилось достаточно публикаций по OLAP: история,
описание, технология, готовые решения. Но для большинства специалистов,
занимающихся учётом, управлением и планированием, этот термин
по-прежнему остаётся чем-то если и не загадочным, то далёким
от их обычной работы.
Цель этой статьи — познакомить читателей с практическим опытом
реализации решения для подготовки отчётности на основе OLAP-технологий
в ООО “Мострансгаз” — крупнейшем отечественном предприятии, занимающемся
транспортировкой газа.
Дмитрий ДОЛОТОВ
Руководитель отдела систем бухгалтерского
и налогового учёта ООО “Мострансгаз”
Этот непонятный OLAP
OLAP (On-line Analytical Processing) обычно переводится как “аналитическая обработка данных”. И это определение вводит в заблуждение потенциальных пользователей технологии. В России бытует твёрдое убеждение, что эти системы используются исключительно высококвалифицированными специалистами и аналитиками для проведения сложного анализа. На Западе же их рассматривают как системы, упрощающие понимание данных и обмен ими между всеми сотрудниками компании. В правильности именно западного подхода к OLAP мы убедились на собственном опыте.
Для ИТ-практиков OLAP — это технология быстрого создания интерактивных отчётов. Как правило, OLAP-отчёт может быть разработан практически без программирования, методом drag & drop, т. е. переноса групп данных из одного места в другое при помощи мыши. Проектирования и программирования требует только этап извлечения и подготовки данных для отчётов из корпоративных систем предприятия. Серия отчётов для определённой предметной области может быть настроена буквально за несколько часов.
Для конечных пользователей OLAP — это табличный отчёт с промежуточными и окончательными итогами. Он выпускается практически мгновенно, и можно самостоятельно изменить его форму — расположение колонок и строк, порядок фильтров и сортировки и т. д. Это позволяет взглянуть на одни и те же данные под разными углами зрения. Например, получив перечень по дебиторской задолженности, углубиться в архивные данные и посмотреть, были ли у должника проблемы с платежами в прошлом квартале, полгода назад, в ушедшем году. Разносторонний анализ позволяет быстро оценить ситуацию и получить максимум информации о проблеме, чтобы на этой основе наметить пути её решения.
Каким образом работает OLAP-система? OLAP-клиент получает данные из исходной информационной системы и организует их в иерархические структуры в памяти компьютера пользователя. Это позволяет выполнять вычисления промежуточных и окончательных итогов (фактов) практически мгновенно. Пользователь получает простой и интуитивно понятный интерфейс отчёта. Это таблица, в которой с помощью мыши можно менять местами строки и колонки (измерения), перетаскивать их в область фильтров, тем самым сворачивая данные, устанавливая самые сложные комбинации фильтров. Функциональные возможности системы позволяют за один сеанс работы выпустить десятки разновидностей одного и того же отчёта.
С появлением и использованием OLAP-инструмента традиционная задача отдела автоматизации — разработка новых отчётов, в которых одни данные показаны в разрезе других, — превращается в другую задачу, суть которой заключается в том, чтобы предоставить пользователю исходные данные. После этого экономист или бухгалтер не тратит время на написание служебных записок с требованием создать новую разновидность отчёта и не ждёт, пока программисты спроектируют, разработают и протестируют его. Пользователь сам конструирует нужный вид отчёта, выбирая из настроенной для него заготовки нужные поля, меняя порядок их следования, устанавливая фильтры и др. Созданный отчёт может быть проиллюстрирован графиками и диаграммами, распечатан на бумагу или выгружен в Excel.
Итак, OLAP-инструмент даёт два основных преимущества пользователю:
· высокую скорость и простоту настройки нового отчёта;
· высокую скорость выпуска отчётов.
Подготовка отчётности в ERP-системах
Обратимся к определению ERP-системы: ERP-система (Enterprise Resource Planning system) — комплексная интегрированная система, создающая единую среду для автоматизации планирования, учёта, контроля и анализа всех основных бизнес-операций предприятия.
Традиционно создатели ERP-систем наибольшее внимание уделяют функциям, направленным на реализацию бизнес-процессов предприятий, документооборота, учёта, планирования и других сфер. Поэтому в первую очередь оптимизируется ввод новых учётных данных в систему. При этом на второй план уходит возможность использования накопленных данных, развитие встроенной системы отчётности и, что наиболее важно, преобразование огромных массивов данных в информацию для принятия управленческих решений.
Все без исключения ERP-системы имеют подсистему формирования отчётов. Но созданные с её помощью отчёты не позволяют менеджменту предприятия оценить финансовые показатели деятельности, выявить наиболее прибыльные продукты и услуги, проанализировать затраты, дебиторскую и кредиторскую задолженность и т. д. Обычно в составе подсистемы отчётности поставляется базовый минимум отчётов по предметным разделам, например: наличие товаров на складах; ведомость износа основных фондов; кассовая книга; ведомость реализации услуг. Номенклатура, уровень достаточности и глубина штатных отчётов определяются разработчиками без учёта потребностей конкретного конечного пользователя продукта. В результате штатные отчёты ориентированы на пользователей “нижнего” уровня. Эти отчёты выпускаются из базы данных, на которой работает учётная система. Скорость формирования таких отчётов находится в обратной зависимости от их информационного состава, количества и сложности фильтров и группировок.
Создать дополнительные отчёты, в том числе управленческие, можно с помощью инструментария для разработки отчётов, который поставляется в составе ERP-системы. В одних системах это “продвинутый” конструктор с возможностью визуального проектирования, в других — встроенный язык программирования, но в любом случае настройка нового отчёта выполняется силами программистов и занимает от нескольких часов до нескольких дней в зависимости от сложности работы. Даже незначительное изменение структуры отчёта требует серьёзных затрат временн€ых и человеческих ресурсов.
Перечислим факторы, которые препятствуют свободному получению информации из учётной системы:
· ограниченный набор базовой отчётности;
· необходимость привлечения программистов для настройки дополнительных отчётов;
· низкая скорость формирования сложных отчётов из общей с системой ввода данных СУБД;
· высокие требования к производительности локальных рабочих станций пользователей при формировании на них сложных отчётов.
Выходом из этой ситуации может быть платформенное разделение учёта и отчётности, при котором учётная система оптимизирована для отражения бизнес-процессов, а отчётная является надстройкой над данными учётной системы и предназначена для быстрого создания и выпуска разнообразных отчётов.
Интеграция OLAP-инструментов с ERP-системой
Рассмотрим возможный вариант реализации разделения учёта и отчётности на примере интеграции ERP-системы и OLAP-инструментов. В ООО “Мострансгаз” данная модель была построена на основе двух систем:
· программного комплекса “Галактика” версии 5.84 с использованием модулей “Контур оперативного учёта” и “Контур бухгалтерского учёта” (корпорация “Галактика”);
· OLAP-инструментов аналитической платформы “Контур”: “Контур Стандарт” версии 4.0, “Контур Дизайнер кубов” версии 1.0 и “Контур Генератор кубов” версии 1.0 (компания Intersoft Lab).
Предложенная модель могла быть реализована и с использованием других программных продуктов соответствующего функционального содержания. Выбор указанных систем был продиктован наличием закупленных версий, удачным сочетанием их функциональных возможностей и достаточным опытом их эксплуатации.
Система “Галактика” работает на платформе СУБД Btrieve/Pervasive, что при невысоких расходах на сопровождение обеспечивает вполне приемлемую скорость работы пользователей, выполняющих операции ввода данных и выпуска текущих отчётов. Однако отчёты за длительный период, а также отчёты, построенные на нестандартных запросах, выполняются недостаточно быстро. Это побудило ИТ-менеджеров “Мострансгаза” применить современные OLAP-системы аналитической платформы “Контур” для выпуска отчётов.
Аналитическая платформа “Контур”
“Контур Стандарт” — это инструмент для генерации произвольных табличных отчётов и анализа данных различных информационных систем. В этой системе можно настроить прямой доступ к локальным таблицам и реляционным базам данных, сконструировать SQL-запросы и выпускать на них OLAP-отчёты в режиме реального времени. Созданный отчёт сохраняется для работы в режиме off-line и передачи удалённым пользователям в виде файлов микрокубов.
“Микрокуб” — это мобильный контейнер OLAP-отчётов и данных для представления в отчётах. В нём хранятся данные, выгруженные из автоматизированных корпоративных систем, алгоритмы расчёта вычисляемых показателей и формы OLAP-отчётов. Данные в микрокубе подготовлены для многомерного анализа и оперативного получения показателей в различных разрезах. При помещении в микрокуб объём исходной информации сжимается в десятки раз, что позволяет передавать их по интернет-протоколам и пересылать по электронной почте.
“Контур Дизайнер Кубов” предназначен для проектирования микрокубов и сценариев их генерации.
“Контур Генератор кубов” используется для массовой генерации микрокубов по готовым сценариям.
Общая схема решения выглядит так, как показано на рис. 1.
Пользователи ПК “Галактика” работают в штатном режиме, т. е. вводят данные и выпускают текущие отчёты для проверки корректности выполненных транзакций.
Параллельно планировщик задач с заданным периодом в 30 минут запускает перекачку данных из БД “Галактики” в промежуточный склад данных. Перегрузка происходит в фоновом режиме без отключения пользователей. Промежуточный склад данных является копией базы данных ERP-системы. В данном случае склад реализован на платформе Firebird, но нет никаких ограничений и по другим СУБД. Основная задача создания промежуточного слоя данных — разделение системы учёта и системы отчётности.
Данные промежуточного склада используются для создания микрокубов — витрин данных по разным направлениям отчётности (например, “исполнение договоров по капитальному ремонту” или “состав затрат с местами их возникновения”). Создание микрокубов и настройка OLAP-отчётов делегированы аналитикам службы информационных технологий.
Аналитик настройки отчётности в визуальной среде “Контур Дизайнер кубов” проектирует структуры микрокубов, информационный состав и внешний вид OLAP-отчётов. Все настройки сохраняются в виде файлов сценариев.
По готовым сценариям модуль “Контур Генератор кубов” каждые 30 минут генерирует микрокубы из промежуточного склада данных. Готовые микрокубы содержат в себе метаданные (настройки внешнего вида отчётов, доступных пользователю измерений и фильтров и др.) и просто данные (выборки из промежуточного склада данных).
При помощи OLAP-клиента “Контур Стандарт” микрокубы доступны аналитикам, экономистам, бухгалтерам и другим потребителям отчётов.
Практические примеры использования OLAP для подготовки отчётности
Какие задачи и как помогают решить OLAP-технологии пользователям ERP-системы? Инструменты OLAP извлекают данные из БД ERP-системы и представляют их в интерактивных отчётах для специалистов различных профилей — бухгалтеров, экономистов, руководителей и т. д. Контроль движения материальных ценностей между складами, сравнительный анализ закупочных цен поставщиков, оценка сбыта по регионам, анализ исполнения бюджетов — таков далеко не полный перечень направлений использования OLAP-систем на предприятии. Фактически интеграция ERP и OLAP выводит автоматизацию поддержки управления предприятием на новый качественный уровень.
Рассмотрим несколько примеров использования OLAP в “Мострансгазе”.
Представление штатной отчётности
Любую базовую отчётность ERP-системы можно представить в виде OLAP-таблицы. Это значит, что пользователь сможет сам управлять формой и содержанием отчёта с помощью стандартных OLAP-операций — группировки и детализации данных, фильтрации по измерениям и периодам и т. д. После каждой операции OLAP-система выполняет перерасчёт промежуточных и окончательных итогов и тут же отображает новый отчёт.
Это даёт сотрудникам разных подразделений свободный доступ к огромным объёмам информации для анализа. Например, все стандартные “шахматки” по счетам за год можно поместить в одну единственную OLAP-таблицу. В колонках и строках таблицы будут представлены корреспондирующие счета, в ячейках на пересечении колонок и строк — суммы проводок. С помощью фильтров бухгалтер выбирает счета для представления в отчёте, углубляется в интересующий синтетический счёт, например 10 “Материалы” (рис. 2), до уровня субсчетов и сразу же получает “шахматку” по аналитике этого счёта.
Анализ дебиторской и кредиторской задолженности
Классическая задача, которую решают OLAP-системы, — генерация основных периодов из даты. Это значит, что на основе фактических значений за каждую дату система автоматически вычисляет итоговые значения показателей за заданные периоды — неделю, декаду, месяц, квартал, год.
Это помогает в решении множества повседневных задач бухгалтерии предприятия. Например — значительно сокращает время на поиск задолженности, по которой приближается срок исковой давности, при создании резерва по сомнительной задолженности и в других случаях анализа дебиторской и кредиторской задолженности. Для этого в один OLAP-отчёт выводятся остатки по счёту 76 “Расчёты с дебиторами и кредиторами” в разрезе периодов возникновения задолженности, например по годам.
“Мострансгаз” имеет договорные отношения с огромным количеством контрагентов. В OLAP-отчёте можно оперативно просмотреть задолженность каждого из них и оценить суммы задолженности по разным видам деятельности в разрезе контрагентов. Для этого в отчёт добавляются два измерения: “Контрагент” (объекты аналитического учёта по счёту 76) и “За что” (предметы договоров с контрагентами, по которым образовалась задолженность). Итоги по новым измерениям рассчитываются автоматически (рис. 3).
Поиск встречной задолженности
Для поиска встречной задолженности в OLAP-отчёте дебетовые и кредитовые сальдо по счетам 60 “Расчёты с поставщиками”, 62 “Расчёты с покупателями и заказчиками” и 76 “Расчёты с дебиторами и кредиторами” представляются в разрезе контрагентов (измерение “Контрагент”), договоров (измерение “За что”) и документов оплаты по договору (измерение “Документ”).
Сравнивая промежуточные итоги по дебету и кредиту счетов, бухгалтер видит взаимную задолженность с расшифровкой “с кем” (“Контрагент”), “за что” и “по каким документам” (рис. 4).
Анализ задолженности и НДС
В OLAP-таблице с помощью фильтров по фактам и измерениям легко подобрать удобную для решения конкретной задачи форму отчёта и выделить данные для сравнения. Можно изменить масштаб (1 5 N) и формат (абсолютное значение или проценты) представления данных. Две последние функции не являются обязательными для всех OLAP-систем, но они исключительно удобны для решения бухгалтерских задач.
Например, в отчёте “Сальдо 2003 года по кварталам и датам” (рис. 5) для сопоставления корректности сальдо по кредиторской задолженности на счёте 60 “Расчёты с поставщиками” и налога на добавленную стоимость (НДС) на счёте 19 “НДС по приобретённым ценностям” устанавливаются два фильтра. Первый фильтр — по измерению “Счёт” для отображения в отчёте данных только по двум счетам — 19 и 60. Второй фильтр — по фактам, он включает отображение “свёрнутого” сальдо по счетам. В результате в соседних колонках в отчёте отражаются дебетовое и кредитовое сальдо по выбранным счетам в разрезе счетов-фактур (измерение “С/Ф”), договоров и контрагентов.
Чтобы проверить корректность выделения НДС (18/118, до 2004 года — 20/120), бухгалтер одной кнопкой переводит сальдо в проценты по строкам таблицы фактов. В результате в соседних колонках по каждому счёту-фактуре выводится сумма оплаты и выделенный из неё НДС. Если отношение 20/120 или 18/118 нарушено, то ситуация по бухгалтерскому документу требует дополнительного разбора и анализа (рис. 6).
Анализ затрат предприятия
Для анализа затрат предприятия в OLAP-отчёте представляются проводки по затратным счетам (20 “Основное производство” и 23 “Вспомогательное производство”) в разрезе объектов аналитического учёта (измерение “Статья затрат”) и их группировок (измерение “Вид деятельности”). В результате с помощью фильтров можно получить в одном отчёте группировки затратных статей из различных синтетических счетов (рис. 7).
Помимо фильтров у OLAP есть такая полезная функция, как сортировка измерений по значениям фактов. Она удобна для выявления приоритетных или, наоборот, наименее выгодных позиций — филиалов, контрагентов, видов деятельности, продуктов и т. п. Например, чтобы найти наиболее затратные статьи заданного периода, можно отсортировать измерение “Статья затрат” по возрастанию.
Управляя составом измерений отчёта, можно оценивать затраты предприятия с разных позиций. Например, для анализа затрат в разрезе структурных подразделений предприятия в отчёт достаточно добавить специальное измерение, отслеживающее ситуацию по филиалам (рис. 8).
Анализ выполнения работ подрядчиками
OLAP-инструменты “говорят” со своими потребителями на одном языке. Это достигается за счёт создания семантического слоя, когда полям из БД ERP-системы присваиваются названия в терминах предметной области. В результате переименования колонки, строки и цифры в OLAP-таблице приобретают прикладной смысл, понятный конечным пользователям отчётов.
Наглядный пример — отчёт по картотеке незавершённого строительства за 2003 год (рис. 9). Он показывает состояние расчётов с подрядчиками (измерение “Контрагент”), из которых можно оценивать ход выполнения работ по объектам строительства (измерение “Стройка”). Уплаченные суммы представлены в разрезе бухгалтерских документов — счетов-фактур (измерение “Счёт-фактура”) и соответствующих платёжных документов и актов сдачи-приёмки (измерение “Номер документа”). В таблице фактов представлены остатки по счетам 08 “Вложения во внеоборотные активы” (факт “Выполнен. работ”.), 19 “НДС по приобретённым ценностям” (факт “НДС по работам”) и 60 “Расчёты с поставщиками” (факт “Оплачено”). К примеру, из таблицы видно, что за работы по строительству 14-этажного жилого дома на Можайском шоссе подрядчику “ЖилСтройКомплект” был оплачен аванс в размере 41 800 руб. по платёжному документу А08016. В акте сдачи-приёмки работ № 34 было зафиксировано, что работы на сумму 41 800 руб., не облагаемые НДС, выполнены. Таким образом, счёт-фактура № 256 был закрыт.
Автоматический подсчёт промежуточных итогов по разным измерениям таблицы позволяет тут же получить дополнительную информацию — суммарные затраты по объекту строительства или конкретному поставщику.
Анализ прибыли и убытков предприятия по видам деятельности
Для “Отчёта о прибыли и убытках” бухгалтер готовит различные группировки по счёту 99 “Прибыли и убытки”. В OLAP-системе их можно получить практически сразу.
В одном OLAP-отчёте объединённые в группы проводки по счёту 99 показывают источники возникновения прибыли (в разрезе измерения “Филиал”) и прибыльные бизнес-направления (в разрезе измерения “Вид деятельности”). По каждой группе проводок автоматически рассчитываются итоговые значения для сравнения составляющих прибыли и убытков.
Если дополнительно разложить данные по датам на периоды — по месяцам, кварталам (рис. 10) и др., — то можно с разным масштабом оценить динамику прибыли.
Реальный выигрыш
Накопленный опыт эксплуатации OLAP-системы как надстройки над ERP показал ряд положительных моментов:
· формирование глубоких аналитических отчётов у пользователей выполняется моментально;
· настройка отчётов для пользователей при помощи визуальных средств проектирования, которые входят в состав OLAP-системы, происходит значительно быстрее, чем при программировании отчётов. Аналитик, настраивающий отчёты, может совершенно не владеть навыками программирования;
· при работе с OLAP-отчётами пользователь не ограничен в возможностях анализа и синтеза данных. При необходимости он самостоятельно модифицирует представление отчёта без привлечения программиста;
· присваивая одним и тем же измерениям различные названия, можно настраивать отчёты для потребителей информации различных уровней. При этом в отчётах за разными названиями может скрываться одна и та же информация;
· за счёт высокой скорости получения разнообразных отчётов снижается совокупное время, затрачиваемое на анализ финансового состояния предприятия;
· сокращаются расходы на разработку и сопровождение системы отчётности предприятия, обеспечивается независимость от поставщика ERP-систем по номенклатуре отчётов.
Надеемся, что всё изложенное в статье будет полезно ИТ-службам и прикладным специалистам предприятий и поможет им преодолеть искусственный барьер перед использованием OLAP-инструментов для подготовки разнообразной отчётности на основе ERP-систем.
Начать дискуссию