Создание витрины данных в excel

Обновлено: 02.07.2024

Хочу попросить совета у мудрых, коим удалось на практике реализовать концепцию витрин данных (Data Mart), т.к. сейчас передо мной встала именно эта задача. А опыт решения напрочь отсутствует.
Инструментарий в моем распоряжении пока такой: SQL-Server (включая Analysis Services) и MS Office. На SQL-Server находится корпоративная БД, есть несколько кубиков, и тьма представлений и хранимых процедур, собирающих отчеты для конечных пользователей. Визуализаций отчетов – в Экселе.

Хочется придти к известной 3х-уровневой схеме:
1. Реляционная база данных (нормализованная)
2. Витрины данных (денормализованные)
3. Отчеты конкретных пользователей

При помощи витрин хочется решить следующие задачи:
1. Дать определения основным бизнес-терминам (что такое продажи, клиенты, каналы сбыта и т.д.).
2. Ограничить тяжелый SQL-кодинг уровнем витрин, в идеальном варианте – чтобы «аналитик» мог формировать отчеты из витрин вообще не прибегая к SQL.

Это теория. На практике появляются проблемы…
Физически я понимаю витрины как набор тематических представлений, например, «продажи», «работа с клиентами» (звонки, встречи и т.д.), «персонал» и так далее. Не возникает проблем собрать на их основе одноименные кубики и отдать их «аналитикам» на растерзание…
Однако любой управленческий отчет есть синтез плохо совместимых данных. Например, руководителю наверняка захочется в одном отчете увидеть в разрезе по филиалам показатели по продажам, по работе с клиентами, по персоналу и т.д. И вот тут я уже не представляю себе инструментарий, при помощи которого «аналитик» (не знающий SQL) может скрестить данные из нескольких представлений (кубов). Есть несколько идей, как организовать связку "Витрины-Отчеты", например:
1. Подключать кубы к Экселю и в нем формулами (либо кат-энд-пейстом) собирать нужные отчеты. При этом отчеты получатся очень громоздкими и тяжело поддерживаемыми (например, формулы в Экселе будут постоянно съезжать), зато в качестве «аналитика» можно использовать достаточно дешевого специалиста по экселю.
2. Подключать кубы к Аксесу и конструктором запросов собирать нужные отчеты. Тут уже требуется специалист понимающий принципы БД.
3. Собирать отчеты в SQL на уровне SQL Server’а. Самый быстрый и простой для меня способ, однако в перспективе означает армию SQL-программистов на поддержке отчетов

Как на практике выглядит цепочка БД-Витрины-Отчеты? Какие специалисты и с каким набором компетенций должны обслуживать связку «Витрины-Отчеты»? И какой оптимальный для этого инструментарий?

Технологии Big Data создавались в качестве ответа на вопрос «как обработать много данных». А что делать, если объем информации не является единственной проблемой? В промышленности и прочих серьезных применениях часто приходится иметь дело с большими данными сложной и переменной структуры, разрозненными массивами информации. Встречаются задачи, способ решения которых наперед не известен, и аналитику необходимы средства исследования исходных данных или результатов вычислений на их основе без привлечения программиста. Нужны инструменты, сочетающие функциональную мощь систем BI (а лучше – превосходящие ее) со способностью к обработке огромных объемов информации.

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

Для рассказа нам понадобится простой пример сложной задачи. Рассмотрим некий промышленный комплекс, обладающий огромным количеством оборудования, обвешанного различными датчиками и сенсорами, регулярно сообщающими сведения о его состоянии. Для простоты рассмотрим только два агрегата, котел и резервуар, и три датчика: температуры котла и резервуара, а также давления в котле. Эти датчики контролируются АСУ разных производителей и выдают информацию в разные хранилища: сведения о температуре и давлении в котле поступают в HBase, а данные о температуре в резервуаре пишутся в лог-файлы, расположенные в HDFS. Следующая схема иллюстрирует процесс сбора данных.

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

Сущность	Порядок числа записей	Тип хранилища
Единицы оборудования	Тысячи	Мастер-данные
Датчики, сенсоры	Сотни тысяч	БД PostgreSQL
Показания датчиков	Десятки миллиардов в год (вопрос глубины хранения в этой статье не ставим)	Файлы в HDFS, HBase

Способы хранения для данных разных типов зависят от их объема, структуры и требуемого режима доступа. В данном случае мы выбрали именно такие средства для создания «разнобоя», но и на реальных предприятиях чаще всего нет возможности свободно их выбирать – все зависит от сложившегося ИТ-ландшафта. Аналитической же системе нужно собрать весь «зоопарк» под одной крышей.

Какие единицы маслонаполненного оборудования работали при температуре выше 300 градусов за последнюю неделю?
Какое оборудование находится в состоянии, выходящем за пределы рабочего диапазона?

Итак, наш аналитик будет формулировать запросы в привычных ему терминах, и получать в ответ наборы данных – независимо от того, из какого источника эти данные извлечены. Рассмотрим пример простого запроса, на который можно найти ответ в нашем наборе информации. Пусть аналитик интересуется оборудованием, установленные на котором сенсоры одновременно измерили температуру больше 400 0 и давление больше 5 мПа в течение заданного периода времени. В этой фразе мы выделили жирным слова, соответствующие сущностям информационной модели: оборудование, сенсор, измерение. Курсивом выделены атрибуты и связи этих сущностей. Наш запрос можно представить в виде такого графа (под каждым типом данных мы указали хранилище, в котором они находятся):

При взгляде на этот граф становится понятной схема выполнения запроса. Сначала нужно отфильтровать измерения температуры за заданный период со значением больше 400 0 C, и измерения давления со значением больше 5 мПа; затем нужно найти среди них те, которые выполнены сенсорами, установленные на одной и той же единице оборудования, и при этом выполнены одновременно. Именно так и будет действовать витрина данных.
Схема нашей системы будет такой:

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

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

2. В нашем примере данные одного и того же типа (измерения температуры) хранятся одновременно в двух разных источниках – HBase и текстовом файле HDFS. Однако для выполнения приведенного запроса обращаться к файлу не нужно, т.к. в нем заведомо нет полезной информации: ведь в файле хранятся измерения температуры резервуара, а давление в резервуарах не измеряется. Этот момент дает представление о том, как должен работать оптимизатор выполнения запросов.

3. Витрина данных не только компонует и связывает информацию из различных источников, но и делает логические выводы на ней в соответствии с заданными правилами. Автоматизация получения логических выводов – одно из главных практических преимуществ семантики. В нашем примере с помощью правил решена проблема получения выводов о состоянии устройства на основе данных измерений. Температура и давление содержатся в двух разных сущностях типа «Измерение», а для описания состояния устройства необходимо их объединить. Логические правила применяются к содержимому временного графа результатов, и порождают в нем новую информацию, которая отсутствовала в источниках.

4. В качестве источников данных могут выступать не только хранилища, но и сервисы. В нашем примере мы спрятали за сервисом расчет предпосылок к возникновению аварийного состояния при помощи одного из алгоритмов Spark MLlib. Этот сервис получает на вход информацию о состоянии устройства, и оценивает его с точки зрения наличия предпосылок к аварии (для обучения использованы ретроспективные данные о том, какие условия предшествовали реально случившимся авариям; в качестве исходных данных нужно рассматривать не только мгновенные значения физических характеристик устройства, но и элементы основных данных – например, степень его износа).
Эта возможность очень важна, так как позволяет аналитику самому выполнять запуск расчетных модулей, подготовленных программистами, передавая им на вход различные массивы данных. В этом случае аналитик будет работать уже не с исходными данными, а с результатами вычислений на их основе.

5. Аналитик строит запросы при помощи интерфейсов нашей Системы управления знаниями, среди которых – как несколько вариантов формального конструктора запросов, так и интерфейс поиска на контролируемом естественном языке. На следующем рисунке слева показана форма построения запроса на контролируемом языке, а справа – пример результатов другого запроса.

Конечно, в любой бочке меда найдется ложка дегтя. В данном случае она состоит в том, что на действительно больших данных приведенная архитектура будет работать не так уж быстро. С другой стороны, при работе аналитика в режиме «свободного поиска» решения проблем быстродействие для него обычно не принципиально; в любом случае, витрина будет выдавать результаты куда быстрее, чем программист, к которому при ее отсутствии аналитику придется обращаться за ручным выполнением каждого своего запроса.

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.

Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.

Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.

Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,

Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.

Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.

Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.

Начало работы

Во-первых, вам нужно получить некоторые данные.

В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.

В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.

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

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

Выберите одну или несколько таблиц и нажмите кнопку Загрузить.

Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).

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

Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.

Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.

Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

Советы по уменьшению размера модели данных см. в Excel и Power Pivot.

Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.

Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.

Создание связей между таблицами

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

Перейдите на > PowerPivot .

На вкладке Главная выберите представление диаграммы.

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

Затем перетащите поле первичного ключа из одной таблицы в следующую. Ниже приведен пример представления диаграммы таблиц наших учащихся:

Мы создали следующие ссылки:

tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося

Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".

tbl_Semesters | ИД семестра > tbl_Grades | Семестр

tbl_Classes | Номер > tbl_Grades | Номер класса

Имена полей не должны быть одинаковыми для создания связи, но они должны быть одного типа данных.

Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.

Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).

Создание и создание с помощью модели данных для создания сводная диаграмма

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

В Power Pivot, перейдите вуправление .

На вкладке Главная выберите вкладку "PivotTable".

Выберите место для размещения таблицы: новый или текущий.

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

Добавление имеющихся несвязанных данных в модель данных

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

Добавьте данные одним из следующих способов.

Щелкните Power Pivot > Добавить в модель данных.

Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в Power Pivot таблицу

В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Размышления о разработке программного обеспечения и информационных систем

То, что действительно важно, но чему нигде не учат

Обычно, говоря о структуре реляционной базы данных, имеют в виду нечто такое:

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

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

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

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

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

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

А вот читать данные из витрины очень просто. В ней нет ничего лишнего. Только то, что необходимо при решении конкретной задачи. А потребуется другой отчёт - сделаем другую витрину!

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

Таким образом, весь процесс работы с данными выглядит следующим образом.

Исходные данные поступают в нормализованную структуру в виде справочной и оперативной информации.

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

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

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

В заключение дам несколько ремарок.

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

Соответственно, в структуре витрины коды классификаторов должны присутствовать. Но если в вашей конкретной системе всё не так однозначно, то вы вправе сами решить, нужны ли вам идентификаторы исходных данных в витрине, или нет.

Во-вторых, в приведённом примере витрина не содержит явных ссылок на другие таблицы. Её структура предельно простая - одна единственная таблица. Но это не значит, что при определённых условиях я не создам витрину на основе нескольких взаимосвязанных таблиц. Такая структура может быть сложной. Можете сами поискать примеры по ключевым словам "снежинка", "звезда" или "созвездие".

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

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

В-пятых, всё сказанное выше хорошо укладывается в шаблон CQRS (Command/Query Responsibility Segregation - разделение ответственности команд и запросов). В рамках этого подхода в системе есть две модели: первая реализует команды и позволяет соблюдать бизнес-процессы, а вторая просто читает данные и передаёт их потребителям. Шаблон CQRS часто используется при предметно-ориентированном проектировании (Domain-Driven Design, DDD).

Читайте также: