CC BY
31
МА ТЕМА ТИЧЕСКИЕ И ИНСТРУМЕНТАЛЬНЫЕ МЕТОДЫ ЭКОНОМИКИ
УДК 681.3.01
Ю.Б. Бекаревич
канд. техн. наук, доцент, кафедра экономической кибернетики, ФГБОУВПО «Санкт-Петербургский государственный университет»
Н.В. Пушкина
канд. техн. наук, доцент, кафедра информатики, ФГБОУ ВПО ««Санкт-Петербургский государственный экономический университет»
ТЕХНОЛОГИЯ ПОДГОТОВКИ МОДЕЛИ ОПЕРАТИВНОГО АНАЛИЗА ДАННЫХ НА ОСНОВЕ РЕЛЯЦИОННЫХ ИСТОЧНИКОВ
Аннотация. Для малого и среднего бизнеса при ограниченных ресурсах актуально решение аналитических задач на наглядных моделях, адекватных структуре реляционного источника данных, в интерактивном режиме с удобной визуализацией результатов. Предлагаются меры, необходимые для автоматического построения модели анализа при импорте таблиц и их связей из реляционного источника. Рассматривается технология подготовки модели оперативного анализа, обеспечивающая экономию ресурсов за счет включения в нее таблиц с агрегированными данными и ограничения множества полей потребностями анализа. Показано как улучшить характеристики модели, создавая иерархии на основе таблицы измерения, полученной объединением полей таблиц смежных уровней.
Ключевые слова: модель данных, оперативный анализ, агрегирование, реляционные источники
данных.
Yu.B. Bekarevich, Saint-Petersburg State University
N.V. Pushkina, Saint-Petersburg State University of Economics
TECHNOLOGY OF PREPARATION MODEL OPERATIONAL ANALYSIS DATA BASED ON RELATIONAL DATA SOURCES
Abstract. For small and medium businesses with limited resources is topical the solution of analytical tasks on visual model, adequate structure of a relational data source, interactively and user-friendly visualization of the results. Proposed measures necessary for the automatic construction of the analysis model when you import tables and their relationships from the relational source. We consider technology of preparation the model of operational analysis, that enables resource savings due to the inclusion of tables with aggregated data and a limited number of fields of analysis. It is shown how to improve the characteristics of the model, creating a hierarchy based on the measurement table, obtained by combining the fields of tables adjacent levels.
Keywords: data model, operational analysis, aggregation, relational data sources.
Специалисты должны иметь возможность оперативно анализировать информацию о деятельности предприятия. Целью оперативного анализа является быстрое составление отчетности по агрегированным показателям бизнес-процессов и представление результатов этого анализа в удобном виде.
Технология оперативного анализа данных охватывает весь спектр обработки информации, необходимой для принятия решений. Эта информация представлена анализируемыми фактами, зависящими от большого числа измерений. Многомерный ана-
лиз данных обеспечивают гибкость и производительность при решении аналитических задач в крупных организациях. Однако он является чересчур громоздким и неоправданно сложным для бизнес-анализа в небольших предприятиях, так как требует использования сложных серверных аналитических систем [5]. Для малого и среднего бизнеса одним из доступных инструментальных средств являются отчеты сводных таблиц и диаграмм MS Excel и надстройки Power Pivot. Новые версии этих средств позволяют аналитику в интерактивном режиме подготовить модели, обеспечивающие адекватное представление взаимосвязей данных реляционных таблиц.
В качестве основного источника оперативного анализа, как правило, используются реляционные источники: хранилища данных или транзакционные базы предприятия. Хранилища данных в качестве источника обеспечивают быстрое построение моделей для анализа данных, так как их структура ориентирована на решение задач анализа. Более дешевым является использование в качестве реляционного источника тран-закционных баз данных, например, таких как базы данных MS Access, многопользовательские базы данных MS SQL Server, Oracle и др. Оперативный анализ на этих источниках выполняется средствами Excel и Power Pivot [3]. Эти средства могут использовать также предварительно созданные в службах SQL Server Analysis Services табличные модели или кубы многомерной модели.
Актуальным является упрощение подготовки модели данных оперативного анализа. При создании табличной модели необходимо, чтобы структура реляционного источника удовлетворяла ряду требований. Если они будут удовлетворены, модель может быть получена автоматически. При разработке модели достаточно в соответствии с задачами анализа в рамках предметной области установить анализируемые показатели -факты и параметры, влияющие на них, то есть измерения. При этом целесообразно предусмотреть агрегацию фактов в соответствии с иерархией измерений.
При использовании MS Excel 2013/2016 и Power Pivot, предусмотрена возможность импорта нескольких таблиц реляционной базе. Модель для анализа данных дает удобное представление данных в виде таблиц фактов и связанных с ними таблиц измерений. Для построения модели на основе фактов и измерений, необходимых для заданного исследования, должна быть определена достаточная подсхема базы данных, которая будет реляционным источником модели.
При подготовке модели должны быть установлены связи между импортированными таблицами. Для связей в модели все ключи таблиц измерений должны быть простыми числовыми [2]. Таблицы фактов могут иметь составные ключи. Для обеспечения автоматического построения связей в модели необходимо в таблицы измерений базы данных, имеющие составной ключ, ввести дополнительные простые ключи. Для минимизации структурных изменений в реляционный источник целесообразно введение простых ключей только в таблицы измерений. Это позволяет сохранить связи таблиц базы данных по реальным ключам там, где это возможно, и избежать хранения дополнительных индексов таблиц в базе. Рассмотренные меры достаточны для автоматического построения табличной модели уже при импорте таблиц и их связей.
На рисунке 1 приведен пример модели, построенной в Power Pivot для оперативного анализа данных по плановым и фактическим поставкам товаров. Модель получена автоматически при импорте таблиц и связей из реляционной базы данных, подготовленной в соответствии с предлагаемыми мерами.
Рисунок 1 - Модель данных для анализа поставок товаров
Модель включает две таблицы фактов Contract_spec и Shipment и ряд таблиц измерений, часть из них является общими для обеих таблиц фактов. Эта модель обеспечивает анализ показателей по каждой из таблиц фактов и сравнительный анализ фактов из обеих таблиц. При этом могут быть выполнены необходимые вычисления, разработаны Key Performance Indicators (KPI) [4] для руководителей и сотрудников, соответствующие целям бизнес-процесса. Здесь в качестве фактов рассматривается заказанное количество товара и стоимость заказанного в таблице Contract_spec. Измерения, влияющие на указанные факты, представлены таблицами Product, Contract, Customer, Month_year. Используя указанные факты и измерения, можно решать различные задачи
анализа по поставкам товаров [2].
При выполнении анализа данных на моделях, в которых представлены данные нормализованных таблиц реляционного источника, вычисление агрегатов выполняется на основе представленных в них детальных данных во время анализа. Чтобы снизить затраты времени, связанные с вычислением агрегатов, предлагается при подготовке модели заранее создавать соответствующие запросы SQL. На основе таких запросов должны формироваться таблицы модели с наиболее часто используемыми агрегатами.
Для минимизации модели при ее подготовке целесообразно включать только атрибуты, необходимые в процессе анализа. Характеристики модели могут быть улучшены также объединением полей различных взаимосвязанных таблиц измерения в одной таблице нижнего уровня. Для полей объединенной таблицы возможно создание иерархий атрибутов измерения, использование которых делает более удобным анализ данных в сводных отчетах.
Создать запросы с агрегированными данными можно на стороне базы данных или на стороне клиента в Power Pivot. Запросы будут формировать в модели содержимое таблиц фактов с агрегированными данными. Excel импортирует из реляционного источника данных таблицы запросов вместе с таблицами базы данных. При импорте запросов с агрегатами данных и связанных с ними таблиц измерений будет автоматически получена более компактная модель для анализа данных [1].
Созданная рассматриваемыми средствами модель данных дает возможность обновления отдельных таблиц модели непосредственно из оперативного источника. Обновление требуется, как правило, для таблиц фактов. Таблицы измерений, создающиеся, прежде всего, на основе справочных и условно постоянных данных, могут обновляться значительно реже таблиц фактов. Устанавливая регламент выборочного обновления содержимого таблиц модели, можно значительно минимизировать необходимое время и объем передаваемых при этом данных.
Оперативный анализ может выполняться на основе одной ненормализованной таблицы, в которой объединены все необходимые для анализа данные. Именно такой вариант обеспечивается в ранних версиях Excel, где можно импортировать только одну таблицу. Такая таблица может быть подготовлена с помощью запросов SQL непосредственно в базе данных. Она представляет фактически многомерную модель данных. В новых версиях Excel допускается импорт нескольких таблиц и построение на их основе модели данных для анализа [6].
Современные технологии позволяют выполнять аналитическую работу с удаленных компьютеров и мобильных устройств при наличии браузера и доступа к Internet. Если модель была создана по рассмотренной выше технологии, для удаленного доступа к ней достаточно разместить соответствующую книгу Excel в облаке, например, OneDrive для бизнеса. Для обеспечения возможности обновления данных модели и ее модификации база данных, к которой подключена книга Excel, также должна быть размещена в облаке.
При работе с удаленного компьютера, на котором установлен Excel, аналитику
доступен полный набор средств для построения модели и анализа данных. В этом случае путем подключения к удаленной реляционной базе данных, импорта таблиц и связей модель может быть построена автоматически. При использовании средств Power Pivot модель будет представлена графически в виде схемы данных (рис. 1). Если аналитик работает в браузере, для анализа данных в сводных таблицах может использоваться Excel Online. При этом в зависимости от типа устройства, на котором работает пользователь, предоставляется разный набор функциональных возможностей. Например, на смартфоне возможна работа только с ранее сформированной сводной таблицей. На планшете с достаточными ресурсами возможно создание новых сводных таблиц, а также отображение и модификация табличной модели.
Выводы. Сформулированы предложения по подготовке реляционного источника модели данных для оперативного анализа, обеспечивающие ее автоматическое построение в среде Excel и Power Pivot. Таким образом, создаются условия для самостоятельной подготовки модели аналитиком, без обращения к IT-специалисту. Для экономии ресурсов рекомендуется предварительное агрегирование детальных данных в таблицах реляционного источника. Ограничение объемов данных, передаваемых из источника, обеспечивается за счет выборочного обновления отдельных таблиц модели. Размещение модели и источника данных в облаках интернет обеспечивает удаленную аналитическую работу с различных устройств.
Список литературы:
1. Бекаревич Ю.Б., Пушкина Н.В. Microsoft Access 2016: самоучитель. СПб.: Изд-во БХВ-Санкт-Петербург, 2017. 450 с.
2. Бекаревич Ю. Б., Пушкина Н. В. Особенности оперативного анализа данных при использовании табличной модели // Проблемы экономики и менеджмента. Ижевск, 2015. № 2 (42). С. 120-126.
3. Ferrari A., Russo M. Microsoft Excel 2013 Building Data Models with PowerPivot (Business Skills). Microsoft, 2014.
4. Бекаревич Ю.Б., Пушкина Н.В. Технологии оперативного анализа данных: учебное пособие. СПб.: Изд-во СПбГЭУ, 2013. 109 с.
5. Kimball R., Ross M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. Third Edition. John Wiley and Sons, 2013.
6. Бекаревич Ю.Б., Пушкина Н.В. Модели оперативного анализа данныхь // Информационные технологии в бизнесе: сборник научных статей 8 международной научной конференции, 19-20 июня 2013 г. СПб.: Изд-во Инфо-да, 2013. С. 28-35.
