CC BY
100
МА ТЕМА ТИЧЕСКИЕ И ИНСТРУМЕНТАЛЬНЫЕ МЕТОДЫ ЭКОНОМИКИ
УДК 681.3.01
Ю.Б. Бекаревич
канд. техн. наук, доцент, кафедра экономической кибернетики, ФГБОУВПО «Санкт-Петербургский государственный университет»
Н.В. Пушкина
канд. техн. наук, доцент, кафедра информатики, ФГБОУ ВПО ««Санкт-Петербургский государственный экономический университет»
ВОПРОСЫ ЭФФЕКТИВНОСТИ ТАБЛИЧНОЙ МОДЕЛИ АНАЛИЗА ДАННЫХ
Аннотация. Рассмотрены подходы к построению эффективной табличной модели оперативного анализа данных. Предлагается, сохраняя преимущества нормализованной базы как источника данных модели анализа, вводить для построения модели суррогатные ключи только для таблиц измерений с реальным составным ключом. Такая реструктуризация сохраняет возможности обеспечения целостности базы, необходимой для поддержания источника анализа в актуальном состоянии. Агрегация данных на этапе подготовки модели обеспечивает резкое снижение объемов памяти и времени ответа на решение задач анализа.
Ключевые слова: оперативной анализ данных, эффективность табличной модели Excel, Power Pivot, агрегирование данных, источники данных анализа, реляционная база данных.
Yu.B. Bekarevich, Saint-Petersburg State University
N.V. Pushkina, Saint-Petersburg State University of Economics
QUESTIONS OF EFFICIENCY TABULAR MODELS OF DATA ANALYSIS
Abstract. Consider approaches to build an effective tabular model of data operational analysis. Is proposed, retaining the benefits of a normalized base as a source of data analysis models, to build models enter surrogate keys to only for the dimension tables with real composite key. Such restructuring retains the possibility of ensuring the integrity of the base necessary to maintain the source of the analysis into current state. The aggregation of data at the stage of the model provides a sharp decrease in volumes memory and response times at solving of tasks of analysis.
Keywords: operational analysis of data, efficiency of tabular model Excel, Power Pivot, data aggregation, data sources for analysis, a relational database.
Для своевременного принятия управленческих решений актуальна оперативность получения данных, позволяющих объективно представить ситуацию. Основным источником таких данных в большинстве предприятий являются транзакционные базы, в которых накапливаются большие объемы данных управленческого учета. Для оперативного анализа данных широко используются сводные таблицы и диаграммы, в том числе такого популярного клиентского программного продукта, как Microsoft Excel.
В приложении Excel обеспечивается подключение к корпоративной базе пред-
приятия и построение табличной модели данных, которая практически повторяет модель данных, реализованную в реляционной базе, и дает аналитику удобное представление данных в виде таблиц фактов и связанных с ними таблиц измерений. Подключение возможно к таким источникам реляционных данных как Microsoft SQL Server, Access, SQL Microsoft Azure, хранилища данных Microsoft SQL Server. Допустимо использование и других типов реляционных источников данных, таких как Oracle, OLE DB, ODBC, Teradata.
На больших предприятиях для анализа данных часто используются в качестве источника данных специально предназначенные хранилища. Однако для предприятий создание хранилищ слишком дорогостоящее и трудоемкое мероприятие, требующее дополнительных аппаратных и программных ресурсов, а также привлечения квалифицированных специалистов соответствующего профиля. Более доступным источником данных для анализа является оперативная база предприятия. На ее основе средствами Excel строится модель данных анализа.
Табличная модель может включать несколько таблиц фактов, которые ссылаются на одни и те же или разные таблицы измерений. Взаимосвязи таблиц фактов и измерений могут образовывать схему «звезда» или схему «снежинка» [1]. Надстройка Power Pivot в Excel обеспечивает графическое представление табличной модели, аналогичное схемам данных в реляционных базах.
Для анализа данных в сводных таблицах Excel, использующих в качестве источника данных реляционную базу, производится построение табличной модели анализа на основе выбранных из базы таблиц и связей между ними. Построенная таким образом модель соответствует схеме данных базы. Для того, чтобы модель была построена автоматически при импорте таблиц базы необходимо соблюдение ряда условий. Поскольку в табличных моделях не реализуется связь таблиц по составным ключам, в модели все ключи таблиц измерений должны быть простыми. Таблицы фактов могут иметь составные ключи, так как они располагаются на нижнем уровне иерархии и не имеют подчиненных таблиц, с которыми надо устанавливать связь. В том случае, если таблицы измерений имеют составной ключ, для них необходимо ввести суррогатные ключи. При этом в базе данных для обеспечения целостности для реальных составных ключей таблиц измерений должны быть определены уникальные индексы. Для связи таблиц суррогатный ключ таблицы измерения должен быть включен в подчиненную таблицу в качестве внешнего ключа.
Заметим, что часто разработчики баз данных используют для всех таблиц суррогатные ключи вне зависимости от того, являются ли они простыми или составными. При построении модели целесообразно ограничиться введением суррогатных ключей только в названном выше случае. Это позволяет сохранить связи таблиц базы данных по реальным ключам там, где это возможно, и избежать хранения дополнительных индексов таблиц в базе.
Ниже приведен пример базы, содержащей данные о договорах и отгрузках товаров покупателям по накладным (рис. 1). Все таблицы этой базы имеют реальные клю-
чи. Только для таблицы НАКЛАДНАЯ, имеющей реальный составной ключ, введен суррогатный ключ КОД_НАКЛ с типом данных Счетчик. При этом для реального ключа Номер накладной + Код склада определен уникальный индекс, не позволяющий вводить одну накладную дважды. Обратим внимание, что попутным эффектом от введения суррогатного ключа является исключение составного ключа связи Номер накладной + Код склада из таблицы ОТГРУЗКА, что обеспечивает исключение повторяемости данных в подчиненной таблице.
Рисунок 1 - Схема данных базы с суррогатным ключом в таблице НАКЛАДНАЯ
В сводных таблицах реализованы средства работы с моделями, включающими несколько таблиц фактов [1]. В общем случае таблицы фактов могут иметь разные или общие таблицы измерений. Связанные с таблицами фактов измерения, могут быть представлены одной таблицей или несколькими иерархически связанными таблицами. В схеме на рисунке 1 имеются две таблицы фактов ПОСТАВКА_ПЛАН и ОТГРУЗКА, которые подчинены нескольким иерархически связанным таблицам измерений. Например, таблица фактов ПОСТАВКА_ПЛАН связана с таблицами измерений ДОГОВОР-ПОКУПАТЕЛЬ, или ДОГОВОР-ИСПОЛНИТЕЛЬ. При этом возможно получение агрегированных данных по разным уровням измерения.
Если импортированные таблицы базы содержат не используемые при анализе атрибуты, то для хранения больших объемов данных модели потребуется файл и оперативная память значительных размеров. Книги Excel, содержащие большие модели и визуализации данных, могут превысить максимальный размер файла Excel, который до-
пустим при работе в среде SharePoint Online и Office Online в Office 365. Необходимость в большом объеме памяти негативно влияет на работу одновременно выполняющихся приложений, использующих те же системные ресурсы. Большие модели данных усложняют анализ данных и содержат громоздкие списки полей таблиц модели. При выполнении анализа данных на моделях, в которых представлены данные нормализованных таблиц реляционной базы, требуется вычисление агрегатов, на основе представленных детальных данных. Это приводит к значительным затратам времени при получении результатов анализа. Все это делает актуальным повышение эффективности модели анализа данных, обеспечивающей минимизацию затрат ресурсов.
Для эффективного использования памяти и получения быстрых ответов на вопросы аналитика при разработке больших табличных моделей целесообразно включать только нужные для анализа поля таблиц и создавать запросы, на основе которых в модели должны формироваться таблицы с наиболее часто используемыми агрегатами.
Сокращение числа полей в таблицах модели может заметно уменьшить объем модели. Кроме того, оставляя в таблице только те поля, которые используются при анализе, значительно упрощается процесс построения сводных отчетов. Сокращение числа полей в таблицах модели может быть выполнено средствами надстройки Excel - Power Pivot, не прибегая к изменению запроса SQL для заполнения таблицы.
Объединение полей различных взаимосвязанных таблиц измерения в одной таблице нижнего уровня иерархии измерения также улучшает характеристики модели. Для такого объединения полей таблиц можно в редакторе изменить запрос таблицы нижнего уровня иерархии измерения, который автоматически создается при импорте. При этом в инструкции SELECT нужно модифицировать список выбираемых полей и используемых таблиц. Заметим, что для полей объединенной таблицы целесообразно создать иерархию. Использование иерархий упрощает построение сводных отчетов.
Поясним рассмотренные подходы к повышению эффективности модели на приведенном выше примере. Нужные реквизиты из таблицы ПОКУПАТЕЛЬ можно включить в таблицу ДОГОВОР. Включение повторяющихся значений из таблицы ПОКУПАТЕЛЬ за счет сжатия данных практически не приведет к увеличению размеров таблицы ДОГОВОР. При этом таблица ПОКУПАТЕЛЬ удаляется из модели. Из таблицы ДОГОВОР исключается поле связи с таблицей ПОКУПАТЕЛЬ. Аналогичным образом можно в таблицу ДОГОВОР включить нужные поля их таблицы ИСПОЛНИТЕЛЬ. Для удобства анализа данных в таблице ДОГОВОР могут быть определены иерархии по наименованию покупателя и номеру договора, фамилии исполнителя и номеру договора, должности и фамилии исполнителя.
Подготовка таблиц модели с агрегированными данными может быть выполнена с помощью запросов на выборку или представлений, в которых определяется, что нужно извлечь из реляционной базы данных. Создать запросы можно как на стороне базы данных, так и на стороне клиента в Power Pivot. Запросы будут формировать содержимое таблиц с агрегированными данными, являющихся таблицами фактов.
Если запросы создать в базе данных, там же можно определить и связи запроса с
таблицами базы. При импорте таких запросов и связанных с ними таблиц измерений в книгу Excel на их основе будет автоматически получена более компактная модель данных.
Создание запросов с агрегированными данными в Power Pivot может быть выполнено при получении внешних данных из базы или путем изменения свойств таблицы, ранее импортированной в книгу. В первом случае нужно выбрать способ импорта реляционных данных с подготовкой запроса, определяющего данные для импорта, используя текстовый редактор или конструктор. Во втором случае для агрегирования данных предлагается модификация запроса в свойствах таблицы. Для любой таблицы, импортированной в книгу, автоматически создается запрос, в котором по умолчанию записана инструкция, обеспечивающая заполнение таблицы модели данными из соответствующей таблицы подключенного источника. При изменении запроса можно сформировать новую таблицу с агрегированными данными, в том числе получаемыми в результате объединения данных нескольких таблиц. В этом случае необходимо в модели данных установить связь вновь полученной таблицы с нужными таблицами измерений. Для создания запросов используется редактор Power Pivot, связанный со свойствами таблицы.
Для рассмотренного выше примера приведем модифицированный запрос, который формирует новую таблицу с агрегированными данными, содержащую суммарные количества и суммы за отгруженный товар помесячно.
SELECT ОТГРУЗКА.КОД_ТОВ, Month([ДАТА_ОТГ]) AS [Месяц отгрузки], SUM(ОТГРУЗКА.КОЛ_ОТГР) AS ^ш-КОЛ_ОТГР],
SUM(ОТГРУЗКА.СУММА_ОТГР) AS ^ш-СУММА_ОТГР]
FROM НАКЛАДНАЯ INNER JOIN ОТГРУЗКА ON НАКЛАДНАЯ.КОД_НАКЛ = ОТГРУЗКА.КОД_НАКЛ
GROUP BY ОТГРУЗКА.КОД_ТОВ, Month([ДАТА_ОТГ]);
Аналогично может быть сформирован запрос для получения таблицы с агрегированными данными о плановых отгрузках товаров. Пример модели данных с таблицами агрегированных данных по планируемым и фактическим отгрузкам, полученных в результате выполнения запросов, приведен на рисунке 2. В модели новым таблицам соответственно присвоены имена План и Факт.
Так в рассматриваемом примере (рис. 3) при использовании для объединения таблиц План и Факт по умолчанию оператора FULL JOIN, в сводном отчете представлены не только плановые показатели, для которых имеются факты отгрузки (строки 6, 11, 15), но и плановые показатели, для которых нет фактов (строки 7-9, 13, 18), и факты для отсутствующих планов (строки 12, 16, 17, 20, 22).
Таким образом, в сводной таблице реализуется полное объединение между таблицами План и Факт. Для расчета долга в приведенном сводном отчете использовано Вычисляемое поле:
=SUM('План'[Sum-СУММА_ПОСТ])-SUM('Факт'[Sum-СУММА_ОТГР]).
Рисунок 2 - Модель данных в Power Pivot
Рисунок 3 - Сводный отчет при объединении таблиц фактов оператором FULL JOIN
Заметим, что при использовании таблиц с агрегированными данными ответ может быть получен мгновенно, так как не требуется вычисления агрегатов. Аналогичный результат может быть получен и просто по таблицам. Но при этом агрегирование будет выполняться при формировании полей сводной таблицы, поэтому может потребоваться значительное время.
Если необходимо отслеживать только выполнение плановых показателей, целесообразно создать запрос, в котором таблицы План и Факт объединяются оператором LEFT JOIN. На рисунке 4 приведен сводный отчет, построенный на таблице запроса Отклонение, в котором таблицы План и Факт объединяются с помощью LEFT JOIN и вычислено значение Отклонения.
В С D Е F G
номер месяца 1 Тт
Знамения Товар т Т001 ТОО 2 TOOÎ Т006 Общий итог
Сумма по плану Сумма по факту Отклонение 100 000р. 50 000р. 50 000р. 546120р. 500 000р. 110 960р. 546120р. 3S9 040р. 36 000р. 36 000р. 11S2120р. 160 960р. 1 021160р.
Рисунок 4 - Сводный отчет при использовании запроса с оператором LEFT JOIN
Выводы
При создании табличной модели анализа предлагается ограничиться введением суррогатных ключей только для таблиц измерений с составными ключами. Этого достаточно для построения модели, сохранения большинства связей нормализованной базы источника, и обеспечивает сокращение объема хранимых данных.
Для повышения эффективности процедуры анализа с уменьшением трудоемкости и временных затрат предусматривается предварительное агрегирование детальных данных путем создания новых таблиц модели на основе запросов. Кроме того, предлагается при импорте данных базы в табличную модель исключать заведомо не используемые при анализе атрибуты.
Список литературы:
1. Бекаревич Ю.Б., Пушкина Н.В. Особенности оперативного анализа данных при использовании табличной модели // Проблемы экономики и менеджмента. -Ижевск, 2015. - № 2 (42). - С. 120-126.
2. Бекаревич Ю.Б., Пушкина Н.В. Самоучитель Microsoft Access 2013. - СПб.: Изд-во БХВ -С анкт-Петер бур г, 2014. - 450 с.
3. Бекаревич Ю.Б., Пушкина Н.В. Технологии оперативного анализа данных: учебное пособие. - СПб.: Изд-во СПбГЭУ, 2013. - 109 с.
