Создание витрины данных в excel
Модель данных позволяет интегрировать данные из нескольких таблиц, эффективно построение источника реляционных данных 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.
Если коротко, то витрины (витрина от англ. data mart) – это набор структурированных данных. Обычно это данные по определенной теме или задаче в компании. Например, витрина с данными о заказчиках для отдела маркетинга может содержать подробные данные по договорам, истории заказов и поставок, оплатах, звонках и адресах доставки. Ничего лишнего, только нужные и актуальные очищенные данные, полученные из других ИС предприятия. Таких витрин даже на одном предприятии может быть множество.
Чаще всего с помощью витрин анализируют данные и строят ML-модели. Также витрины могут использоваться на предприятиях в качестве мастер-данных, например как справочники. Помимо этого, витрина может выступать периферическим узлом в сетях обмена данными между различными участниками. Примером концепции построения таких сетей для обмена данными является Data mesh (вот тут есть хороший перевод статьи по теме Хабр).
Типовой проект внедрения витрин состоит из технологической и прикладной частей. Если для решения технологических задач брать готовый инструмент, а не писать систему с нуля, то можно заложить больше ресурсов на прикладные задачи, которым зачастую уделяют незаслуженно мало внимания. Для B2B и других проектов, предполагающих внедрение множества витрин у различных заказчиков, готовый инструмент позволит существенно снизить технические риски, уменьшить затраты и сократить сроки внедрения.
Что требуется от витрины?
Сразу хотелось бы ответить на вопрос: а почему нельзя просто взять любую из существующих СУБД и сразу закрыть технологические задачи?
На самом деле, можно, но, как обычно, всё дело в деталях, а точнее в требованиях к витринам, которые нередко упускаются из вида и могут болезненно проявиться уже на поздних этапах, например при ОПЭ:
Изоляция данных. Обновление данных, например загрузка справочника, может быть растянуто во времени, при этом до окончания загрузки текущая версия справочника должна быть полностью доступна с исключением «грязного чтения» загружаемой версии.
Гарантии атомарности операций при обновлении данных. В случае сбоев и ошибок загрузки данных витрина остаётся в состоянии, которое предшествовало сбойному процессу. Другими словами, или данные обновляются полностью, или не обновляются вовсе, не оставляя следов сбойных операций.
Устойчивость к дубликатам изменений. Весьма сложно и дорого реализовывать во всех ИС-источниках данных выгрузку по принципу exactly-once. Наличие дублей одинаковых изменений объектов не должно приводить к нарушению логической целостности состояния витрин.
Системная темпоральность. Мало какая реляционная СУБД имеет функцию системной темпоральности «из коробки». Ведение системного времени и версионирование записей по системному времени позволяет сравнивать состояние данных витрины между двумя разными моментами времени или проводить «расследование», основываясь на данных, которые были в витрине в определенный момент в прошлом. Одним из вариантов обеспечения темпоральности является реализация SCD2 с ведением диапазонов сроков действия для версий записи.
Эффективное выполнение различных видов запросов: сравнительно редких и тяжелых аналитических запросов, затрагивающих большой объем данных (OLAP-нагрузка), и множества одновременных простых запросов (OLTP-нагрузка). Как правило, СУБД заточены на какой-то один вариант нагрузки: OLAP или OLTP.
Концепция
С середины 2020 года наша команда разрабатывает Систему, предназначенную для построения витрин данных. Начав с разработки прототипа, мы продолжили развивать функционал в рамках той же архитектуры. Сейчас это открытое программное обеспечение, которое мы используем при внедрениях витрин данных.
У нас в тех. проекте записано: «Простор – интеграционная система, обеспечивающая унифицированный интерфейс темпоральной реляционной СУБД к гетерогенному хранилищу данных». Гетерогенное хранилище позволяет использовать сильные стороны каждой из СУБД, входящих в состав хранилища, и не быть заложником недостатков одной из них.
В Просторе гетерогенное хранилище представлено такими СУБД:
Greenplum – аналитическая СУБД, предназначенная для OLAP-нагрузки. Хорошо горизонтально масштабируется, имеет высокий уровень поддержки стандарта SQL.
Clickhouse – аналитическая СУБД. Демонстрирует одни из лучших в классе показатели выполнения агрегационных запросов. Не полностью поддерживает SQL и имеет ряд иных ограничений при эксплуатации, например при изменении или удалении записей.
Tarantool – In-memory СУБД с персистентным хранением данных. Отличные показатели при OLTP-нагрузке (чтение отдельных записей). В кластерном режиме имеет ограничения по исполнению SQL-запросов.
PostgreSQL – всеми любимая реляционная СУБД. Хорошо держит OLTP-нагрузку, но горизонтально не масштабируется и, соответственно, не подходит для аналитических запросов с действительно большим объемом данных.
Состав СУБД хранилища данных можно изменять в зависимости от характера предполагаемой нагрузки или уже в процессе эксплуатации. Для небольших витрин можно использовать одну СУБД, например PostgreSQL. Для крупных витрин, содержащих большие объемы данных и предполагающих разнородные запросы, можно использовать различные сочетания, например Greenplum + Tarantool или Greenplum + Tarantool + Clickhouse.
Ядро системы – сервис, выполняющий роль координатора и диспетчера. Обеспечивает единый интерфейс доступа, маршрутизирует запросы, управляет процессами загрузки и выгрузки данных, контролирует целостность данных. Также ядро парсит входящие SQL-запросы и обогащает их до вида, готового к исполнению в той или иной СУБД. Непосредственно выполнением запросов занимаются СУБД хранилища.
Обмен большими объемами данных между витриной и поставщиками/потребителями этих данных выполняется через Kafka. Но если речь идет о небольших объемах данных (сотни записей), то загружать или читать данные можно напрямую через Ядро.
Ядро управляет специальными компонентами – коннекторами, предназначенными для массивно-параллельной загрузки данных из Kafka в СУБД хранилища и массивно-параллельной выгрузки данных в Kafka из СУБД хранилища.
С точки зрения пользователя
Если пользователем называть поставщика или потребителя данных, то с точки зрения такого «пользователя» Простор выглядит так:
Единый интерфейс доступа – JDBC 4.2. Подключиться к Простору можно как к обычной реляционной СУБД, например, используя SQL-клиент, в котором доступны все элементы логической модели и запросы к ним.
Единая логическая реляционная модель данных, скрывающая «под капотом» реальные физические модели данных СУБД хранилища.
При изменении логической модели данных автоматически изменяются и соответствующие физические модели в СУБД хранилища. Логическая модель – внешнее пользовательское представление модели данных витрины. Включает следующие логические сущности:
a. Логическая таблица (table) – для «пользователя» это обычная таблица, но с возможностью указать момент времени в прошлом, относительно которого требуется «наблюдать» данные таблицы.
Также для логической таблицы можно ограничить СУБД хранилища, в которых она будет физически расположена.
b. Логическое представление (view) – сохраненный именованный SQL-запрос, к которому можно выполнять запросы, также с возможностью указания момента времени «наблюдения» данных.
c. Логическое материализованное представление (materialized view) – необычная логическая таблица, новые или измененные данные в которую попадают автоматически на основании сохраненного запроса к другим логическим таблицам, расположенным в других СУБД хранилища. Особой возможностью запросов к логическим материализованным представлениям является автоматическое перенаправление такого запроса к исходным логическим таблицам, если отставание данных материализованного представления больше заданного предела. Материализованные представления позволяют реализовать более интересные варианты топологии витрины, в которых одна из СУБД исполняет роль отказоустойчивого мастера, а другая — содержит материализованные read-only-представления.
d. Логическая внешняя таблица – виртуальная таблица, по сути являющаяся указателем на источник или приёмник данных. Записывая или считывая данные из этой таблицы, можно управлять загрузкой и выгрузкой данных.
Язык запросов – подмножество стандарта SQL с дополнительными функциями и командами. Позволяет управлять состоянием, моделью данных и самими данными витрины. Запросы на выборку данных автоматически маршрутизируются в наиболее эффективную для их исполнения СУБД хранилища из доступных.
Отдельно стоит упомянуть механизм дельт, позволяющий «пользователю» указывать начало и конец логически целостной пачки изменений. Внешне этот механизм несколько напоминает ACID-транзакции, позволяя обрамить набор операций по изменению данных витрины командами начала дельты и ее окончания (комита).
Все изменения, выполненные в одной дельте, помечаются единой меткой времени комита дельты. Изменения данных, производимые в рамках открытой дельты, изолированы от пользовательских запросов с целью исключения «грязного чтения». Можно утверждать, что изменения в рамках дельты доступны для пользователя целиком и одномоментно или не доступны вовсе. Если необходимо, то открытую дельту можно откатить.
Неотъемлемой частью Простора как продукта является подробная открытая документация, обновляемая одновременно с выпуском каждого релиза. С перечнем базовых функций и примерами их использования можно ознакомиться по ссылке.
Заключение
Простор – система для построения витрин данных, доступная под лицензией Apache 2.0. Для тестирования и ознакомления с возможностями системы можно развернуть минимальную конфигурацию, где Простор использует в качестве хранилища только PostgreSQL. Инструкция по развертыванию доступна тут. Если объем данных для витрины не очень большой, то такая конфигурация может использоваться и для PROD.
Технологии 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. Аналитик строит запросы при помощи интерфейсов нашей Системы управления знаниями, среди которых – как несколько вариантов формального конструктора запросов, так и интерфейс поиска на контролируемом естественном языке. На следующем рисунке слева показана форма построения запроса на контролируемом языке, а справа – пример результатов другого запроса.
Конечно, в любой бочке меда найдется ложка дегтя. В данном случае она состоит в том, что на действительно больших данных приведенная архитектура будет работать не так уж быстро. С другой стороны, при работе аналитика в режиме «свободного поиска» решения проблем быстродействие для него обычно не принципиально; в любом случае, витрина будет выдавать результаты куда быстрее, чем программист, к которому при ее отсутствии аналитику придется обращаться за ручным выполнением каждого своего запроса.
Сегодня хочу рассказать историю проекта по запуску небольшого хранилища данных DWH одной из известных российских инвестиционных площадок по финансированию малого и среднего бизнеса.
Два года назад в России стал довольно активно развиваться рынок краудлендинга. Краудлендинг — это процесс, при котором группа инвесторов дает займы компаниям. Но изюминка заключается в том, что в роли инвесторов выступают обычные люди, такие как мы с вами. А заемщиками выступают обычные организации типа ИП или ООО, которые в основном управляют либо розничным бизнесом, либо e-commerce. Соответственно была запущена инвестиционная платформа, которая сводила вместе инвесторов и заемщиков.
Не стоит и говорить, что сразу после запуска площадки руководству потребовалась управленческая и аналитическая отчетность для управления бизнесом. Была поставлена цель быстро настроить предоставление ежедневных оперативных ключевых показателей для кампании.
Сам WEB-сайт инвестиционной площадки и все его внутренние процессы, или так называемый «кредитный конвейер», крутились на БД PostgreSQL. Кредитный конвейер — это собирательное понятие, описывающее весь процесс выдачи компаниями кредитов, начиная от подачи заемщиком заявки на займ и заканчивая погашением кредита.
Стек технологий, который был выбран для внедрения этого проекта, был БД MS SQL + SQL Server Analysis Service + сводные таблицы OLAP в Excel (BI система) для разработки ежедневных кубов OLAP.
Перед дальнейшим рассказом хотел бы расшифровать некоторые термины:
- OLAP — это online analytical processing, он же — оперативный анализ данных. Для обычного потребителя отчета это выглядит как сводная таблица в Excel. Но подключается она к SQL Server Analysis Service для отбора данных и отображения их в Excel.
- DWH – аббревиатура от Data Warehouse, т.е. хранилище данных. Некая база данных, где собирается вся информация о нашем проекте.
- ETL-процесс — Extraction-Transformation-Loading т.е. Извлечение, Обработка, Загрузка. Подразумевается процесс загрузки данных из одной или нескольких исходных систем в DWH. Этот процесс извлекает информацию из внешних систем-источников, трансформирует ее, очищают и загружают в единое хранилище.
- BI-система – инструмент аналитики и визуализации показателей. Business Intelligence (BI) позволяет компаниям собрать информацию из различных источников, проанализировать ее и представить в наиболее понятном и удобном для восприятия виде. С помощью различных программ и инструментов BI специалисты анализируют большие массивы данных, на основании которых разрабатывают и автоматизируют отчетность и дашборды. На основе полученных данных сотрудники принимают ключевые для развития компании решения
Важно понимать, что под сводными таблицами в Excel понимается не просто статичный отчет, а полноценная BI-система для аналитики данных. Набор показателей и фильтров, которыми можно «вращать» в реальном времени. Добавлять/удалять показатели, настраивать фильтры и смотреть статистику в различных разрезах — все это называется общим термином аналитика показателей.
Круг задач, которые нужно решить, был следующим:
- Настроить ежедневную выгрузку данных из БД PostgreSQL в хранилище DWH.
- Собрать структуры данных и детальные витрины показателей Data Marts.
- На основе этих витрин собрать кубы на MS Analysis Service и при этом посчитать порядка 30 показателей и измерений.
- Собрать сводную таблицу с помощью которого можно смотреть показатели и проводить аналитику. Добавлять показатели, фильтры и разрезы для получения нужной статистики.
Архитектура для данного проекта была принята следующая:
Схема движения потоков данных в DWH
Поскольку во всем проекте было вовлечено минимум сотрудников, то процесс разработки протекал максимально эффективно. Два бизнес-заказчика предоставляли требования к отчетности, а владелец источника предоставлял доступы и описания как к различным таблицам, так и к структурам данных. Я же выполнял роль и разработчика, и аналитика в одном лице. За счет того, что каждый сотрудник выполнял свою роль по максимуму, коммуникация внутри данного круга лиц была очень быстрой и эффективной. А сам процесс разворачивания хранилища протекал очень продуктивно.
Что было сделано в первую очередь?
В первую очередь был настроен обмен данными с хранилищем через ETL-процессы. В MS SQL были созданы коннекторы, которые могли подключаться источнику данных. Под источником понимается другая БД PostgreSQL. Также при создании коннектора установили драйверы для PostgreSQL и настроили системный DSN.
Пример создания подключения к базе
Создание «связного сервиса» в MS SQL
В MS SQL есть возможность прямого подключения к другим БД, так называемый «Связный сервис». В других базах она может называться по-разному (например, в oracle — это db-link), но суть одна и та же. Установив на сервере драйверы для подключения к PostgreSQL можно напрямую из MS SQL выполнять запросы в другую БД и выгружать данные. Соответственно, создав небольшую SQL-процедуру очистки и загрузки данных, как на примере ниже, можно выгружать по линкам любые таблицы из любых источников.
Пример самой простой процедуры полной загрузки таблицы из источника
Также стоит рассказать, про так называемый, сервис «Агент SQL сервер» в MS SQL позволяющий создавать планировщик заданий, запускающийся по расписанию (обычно ночью) и запускающий эти процедуры для загрузки данных в хранилище. Таким образом мы получили набор таблиц, структура которых идентична источнику, на основе которых далее можно выполнять обработку и строить витрины.
Пример создания планировщика заданий на MS SQL c запуском процедур и кубов OLAP
Витрины данных (Data Marts)
Следующим большим шагом проекта было создание витрин данных. В самой базе было создано всего 3 основные смысловые схемы:
stg — от слова STAGE. В ней лежат таблицы структура данных приближенная к источникам. Туда загружаются данные напрямую из источника.
dim — от слова dimensions. Схема, в которой хранятся справочники.
dds — от слова detail data stage т.е. детальный слой данных. В ней собираются уже обработанные данные взятые из stg.
Для общего понимания обработка dds выглядит как набор процедур и функций порядка 2000 строк чистого SQL кода. Важно заметить, что динамический SQL не использовался, что сильно упрощало жизнь.
Ну и один из последних шагов — создание кубов на MS SQL Analysis Service. Выполнялось все это с помощью инструмента Visual Studio. В них создается физическая и логическая структура сущностей на основе витрин DDS. А также настраиваются показатели и измерения с бизнес понятными переводом для потребителей отчетов.
Пример формы создания логической структуры данных OLAP кубов
Пример настройки связей для фактовых сущностей в кубах
Пример настройки вычисляемых полей в OLAP
Важно заметить, что при построении таких хранилищ и отчетности очень важным моментом является стабилизация отчетности в плане качества данных. Тестирование показателей и устранение расхождений также отнимает большое количество времени и трудозатрат при построении витрин и показателей. Несмотря на тот факт, что вся настройка OLAP выполняется в интерфейсах, сам инструмент является очень простыми и удобным в разработке.
Также очень важно понимать, что кубы OLAP очень удобно использовать на небольших хранилищах не более чем в несколько терабайт. Если речь идет о построении больших хранилищ, например для ритейла, телекоммуникаций или банковской сферы, то объемы данных начинают исчисляться десятками терабайт. Большие объемы требуют полноценные BI-решения с наличием высокопроизводительных серверов. Например, такие продукты как Oracle BI, SAS BI являются полноценными Enterprise решениями стоимость которых начинаются с цифры с нескольким количеством нулей и исчисляется в долларах.
В общем была создана система аналитики на основе OLAP со стартовым набором показателей, которая сильно помогла в принятии дальнейших управленческих решений для развития бизнеса.
Пример сводной таблицы, который подключается к Analysis Service для вращения кубов
В заключение, скажу, что это был только первый этап построения аналитической отчетности. На текущий момент хранилище очень сильно выросло и в объемах, и в количестве источников. Но базис, заложенный в начале, очень сильно помог в развитии дальнейшего бизнеса. А смысл всей истории в том, что не обязательно тратить месяцы на разработку и моделирование архитектуры для построения DWH, как обычно это бывает. При запуске бизнеса или стартапа, можно пожертвовать техническим долгом для быстрого достижения бизнес целей и делать это можно вполне успешно.
Логическая витрина данных - инструмент, позволяющий связать информацию из разных источников в единый граф знаний (Knowledge Graph) без создания общего физического хранилища. Наш продукт "Логическая витрина данных" - это модуль расширения для АрхиГраф.MDM, который дает возможность извлекать данные по запросу из любых внешних источников: реляционных, документ-ориентированных и графовых СУБД, веб-сервисов и даже файлов. В стандартный комплект поставки входят драйверы витрины для СУБД Oracle, SQL Server, Postgres, MongoDB, точек доступа SPARQL. Этот список может быть расширен в соответствии с задачами заказчика.
Запрос к API АрхиГраф.MDM строится в терминах онтологической модели предметной области. Настройки витрины хранят правила соответствия классов и свойств онтологии элементам структуры хранилищ, а также реквизиты доступа к ним.
Получив запрос, Логическая витрина данных определяет, в каких источниках находится информация, необходимая для формирования ответа на него. Витрина выполняет серию обращений как к хранилищам систем-источников, так и к СУБД под управлением самой платформы АрхиГраф.MDM, агрегирует полученную информацию, строит ответ и возвращает его клиенту.
Таким образом, клиент АрхиГраф.MDM может работать с данными как с единым, связным графом, не заботясь о том, где физически находится информация и какова ее структура. Изменение настроек мэппинга позволяет быстро реагировать на изменение структуры данных в хранилищах, подключать новые источники данных, расширять набор доступных через API типов сущностей и свойств.
Логическая витрина данных способна объединять в единое целое разрозненную, слабо связанную, сложную и динамичную по структуре информацию: от каталогов активов до временных рядов показаний датчиков, от сведений о ремонтах до событийной информации.
Общая схема работы витрины показана на следующем рисунке:
- аналитик делает запрос;
- логическая витрина данных представляет его в виде запроса к графу и передает SPARQL точке доступа АрхиГраф.MDM;
- витрина определяет, где находятся данные, необходимые для ответа на этот запрос;
- витрина выполняет частные запросы исходных данных к разным источникам, используя необходимые фильтры;
- получает ответы и интегрирует их в единый временный граф;
- выполняет пост-обработку графа, заключающуюся, например, в применении правил форматно-логического контроля и контроля целостности;
- возвращает ответ аналитику.
Аналитик может строить запросы при любого приложения, работающего с АрхиГраф.MDM, в том числе редактора АрхиГраф.Мир или Системы управления знаниями АрхиГраф.СУЗ.
Интерфейс АрхиГраф.СУЗ позволяет аналитику выбрать класс искомых объектов, задать условия фильтра и получить список информационных объектов, отвечающих этим условиям. Логическая витрина данных, выполняя запрос, не только приводит структуру информации, извлеченной из систем-источников, в соответствие с онтологической моделью, но и консолидирует данные об одних и тех же бизнес-объектах, полученных из разных систем. Для этого используются ключевые свойства объектов, набор которых также определен в модели, или таблицы соответствия идентификаторов.
Консолидированный объект может иметь значения свойств, полученные из разных систем-источников. Это позволяет сравнить свойства объекта в разных системах, выявить неполную и не актуальную информацию. К свойствам объекта также могут быть применены правила контроля ограничений, сконструированные в АрхиГраф.СУЗ - это позволит стюарду данных найти информационные объекты, нарушающие условия форматно-логического контроля или контроля целостности.
Логическая витрина данных является необходимым инструментом реализации дата-центрической ИТ-архитектуры.
Читайте также: