CC BY
129
Проблемы экономики и менеджмента
МА ТЕМА ТИЧЕСКИЕ И ИНСТРУМЕНТАЛЬНЫЕ МЕТОДЫ ЭКОНОМИКИ
УДК 681.3.01
Ю.Б. Бекаревич
канд. техн. наук, доцент, кафедра экономической кибернетики, ФГБОУВПО «Санкт-Петербургский государственный университет»
Н.В. Пушкина
канд. техн. наук, доцент, кафедра информатики, ФГБОУ ВПО «Санкт-Петербургский государственный экономический университет»
ОСОБЕННОСТИ ОПЕРАТИВНОГО АНАЛИЗА ДАННЫХ ПРИ ИСПОЛЬЗОВАНИИ ТАБЛИЧНОЙ МОДЕЛИ
Аннотация. Статья посвящена оперативной аналитической обработке данных, которая является важнейшей составляющей бизнес-аналитики. Рассматриваются особенности табличной модели для анализа данных в Excel 2013 с надстройкой Power Pivot при использовании реляционных баз в качестве источника данных. Обоснованы преимущества нормализации данных для построения модели с иерархией связанных таблиц измерений. Показана целесообразность создания представлений в базе данных для получения таблиц фактов в модели и упрощения процедуры анализа.
Ключевые слова: оперативной анализ данных, сводные таблицы, табличная модель Excel 2013, Power Pivot, источники данных для анализа, реляционная база данных.
Yu.B. Bekarevich, Saint-Petersburg State University
N.V. Pushkina, Saint-Petersburg State University of Economics
FEATURES OPERATIONAL DATA ANALYSIS WHILE USING TABULAR MODEL
Abstract. The article is devoted online analytical processing data, which is an essential component of business intelligence. The features of the tabular model for data analysis in Excel 2013 with a superstructure Power Pivot for use as a source of relational databases. There is justification advantages of normalization of data to build the model with a hierarchy of related dimension tables. The expediency of creating views in the database for fact tables in the model and a simple procedure analysis.
Keywords: operational data analysis, pivot tables, tabular model Excel 2013, Power Pivot, data sources for analysis, a relational database.
Для принятия управленческих решений необходимо располагать актуальной и содержательной информацией, поставляемой в результате оперативного анализа корпоративных данных в виде различных показателей, зависимых от большого числа характеризующих параметров. Возросший интерес к методам IT бизнес-аналитики со стороны малого и среднего бизнеса требует поиска удобных инструментов и методов оперативного извлечения нужной информации из больших массивов данных и формирования соответствующих отчетов. Многомерный анализ данных, отражая в своей модели в полной мере естественную структуру данных из любой предметной области, обеспечивает гибкость и производительность при решении аналитических задач в крупных организациях. В то же
120
№ 2 (42) - 2015
Проблемы экономики и менеджмента
время он является чересчур громоздким и неоправданно сложным для анализа в небольших организациях. Соответственно, возникает потребность в упрощенных технологиях, адаптированных для задач аналитики небольших организаций.
В службы Analysis Services MS SQL Server последних версий помимо средств создания на сервере многомерной семантической модели для анализа включены средства поддержки табличной модели данных. Локальная модель аналитических данных для эффективного анализа больших объемов может быть построена в книге MS Office Excel 2013. Модель данных, поддерживающую любое количество отчетов сводных таблиц, диаграмм и визуализацию отчетов Power View в одной книге, можно создавать непосредственно в Excel или с помощью надстройки Power Pivot. При этом для моделирования данных используются табличные решения, основанные на реляционных конструкциях моделирования, таких как таблицы и связи. Для экономного хранения и вычисления данных в памяти используется аналитический обработчик. Табличная модель позволяет аналитику лучше видеть и понимать структуру анализируемых данных и позволяет быстрее выполнить проектирование, тестирование и развертывание решений аналитики.
Сводные таблицы Excel предназначены как для быстрого получения ответов на произвольные запросы пользователей, так и быстрого составления отчетности по агрегированным показателям в различных разрезах и с произвольной глубиной детализации оперативных данных. Отчет сводной диаграммы используется для визуализации данных из отчета сводной таблицы и упрощения процедуры сравнений, поиска закономерностей и тенденций. Анализ данных в сводных таблицах и диаграммах позволяет в реальном времени получать ответы на вопросы, интересующие аналитика, и представлять результаты этого анализа в удобном для восприятия и принятия решений виде.
В процессе своей деятельности организации накапливают большие объемы данных, которые хранят в себе потенциальные возможности по извлечению полезной аналитической информации для выявления скрытых тенденций и принятия новых решений. Повсеместное ведение баз данных оперативной информации на всех уровнях управления позволяет обратить особое внимание на использование хранящихся в них данных как на основной источник для построения аналитических моделей в Excel.
В модели данных Excel 2013 в одних плоских таблицах хранятся измерения, а в других - факты (агрегируемые данные). Таблица фактов содержит количественные характеристики объектов и событий, совокупность которых предполагается в дальнейшем анализировать и является основой для связанных с ней таблиц измерений.
Для оперативного анализа данных с помощью сводных таблиц и диаграмм Excel могут быть использованы не только таблицы книг Excel, но и импортируемые из различных внешних источников данные, в которых содержится актуальная информация необходимая для анализа и принятия решений. Поскольку реляционные СУБД лежат в основе построения большинства систем оперативной обработки данных и поддерживают реляционные модели данных, их использование позволяет упростить процедуру разработки, загрузки и интеграции данных в табличные модели анализа. В качестве внешнего источника данных сводных таблиц Excel могут использоваться как локальные, так и серверные реля-
№ 2 (42) - 2015
121
Проблемы экономики и менеджмента
ционные базы данных: такие как Microsoft Access, Microsoft SQL Server, Oracle, DB2 и др. При выборе и импорте из базы данных несколько связанных таблиц в книге Excel 2013 автоматически строится табличная модель данных. В модель импортируются как таблицы, так и связи между ними, определенные в базе данных.
Когда модель данных построена на нескольких источниках, импорт из каждого источника выполняется на отдельном листе книги Excel. Импортированные из разных источников таблицы отображаются в общей модели. Связи таблиц из разных источников устанавливаются вручную.
Для эффективного анализа таблицы в модели данных должны содержать только те столбцы и строки, которые действительно используются при анализе. Использование мастера импорта таблиц в надстройке Power Pivot позволяет выбрать в таблице только нужные столбцы и отфильтровать строки для включения их в модель. Кроме таблиц в модель данных из реляционной базы могут быть импортированы запросы-представления. Представления не могут иметь параметров, но могут содержать условия отбора записей и группировку. Если для подготовленных в базе данных представлений определены связи, то при импорте они также будут включены в модель данных.
В соответствии с этими возможностями целесообразно из базы данных импортировать основные таблицы - справочники, являющиеся измерениями, и представления, созданные на основе оперативных данных. Такие представления в аналитической модели являются таблицами фактов. При подготовке представлений возможно исключение ненужных столбцов, фильтрация строк, а также агрегирование данных по нужным измерениям. Например, выборка строк с заданными значениями в некотором столбце может исключить из таблицы строки, представляющие для анализа ничтожные значения (такие как копеечные стоимости покупки). В представлении можно задать выборку данных за актуальный для анализа интервал времени: текущий месяц, день, несколько месяцев или дней и т.д. Следует отметить, что использование представлений освобождает аналитика от необходимости ручной настройки по выбору столбцов и фильтрации строк при импорте таблиц с помощью мастера Power Pivot, хотя и делает модель более жесткой.
Использование представлений уменьшает объем передаваемых из источника данных, объем оперативной памяти, необходимой для хранения данных. При этом может существенно сокращаться время получения результатов анализа за счет проведения агрегирования фактов на стороне источника данных. Компьютер источника, как правило, обладает более мощными ресурсами, чем компьютер аналитика. При использовании Power Pivot обновление данных в книге для нового анализа может быть выполнено только для представлений, которые должны возвращать обновленные данные. Например, данные, накопленные за текущий день, за последний месяц и т. п. Выполненное в Power Pivot обновление только нужных представлений сразу же отображается в сводных таблицах и диаграммах.
Выполнение агрегации в представлениях источника данных может сокращать возможности иерархического отображения результатов анализа. Если агрегация прове-
122
№ 2 (42) - 2015
Проблемы экономики и менеджмента
дена на самом верхнем уровне иерархии, при анализе нельзя получить данных для более низких уровней. Например, если провести агрегирование данных по странам, не будет доступа к данным по городам и покупателям - более низким уровням иерархии. Агрегирование по самому низкому уровню иерархии, меньше сокращая объемы данных, обеспечивает полную детализацию анализа. Агрегирование для более высоких уровней может выполняться при анализе. Для того чтобы ускорить процесс анализа данных и в тоже время сохранить возможность получения срезов по любым уровням измерения, можно в различных представлениях получать агрегированные значения для разных уровней иерархии.
На логическом уровне таблицы в модели данных Excel 2013 должны быть представлены схемой «звезда» или «снежинка». В каждой из схем в центре располагается таблица фактов, с которой связаны все таблицы измерений. Измерения в таблице фактов представлены кодами. Данные для каждого измерения берутся из соответствующей таблицы-справочника, в котором коду соответствует значение измерения. Размещение информация о каждом измерении в отдельной таблице упрощает их просмотр, а саму схему делает логически прозрачной и понятной пользователю.
Измерения в схеме «звезда» (рис. 1), представлены одной, в общем случае ненормализованной таблицей. Для того чтобы определить уровни иерархии в ненормализованной таблице и тем самым обеспечить как агрегацию, так и детализацию анализируемых данных, можно воспользоваться средствами Power Pivot.
Рисунок 1 - Схема «звезда»
Главной особенностью схемы «снежинка» (рис. 2) является то, что информация об одном измерении может храниться в нескольких связанных нормализованных таблицах. Например, если продаваемые товары объединены в группы (имеет место иерар-
№ 2 (42) - 2015
123
Проблемы экономики и менеджмента
хия), то придется тем или иным способом показать, к какой группе относится каждый товар. Использование одной ненормализованной таблицы приводит к многократному повторению характеристик группы для различных товаров. Это не только вызовет рост избыточности, но и повышает вероятность возникновения противоречий. Выделение характеристик групп в отдельную связанную нормализованную таблицу позволит избежать этого. Таким образом, если хотя бы одна из таблиц измерений ссылается на другую таблицу измерений, говорят о применении схемы «снежинка».
Рисунок 2 - Схема «снежинка»
Основным функциональным отличием схемы «снежинка» от схемы «звезда» является явное выделение и возможность работы с иерархическими уровнями, определяющими степень детализации данных. В приведенном примере схема «снежинка» позволяет работать с данными на уровне максимальной детализации, например, с каждым товаром отдельно, или использовать обобщенное представление по группам товаров с соответствующей агрегацией фактов.
При использовании нормализованной реляционной базы в качестве источника данных схема «снежинка» практически полностью совпадает со структурой базы и процедура импорта измерений более эффективна и проста, поскольку производится из отдельных таблиц. Вероятность появления ошибок, несоответствия данных при использовании нормализованных таблиц базы намного ниже. Даже при частом изменении размерности данных в модели можно ограничиться добавлением новых таблиц. Использование схемы «снежинка» обеспечивает большую, по сравнению со схемой «звезда», компактность представления данных, поскольку все значения измерений упоминаются только один раз. Следует заметить, что высокая степень нормализации таблиц измерений в схеме «снежинка», может приводить к снижению быстродействия в аналитических запросах, поскольку требуется объединение большого числа таблиц.
Таблицы измерений существуют независимо от таблиц фактов и в модели могут
124
№ 2 (42) - 2015
Проблемы экономики и менеджмента
быть связаны с несколькими таблицами фактов (рис. 3).
Рисунок 3 - Схема использования измерений в двух таблицах фактов
Для корректного проведения оперативного анализа данных необходимо, чтобы таблицы модели были объединены в один из типов схем.
Ниже в примере показана модель данных для анализа выполнения договорных обязательств перед покупателями (рис. 4).
ЕШ КОД_ТОВ Щ КО/ЩОГ Ш Б11т-КОЛ_СЛТР Ш МесяцОтг
Н номер месяца Ш название месяца
Рисунок 4 - Модель данных в Power Pivot MS Excel 2013
Модель включает две таблицы фактов ПланПоставок и Отгружено, которые имеют одинаковые таблицы измерений. Вариант отчета сводной таблицы (рис. 5), построенной на основе представленной модели данных наглядно демонстрирует возможности автоматического агрегирования данных разных таблиц фактов. Агрегирование выполнено как по сумме заказанных товаров, так и по сумме отгруженных товаров по покупателям и договорам за выбранный период. Таким образом, одновременно используются две таблицы фактов и связанные с ними измерения, отображаемые в двух пере-
№ 2 (42) - 2015
125
Проблемы экономики и менеджмента
секающихся схемах типа «снежинка».
Представленная сводная таблица содержит поля из четырех разных таблиц. Эта задача легко решается благодаря созданным связям между таблицами. Если связи между таблицами и представлениями существовали в базе данных, они будут импортированы вместе с таблицами в модель. Таким образом, анализ данных в сводных таблицах Excel обеспечивает агрегирование данных в соответствии с табличной моделью, как по ветви, ведущей к планам поставок, так и по ветви, ведущей к отгрузкам при непосредственном обращении к данным соответствующих таблиц.
Рисунок 5 - Отчет сводной таблицы для анализа по договорам
Выводы. Табличная модель данных Excel 2013, соответствующая реляционному представлению данных в базах, позволяет анализировать большие объемы данных в локальных системах, обеспечивая пользователя наглядным и понятным представлением анализируемых фактов и измерений. Использование представлений для таблиц фактов, выполняющихся на стороне мощного источника данных, позволяет сократить время на обновление данных модели и объем требуемой памяти при сохранении гибкости аналитической модели. Агрегация и фильтрация данных в представлениях обеспечат скорость получения в Excel актуальных для анализа данных и более быстрые ответы на запросы аналитика. Модель, включающая несколько таблиц фактов, имеющих общие измерения, позволяет выполнять в сводных таблицах агрегирование различных фактов, обеспечивая сравнительный анализ различных показателей.
Список литературы:
1. Бекаревич Ю.Б., Пушкина Н.В. Самоучитель Microsoft Access 2013. (Глава 8. Оперативный анализ данных реляционной базы Access). - СПб.: Изд-во БХВ-Санкт-Петербург, 2014. - 450 с.
2. Бекаревич Ю.Б., Пушкина Н.В. Облачные веб-приложения для удаленной интерактивной работы пользователей с базами данных в экономической сфере // Проблемы экономики и менеджмента. - Ижевск, 2014. - № 1 (29). -С. 98-102.
3. Бекаревич Ю.Б., Пушкина Н.В. Технологии оперативного анализа данных. -Учебное пособие. - СПб.: Изд-во СПбГЭУ, - 2013. - 109 с.
4. Бекаревич Ю.Б., Пушкина Н.В. Оперативный анализ данных средствами офисных приложений Microsoft // Информационные технологии в бизнесе: материалы 8-й междунар. науч.-практ. конф. 19-20 июня 2013 г./ под ред. В.В. Трофимова и В.Ф. Минакова. - СПб.: Изд-во Инфо-да, 2013. - С. 13-17.
126
№ 2 (42) - 2015
