Какой модели данных не бывает

Процесс моделирования данных при разработке приложений

В большинстве источников моделирование данных (в контексте создания приложений) рассматривается как последовательное создание трёх моделей данных — концептуальной, логической и физический. Такого порядка придерживаются, например, DMBOK2 и BABOK, а также многочисленные статьи в сети Интернет.

Рискну предложить несколько дополнений и уточнений к этому процессу — как на основании собственного опыта, так и обобщения опыта коллег, с которыми обсуждал этот вопрос.

Дополнение 1. Разделение модели данных предметной области и модели данных приложения

При переходе от логической к физической модели происходит революция — меняется объект моделирования.

Концептуальная и логическая модели составляются для предметной области. Сущности данных моделей — это понятия предметной области.

Физическая модель составляется для базы данных приложения. Сущность физической модели — это таблица (или другая структура данных в зависимости от типа СУБД).

Поэтому не совсем корректно рисовать это как единый процесс, более точно будет так:

Почему это так важно? Потому что меняется почти всё — и цель, и количество моделей, и участники, и даже зачастую инструмент и нотация.

Всегда лучше уточнять — что именно отражает модель, предметную область или базу данных приложения.

Что именно меняется?

Меняется цель

Модель данных предметной области составляется для (основные цели):

• Чтобы самим разобраться в предметной области.
• Чтобы верифицировать своё понимание предметной области с заказчиком или доменными экспертами.
• В некоторых случаях — чтобы сформировать единый понятийный аппарат. Слово «клиент» неоднозначно, а «сущность логической модели Клиент» имеет вполне конкретную семантику. Но чаще для этого используют глоссарий.

Создание модели данных приложения — это просто часть разработки. Если в приложении есть база данных, то её нужно спроектировать, иначе она ниоткуда не появится.

Меняется кратность

Если концептуальная и логическая модели соотносятся друг с другом как 1:1 (т.к. объект моделирования один и тот же), то связь между логической и физической — скорее many-to-many.

Приложение может иметь несколько БД — например, часть данных хранится в РСУБД, часть в NoSQL. Логично сделать две разные модели данных для двух физически обособленных БД.

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

Приложение может включать в себя функциональность в разных предметных областях. Это характерно, например, для ведомственных систем организаций с несколькими видами деятельности (ФНС России регистрирует юрлица и собирает налоги — совершенно разные виды деятельности). Например, мне известна одна организация которая занимается поставкой автозапчастей и изготовлением женских сумок, и при этом внедряет единую ERP. Странно рисовать одну модель предметной области для автозапчастей и для сумок — между ними нет ничего общего.

Меняются участники

Модель данных предметной области составляет, как правило, бизнес-аналитик (или фуллстек-аналитик, если эта роль совмещается с системным аналитиком).
Модель данных приложения — архитектор БД, бекенд-разработчик или реже системный аналитик.

Сравнительно редко в одной голове совмещается и знание предметной области, и знание конкретной СУБД на уровне достаточном для качественного проектирования БД.

Меняется нотация

Для создания модели данных предметной области чаще используют те или иные виды ER-диаграмм — например, в нотации Crow’s foot или Min-Max. Иногда используют и UML Class Diagram, хотя о корректности этого можно спорить. Но в любом случае это модель «сущность-связь».

DMBOK2 приводит пример «многоуровневой концептуальной модели данных». — это не сущность-связь. При этом на практике я ни разу с использованием иных видов моделей для концептуального моделирования не сталкивался.
Если у вас есть иной опыт, напишите в комментариях.

Нотация модели данных приложения зависит от типа БД — для документной и для реляционной СУБД используются совершенно разные нотации.

Меняются инструменты

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

Физическая модель данных должна в итоге превратиться в структуру БД (например, путем преобразования в DDL-скрипты). Странно сперва рисовать где-то модель (как картинку), а потом руками писать скрипты — двойная работа. Разумно использовать инструмент, не приводящий к двойной работе.

При этом, разумеется, можно использовать инструменты проектирования наподобие Enterprise Architect или Power Designer — которые позволяют взаимосвязанно создавать все три модели в одном инструменты.

Также важно отметить, что эти модели часто составляют разные люди. Сложно найти единый инструмент, одинаково удобный как для бизнес-аналитика, так и для бекенд-разработчика.

Дополнение 2. Существование не только логической модели данных предметной области, но и логической модели данных приложения

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

Но есть ряд случаев когда это невозможно либо неэффективно, и возникает промежуточное звено — назовём его «логическая модель данных приложения«.

Слово «логический» подразумевает отсутствие привязки к конкретной СУБД. Максимум может быть привязка к типу СУБД — реляционная она или например документная.

Слово «приложения» подразумевает, что сущностями в данной модели являются не понятия предметной области, а будущие таблицы БД (или иные сущности в рамках СУБД приложения, если она не реляционная).

Такая модель данных в нужной степени денормализована, обогащена служебными таблицами и полями, в ней указаны типы данных без привязки к СУБД — т.е. «строка до 500 символов» вместо «nvarchar(500)» и обязательность полей.

Также можно обогатить эту модель указанием полей по которым реализуется поиск (чтобы создать индексы) или бизнес-ключей (чтобы создать уникальные индексы).

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

При этом на схеме он отражен — потому что тем или иным образом, но физическая структуры базы данных всё равно появляется.

В каких же случаях возникает «логическая модель данных приложения»? Ниже перечислены четыре случая, которые встречались в моей практике.

Случай 1. Специфика команды

В статье Умный аналитик – глупый разработчик vs. глупый аналитик – умный разработчик приведена ситуация, когда системный аналитик практически единолично занимается проектированием. Такое бывает на практике, и чаще чем хотелось бы.

«Глупый разработчик» не надо понимать буквально — как указание на низкий уровень когнитивных способностей.

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

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

Что делать системному аналитику — если он не является экспертом в СУБД? Можно составить логическую модель данных приложения. «Глупым» разработчикам по ней корректно составить физическую модель гораздо проще.

Случай 2. Использование фреймворка, реализующего принцип «code first»

Как выглядит проектирование физической модели при использовании фреймворка использующего принцип «code first»? Фактически это делает разработчик, когда составляет класс entity и добавляет к нему аннотации. Всё остальное происходит «автомагически».

Подход «code first» — генерация структуры БД по классам.
Такие возможности есть в Entity Framework, JPA2, SQLAlchemy и т.д. На Хабре есть статьи про это, например https://habr.com/ru/post/234827/ и https://habr.com/ru/post/174709/ (случайная выбрал две статьи).

Как системному аналитику участвовать в процессе проектирования модели данных приложения — если исходя из проектной ситуации это необходимо?

Сидеть рядом с разработчиком и вместе с ним проектировать классы и расставлять аннотации? Так себе идея. Да и хочется это сделать заранее, а не прямо во время разработки.

Обсудить заранее с разработчиком и написать большущий комментарий к задаче в Jira? Тоже не очень, несистемно и может потеряться.

Решение — составить логическую модель данных приложения (которая потом практически 1:1 воплотится в классы). Составить её можно как вместе с разработчиком (если он может и готов участвовать в проектировании), так и без него (если он «глупый»).

В этом случае модель данных приложения может быть с равным успехом быть представлена как в виде UML Class Diagram (т.к. первичны именно классы), так и в виде ER-диаграммы (т.к. классы соответствуют таблицам).

Случай 3. Приложение поддерживает разные СУБД

Существуют приложения, которые поддерживают разные СУБД. Как правило, в слое данных (data layer) таких приложений реализован дополнительный уровень абстракции, который изолирует приложение от специфики СУБД.

1С: Предприятие, например, поддерживает 5 СУБД. Получается, что одно приложение может иметь 5 разных физических моделей данных. Напрашивается обобщение этих 5 физических моделей данных в виде одной логической — ведь там по сути одни и те же данные. И это тоже будет «логическая модель данных приложения».

Если в каком-то месте (например, в описании алгоритма формирования отчета) нужно сослаться на поле в БД, то можно не указывать 5 разных вариантов для разных СУБД, а один раз указать поле из логической модели данных приложения.

Если коллеги из 1С случайно прочитают этот пост, прошу их уточнить в комментариях — есть у них такая сущность как «логическая модель данных приложения» или нет.

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

Большой разрыв может возникать, например, в случае реализации приложения путем расширения базового продукта (как примеры — Liferay, Sharepoint, Docushare, Alfresco), который уже имеет структуру БД с предусмотренными точками расширения. Или в случае использования фреймворка, который накладывает ограничения на структуру базы данных.
В этом случае структура базы данных и модель данных предметной области имеют между собой очень мало общего.

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

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

С другой стороны, для неё описан мэппинг на модель данных предметной области, она документирована и удобна для использования аналитиком.

Дополнение 3. Обязательность моделей данных предметной области

Насколько обязательным этапом является составление моделей данных предметной области?

Концептуальная модель данных предметной области, с моей точки зрения, должна быть всегда. В отдельных случаях она может не документироваться в виде артефакта и существовать только в голове членов команды — например, если в проектировании участвует только системный аналитик и для него это не первый проект в данной предметной области.
Но такой подход имеет много недостатков — тот же bus factor и т.д. Желательно её документировать.

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

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

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

Получается следующая схема:

Итого

1. Необходимо различать модель данных предметной области и модель данных приложения.
2. Концептуальная модель данных — всегда для предметной области, физическая модель данных — всегда для приложения, логическая модель данных — может быть как для предметной области, так и для приложения.
3. Логическая модель данных приложения зависит от типа СУБД (например, реляционная СУБД и документная СУБД), но не от конкретной СУБД (для PostgreSQL и для Oracle может использоваться одна модель). Физическая модель данных приложения зависит и от типа СУБД, и от конкретной СУБД.
4. В зависимости от специфики проекта может составляться различный набор моделей данных.
5. Почти всегда должны составляться: концептуальная модель данных предметной области и одна из логических моделей данных (предметной области или приложения).

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

В моих конкретных условиях он оказался оптимальным по отношения «полученная польза / затраченные усилия».

А какие виды моделей данных составляете вы в своих проектах?

P.S. Дополнение 4. Модель данных микросервиса

У меня был всего один кейс, чего недостаточно для каких-либо выводов. Поэтому разместил этот блок после раздела «Итого». Если у вас есть другой опыт, напишите о нём в комментариях — буду рад о нём узнать.

Модель данных предметной области никак не зависит архитектуры, в том числе наличия или отсутствия микросервисов. Но как организован переход от логической модели к физической в случае микросервисной архитектуре?

Как это было у нас. На этапе сбора требований «по классике» были составлены концептуальная и логическая модели данных предметной области.

После того, как на этапе проектирования были выделены микросервисы и определены зоны их ответственности, была сделана «нарезка» логической модели предметной области — какой микросервис будет хранить какие сущности. Это нужно для того, чтобы передать этот кусок модели (вместе с иными требованиями) той подкоманде, которая будет заниматься микросервисом. Приложение было большим, подкоманд много, некоторые из них субподрядные. Давать всем общую модель было избыточно.

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

Как назвать эту модель? С одной стороны, она остаётся логической моделью предметной области — сущности это понятия предметной области, а не таблицы, уровень детализации соответствует логической модели. С другой стороны, она уже частично привязана к реализации — то есть определён микросервис, в котором эта сущность будет имплементирована.

Таким образом, между логической моделью предметной области и моделью данных приложения (неважно, логической или физической) может существовать еще один промежуточный этап — «логическая модель данных предметной области, планируемая к реализации в рамках компонента архитектуры приложения».

• модель данных
• системный анализ
• концептуальная модель
• логическая модель данных
• физическая модель данных
• моделирование данных

• Анализ и проектирование систем
• Терминология IT
• Микросервисы

Виды баз данных

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

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

Что такое база данных

База данных — это набор сведений об объектах, структурированный определенным образом. Обычно базы данных управляются специальным ПО, или системами управления базами данных (СУБД).

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

Простейшие типы баз данных

К таким базам данных относятся БД, где хранятся данные с простой структурой: например, список разрешенных IP-адресов для доступа к сети, настройки окружения проекта, список подписчиков на рассылку компании и прочее. Они все еще широко распространены.

Текстовые файлы

Информация об объектах собирается в простых по структуре файлах различных форматов – txt, csv и др. Для разделения полей применяются пробелы, табуляция, запятые, точка с запятой и двоеточие.

Примеры: etc/passwd и etc/fstab в Unix-подобных системах, csv-файлы, ini-файлы и др.

Особенности:

1. Просто использовать. Для работы с файлами достаточно примитивного текстового редактора.
2. Удобно работать с конфигурационными данными приложений (учетные данные, настройки подключения к удаленным серверам и устройствам, порты и пр.).

Ограничения:

1. Сложно установить связи между компонентами данных.
2. Не для всех типов информации.

Иерархические базы данных

В отличие от текстовых файлов здесь между хранимыми объектами устанавливаются связи. Объекты делятся на родителей (основные классы или категории объектов) и потомков (экземпляры этих классов или категорий). При этом у каждого потомка может быть не более одного родителя.

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

Примеры: Организация файловых систем; DNS и LDAP-соединения.

Особенности:

• Отношения между объектами реализованы в виде физических указателей. Например, в файловой системе путь к папке или файлу строится из имен корневых и вложенных каталогов;
• Моделирование отношений вложенности и подчиненности.

Ограничения: Технология иерархической организации не предполагает связи «многие-ко-многим», а значит, система хранения данных довольно ограничена.

Сетевые базы данных

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

Пример: IDMS — специализированная СУБД для мейнфреймов.

Реляционные базы данных

Данный тип БД является старейшим: теоретические основы подхода заложены британским ученым Эдгаром Коддом в 1970 году. Здесь данные формируются в таблицы из строк и столбцов. В строках приводятся сведения об объектах (значения свойств), а в столбцах — сами свойства объектов (поля).

Нормализация

Сложные взаимоотношения объектов в реляционных БД моделируются с помощью внешних ключей – ссылок на другие таблицы. Это позволяет подходить к вопросу проектирования базы данных с позиций нормализации – минимизации избыточности при описании свойств объектов.

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

Такой подход позволяет:

1. Минимизировать объем базы данных: не нужно каждому блюду прописывать название категории.
2. Повысить целостность системы: в указанном примере все блюда привязаны к категориям меню. Добавление блюда без категории невозможно, равно как и указание в качестве ссылки индекса несуществующей категории.
3. Упростить масштабирование: новые блюда могут быть добавлены в существующие категории. Также не исключается добавление новых категорий, привязка новых блюд к ним и перераспределение блюд по категориям.
4. Повысить отказоустойчивость: за счет оптимальной организации схемы таблиц запросы на выборку и агрегацию будут работать с меньшим объемом данных, а значит, быстрее, чем без нормализации. При увеличении числа записей в таблицах со временем это позволит поддерживать положительный пользовательский опыт.

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

Связь преподавателя с отделом организована через секцию и курс (внешние ключи id курса и id преподавателя в таблице Секция, а также Отдел в таблице Курс). Связь ученика с направлением обучения реализована через таблицу Направление обучения студента (внешние ключи id студента и id направления обучения).

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

Язык запросов SQL

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

• делать выборки,
• проводить агрегации и группировки,
• изменять и удалять данные,
• модифицировать структуру БД (создавать таблицы, поля),
• управлять доступом пользователей к тем или иным операциям и пр.

Денормализация

Помимо нормализации, в реляционных БД существует и обратный процесс — денормализация. Он направлен на перенос наиболее часто используемых полей из внешних таблиц во внутренние. Рассмотрим это на примере мессенджера.

Пользователь (user) оставляет сообщения (messages) в чатах (chat). Структура данных такова, что сообщения связаны с пользователем и чатом через внешние ключи (user_from и user_to, а также chat_id в таблице сообщений; user_id и chat_id в таблице user_chat_link). Поскольку схема нормализована, то различные запросы на выборку, подсчет и агрегацию статистики по чатам, пользователям и сообщениям необходимо выполнять с помощью присоединения внешних таблиц.

На относительно небольших объемах данных эти запросы будут отрабатывать быстро, а с увеличением размера базы – замедляться. Причина кроется в механизме присоединения. Он основан на построчном сравнении двух и более таблиц по условию соединения — например, равенство chat_id в messages и id в chat. А это дает нагрузку на сервер базы данных, которая с ростом ее размера только увеличивается. Для оптимизации такого рода запросов и существует механизм денормализации.

В таблицу связи пользователя и чата user_chat_link добавлены дублирующие поля имени чата (chat_name) и аватара (chat_logo). Также туда выводятся последнее сообщение (last_msg) и количество непрочитанных сообщений (unread_msg_count).

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

За счет дополнительных полей оптимизируются запросы на чтение и агрегацию данных, однако ценой этого является вынужденная избыточность и усложнение бизнес-логики приложения. В частности, усложняется написание запросов изменения данных (update и delete), а также модификации структуры базы (create).

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

Преимущества реляционного подхода:

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

Недостатки подхода: жесткая структура сведений об объектах.

Примеры: MySQL, MariaDB, PostgreSQL, SQLite и др.

NoSQL и нереляционные базы данных

Все преимущества и недостатки реляционных БД основаны на жесткой структуризации и типизации сведений об объектах. С одной стороны, можно оптимизировать хранение и индексирование данных за счет нормализации или же денормализации. С другой — сложно организовать хранение и обработку плохо структурированных (например, объекты кэша) или вовсе не структурированных данных (например, данные из нескольких источников).

Для борьбы с этими ограничениями было разработано семейство нереляционных БД. Рассмотрим их подробнее.

Базы данных «Ключ-значение»

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

Особенности:

1. Хранение и обработка разных по типу и содержанию данных: в одном хранилище под разными ключами могут находиться файлы, строки, текст, числа, JSON-объекты и другие типы данных.
2. Высокая скорость доступа к данным за счет адресного хранения.
3. Легкое масштабирование. Можно создать правила шардирования по определенным ключам – например, сессии пользователей разных сайтов хранятся в различных сегментах БД.

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

Примеры: Amazon, DynamoDB, Redis, Riak, LevelDB, различные хранилища кэша – например, Memcached и пр.

Документоориентированные БД

В отличие от баз типа «Ключ-значение» данные здесь хранятся в структурированных форматах – XML, JSON, BSON. Тем не менее, сохраняется адресный доступ к данным по ключу. При этом содержимое документа может иметь различный набор свойств.

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

Особенности:

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

Примеры: MongoDB, RethinkDB, CouchDB, DocumentDB.

Графовые базы данных

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

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

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

Примеры: Neo4J, JanusGraph, Dgraph, OrientDB.

Колоночные базы данных

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

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

На рисунке приведен пример колоночного хранения информации о фруктах. Известно три типа фруктов: яблоки, виноград, бананы. Все они объединены в семейство фруктов.

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

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

Особенности:

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

Примеры: Cassandra, HBase, ClickHouse.

Базы данных временных рядов

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

На рисунке выше приведен пример использования такой БД для отслеживания состояния ПК во времени по ряду показателей – температуре процессора, загрузке системы и потреблению оперативной памяти.

Особенности: Можно обрабатывать постоянный поток входных данных.

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

Примеры БД: OpenTSDB, Prometheus, InfluxDB, TimescaleDB

Комбинированные базы

Эта разновидность баз совмещает в себе SQL- и NoSQL-подходы к организации хранения и обработки данных. Этот класс баз включает в себя NewSQL и многомодельные решения. Рассмотрим их подробнее.

Базы данных NewSQL

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

Термин предложил в 2011 году аналитик компании 451 Group Мэтью Аслет. Он отмечал высокую потребность в таких системах для сфер, работающих с критическими данными, — здравоохранение, FinTech и пр. Характерными признаками этих решений являются: использование алгоритмов обеспечения консенсуса (алгоритм Paxos, Raft и др.), шардирование и заточка под горизонтальное масштабирование.

Особенности:

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

Ограничения: Высокие требования к аппаратным ресурсам разработчиков. Но если разрабатываемый продукт является высоконагруженной системой, то применение такой БД имеет смысл.

Примеры баз такого типа: MemSQL, VoltDB, Spanner и др.

Многомодельные базы

Такие БД сочетают в себе несколько подходов к организации данных одновременно. Это обеспечивает функциональное разнообразие при разработке систем с их использованием.

Особенности:

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

Пример решения данного типа: ArangoDB.

Базы данных в Selectel

В Selectel вы можете запустить готовые облачные базы данных — поддерживаем такие СУБД, как PostgreSQL (в том числе для 1С:Предприятие), MySQL, Redis, TimescaleDB.

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

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

Запустите свою базу данных в облаке

Быстрое развертывание самых популярных реляционных и NoSQl-баз данных.

Заключение

В данной статье мы рассмотрели 11 видов баз данных. Каждый имеет свои особенности и ограничения. Решение о выборе того или иного вида необходимо принимать с учетом:

• сложности хранимых данных и взаимосвязей между ними,
• производительности операций чтения/записи и модификации структуры БД на планируемом объеме данных,
• опыта команды разработки,
• стадии жизненного цикла разрабатываемого продукта (производите ли вы доработку действующего решения либо создаете что-то принципиально новое, каковы ваши текущие и перспективные ресурсные возможности).

Автор: Роман Андреев.

Отраслевые модели данных

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

Под моделями данных понимаются абстрактные модели, описывающие способ представления данных и доступ к ним. Модели данных определяют элементы данных и связи между ними в той или иной области. Модель данных – это навигационный инструмент как для бизнес-, так и для IT-профессионалов, в котором используется определенный набор символов и слов для точного объяснения определенного класса реальной информации. Это позволяет улучшить взаимопонимание внутри организации и, таким образом, создать более гибкую и стабильную среду для работы приложений.

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

Как правило, выделяются модели более высокого уровня (и более общие по содержанию) и более низкого (соответственно, более детальные). Верхний уровень моделирования – это так называемые концептуальные модели данных (conceptual data models), которые дают самую общую картину функционирования предприятия или организации. Концептуальная модель включает основные концепции или предметные области, критичные для функционирования организации; обычно их количество не превышает 12-15. Такая модель описывает классы сущностей, важных для организации (бизнес-объекты), их характеристики (атрибуты) и ассоциации между парами этих классов (т.е. связи). Поскольку в бизнес-моделировании терминология еще окончательно не устоялась, в различных англоязычных источниках концептуальные модели данных могут также носить название subject area model (что можно перевести как модели предметных областей) или subject enterprise data model (предметные корпоративные модели данных).

Следующий иерархический уровень – это логические модели данных (logical data models). Они также могут называться корпоративными моделями данных или бизнес-моделями. Эти модели содержат структуры данных, их атрибуты и бизнес-правила и представляют информацию, используемую предприятием, с точки зрения бизнес-перспективы. В такой модели данные организованы в виде сущностей и связей между ними. Логическая модель представляет данные таким образом, что они легко воспринимаются бизнес-пользователями. В логической модели может быть выделен словарь данных – перечень всех сущностей с их точными определениями, что позволяет различным категориям пользователей иметь общее понимание всех входных и информационных выходных потоков модели. Следующий, более низкий уровень моделирования – это уже физическая реализация логической модели с помощью конкретных программных средств и технических платформ.

Логическая модель содержит детальное корпоративное бизнес-решение, которое обычно принимает форму нормализованной модели. Нормализация – это процесс, который гарантирует, что каждый элемент данных в модели имеет только одно значение и полностью и однозначно зависит от первичного ключа. Элементы данных организуются в группы согласно их уникальной идентификации. Бизнес-правила, управляющие элементами данных, должны быть полностью включены в нормализованную модель с предварительной проверкой их достоверности и корректности. Например, такой элемент данных, как Имя клиента, скорее всего, будет разделен на Имя и Фамилию и сгруппирован с другими соответствующими элементами данных в сущность Клиент с первичным ключом Идентификатор клиента.

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

Важно различать логическую и семантическую модель данных. Логическая модель данных представляет корпоративное бизнес-решение, а семантическая – прикладное бизнес-решение. Одна и та же корпоративная логическая модель данных может быть реализована с помощью различных семантических моделей, т.е. семантические модели могут рассматриваться как следующий уровень моделирования, приближающийся к физическим моделям. При этом каждая из таких моделей будет представлять отдельный «срез» корпоративной модели данных в соответствии с требованиями различных приложений. Например, в корпоративной логической модели данных сущность Клиент будет полностью нормализована, а в семантической модели для витрины данных может быть представлена в виде многомерной структуры.

У компании может быть два пути создания корпоративной логической модели данных: строить ее самостоятельно или воспользоваться готовой отраслевой моделью (industry logical data model). В данном случае различия в терминах отражают лишь разные подходы к построению одной и той же логической модели. В том случае, если компания самостоятельно разрабатывает и внедряет собственную логическую модель данных, то такая модель, как правило, носит название просто корпоративной логической модели. Если же организация решает воспользоваться готовым продуктом профессионального поставщика, то тогда можно говорить об отраслевой логической модели данных. Последняя представляет собой готовую логическую модель данных, с высокой степенью точности отражающую функционирование определенной отрасли. Отраслевая логическая модель – это предметно-ориентированный и интегрированный вид всей информации, которая должна находиться в корпоративном Хранилище данных для получения ответов как на стратегические, так и на тактические бизнес-вопросы. Как и любая другая логическая модель данных, отраслевая модель не зависит от прикладных решений. Она также не включает производные данные или другие вычисления для более быстрого извлечения данных. Как правило, большинство логических структур такой модели находят хорошее воплощение в ее эффективной физической реализации. Такие модели разрабатываются многими поставщиками для самых различных областей деятельности: финансовой сферы, производства, туризма, здравоохранения, страхования и т.д.

Отраслевая логическая модель данных содержит информацию, общую для отрасли, и поэтому не может быть исчерпывающим решением для компании. Большинству компаний приходится увеличивать модель в среднем на 25% за счет добавления элементов данных и расширения определений. Готовые модели содержат только ключевые элементы данных, а остальные элементы должны быть добавлены к соответствующим бизнес-объектам в процессе установки модели в компании.

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

Специалист в области бизнес-аналитики (Business Intelligence) Стив Хобермэн (Steve Hoberman) выделяет пять факторов, которые необходимо принимать во внимание при решении вопроса о приобретении отраслевой модель данных. Первый – это время и средства, необходимые для построения модели. Если организации необходимо быстро добиться результатов, то отраслевая модель даст преимущество. Использование отраслевой модели не может немедленно обеспечить картину всей организации, но способно сэкономить значительное количество времени. Вместо собственно моделирования время будет потрачено на связывание существующих структур с отраслевой моделью, а также на обсуждение того, как лучше ее настроить под нужды организации (например, какие определения должны быть изменены, а какие элементы данных – добавлены).

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

Третий фактор – опыт в оценке рисков и моделировании. Создание корпоративной модели данных требует квалифицированных ресурсов как со стороны бизнеса, так и IT-персонала. Как правило, менеджеры хорошо знают либо работу организации в целом, либо деятельность конкретного отдела. Лишь немногие их них обладают как широкими (в масштабах всей компании), так и глубокими (в рамках подразделений) знаниями о своем бизнесе. Большинство менеджеров обычно хорошо знают только одну область. Поэтому, для того чтобы получить общекорпоративную картину, требуются существенные бизнес-ресурсы. Это увеличивает и требования к IT-персоналу. Чем больше бизнес-ресурсов требуется для создания и тестирования модели, тем более опытными должны быть аналитики. Они должны не только знать, как получить информацию от бизнес-персонала, но также уметь находить общую точку зрения в спорных областях и быть способными представлять всю эту информацию в интегрированном виде. Тот, кто занимается созданием модели (во многих случаях это тот же аналитик), должен обладать хорошими навыками моделирования. Создание корпоративных логических моделей требует моделирования «для будущего» и способности конвертировать сложный бизнес в буквальном смысле «в квадраты и линии».

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

Четвертый фактор – существующая инфраструктура приложений и связи с поставщиками. Если организация уже использует много инструментов одного и того же поставщика и имеет налаженные связи с ним, то имеет смысл и отраслевую модель заказывать у него же. Такая модель сможет свободно работать с другими продуктами этого же поставщика.

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

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

Отраслевые модели данных обеспечивают компаниям единое интегрированное представление их бизнес-информации. Многим компаниям бывает непросто осуществить интеграцию своих данных, хотя это является необходимым условием для большинства общекорпоративных проектов. По данным исследования Института Хранилищ данных (The Data Warehousing Institute, TDWI), более 69% опрошенных организаций обнаружили, что интеграция является существенным барьером при внедрении новых приложений. Напротив, осуществление интеграции данных приносит компании ощутимый доход.

Отраслевая модель данных, помимо связей с уже существующими системами, дает большие преимущества при осуществлении общекорпоративных проектов, таких как планирование ресурсов предприятия (Enterprise Resource Planning, ERP), управление основными данными, бизнес-аналитика, повышение качества данных и повышение квалификации сотрудников.

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

Публикации

1. Стив Хобермэн (Steve Hoberman). Использование отраслевой логической модели данных в качестве корпоративной модели (Leveraging the Industry Logical Data Model as Your Enterprise Data Model).
2. Клодиа Имхоф (Claudia Imhoff). Оперативное создание Хранилищ данных и выполнение проектов Business Intelligence с помощью моделирования данных (Fast-Tracking Data Warehousing & Business Intelligence Projects via Intelligent Data Modeling)

Автор: По материалам зарубежных сайтов

Учебник. Расширение связей модели данных с использованием Excel, Power Pivot и DAX

Аннотация. Это второй учебник из серии. В первом учебнике «Импортданных в модель данных и создание модели данных» была создана книга Excel с данными, импортируемыми из нескольких источников.

Примечание: В этой статье описаны модели данных в Excel 2013. Тем не менее те же функции моделирования данных и Power Pivot, которые впервые представлены в Excel 2013, также относятся к Excel 2016.

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

Ниже перечислены разделы этого учебника.

• Добавление связи с помощью представления схемы в Power Pivot
• Расширение модели данных с использованием вычисляемых столбцов
• Создание иерархии
• Использование иерархий в сводных таблицах
• Контрольная точка и тест

В конце учебника есть тест, с помощью которого можно проверить свои знания.

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

1. Импорт данных в Excel и создание модели данных
2. Расширение связей модели данных с помощью Excel, Power Pivot и DAX
3. Создание отчетов Power View на основе карт
4. Объединение интернет-данных и настройка параметров отчета Power View по умолчанию
5. Справка по Power Pivot
6. Создание впечатляющих отчетов Power View, часть 2

Рекомендуется изучить их по порядку.

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

Добавление связи с помощью представления диаграммы в Power Pivot

В этом разделе вы научитесь расширять модель с помощью надстройки Microsoft Office Power Pivot в Excel 2013. Представление диаграммы в Microsoft SQL Server PowerPivot для Excel упрощает создание отношений. Прежде всего вам нужно убедиться, что надстройка Power Pivot включена.

Примечание. Power Pivot в Microsoft Excel 2013 входит в состав Office профессиональный плюс. Дополнительные сведения см. в надстройка «Запуск Power Pivot в Microsoft Excel 2013».

Добавление Power Pivot на ленту Excel путем включения Power Pivot надстройки

Если надстройка Power Pivot включена, в Excel 2013 отображается вкладка ленты под названием POWER PIVOT. Чтобы включить Power Pivot, выполните следующие действия:

1. Выберите ФАЙЛ > Параметры > Надстройки.
2. В поле Управление внизу выберите Надстройки COM> Перейти.
3. Установите флажок Microsoft Office Power Pivot в Microsoft Excel 2013, а затем нажмите кнопку ОК.

На ленте Excel появится вкладка POWER PIVOT.

Добавление связи с помощью представления диаграммы в Power Pivot

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

1. В Excel щелкните ярлычок Hosts, чтобы сделать этот лист активным.
2. На ленте выберите POWER PIVOT > Таблицы > Добавить в модель данных. Этот шаг добавляет таблицу Hosts в модель данных. Также откроется надстройка Power Pivot, которую можно использовать для выполнения оставшихся шагов задачи.
3. Обратите внимание, Power Pivot показывает все таблицы в модели, включая таблицы «Hosts». Просмотрите несколько таблиц. В Power Pivot вы можете просмотреть все данные, которые содержит модель, даже если они не отображаются ни на каких листах Excel, например данные о дисциплинах, соревнованиях и медалях ниже, а также данных S_Teams,W_Teamsиsports.

В окне Power Pivot в группе Просмотр выберите команду Представление диаграммы.

С помощью полосы прокрутки измените размер диаграммы таким образом, чтобы видеть все объекты в диаграмме. Перетаскружая заголовок таблицы, можно изменить их положение, чтобы они были видны и расположились рядом друг с другом. Обратите внимание, что четыре таблицы не связаны с остальными таблицами: Hosts,Events,W_Teamsи S_Teams.

Вы заметите, что как в таблице Medals, так и в таблице Events есть поле под названием DisciplineEvent. При дальнейшем изучении вы определяете, что поле DisciplineEvent в таблице Events состоит из уникальных, неповторяющихся значений.

Примечание: Поле DisciplineEvent представляет уникальное сочетание каждой дисциплины и соревнования. С другой стороны, в таблице Medals поле DisciplineEvent повторяется много раз. Это понятно, потому что для каждого сочетания дисциплины и соревнования есть три медали (золото, серебро, бронза), которые присуждаются на каждых Олимпийских играх. Поэтому между этими таблицами существует отношение «один ко многим» (одна уникальная запись, включающая дисциплину и соревнование, в таблице Disciplines, и несколько записей для каждого значения «дисциплина + соревнование»).

1. Создайте связь между таблицами Medals и Events. В представлении диаграммы перетащите поле DisciplineEvent из таблицы Events на поле DisciplineEvent таблицы Medals. Между ними появится линия, показывающая, что связь была создана.
2. Щелкните линию, которая соединяет Events и Medals. Выделенные поля определяют связь, как показано на следующем экране.

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

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

Приятно, когда в модели данных есть все поля, необходимые для создания отношений, и данные объединения, требуемые для визуализации с помощью Power View или сводных таблиц. Но это не всегда так, поэтому в следующем разделе описывается, как с помощью DAX создать новый столбец, который можно использовать для создания связи между таблицами.

Расширение модели данных с использованием вычисляемых столбцов

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

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

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

В Hosts мы можем создать уникальный вычисляемый столбец, объединив поля Edition (год проведения Олимпийских игр) и Season (лето или зима). В таблице Medals также есть поля Edition и Season, так что если мы создадим в каждой из этих таблиц вычисляемый столбец, которые объединяет поля Edition и Season, мы сможем установить связь между Hosts и Medals. На следующем экране показана таблица Hosts с выбранными полями Edition и Season

Создание вычисляемых столбцов с помощью DAX

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

В надстройке Power Pivot вы можете использовать формулы DAX, чтобы создавать вычисления. DAX — язык формул для Power Pivot и сводных таблиц, доступный в Power Pivot и предназначенный для реляционных данных и контекстуального анализа. Формулы DAX можно создавать в новом столбце Power Pivot и в области вычислений в Power Pivot.

1. Чтобы убедиться, что выбрано представление данных, а не представление схемы, на вкладке Power Pivot выберите пункты В начало > Просмотр > Представление данных.
2. Выберите таблицу Hosts в Power Pivot. Рядом с существующими столбцами есть пустой столбец под названием Добавить столбец. Power Pivot предоставляет этот столбец в качестве заполнителя. В Power Pivot есть много способов добавления нового столбца в таблицу, один из которых — выбрать пустой столбец с названием Добавить столбец.

Введите указанную ниже формулу DAX в строке формул. Функция CONCATENATE объединяет несколько полей в одно. По мере ввода функция автозаполнения поможет ввести полные имена столбцов и таблиц и представит доступные функции. Используйте клавишу TAB для выбора предложений автозаполнения. Вы также можете просто щелкнуть столбец при вводе формулы, и Power Pivot вставит его имя в формулу.

Таблица Hosts готова. Теперь давайте создадим вычисляемый столбец в таблице Medals, который соответствует формату столбца EditionID в таблице Hosts, чтобы можно было создать связь между ними.

1. Для начала создайте новый столбец в таблице Medals (так же, как в таблице Hosts). В Power Pivot выберите таблицу Medals и щелкните Конструктор > Столбцы > Добавить. Обратите внимание, что установлен флажок Добавить столбец. Это дает тот же результат, что и выбор столбца Добавить столбец.
2. Формат столбца Edition в Medals отличается от формата Edition в таблице Hosts. Прежде чем объединять (сцеплять) столбцы Edition и Season для создания столбца EditionID, нам нужно создать промежуточное поле, которое возвращает данные Edition в правильном формате. В строке формул над таблицей введите следующую формулу DAX.

= YEAR([Edition])

Обратите внимание, что многие значения в поле EditionID таблицы Medals повторяются. Это и ожидалось, так как во время каждых Олимпийских игр (которые теперь представлены значением EditionID) было вручено много медалей. Уникальной в таблице Medals является каждая медаль. Уникальным идентификатором для каждой записи в таблице Medals и ее первичным ключом является поле MedalKey.

Следующим шагом является создание связи между таблицами Hosts и Medals.

Создание связи с помощью вычисляемых столбцов

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

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

Разверните таблицу Hosts, чтобы можно было просмотреть все ее поля. Мы создали столбец EditionID, который является первичным ключом (уникальным, не повторяющимся полем) таблицы Hosts, и столбец EditionID в таблице Medals, чтобы установить связь между ними. Нам нужно найти их и создать связь. На ленте Power Pivot есть функция Найти, которая позволяет искать нужные поля в модели данных. На следующем экране показано окно Поиск метаданных, в поле Найти которого указано «EditionID».

Расположите таблицу Hosts рядом с таблицей Medals.

Перетащите столбец EditionID из таблицы Medals на столбец EditionID в таблице Hosts. Power Pivot создаст между таблицами связь, основанную на столбце EditionID, и нарисует между двумя столбцами линию, которая показывает ее.

В этом разделе вы научились новому способу добавления столбцов, создали вычисляемый столбец с помощью DAX и использовали его для создания связи между таблицами. Теперь таблица Hosts интегрирована в модель данных, а ее данные доступны для сводной таблицы на листе Лист1. Вы также можете использовать связанные данные для создания дополнительных сводных таблиц, сводных диаграмм, отчетов Power View и т. д.

Создание иерархии

Данные, входящие в большинство моделей, по сути своей иерархичны. Распространенные примеры: данные календаря, географические данные и категории товаров. Создание иерархий в Power Pivot полезно тем, что позволяет перетаскивать в отчет элемент (иерархию), а не собирать и упорядочивать одни и те же поля каждый раз заново.

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

В этом разделе вы создадите две иерархии в данных Olympic, которые вы используете в этом учебнике. Затем на их примере мы покажем, как иерархии упрощают организацию данных в сводных таблицах и (в следующем учебнике) в Power View.

Создание иерархии Sport

1. В Power Pivot выберите Представление диаграммы. Разверните таблицу Events, чтобы было проще работать с ее полями.
2. Нажмите и удерживайте CTRL, а затем щелкните поля «Спорт», «Дисциплина» и «Соревнования». Выбрав эти три поля, щелкните правой кнопкой мыши и выберите «Создать иерархию». В нижней части таблицы будет создан родительский узел иерархии Hierarchy 1,а выбранные столбцы будут скопированы в иерархию в качестве потомков. Убедитесь, что Sport отображается сначала в иерархии, а затем Discipline, а затем Event.
3. Дважды щелкните заголовок Иерархия1 и введите SDE, чтобы переименовать новую иерархию. Теперь у вас есть иерархия, которая включает в себя виды спорта, дисциплины и соревнования. Таблица Events теперь выглядит так, как показано на следующем экране.

Создание иерархии Location

В представлении схемы в Power Pivot выберите таблицу Hosts и нажмите кнопку «Создать иерархию» в заголовке таблицы, как показано на следующем экране.

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

Введите в качестве имени новой иерархии Locations.

Есть много способов добавления столбцов в иерархию. Перетащите поля Season, City и NOC_CountryRegion на имя иерархии (в данном случае — Locations), пока имя иерархии не будет выделено, а затем отпустите, чтобы добавить их.

Щелкните правой кнопкой мыши EditionID и выберите пункт Добавить в иерархию. Выберите Locations.

Убедитесь, что дочерние узлы в иерархии расположены в нужном порядке. Порядок должен быть: Season, NOC, City, EditionID. Если дочерние узлы расположены не по порядку, просто перетащите их в нужное место в иерархии. Ваша таблица должна выглядеть так, как на следующем экране.

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

Использование иерархий в сводных таблицах

Теперь, когда у нас иерархии Sports и Locations, мы можем добавить их в сводные таблицы или Power View и быстро сгруппировать данные или получить другие полезные результаты. Перед созданием иерархий вы добавили отдельные поля в сводную таблицу и упорядочили их так, как вы хотите их просматривать.

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

1. Вернитесь назад в Excel.
2. На листе Лист1 удалите поля из области строк полей сводной таблицы, а затем удалите все поля из области столбцов. Убедитесь, что сводная таблица выбрана (сейчас она довольно небольшая, так что вы можете выбрать ячейку A1, чтобы сделать это). В сводной таблице остались только поля Medal в области фильтров и Count of Medal в области значений. Практически пустая сводная таблица должна быть похожа на изображенную на следующем экране.
3. В области полей сводной таблицы перетащите SDE из таблицы Events в область строк. Затем перетащите Locations из таблицы Hosts в область СТОЛБЦЫ. Просто путем перетаскивания этих двух иерархий можно заполнить сводную таблицу большим количеством данных, все из которых организованы в иерархии, созданные на предыдущих шагах. Ваша таблица должна выглядеть так, как на следующем экране.
   
4. Отфильтруем данные, чтобы просмотреть только первые десять строк соревнований. В сводной таблице щелкните стрелку в поле Метки строк, щелкните (Выбрать все), чтобы снять выделение, а затем установите флажки рядом с первыми десятью видами спорта. Сводная таблица теперь выглядит так, как показано на следующем экране.
   
5. Вы можете развернуть любой из этих видов спорта, которые являются верхним уровнем в иерархии SDE, и просмотреть информацию на следующем уровне (дисциплины). Если для этой дисциплины существует более низкий уровень в иерархии, то можно развернуть ее для просмотра соревнований. Вы можете сделать то же самое в иерархии Location, где верхним уровнем является Season, который выводится как Summer и Winter в сводной таблице. При развертывании вида спорта Aquatics выводятся все его дочерние элементы дисциплин и их данные. Если развернуть дисциплину Diving спорта Aquatics, появятся соревнования по ней, как показано на следующем экране. Мы можем сделать то же самое для дисциплины Water Polo и увидеть, что у нее только оно соревнование.
   

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

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

1. В области полей сводной таблицы удалите Locations из области столбцов. Затем удалите SDE из области строк. Мы вернулись к базовой сводной таблице.
2. Из таблицы Hosts перетащите Season, City, NOC_CountryRegion и EditionID в область столбцов и расположите их в указанном порядке сверху вниз.
3. Из таблицы Events перетащите Sport, Discipline и Event в область строк и расположите их в указанном порядке сверху вниз.
4. В сводной таблице отфильтруйте метки строк, чтобы показать десять первых видов спорта.
5. Сверните все строки и столбцы, а затем разверните Aquatics, Diving и Water Polo . Книга выглядит так, как показано на следующем экране.

Экран выглядит похоже, хотя вы перетащили семь отдельных полей в области Поля сводной таблицы, вместо того чтобы просто перетащить две иерархии. Если вы единственный человек, который создает сводные таблицы или отчеты Power View, основываясь на этих данных, создание иерархий может показаться не более чем удобным способом. Но когда отчеты создает много людей, которым требуется знать надлежащий порядок полей для правильного представления данных, иерархии становятся инструментом повышения производительности и обеспечения единообразия.

Из другого учебника вы узнаете, как использовать иерархии и другие поля в привлекательных отчетах, созданных с помощью Power View.

Контрольная точка и тест

Повторите изученный материал

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

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

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

ТЕСТ

Хотите проверить, насколько хорошо вы усвоили пройденный материал? Приступим. Этот тест посвящен функциям, возможностям и требованиям, о которых вы узнали в этом учебнике. Внизу страницы вы найдете ответы на вопросы. Удачи!

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

А. Связи между таблицами создаются в Power View.

Б. Связи между таблицами создаются в представлении конструирования в Power Pivot.

В. Связи между таблицами создаются в сетке в Power Pivot.

Г. Все вышеперечисленные.

Вопрос 2. ИСТИНА или ЛОЖЬ. Связи между таблицами можно устанавливать на основе уникального идентификатора, который создается с помощью формул DAX.

Вопрос 3. Где можно создать формулу DAX?

А. В области вычислений Power Pivot.

Б. В новом столбце в Power Pivot.

В. В любой ячейке Excel 2013.

Вопрос 4. Какое из приведенных утверждений об иерархиях является верным?

А. После создания иерархии поля, включенные в нее, больше не доступны по отдельности.

Б. После создания иерархии поля, включенные в нее, можно использовать в клиентских средствах, просто перетащив иерархию в Power View или область сводной таблицы.

В. После создания иерархии соответствующие данные в модели данных объединяются в одно поле.

Г. В Power Pivot нельзя создавать иерархии.

Ответы на вопросы теста

1. Правильный ответ: D
2. Правильный ответ: А
3. Правильный ответ: D
4. Правильный ответ: Б

Примечания: Ниже перечислены источники данных и изображений в этом цикле учебников.

• Набор данных об Олимпийских играх © Guardian News & Media Ltd.
• Изображения флагов из справочника CIA Factbook ( cia.gov ).
• Данные о населении из документов Всемирного банка ( worldbank.org ).
• Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.