Olap для маленькой компании. Категории информационных систем

OLAP - аббревиатура от английского On-Line Analytical Processing - это название не конкретного продукта, а целой технологии. По-русски удобнее всего называть OLAP оперативной аналитической обработкой. Хотя в некоторых изданиях аналитическую обработку называют и онлайновой, и интерактивной, однако прилагательное "оперативная" как нельзя более точно отражает смысл технологии OLAP.

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

Рассмотрим, как обычно происходит процесс разработки решений.

Исторически сложилось так, что решения по автоматизации оперативной деятельности наиболее развиты. Речь идет о системах транзакционной обработки данных (OLTP), иначе называемых оперативными системами. Эти системы обеспечивают регистрацию некоторых фактов, их непродолжительное хранение и сохранение в архивах. Основу таких систем обеспечивают системы управления реляционными базами данных (РСУБД). Традиционным подходом являются попытки использовать уже построенные оперативные системы для поддержки принятия решений. Обычно пытаются строить развитую систему запросов к оперативной системе и использовать полученные после интерпретации отчеты непосредственно для поддержки решений. Отчеты могут строиться на заказной базе, т.е. руководитель запрашивает отчет, и на регулярной, когда отчеты строятся по достижении некоторых событий или времени. Например, традиционный процесс поддержки принятия решений может выглядеть таким образом: руководитель идет к специалисту информационного отдела и делится с ним своим вопросом. Затем специалист информационного отдела строит запрос к оперативной системе, получает электронный отчет, интерпретирует его и доводит его до сведения руководящего персонала.

Конечно, такая схема обеспечивает в какой-то мере поддержку принятия решений, но она имеет крайне низкую эффективность и огромное число недостатков. Ничтожное количество данных используется для поддержки критически важных решений. Есть и другие проблемы. Подобный процесс очень медленен, так как длителен сам процесс написания запросов и интерпретации электронного отчета. Он занимает многие дни, в то время как руководителю, быть может, необходимо принять решение прямо сейчас, немедленно. Если учесть, что руководителя после получения отчета может заинтересовать другой вопрос (скажем, уточняющий или требующий рассмотрения данных в другом разрезе), то этот медленный цикл должен повториться. А так как процесс анализа данных оперативных систем будет происходить итерационно, то времени тратится ещё больше. Другая проблема - различие областей деятельности специалиста по информационным технологиям и руководителя, которые могут мыслить в разных категориях и, как следствие, - не понимать друг друга. Это значит, что потребуются дополнительные уточняющие итерации, а это снова время, которого всегда не хватает. Ещё одной важной проблемой является сложность отчетов для понимания. У руководителя нет времени выбирать интересующие цифры из отчёта, тем более что их может оказаться слишком много (вспомним огромные многостраничные отчеты, в которых реально используются несколько страниц, а остальные - на всякий случай). Отметим также, что работа по интерпретации ложится чаще всего на специалистов информационных отделов. То есть грамотный специалист отвлекается на рутинную и малоэффективную работу по рисованию диаграмм и т.п., что, естественно, не может благоприятно сказываться на его квалификации. Кроме того, не является секретом присутствие в цепочке интерпретации благожелателей, заинтересованных в преднамеренном искажении поступающей информации.

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

В действительности эти проблемы не являются следствием низкого качества оперативной системы или ее неудачной постройки. Корни проблем кроются в фундаментальном отличии той оперативной деятельности, которая автоматизируется оперативной системой, и деятельностью по разработке и принятию решений. Отличие это состоит в том, что данные оперативных систем являются просто записями о некоторых имевших место событиях, фактах, но никак не информацией в общем смысле этого слова. Информация - это то, что снижает неопределенность в какой-либо области. И было бы очень неплохо, если бы информация снижала неопределенность в области подготовки решений. По поводу непригодности для этой цели оперативных систем, построенных на РСУБД, в свое время высказался небезызвестный E.F. Codd, человек, стоявший в 70-е годы у истоков технологий систем управления реляционными БД: "Хотя системы управления реляционными БД доступны для пользователей, они никогда не считались средством, дающим мощные функции по синтезу, анализу и консолидации (функций, называемых многомерным анализом данных)". Речь идет именно о синтезе информации, о том, чтобы превращать данные оперативных систем в информацию и даже в качественные оценки. OLAP позволяет выполнять такое превращение.

В основе OLAP лежит идея многомерной модели данных. Человеческое мышление многомерно по определению. Когда человек задает вопросы, он налагает ограничения, тем самым формулируя вопросы во многих измерениях - поэтому процесс анализа в многомерной модели весьма приближен к реальности человеческого мышления. По измерениям в многомерной модели откладывают факторы, влияющие на деятельность предприятия (например: время, продукты, отделения компании, географию и т.п.). Таким образом получают гиперкуб (конечно, название не очень удачное, поскольку под кубом обычно понимают фигуру с равными ребрами, что в данном случае далеко не так), который затем наполняется показателями деятельности предприятия (цены, продажи, план, прибыли, убытки и т.п.). Наполнение это может вестись как реальными данными оперативных систем, так и прогнозируемыми на основе исторических данных. Измерения гиперкуба могут носить сложный характер, быть иерархическими, между ними могут быть установлены отношения. В процессе анализа пользователь может менять точку зрения на данные (так называемая операция смены логического взгляда), тем самым просматривая данные в различных разрезах и разрешая конкретные задачи. Над кубами могут выполняться различные операции, включая прогнозирование и условное планирование (анализ типа "что, если"). Причем операции выполняются над кубами, т.е. произведение, например, даст в результате произведение-гиперкуб, каждая ячейка которого является произведением ячеек соответствующих гиперкубов-множителей. Естественно, возможно выполнение операций над гиперкубами, имеющими различное число измерений.

Немного истории

Идея обработки данных на многомерных массивах не является новой. Фактически она восходит к 1962 году, когда Ken Iverson опубликовал свою книгу "Язык программирования" ("A Programming Language", APL). Первая практическая реализация APL состоялась в поздних шестидесятых компанией IBM. APL - это очень изящный, математически определённый язык с многомерными переменными и обрабатываемыми операциями. Он подразумевался как оригинальное, мощное по сравнению с другими практическими языками программирования средство по работе с многомерными преобразованиями.

Однако идея долгое время не получала массового применения, поскольку не пришло еще время графических интерфейсов, печатающих устройств высокого качества, а отображение греческих символов требовало специальных экранов, клавиатур и печатающих устройств. Позднее английские слова иногда использовали для замены греческих операторов, однако борцы за чистоту APL пресекли попытки популяризации их любимого языка. APL также поглощал машинные ресурсы. В те дни его использование требовало больших затрат. Программы очень медленно выполнялись и, кроме того, сам их запуск обходился очень дорого: требовалось много памяти, по тем временам просто шокирующие объемы (около 6 МБ).

Однако, досада от этих первоначальных ошибок не убила идею. Она использовалась во многих деловых приложениях 70-х, 80-х годов. Многие из этих приложений имели черты современных систем аналитической обработки. Так, IBM разработала операционную систему для APL, названную VSPC, и некоторые люди считали ее идеальной средой для персонального использования, пока электронные таблицы не стали повсеместно распространены.

Но APL был слишком сложен в использовании, тем более что каждый раз появлялись несоответствия между самим языком и оборудованием, на котором делались попытки его реализации.

В 80-х годах APL стал доступен на персональных машинах, но не нашел рыночного применения. Альтернативой было программирование многомерных приложений с использованием массивов в других языках. Это было очень тяжелой задачей даже для профессиональных программистов, что вынуждало ждать следующего поколения многомерных программных продуктов.

В 1972 году несколько прикладных многомерных программных продуктов, ранее использовавшихся в учебных целях, нашли коммерческое применение: например, Express. Он в полностью переписанном виде остаётся и сейчас, однако оригинальные концепции 70-х годов перестали быть актуальными. Сегодня, в 90-х, Express является одной из наиболее популярных OLAP-технологий, и Oracle (r) будет продвигать его и дополнять новыми возможностями.

Больше многомерных продуктов появилось в 80-х годах. В начале десятилетия - продукт с названием Stratagem, позднее называемый Acumate (сегодня владельцем является Kenan Technologies), который еще продвигался до начала 90-х, но сегодня, в отличие от Express, практически не используется.

Comshare System W был многомерным продуктом другого стиля. Представленный в 1981 году, он был первым, где предполагалась большая ориентированность на конечного пользователя и на разработку финансовых приложений. Он привнёс много новых концепций, которые, правда, не были хорошо адаптированы: такие, как полностью непроцедурные правила, полноэкранный просмотр и редактирование многомерных данных, автоматическое перевычисление и пакетная интеграция с реляционными данными. Однако Comshare System W был достаточно тяжел для аппаратного обеспечения того времени по сравнению с другими продуктами. Он меньше использовался в будущем, всё меньше продавался, и в продукте не делалось никаких улучшений. Хотя он и сегодня доступен на UNIX, он не является клиент-серверным, что не способствует повышению его предложения на рынке аналитических продуктов. В поздних 80-х Comshare выпустил продукт для DOS, а позднее для Windows. Эти продукты назывались Commander Prism и использовали те же концепции, что и System W.

Другой творческий продукт поздних 80-х назывался Metaphor. Он предназначался для профессиональных маркетологов. Он также предложил много новых концепций, которые только сегодня начинают широко использоваться: клиент-серверные вычисления, использование многомерной модели для реляционных данных, объектно ориентированная разработка приложений. Однако стандартное аппаратное обеспечение персональных машин тех дней не было способно работать с Metaphor и поставщики были вынуждены разрабатывать собственные стандарты на персональные машины и сети. Постепенно Metaphor стал работать удачнее и на серийных персональных машинах, однако продукт был выполнен исключительно для OS/2 и имел свой собственный графический интерфейс пользователя.

Затем Metaphor заключил маркетинговый альянс с IBM, которой впоследствии и был поглощён. В середине 1994 года IBM решила интегрировать технологию Metaphor (переименованную в DIS) со своими будущими технологиями и тем самым прекратить финансирование отдельного направления. Однако заказчики выразили своё неудовольствие и потребовали продолжить поддержку продукта. Поддержка была продолжена для оставшихся заказчиков, а IBM перевыпустила продукт под новым названием DIS, что, однако, не сделало его популярным. Но творческие, новаторские концепции Metaphor не были забыты и видны сегодня во многих продуктах.

В середине 80-х родился термин EIS (Executive Information System - информационная система руководителя). Первым продуктом, ясно продемонстрировавшим это направление, был Pilot"Аs Command Center. Это был продукт, который позволял выполнять совместные вычисления, то, что мы называем сегодня клиент-серверными вычислениями. Поскольку мощность персональных компьютеров 80-х годов была ограничена, продукт был очень "сервероцентричен", однако этот принцип и сегодня очень популярен. Pilot недолго продавал Command Center, но предложил много концепций, которые можно узнать в сегодняшних OLAP-продуктах, включая автоматическую поддержку временных промежутков, многомерные клиент-серверные вычисления и упрощённое управление процессом анализа (мышь, чувствительные экраны и т.п.). Некоторые из этих концепций были повторно применены позднее в Pilot Analysis Server.

В конце 80-х электронные таблицы были доминирующими на рынке инструментов, предоставляющих анализ конечным пользователям. Первая многомерная электронная таблица была представлена продуктом Compete. Он продвигался на рынок как очень дорогой продукт для специалистов, но поставщики не обеспечили возможность захвата рынка этим продуктом, и компания Computer Associates приобрела права на него вместе с другими продуктами, включая Supercalc и 20/20. Основным эффектом от приобретения Compete компанией Computer Associates было резкое снижение цены на него и снятие защиты от копирования, что, естественно, способствовало его распространению. Однако он не был удачным. Compete положен в основу Supercalc 5, но многомерный аспект его не продвигается. Старый Compete всё ещё используется в связи с тем, что в свое время в него были вложены немалые средства.

Компания Lotus была следующей, кто попытался войти на рынок многомерных электронных таблиц с продуктом Improv, который запускается на NeXT машине. Это гарантировало, как минимум, что продажи 1-2-3 не снизятся. Но когда тот со временем был выпущен под Windows, Excel уже имел большую долю рынка, что не позволило Lotus внести какие-либо изменения в распределение рынка. Lotus, подобно CA с Compete, переместила Improv в нижнюю часть рынка, однако и это не стало условием удачного продвижения на рынке, и новые разработки в этой области не получили продолжения. Оказалось, что пользователи персональных компьютеров предпочли электронные таблицы 1-2-3 и не интересуются новыми многомерными возможностями, если они не полностью совместимы с их старыми таблицами. Так же концепции маленьких, настольных электронных таблиц, предлагаемых как персональные приложения, в действительности не оказались удобными и не прижились в настоящем деловом мире. Microsoft (r) пошла по этому пути, добавив PivotTables (в русской редакции это называется "сводные таблицы") к Excel. Хотя немногие пользователи Excel получили выгоду от использования этой возможности, это, вероятно, единственный факт широкого использования в мире возможностей многомерного анализа просто потому, что в мире очень много пользователей Excel.

OLAP, ROLAP, MOLAP...

Общеизвестно, что когда Кодд опубликовал в 1985 году свои правила построения реляционных СУБД, они вызвали бурную реакцию и впоследствии сильно отразились вообще на индустрии СУБД. Однако мало кто знает, что в 1993 году Кодд опубликовал труд под названием "OLAP для пользователей-аналитиков: каким он должен быть". В нем он изложил основные концепции оперативной аналитической обработки и определил 12 правил, которым должны удовлетворять продукты, предоставляющие возможность выполнения оперативной аналитической обработки.

Вот эти правила (текст оригинала по возможности сохранен):

1. Концептуальное многомерное представление. Пользователь-аналитик видит мир предприятия многомерным по своей природе. Соответственно и OLAP-модель должна быть многомерной в своей основе. Многомерная концептуальная схема или пользовательское представление облегчают моделирование и анализ так же, впрочем, как и вычисления.
2. Прозрачность. Вне зависимости от того, является OLAP-продукт частью средств пользователя или нет, этот факт должен быть прозрачен для пользователя. Если OLAP предоставляется клиент-серверными вычислениями, то этот факт также, по возможности, должен быть незаметен для пользователя. OLAP должен предоставляться в контексте истинно открытой архитектуры, позволяя пользователю, где бы он ни находился, связываться при помощи аналитического инструмента с сервером. В дополнение к этому прозрачность должна достигаться и при взаимодействии аналитического инструмента с гомогенной и гетерогенной средами БД.
3. Доступность. Пользователь-аналитик OLAP должен иметь возможность выполнять анализ, базирующийся на общей концептуальной схеме, содержащей данные всего предприятия в реляционной БД, также как и данные из старых наследуемых БД, на общих методах доступа и на общей аналитической модели. Это значит, что OLAP должен предоставлять свою собственную логическую схему для доступа в гетерогенной среде БД и выполнять соответствующие преобразования для предоставления данных пользователю. Более того, необходимо заранее позаботиться о том, где и как, и какие типы физической организации данных действительно будут использоваться. OLAP-система должна выполнять доступ только к действительно требующимся данным, а не применять общий принцип "кухонной воронки", который влечет ненужный ввод.
4. Постоянная производительность при разработке отчетов. Если число измерений или объем базы данных увеличиваются, пользователь-аналитик не должен чувствовать какой-либо существенной деградации в производительности. Для конечного пользователя критичной является как постоянная производительность, так и поддержание легкости в использовании и ограничения сложности OLAP. Если пользователь-аналитик будет испытывать существенные различия в производительности в соответствии с числом измерений, тогда он будет стремиться компенсировать эти различия стратегией разработки, что вызовет представление данных другими путями, но не теми, которыми действительно нужно эти данные представить. Затраты времени на обход системы для компенсации ее неадекватности - это не то, для чего аналитические продукты предназначены.
5. Клиент-серверная архитектура. Большинство данных, которые сегодня требуется подвергать оперативной аналитической обработке, содержатся на мэйнфреймах с доступом через ПК. Это означает, что OLAP-продукты должны быть способны работать в среде клиент-сервер. С этой точки зрения представляется необходимым, чтобы серверный компонент аналитического инструмента был настолько "интеллектуальным", чтобы различные клиенты могли присоединяться к серверу с минимальными затруднениями и интеграционным программированием. "Интеллектуальный" сервер должен быть способен выполнять отображение и консолидацию между несоответствующими логическими и физическими схемами баз данных. Это обеспечит прозрачность и возможность построения общей концептуальной, логической и физической схемы.
6. Общая многомерность. Каждое измерение должно применяться безотносительно своей структуры и операционных способностей. Дополнительные операционные способности могут предоставляться выбранным измерениям, и, поскольку измерения симметричны, отдельно взятая функция может быть предоставлена любому измерению. Базовые структуры данных, формулы и форматы отчетов не должны смещаться в сторону какого-либо измерения.
7. Динамическое управление разреженными матрицами. Физическая схема OLAP-инструмента должна полностью адаптироваться к специфической аналитической модели для оптимального управления разреженными матрицами. Для любой взятой разреженной матрицы существует одна и только одна оптимальная физическая схема. Эта схема предоставляет максимальную эффективность по памяти и операбельность матрицы, если, конечно, весь набор данных помещается в памяти. Для практических операций с большими аналитическими моделями базовые физические данные OLAP-инструмента должны конфигурироваться к любому подмножеству измерений и в любом порядке. Физические методы доступа также должны динамически меняться и содержать различные типы механизмов, таких как: непосредственные вычисления, B-деревья и производные, хеширование, возможность комбинировать эти механизмы при необходимости. Разреженность (измеряется в процентном отношении пустых ячеек ко всем возможным) - это одна из характеристик распространения данных. Невозможность регулировать разреженность может сделать эффективность операций недостижимой. Если OLAP-инструмент не может контролировать и регулировать распространение значений анализируемых данных, модель, претендующая на практичность, базирующаяся на многих путях консолидации и измерениях, в действительности может оказаться ненужной и безнадежной.
8. Многопользовательская поддержка. Часто несколько пользователей-аналитиков испытывают потребность работать совместно с одной аналитической моделью или создавать различные модели из единых данных. Следовательно, OLAP-инструмент должен предоставлять возможности совместного доступа (запроса и дополнения), целостности и безопасности.
9. Неограниченные перекрестные операции. Различные уровни свертки и пути консолидации вследствие их иерархической природы представляют зависимые отношения в OLAP-модели или приложении. Следовательно, сам инструмент должен подразумевать соответствующие вычисления и не требовать от пользователя-аналитика вновь определять эти вычисления и операции. Вычисления, не следующие из этих наследуемых отношений, требуют определения различными формулами в соответствии с некоторым применяющимся языком. Такой язык может позволять вычисления и манипуляцию с данными любых размерностей и не ограничивать отношения между ячейками данных, не обращая внимания на количество общих атрибутов данных конкретных ячеек.
10. Интуитивная манипуляция данными. Переориентация путей консолидации, детализация, укрупнение и другие манипуляции, регламентируемые путями консолидации, должны применяться через отдельное воздействие на ячейки аналитической модели, а также не должны требовать использования системы меню или иных множественных действий с пользовательским интерфейсом. Взгляд пользователя-аналитика на измерения, определенный в аналитической модели, должен содержать всю необходимую информацию, чтобы выполнять вышеуказанные действия.
11. Гибкие возможности получения отчетов. Анализ и представление данных являются простыми, когда строки, столбцы и ячейки данных, которые будут визуально сравниваться между собой, либо находятся вблизи друг от друга, либо располагаются в соответствии с некоторой логической функцией, имеющей место на предприятии. Средства формирования отчетов должны представлять собой синтезируемые данные или информацию, следующую из модели данных в ее любой возможной ориентации. Это означает, что строки, столбцы или страницы должны показывать одновременно от 0 до N измерений, где N - число измерений всей аналитической модели. В дополнение к этому, каждое измерение содержимого, показанное в одной записи, колонке или странице, должно также быть способно показать любое подмножество элементов (значений), содержащихся в измерении, причем в любом порядке.
12. Неограниченная размерность и число уровней агрегации. Исследование о возможном числе необходимых измерений, требующихся в аналитической модели, показало, что одновременно может использоваться до 19 измерений. Отсюда вытекает настоятельная рекомендация, чтобы аналитический инструмент был способен одновременно предоставить как минимум 15 измерений, а предпочтительнее 20. Более того, каждое из общих измерений не должно быть ограничено по числу определяемых пользователем-аналитиком уровней агрегации и путей консолидации.

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

Остановимся несколько подробнее на том, как отличаются OLAP-продукты по своей физической реализации.

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

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

ROLAP. Реляционная (relational) OLAP. Как и подразумевается названием, многомерная структура в таких инструментах реализуется реляционными таблицами. А данные в процессе анализа, соответственно, выбираются из реляционной базы данных аналитическим инструментом.

Недостатки и преимущества каждого подхода, в общем-то, очевидны. Многомерная OLAP обеспечивает лучшую производительность, но структуры нельзя использовать для обработки больших объемов данных, поскольку большая размерность потребует больших аппаратных ресурсов, в то время как разреженность гиперкубов может быть очень высокой, и, следовательно, использование аппаратных мощностей не будет оправданным. Наоборот, реляционная OLAP обеспечивает обработку на больших массивах хранимых данных, так как возможно обеспечение более экономичного хранения, но, вместе с тем, значительно проигрывает многомерной OLAP в скорости работы. Подобные рассуждения привели к выделению нового класса аналитических инструментов - HOLAP. Это гибридная (hybrid) оперативная аналитическая обработка. Инструменты этого класса позволяют сочетать оба подхода - реляционный и многомерный. Доступ может вестись как к данным многомерных баз, так и к данным реляционных.

Есть еще один достаточно экзотический вид оперативной аналитической обработки - DOLAP. Это "настольный" (desktop) OLAP. Речь идет о такой аналитической обработке, где гиперкубы малы, размерность их небольшая, потребности скромны, и для такой аналитической обработки достаточно персональной машины на рабочем столе.

Заключение

Оперативная аналитическая обработка позволяет значительно упростить и ускорить процесс подготовки и принятия решений руководящим персоналом. Оперативная аналитическая обработка служит цели превращения данных в информацию. Она принципиально отличается от традиционного процесса поддержки принятия решений, основанного, чаще всего, на рассмотрении структурированных отчетов. По аналогии, разница между структурированными отчетами и OLAP такая, как между ездой по городу на трамвае и на личном автомобиле. Когда вы едете на трамвае, он двигается по рельсам, что не позволяет хорошо рассмотреть отдаленные здания и тем более приблизиться к ним. Наоборот, езда на личном автомобиле дает полную свободу передвижения (естественно, следует соблюдать ПДД). Можно подъехать к любому зданию и добраться до тех мест, где трамваи не ходят.

Структурированные отчеты - это те рельсы, которые сдерживают свободу в подготовке решений. OLAP - автомобиль для эффективного движения по информационным магистралям.

Кроме этой статьи Вы можете посмотреть по тематеке текущего раздела:
в разделе "Энциклопедия"
7 статей в разделе "Статьи".

Удивительное - рядом...

По ходу работы мне часто требовалось делать сложные отчеты, я все время пытался найти в них что-то общее, чтобы составлять их более просто и универсально, даже написал и опубликовал по этому поводу статью «Дерево Осипова». Однако мою статью раскритиковали и сказали, что все те проблемы, которые я поднял, давно уже решены в MOLAP.RU v.2.4 (www.molap.rgtu.ru) и порекомендовали посмотреть сводные таблицы в EXCEL.
Это оказалось настолько простым, что приложив к этому свои гениальные ручонки, у меня получилась очень простая схема для выгрузки данных из 1С7 или любой другой базы данных (в дальнейшем под 1С подразумевается любая база данных) и анализа в OLAP.
Я думаю, многие схемы выгрузки в OLAP слишком усложнены, я выбираю простоту.

Характеристики :

1. Для работы требуется только EXCEL 2000.
2. Пользователь сам может конструировать отчеты без программирования.
3. Выгрузка из 1С7 в простом формате текстового файла.
4. Для бухгалтерских проводок уже имеется универсальная обработка для выгрузки, работающая в любой конфигурации. Для выгрузки других данных имеются обработки-образцы.
5. Можно заранее сконструировать формы отчетов, а затем применять их к разным данным без их повторного конструирования.
6. Довольно хорошая производительность. На первом длительном этапе данные сначала импортируются в EXCEL из текстового файла и строится куб OLAP, а затем довольно быстро на основе этого куба может быть построен любой отчет. Например, данные о продажах товара по магазину за 3 месяца с ассортиментом 6000 товаров, загружаются в EXCEL 8 минут на Cel600-128M, рейтинг по товарам и группам (OLAP-отчет) пересчитывается за 1 минуту.
7. Данные выгружаются из 1С7 полностью за указанный период (все движения, по всем складам, фирмам, счетам). При импорте в EXCEL возможно использование фильтров, загружающих для анализа только нужные данные (например, из всех движений, только продажи).
8. В настоящее время разработаны способы анализа движений или остатков, но не движений и остатков вместе, хотя это в принципе возможно.

Что такое OLAP : (www.molap.rgtu.ru)

Предположим у вас есть торговая сеть. Пусть данные о торговых операциях выгружены в текстовый файл или таблицу вида:

Дата - дата операции
Месяц - месяц операции
Неделя - неделя операции
Вид - закуп, продажа, возврат, списание
Контрагент - внешняя организация, участвующая в операции
Автор - человек, выписавший накладную

В 1С, например, одна строка этой таблицы будет соответствовать одной строке накладной, некоторые поля (Контрагент, Дата) при этом берутся из шапки накладной.

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

Эта таблица является исходной для OLAP-анализа.

Отчет	Измерения	Данные	Фильтр
Сколько товара и на какую сумму продается за день?	Дата, Товар	Количество, Сумма	Вид="продажа"
Какие контрагенты поставили какой товар на какую сумму помесячно?	Месяц, Контрагент, Товар	Сумма	Вид="закуп"
На какую сумму выписали операторы накладных какого вида за весь период отчета?		Сумма

Пользователь сам определяется, какие из полей таблицы будут Измерениями, какие Данными и какие Фильтры применять. Система сама строит отчет в наглядной табличной форме. Измерения можно размещать в заголовках строк или столбцов таблицы отчета.
Как видно, из одной простой таблицы можно получить множество данных в виде различных отчетов.

Как использовать у себя :

Данные из дистрибутива распаковать именно в каталог c:\fixin (для торговой системы возможно в c:\reports) . Прочитайте readme.txt и выполните все инструкции в нем.

Сначала вы должны написать обработку, которая выгружает данные из 1С в текстовый файл (таблицу). Вам нужно определить состав полей, которые будут выгружаться.
Например, уже готовая универсальная обработка, которая работает в любой конфигурации и выгружает для OLAP-анализа проводки за период, выгружает для анализа следующие поля:

Дата|ДеньНедели|Неделя|Год|Квартал|Месяц|Документ|Фирма|Дебет|ДтНоменклатура
|ДтГруппаНоменклатура|ДтРазделНоменклатура|Кредит|Сумма|ВалСумма|Количество
|Валюта|ДтКонтрагенты|ДтГруппаКонтрагенты|КтКонтрагенты|КтГруппаКонтрагенты|
КтРазныеОбъекты

Где под префиксами Дт(Кт) идут субконто Дебета (Кредита), Группа - это группа данного субконто (если имеется), Раздел - группа группы, Класс - группа раздела.

Для торговой системы поля могут быть такие:

Направление|ВидДвижения|ЗаНал|Товар|Количество|Цена|Сумма|Дата|Фирма
|Склад|Валюта|Документ|ДеньНедели|Неделя|Год|Квартал|Месяц|Автор
|КатегорияТовара|КатегорияДвижения|КатегорияКонтрагента|ГруппаТовара
|ВалСумма|Себестоимость|Контрагент

Для анализа данных используются таблицы "Анализ движений.xls" ("Анализ бухгалтерии.xls"). Открывая их, не отключайте макросы, иначе вы не сможете обновлять отчеты (они запускаются макросами на языке VBA). Исходные данные эти файлы берут из файлов C:\fixin\motions.txt (C:\fixin\buh.txt), в остальном они одинаковые. Поэтому возможно, вам придется скопировать ваши данные в один из этих файлов.
Чтобы в EXCEL загрузились ваши данные, выберите или напишите свой фильтр и нажмите кнопку "Сформировать" на листе "Условия".
Листы отчетов начинаются префиксом "Отч". Перейдите на лист отчета, нажмите "Обновить" и данные отчета изменятся в соответствии с последними загруженными данными.
Если вас не устраивают стандартные отчеты, есть лист ОтчШаблон. Скопируйте его в новый лист и настройте вид отчета, работая со сводной таблицей на этом листе (о работе со сводными таблицами - в любой книге по EXCEL 2000). Рекомендую настраивать отчеты на небольшом наборе данных, а затем уже запускать их на большом массиве, т.к. нет никакой возможности отключить перерисовку таблиц при каждом изменении макета отчета.

Технические комментарии :

При выгрузке данных из 1С пользователь выбирает папку, куда ему выгружать файл. Я сделал это потому, что вполне вероятно в ближайшем будущем будут выгружаться несколько файлов (остатки и движения). Затем по нажатию в Проводнике кнопки "Отправить" --> "На OLAP-анализ в EXCEL 2000" данные копируются из выбранной папки в папку C:\fixin. (чтобы эта команда появилась в списке команды "Отправить" и нужно скопировать файл "На OLAP-анализ в EXCEL 2000.bat" в каталог C:\Windows\SendTo) Поэтому выгружайте данные сразу давая имена файлам motions.txt или buh.txt.

Формат текстового файла:
Первая строка текстового файла - заголовки колонок разделенные "|", остальные строки содержат значения этих колонок, разделенные "|".

Для импорта текстовых файлов в Excel используется Microsoft Query (составная часть EXCEL) для его работы необходимо наличие в каталоге импорта (C:\fixin) файла shema.ini, содержащего следующую информацию:

ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Пояснение: motions.txt и buh.txt - это название раздела, соответствует имени импортируемого файла, описывает, как импортировать текстовый файл в Эксель. Остальные параметры означают, что первая строка содержит названия колонок, разделителем колонок является "|", набор символов - Windows ANSI (для ДОС - OEM).
Тип полей определяется автоматически исходя из содержащихся в колонке данных (дата, число, строка).
Перечень полей не нужно нигде описывать - EXCEL и OLAP сами определят, какие поля содержатся в файле по заголовкам в первой строке.

Внимание, проверьте ваши региональные настройки "Панель управления" --> "Региональные настройки" . В моих обработках числа выгружаются с разделителем запятая, а даты в формате "ДД.ММ.ГГГГ".

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

Я понимаю, что любители MS SQL Server и мощных баз данных начнут ворчать, что у меня слишком все упрощено, что моя обработка загнется на годичной выборке, но в первую очередь я хочу дать преимущества OLAP-анализа для средних организаций. Я бы позиционировал этот продукт как инструмент годичного анализа для оптовых компаний, квартального анализа для розничной торговли и оперативного анализа для любой организации.

Мне пришлось повозиться с VBA, чтобы данные брались из файла с любым списком полей и можно было заранее готовить бланки отчетов.

Описание работы в EXCEL (для пользователей):

Инструкция по использованию отчетов:
1. Отправьте на анализ выгруженные данные (уточните у администратора). Для этого нажмите правой кнопкой на папке, в которую у вас выгрузились данные из 1С и выберите команду "Отправить", затем "На OLAP-анализ в EXCEL 2000".
2. Откройте файл "Анализ движений.xls"
3. Выберите Значение фильтра, нужные вам фильтры можно дописать на закладке "Значения".
4. Нажмите кнопку "Сформировать", при этом выгруженные данные будут загружены в EXCEL.
5. После загрузки данных в EXCEL, можно смотреть различные отчеты. Для этого достаточно нажать кнопку "Обновить" в выбранном отчете. Листы с отчетами начинаются на Отч.
Внимание! После того как вы поменяете значение фильтра, нужно еще раз нажать кнопку "Сформировать", чтобы данные в EXCEL перезагрузились из файла выгрузки в соответствие с фильтрами.

Обработки из демо-примера:

Обработка motionsbuh2011.ert – последняя версия выгрузки проводок из Бухгалтерии 7.7 для анализа в Excel . В ней есть галочка «Присоединить в файл», которая позволяет выгружать данные частями по периодам, присоединяя их в один и тот же файл, а не выгружая в один и тот же файл заново:

Обработка motionswork.ert выгружает данные о продажах для анализа в Excel.

Примеры отчетов :

Шахматка по проводкам:

Загруженность операторов по видам накладных:

P.S. :

Понятно, что по аналогичной схеме можно организовать выгрузку данных из 1С8.
В 2011 году ко мне обращался пользователь, которому нужно было доработать эту обработку в 1С7, чтобы она выгружала большие объемы данных, я нашел аутсорсера и выполнил эту работу. Так что разработка вполне актуальна.

Обработка motionsbuh2011.ert доработана, чтобы справляться с выгрузкой большого объема данных.

OLAP (от англ. OnLine Analytical Processing - оперативная аналитическая обработка данных, также: аналитическая обработка данных в реальном времени, интерактивная аналитическая обработка данных) - подход к аналитической обработке данных, базирующийся на их многомерном иерархическом представлении, являющийся частью более широкой области информационных технологий - бизнес-аналитики ().

Каталог OLAP-решений и проектов смотрите в разделе OLAP на TAdviser.

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

Требования к OLAP-системам. FASMI

Ключевое требование, предъявляемое к OLAP-системам - скорость, позволяющая использовать их в процессе интерактивной работы аналитика с информацией. В этом смысле OLAP-системы противопоставляются, во-первых, традиционным РСУБД , выборки из которых с типовыми для аналитиков запросами, использующими группировку и агрегирование данных, обычно затратны по времени ожидания и загрузке РСУБД , поэтому интерактивная работа с ними при сколько-нибудь значительных объемах данных сложна. Во-вторых, OLAP-системы противопоставляются и обычному плоскофайловому представлению данных, например, в виде часто используемых традиционных электронных таблиц, представление многомерных данных в которых сложно и не интуитивно, а операции по смене среза - точки зрения на данные - также требуют временных затрат и усложняют интерактивную работу с данными.

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

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

При этом такая связка должна удовлетворять очевидным требованиям поддержки импорта-экспорта из нескольких источников данных, осуществления процедур очистки и трансформации данных, унификации используемых классификаторов и справочников. Кроме того, к этим требованиям добавляется необходимость учёта различных циклов обновления данных в существующих информационных системах и унификации требуемого уровня детализации данных. Сложность и многогранность этой проблемы привела к появлению концепции хранилищ данных , и, в узком смысле, к выделению отдельного класса утилит конвертации и преобразования данных - ETL (Extract Transform Load) .

Модели хранения активных данных

Выше мы указали, что OLAP предполагает многомерное иерархическое представление данных, и, в каком-то смысле, противопоставляется базирующимся на РСУБД системам.

Это, однако, не значит, что все OLAP-системы используют многомерную модель для хранения активных, "рабочих" данных системы. Так как модель хранения активных данных оказывает влияние на все диктуемые FASMI-тестом требования, её важность подчёркивается тем, что именно по этому признаку традиционно выделяют подтипы OLAP - многомерный (MOLAP), реляционный (ROLAP) и гибридный (HOLAP).

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

Хранение активных данных в многомерной БД

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

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

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

Среди условных недостатков, характерных для некоторых реализаций многомерных СУБД и базирующихся на них OLAP-систем можно отметить их подверженность непредсказуемому с пользовательской точки зрения росту объёмов занимаемого БД места. Этот эффект вызван желанием максимально уменьшить время реакции системы, диктующим хранить заранее рассчитанные значения агрегатных показателей и иных величин в БД, что вызывает нелинейный рост объёма хранящейся в БД информации с добавлением в неё новых значений данных или измерений.

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

Хранение активных данных в реляционной БД

Могут храниться данные OLAP и в традиционной РСУБД . В большинстве случаев этот подход используется при попытке «безболезненной» интеграции OLAP с существующими учётными системами, либо базирующимися на РСУБД хранилищами данных . Вместе с тем, этот подход требует от РСУБД для обеспечения эффективного выполнения требований FASMI-теста (в частности, обеспечения минимального времени реакции системы) некоторых дополнительных возможностей. Обычно данные OLAP хранятся в денормализованном виде, а часть заранее рассчитанных агрегатов и значений хранится в специальных таблицах. При хранении же в нормализованном виде эффективность РСУБД в качестве метода хранения активных данных снижается.

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

В целом считается, что базирующиеся на РСУБД OLAP-системы медленнее систем, базирующихся на многомерных СУБД, в том числе за счет менее эффективных для задач OLAP структур хранения данных, однако на практике это зависит от особенностей конкретной системы.

Среди достоинств хранения данных в РСУБД обычно называют большую масштабируемость таких систем.

Хранение активных данных в «плоских» файлах

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

Гибридный подход к хранению данных

Большинство производителей OLAP-систем, продвигающих свои комплексные решения, часто включающие помимо собственно OLAP-системы СУБД , инструменты ETL (Extract Transform Load) и отчетности, в настоящее время используют гибридный подход к организации хранения активных данных системы, распределяя их тем или иным образом между РСУБД и специализированным хранилищем, а также между дисковыми структурами и кэшированием в оперативной памяти.

Так как эффективность такого решения зависит от конкретных подходов и алгоритмов, применяемых производителем для определения того, какие данные и где хранить , то поспешно делать выводы о изначально большей эффективности таких решений как класса без оценки конкретных особенностей рассматриваемой системы.

OLAP (англ. on-line analytical processing) – совокупность методов динамической обработки многомерных запросов в аналитических базах данных. Такие источники данных обычно имеют довольно большой объем, и в применяемых для их обработки средствах одним из наиболее важных требований является высокая скорость. В реляционных БД информация хранится в отдельных таблицах, которые хорошо нормализованы. Но сложные многотабличные запросы в них выполняются довольно медленно. Значительно лучшие показатели по скорости обработки в OLAP-системах достигаются за счет особенности структуры хранения данных. Вся информация четко организована, и применяются два типа хранилищ данных: измерения (содержат справочники, разделенные по категориям, например, точки продаж, клиенты, сотрудники, услуги и т.д.) и факты (характеризуют взаимодействие элементов различных измерений, например, 3 марта 2010 г. продавец A оказал услугу клиенту Б в магазине В на сумму Г денежных единиц). Для вычисления результатов в аналитическом кубе применяются меры. Меры представляют собой совокупности фактов, агрегированных по соответствующим выбранным измерениям и их элементам. Благодаря этим особенностям на сложные запросы с многомерными данными затрачивается гораздо меньшее время, чем в реляционных источниках.

Одним из основных вендоров OLAP-систем является корпорация Microsoft . Рассмотрим реализацию принципов OLAP на практических примерах создания аналитического куба в приложениях Microsoft SQL Server Business Intelligence Development Studio (BIDS) и Microsoft Office PerformancePoint Server Planning Business Modeler (PPS) и ознакомимся с возможностями визуального представления многомерных данных в виде графиков, диаграмм и таблиц.

Например, в BIDS необходимо создать OLAP-куб по данным о страховой компании, ее работниках, партнерах (клиентах) и точках продаж. Допустим предположение, что компания предоставляет один вид услуг, поэтому измерение услуг не понадобится.

Сначала определим измерения. С деятельности компании связаны следующие сущности (категории данных):

• Точки продаж
  - Сотрудники
  - Партнеры

Также создаются измерения Время и Сценарий, которые являются обязательными для любого куба.
Далее необходима одна таблица для хранения фактов (таблица фактов).
Информация в таблицы может вноситься вручную, но наиболее распространена загрузка данных с применением мастера импорта из различных источников.
На следующем рисунке представлена последовательность процесса создания и заполнения таблиц измерений и фактов вручную:

Рис.1. Таблицы измерений и фактов в аналитической БД. Последовательность создания
После создания многомерного источника данных в BIDS имеется возможность просмотреть его представление (Data Source View). В нашем примере получится схема, представленная на рисунке ниже.

Рис.2. Представление источника данных (Data Source View) в Business Intellingence Development Studio (BIDS)

Как видим, таблица фактов связана с таблицами измерений посредством однозначного соответствия полей-идентификаторов (PartnerID, EmployeeID и т.д.).

Посмотрим на результат. На вкладке обозревателя куба, перетаскивая меры и измерения в поля итогов, строк, столбцов и фильтров, можем получить представление интересующих данных (к примеру, заключенные сделки по страховым договорам, заключенные определенным работником в 2005 году).

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

Если торговое предприятие входит в сеть, управление которой ведется с помощью iikoChain, то OLAP-отчеты могут быть настроены централизованно в Chain и затем реплицированы в RMS. Редактирование и удаление таких «централизованных» OLAP-отчетов в отдельных торговых предприятиях невозможно.

При репликации отчеты в iikoRMS заменяются аналогичными по названию «централизованными» отчетами, загруженными из iikoChain.

В системе существует два вида OLAP-отчетов:

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

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

Данные отчеты расположены в пункте меню Отчеты . Кроме того, OLAP Отчет по продажам можно построить из списка кассовых смен. Для этого в пункте меню Розничные продажи → Кассовые смены отметьте галочками интересующие смены и в меню Действия выберите пункт Создать новый OLAP-отчет .

В OLAP-Отчете по продажам уже преднастроены отчеты для анализа сумм выручки. Чтобы построить один из этих отчетов, нужно просто выбрать его название в поле Формат отчета .

Чтобы добавить новый отчет:

1. Перетащите параметры в область данных.
2. Нажмите кнопку Сохранить как .
3. Введите название и выберите тип отчета:

• «Для всех пользователей» – отчет доступен всем пользователям.
• «Только для меня» – отчет доступен только создателю.

1. Нажмите кнопку Сохранить .

Пользователь с правом «Редактировать общие OLAP-отчеты» (B_ESOR ) может создавать и редактировать все отчеты, в том числе доступные всем пользователям.

Если права «Редактировать общие OLAP-отчеты» (B_ESOR ) у пользователя нет, то он может создавать и сохранять только личные отчеты. Общие ему доступны только для чтения и редактирования без сохранения изменений

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

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

Для облегчения поиска нужного показателя введите его название в поисковую строку:

Все параметры разбиты на группы. Кнопка с названием группы подсвечивается синим цветом. Если нажать на нее, тем самым сняв подсветку, то кнопки с относящимися к этой группе параметрами исчезнут из списка. Например, если при построении определенного отчета вас не интересует информация о том, в каком из мест обслуживания (отделений) была оформлена позиция и на какой кассе был закрыт заказ, то можно скрыть параметры группы «Организация».

В качестве дополнительного отбора данные отчета могут быть ограничены одним или несколькими значениями интересующего параметра. Для этого:

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

• Включающий : в отчете будут представлены данные только по значениям, отмеченным в фильтре галочками. Значения, которых не было в списке в момент настройки фильтра, в дальнейшем в отчет не попадут (например, если появились новые блюда, то в отчете по продажам они отображены не будут).
• Исключающий : в отчете будут представлены данные по всем значениям кроме тех, которые отмечены галочками в данном фильтре. Для выбора этого типа нажмите на кнопку Включающий , в результате на ней изменится надпись и применится тип фильтра Исключающий .

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

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

Информацию по продажам и проводкам, зарегистрированным в iiko, вы также можете получить через Интернет с помощью

2003 Intelligent enterprise

ERP + OLAP = эффективная система подготовки отчетности

За последние годы появилось достаточно публикаций по 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-системы. В некоторых системах это "продвинутый" конструктор с возможностью визуального проектирования, в некоторых - встроенный язык программирования.

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

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

1. Ограниченный набор базовой отчетности.
2. Необходимость привлечения программистов для настройки дополнительных отчетов.
3. Низкая скорость формирования сложных отчетов из общей с системой ввода данных СУБД.
4. Высокие требования к производительности локальных рабочих станций пользователей при формировании на них сложных отчетов

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

Интеграция OLAP-инструментов с ERP-системой

Рассмотрим возможный вариант реализации разделения учета и отчетности на примере интеграции ERP-системы и OLAP-инструментов. В ООО "Мострансгаз" данная модель была построена на основе:

• программного комплекса "Галактика" версии 5.84 с использованием модулей "Контур оперативного учета" и "Контур бухгалтерского учета"; разработаны компанией "Галактика" (www.galaktika.ru) ;
• OLAP-инструментов Аналитической платформы Контур: Контур Стандарт версии 4.0, Контур Дизайнер кубов версии 1.0 и Контур Генератор кубов версии 1.0; разработаны компанией Intersoft Lab .

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

Система "Галактика" работает на платформе СУБД Btrieve\Pervasive, что при невысоких расходах на сопровождение обеспечивает вполне приемлемую скорость работы пользователей, выполняющих операции ввода данных и выпуска текущих отчетов. Однако отчеты за длительный период, а также отчеты, построенные на нестандартных запросах, выполняются недостаточно быстро. Это побудило ИТ-менеджеров ООО "Мострансгаз" применить современные OLAP-системы Аналитической платформы Контур для выпуска отчетов.

АНАЛИТИЧЕСКАЯ ПЛАТФОРМА КОНТУР

Контур Стандарт - это инструмент для генерации произвольных табличных отчетов и анализа данных различных информационных систем. В этой системе можно настроить прямой доступ к локальным таблицам и реляционным базам данных, сконструировать SQL-запросы и выпускать на них OLAP-отчеты в режиме реального времени. Созданный отчет сохраняется для работы в off-line режиме и передачи удаленным пользователям в виде файлов микрокубов.

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

Контур Дизайнер Кубов предназначен для проектирования микрокубов и сценариев их генерации.

Контур Генератор кубов используется для массовой генерации микрокубов по готовым сценариям.

Более подробно об OLAP-технологии, ее возможностях и конкретных примерах использования можно прочесть в статье В.Некрасова "Мобильный OLAP", опубликованной в журнале "Открытые системы" №5 (2003г.).

Общая схема решения выглядит следующим образом:

Рис. 1. Общая схема решения

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

Параллельно, планировщик задач с заданным периодом в 30 минут запускает перегрузку данных из БД "Галактики" в промежуточный склад данных. Перегрузка происходит в фоновом режиме без отключения пользователей. Промежуточный склад данных является копией базы данных ERP-системы. В данном конкретном случае склад реализован на платформе Firebird, но нет никаких ограничений по другим СУБД. Основная задача создания промежуточного слоя данных - разделение системы учета и системы отчетности.

Данные промежуточного склада используются для создания микрокубов - витрин данных по разным направлениям отчетности (например, "исполнение договоров по капитальному ремонту" или "состав затрат с местами их возникновения"). Создание микрокубов и настройка OLAP-отчетов делегирована аналитикам службы информационных технологий.

Аналитик настройки отчетности в визуальной среде "Контур Дизайнер кубов" проектирует структуры микрокубов, информационный состав и внешний вид OLAP-отчетов. Все настройки сохраняются в виде файлов сценариев.

По готовым сценариям модуль "Контур Генератор кубов" каждые 30 минут генерирует микрокубы из промежуточного склада данных.

Готовые микрокубы содержат в себе метаданные (настройки внешнего вида отчетов, доступных пользователю измерений и фильтров и др.) и данные (выборки из промежуточного склада данных).

При помощи OLAP-клиента "Контур Стандарт" микрокубы доступны аналитикам, экономистам, бухгалтерам и другим потребителям отчетов.

Практические примеры использования OLAP для подготовки отчетности

Какие задачи и как помогают решить OLAP-технологии пользователям ERP-системы? Инструменты OLAP извлекают данные из БД ERP-системы и представляют их в интерактивных отчетах для разных специалистов: бухгалтеров, экономистов, руководителей и т.д. Контроль движения материальных ценностей между складами, сравнительный анализ закупочных цен поставщиков, оценка сбыта по регионам, анализ исполнения бюджетов - таков далеко не полный перечень направлений использования OLAP-систем на предприятии. Фактически интеграция ERP и OLAP выводит автоматизацию поддержки управления предприятием на новый качественный уровень.

Рассмотрим несколько примеров использования OLAP в "Мострансгазе".

Представление штатной отчетности в форме OLAP

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

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

Рис. 2. OLAP-отчет

Анализ дебиторской и кредиторской задолженности

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

Это помогает в решении множества повседневных задач бухгалтерии предприятия. Например, значительно сокращает время на поиск задолженности, по которой приближается срок исковой давности, при создании резерва по сомнительной задолженности и в других случаях анализа дебиторской и кредиторской задолженности. Для этого в один OLAP-отчет выводятся остатки по счету 76 "Расчеты с дебиторами и кредиторами" в разрезе периодов возникновения задолженности, например по годам.

"Мострансгаз" имеет договорные отношения с огромным количеством контрагентов. В OLAP-отчете можно оперативно просмотреть задолженность каждого контрагента и оценить суммы задолженности по разным видам деятельности в разрезе контрагентов. Для этого в отчет добавляются два измерения: "Контрагент" (объекты аналитического учета по счету 76) и "За что" (предметы договоров с контрагентами, по которым образовалась задолженность). Итоги по новым измерениям рассчитываются автоматически (см. рис).

Рис. 3. OLAP-отчет

Поиск встречной задолженности

Для поиска встречной задолженности в OLAP-отчете дебетовые и кредитовые сальдо по счетам 60 "Расчеты с поставщиками", 62 "Расчеты с покупателями и заказчиками" и 76 "Расчеты с дебиторами и кредиторами" представляются в разрезе контрагентов (измерение "Контрагент"), договоров (измерение "За что") и документов оплаты по договору (измерение "Документ").

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

Рис. 4. OLAP-отчет

Анализ задолженности и НДС

В OLAP-таблице с помощью фильтров по фактам и измерениям легко подобрать удобную для решения конкретной задачи форму отчета и выделить данные для сравнения. Можно изменить масштаб (1хN) и формат (абсолютное значение/проценты) представления данных. Две последние функции не являются обязательными для всех OLAP-систем, но они исключительно удобны для решения бухгалтерских задач.

Например, в отчете "Сальдо 2003 года по кварталам и датам" (см. рис.) для сопоставления корректности сальдо по кредиторской задолженности на счете 60 "Расчеты с поставщиками" и налога на добавленную стоимость (НДС) на счете 19 "НДС по приобретенным ценностям" устанавливаются два фильтра. Первый фильтр - по измерению "Счет" для отображения в отчете данных только по двум счетам - 19 и 60. Второй фильтр - по фактам, он включает отображение "свернутого" сальдо по счетам. В результате в соседних колонках в отчете отражаются дебетовое и кредитовое сальдо по выбранным счетам в разрезе счетов-фактур (измерение "С/Ф"), договоров и контрагентов.

Чтобы проверить корректность выделения НДС (18/118, до 2004 года 20/120), бухгалтер одной кнопкой переводит сальдо в проценты по строкам таблицы фактов. В результате в соседних колонках по каждому счету-фактуре выводится сумма оплаты и выделенный из нее НДС. Если отношение 20/120 или 18/118 нарушено, то ситуация по бухгалтерскому документу требует дополнительного разбора и анализа.

Рис. 5. OLAP-отчет

Анализ затрат предприятия

Для анализа затрат предприятия в OLAP-отчете представляются проводки по затратным счетам (20 "Основное производство" и 23 "Вспомогательное производство") в разрезе объектов аналитического учета (измерение "Статья затрат") и их группировок (измерение "Вид деятельности"). В результате с помощью фильтров можно получить в одном отчете группировки затратных статей из различных синтетических счетов.

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

Управляя составом измерений отчета, можно оценивать затраты предприятия с разных позиций. Например, для анализа затрат в разрезе структурных подразделений предприятия в отчет достаточно добавить измерение "Филиал" (см. рис.).

Рис. 6. OLAP-отчет

Анализ выполнения работ подрядчиками

OLAP-инструменты "говорят" со своими потребителями на одном языке. Это достигается за счет создания семантического слоя, когда полям из БД ERP-системы присваиваются названия, в терминах предметной области. В результате переименования колонки, строки и цифры в OLAP-таблице приобретают прикладной смысл, понятный конечным пользователям отчетов.

Наглядный пример - отчет по картотеке незавершенного строительства за 2003 год (см. рис.). Он показывает состояние расчетов с подрядчиками (измерение "Контрагент"), из которых можно оценивать ход выполнения работ по объектам строительства (измерение "Стройка"). Уплаченные суммы представлены в разрезе бухгалтерских документов: счетов-фактур (измерение "Счет-фактура") и соответствующих платежных документов и актов сдачи-приемки (измерение "Номер документа"). В таблице фактов представлены остатки по счетам 08 "Вложения во внеоборотные активы" (факт "Выполнен.работ"), 19 "НДС по приобретенным ценностям" (факт "НДС по работам") и 60 "Расчеты с поставщиками" (факт "Оплачено). К примеру, из таблицы видно, что за работы по строительству 14-этажного жилого дому на Можайском шоссе, подрядчику ЖилСтройКомплект был оплачен аванс в размере 41800 рублей по платежному документу А08016. В акте сдачи-приемки работ №34 было зафиксировано, что работы на сумму 41800 рублей, не облагаемые НДС, выполнены. Таким образом, счет-фактура №256 был закрыт.

Автоматический подсчет промежуточных итогов по разным измерениям таблицы позволяет мгновенно получить дополнительную информацию - суммарные затраты по объекту строительства или конкретному поставщику.

Рис. 7. OLAP-отчет

Анализ прибыли и убытков предприятия по видам деятельности

Для "Отчета о прибыли и убытках" бухгалтер готовит различные группировки по счету 99 "Прибыли и убытки". В OLAP-системе их можно получить практически мгновенно.

В одном OLAP-отчете объединенные в группы проводки по счету 99 показывают источники возникновения прибыли (в разрезе измерения "Филиал") и прибыльные бизнес-направления (в разрезе измерения "Вид деятельности"). По каждой группе проводок автоматически рассчитываются итоговые значения для сравнения составляющих прибылей и убытков.

Если дополнительно разложить данные по датам на периоды: по месяцам, кварталам (см. рис) и др., можно с разным масштабом оценить динамику прибыли.

Рис. 8. OLAP-отчет

Реальный выигрыш

Накопленный опыт эксплуатации OLAP-системы как надстройки над ERP показал ряд существенных положительных моментов:

• Формирование глубоких аналитических отчетов у пользователей выполняется моментально.
• Настройка отчетов для пользователей при помощи визуальных средств проектирования, которые входят в состав OLAP-системы, происходит значительно быстрее, чем при программировании отчетов. Аналитик, настраивающий отчеты, может совершенно не владеть навыками программирования.
• При работе с OLAP-отчетами пользователь не ограничен в возможностях анализа и синтеза данных. При необходимости он самостоятельно модифицирует представление отчета без привлечения программиста.
• Присваивая одним и тем же измерениям различные названия, можно настраивать отчеты для потребителей информации различных уровней. При этом в отчетах за разными названиями может скрываться одна и та же информация.
• За счет высокой скорости получения разнообразных отчетов снижается совокупное время, затрачиваемое на анализ финансового состояния предприятия.
• Сокращаются расходы на разработку и сопровождение системы отчетности предприятия, обеспечивается независимость от поставщика ERP-систем по номенклатуре отчетов.

Автор надеется, что все изложенное в статье будет полезно IT-службам и прикладным специалистам предприятий, и поможет им преодолеть искусственный барьер перед использованием OLAP-инструментов для подготовки разнообразной отчетности на основе ERP-систем.