4_ Аналитическая обработка данных в реальном масштабе времени (OLAP - технология)

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

Понятие OLAP-технологий Базы данных предназначены для сбора и хранения информации с помощью СУБД (OLTP-системы). На их основе строятся оперативные отчеты, не обладающие возможностью их конструирования и изменения на лету (в режиме реального времени). OLAP, Online Analytical Processing, анализ данных в реальном времени - совокупность программных средств хранения, преобразования, агрегирования и выборки данных произвольного формата, основной целью которой является предоставление конечным пользователям структурированной аналитической информации. OLAP - системы поддержки принятия решения (Decision Support System, DSS), предназначены для экономистов, финансовых аналитиков, менеджеров, начальников подразделений, социологов, маркетологов

Предпосылки возникновения OLAP-технологий OLAP-анализ работает с данными, представленными в виде многомерных кубов и позволяет построить отчеты в нужных руководителю срезах и ракурсах практически на лету - без обращения к программистам и ожидания нужной отчетной формы. В середине 80-х родился термин EIS (Executive Information System - информационная система руководителя, т.е. конечного пользователя). В конце 80-х среди инструментов конечного пользователя для анализа данных стали доминировать электронные таблицы. В 1993 г. американский ученый Кодд, автор реляционного типа баз данных, в работе OLAP для пользователей - аналитиков, ввел термин OLAP для систем, претендующих на звание аналитических и сформулировал 12 правил для их идентификации (сейчас правил около 300); В 1995 г. авторами журнала The OLAP Report для OLAP- систем был сформулирован тест FASMI (Fast Analysis of Shared Multidimensional Information) - Быстрый Анализ Разделяемой Многомерной Информации Первые ИС для работы с многомерными таблицами – Lotus и Improve (Lotus), SuperCalc (Computer Associates). Недостатки – отсутствие графического интерфейса, ограничения в объемах информации, отсутствие возможности сохранения многомерного куба. Предложения компании MS появились только в июне 1999 года в MS Excel 2000.

Классификация OLAP-систем Зарубежные системы - Essbase (Hyperion Solutions), PowerPlay (Cognos), SAP AG и Express (Oracle), Intelligent Server (MicroStrategy), Pivot Table и MS OLAP Services (MS), SAS MDDB (SAS Institute), Crystal Reports (Business Objects), DB2 UDB (IBM) Отечественные системы - Галактика (Галактика), Контур Стандарт (InterSoft Lab), 1С:Предприятие v8.0 (1C), Прогноз (Прогноз), БЭСТ (БЭСТ), Инталев-куб (Инталев). Сводные таблицы (Pivot Table) были введены в MS Excel (2000) в 1999 году. Они также доступны в MS Access и MS SQL Server (модуль MS OLAP Services или Analysis Services).

Рост рынка OLAP в г. и ожидаемая тенденция

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

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

Разновидности срезов куба Представление и пространство запросов для менеджера по товарам Представление и пространство запросов для менеджера по каналам сбыта Представление в текущем периоде для бухгалтера

Области применения OLAP-технологий Анализ структуры закупок и продаж Анализ структуры расходов и доходов Результаты выборов и социологических опросов Уровень жизни населения Структура населения по результатам переписи Заболеваемость населения в зависимости от возраста, места проживания и т.д. Текучесть кадров на предприятии и в регионе Урожайность сельскохозяйственных культур Пассажирские и грузовые перевозки, простои транспорта Анализ аварий и загруженности автодорог Хит-парады и т.д.

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

Отчет на основе БД FoxPro Cправочник БИК КО России - файл bnkseek.dbf. Исследуем динамику банковской деятельности в Башкирии и соседних регионах. Поля таблицы: признак банка (20 – головное учреждение, 30 – филиал), регион России, город, банк, дата внесения записи о банке в справочник. Фрагмент файла по состоянию на 8 октября 2007 года:

Шаблон сводной таблицы Сводная таблица создается по команде ДАННЫЕ – СВОДНАЯ ТАБЛИЦА. Области шаблона: страницы (задают условия отбора обрабатываемых данных); строки (задают группировку по строкам); столбцы (задают группировку по столбцам); данные (используется для ввода исследуемого показателя, по которому подводятся итоги)

Заполнение шаблона сводной таблицы Даты можно группировать по команде ГРУППА И СТРУКТУРА – ГРУППИРОВАТЬ. Данные можно детализировать двойным щелчком мышки на поле (drill- down и drill-up) Поля перетаскиваются мышкой из списка на шаблон (drag-and-drop) Поле регион перенесем в область страниц и выберем в списке кодов значение 80 (Башкирия)

Свод КО по Башкирии Поле город поместим в область полей столбцов, поле банк поместим в область значений: Из 50 действующих в Башкирии КО наибольшее их количество было зарегистрировано в 1994 году (25, в том числе 12 РКЦ) и в 2006 году (12). В Уфе сосредоточено 36 КО (в том числе 2 РКЦ). Таблицу можно сохранить как WEB- страницу по команде СОХРАНИТЬ КАК ВЕБ-СТРАНИЦУ, отметить параметры ЛИСТ, ДОБАВИТЬ ИНТЕРАКТИВНОСТЬ и ОПУБЛИКОВАТЬ. В параметрах просмотра страницы отметить работу со сводными таблицами

Свод КО по соседним регионам В поле регион отметим следующие коды: 57 - Пермская область, 65 - Свердловская область, 75 - Челябинская область, 80 - Башкирия, 92 - Татария. Переместим в область значений поле банк. Сортировка записей таблицы выполняется по команде контекстного меню ПАРАМЕТРЫ ПОЛЯ – ДОПОЛНИТЕЛЬНО – ПАРАМЕТРЫ СОРТИРОВКИ – ПО ВОЗРАСТАНИЮ – С ПОМОЩЬЮ ПОЛЯ – КОЛИЧЕСТВО ПО ПОЛЮ БАНК

Соотношение количества головных банков и филиалов В поле признак выберем типы банков: 20 – головные, 30 - филиалы. Определим соотношение головных КО и филиалов КО В 2004 году большинство местных банков Башкирии, являющихся взаимными кредиторами и владельцами ценных бумаг, для координации своей деятельности заключили соглашение, согласно которому они ежемесячно обмениваются балансами.

Отчет на основе БД Access Файл Борей.mdb из поставки MS Office. Подготовим сводные данные для менеджера по продажам и менеджера по кадрам. Результат выполнения запроса.

Рейтинг продавцов Подключение к внешнему источнику данных - через драйвер MS Access (*.mdb) для запроса БД Access или запроса MS Query Диаграмма, построенная на основе таблицы, также интерактивная

Рейтинг покупателей Гистограмма рейтинга товаров (ТОР 10)

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

Тип формы СВОДНАЯ ТАБЛИЦА На основе запроса можно создать форму, указав ей тип Сводная таблица. На элементах формы допустимо ограниченное форматирование и вычисление агрегатных функций

Внедрение сводной таблицы в форму MS Access (1-ый вариант) Лист со сводной таблицей можно внедрить в форму MS Access в свободную рамку объекта. Код VBA на кнопке: Me!СвободныйOLE1. Verb = acOLEVerbOpen Me!СвободныйOLE1. Action = acOLEActivate где КНОПКА1 – имя кнопки, СвободныйOLE1 – имя рамки с внедренным объектом. Выполнение кода приводит к открытию и активизации OLE объекта. Изменения в сводной таблице сохраняются и отображаются на форме.

Перевод OLE объекта в режим редактирования может быть выполнен на месте двойным щелчком мышки по таблице, внедренной в рамку, если в свойствах рамки параметру АВТОМАТИЧЕСКИЙ ЗАПУСК ОБЪЕКТА задать значение ДВОЙНЫМ НАЖАТИЕМ КНОПКИ Внедрение сводной таблицы в форму MS Access (2-ый вариант)

Отчет по проводкам БД 1С БД хранится в файлах *.dbf (драйвер MS dBase Driver версии IV). Данные о хозяйственных операциях (проводки) - файл 1SENTRY.dbf. План счетов - файл 1SACCS.dbf. Сводная таблица

Отчет по проводкам средствами 1С Индекс журнала определяет принадлежность операции к участку учета: ТВ - реализация товаров (готовой продукции). В среде 1С v7.7 сформируем отчет

Отчет 1С (продолжение) Итоги совпадают

ЗАКЛЮЧЕНИЕ В MS Excel (2003) можно создавать сводные таблицы на локальных и серверных БД, на локальных и серверных OLAP-кубах, сохранять локальные OLAP- кубы. В MS Access можно строить на основе запросов сводные таблицы и внедрять в формы файлы MS Excel со сводными таблицами. На сервере Analysis Services (2000) можно создавать OLAP-кубы и модели Data Mining на основе таблиц и запросов БД, а также создавать хранилища данных для построения OLAP-кубов. В Crystal Reports (9.2) можно загружать локальные OLAP-кубы, созданные средствами MS Excel, и серверные OLAP- кубы, созданные на сервере Analysis Services. По простоте и наглядности работы с измерениями сводных таблиц, по популярности интерфейса средства создания сводных таблиц в MS Excel являются наиболее предпочтительными.