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

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

В этом учебнике вы научитесь использовать 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 Power Pivot для 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 > Таблицы > Add to Data Model. Этот шаг добавляет таблицу Hosts в модель данных. Также откроется надстройка Power Pivot, с помощью которой выполняются остальные шаги в этой задаче.

3. Обратите внимание, что в окне Power Pivot отображаются все таблицы модели, включая таблицы узла. Просмотрите несколько таблиц. В Power Pivot можно просмотреть все данные модели, даже если они не отображаются ни на одном из листов Excel, например данные по дисциплинам, соревнованиям и медалям ниже, а также S_Teams, W_Teams и спорт.

   

4. В окне Power Pivot в разделе "Вид " щелкните "Представление схемы".

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

   

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

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

2. Щелкните линию, которая соединяет Events и Medals. Выделенные поля определяют связь, как показано на следующем экране.

   

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

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

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

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

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

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

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

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

        

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

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

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

1. В Power Pivot выберите представление данных "ДОМАШНЯЯ СТРАНИЦА >>", чтобы убедиться, что выбрано представление данных, а не представление схемы.

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

   

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

   =CONCATENATE([Edition],[Season])

4. Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.

5. Все строки вычисляемого столбца будут заполнены значениями. Если вы прокрутите таблицу вниз, вы увидите, что каждая строка уникальна, поэтому мы успешно создали поле, которое однозначно определяет каждую строку в таблице Hosts . Такие поля называются первичным ключом.

6. Переименуем вычисляемый столбец в "КодВыпуска". Любой столбец можно переименовать, дважды щелкнув его или щелкнув его правой кнопкой мыши и выбрав команду Переименовать столбец. После завершения таблица Hosts в PowerPoint будет выглядеть так, как на следующем экране.

   

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

1. Для начала создайте новый столбец в таблице Medals (так же, как в таблице Hosts). В Power Pivot выберите таблицу медалей и нажмите кнопку Добавить столбцы > конструктора>. Обратите внимание, что установлен флажок Добавить столбец. Это дает тот же результат, что и выбор столбца Добавить столбец.

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

   = YEAR([Edition])
   
   

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

4. Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец. Введите Year и нажмите клавишу ВВОД.

5. При создании нового столбца в Power Pivot добавлялся еще один столбец-заполнитель с именем "Добавить столбец". Далее нам нужно создать вычисляемый столбец EditionID, поэтому выберите Добавить столбец. Введите указанную ниже формулу DAX в строке формул и нажмите ВВОД.

   =CONCATENATE([Year],[Season])

6. Переименуйте столбец: дважды щелкните CalculatedColumn1 и введите EditionID.

7. Отсортируйте столбец в порядке возрастания. Таблица медалей в Power Pivot теперь выглядит следующим экраном.

   

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

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

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

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

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

   

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

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

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

   

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

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

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

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

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

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

1. В Power Pivot переключитесь в режим диаграммы. Разверните таблицу Events, чтобы было проще работать с ее полями.

2. Нажмите и удерживайте клавишу CTRL, а затем щелкните поля "Спорт", "Дисциплина" и "Событие". Выделив эти три поля, щелкните правой кнопкой мыши и выберите "Создать иерархию". В нижней части таблицы создается родительский узел иерархии (Иерархия 1), из которого выбранные столбцы копируются в иерархию в качестве дочерних узлов. Убедитесь, что сначала в иерархии отображается "Спорт", затем "Дисциплина", а затем "Событие".

3. Дважды щелкните заголовок Иерархия1 и введите SDE, чтобы переименовать новую иерархию. Теперь у вас есть иерархия, которая включает в себя виды спорта, дисциплины и соревнования. Таблица Events теперь выглядит так, как показано на следующем экране.

   

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

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

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

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

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

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

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

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

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

Теперь, когда у нас иерархии 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 создавать привлекательные отчеты об олимпийских медалях. Вы также научитесь выполнять другие вычисления, оптимизировать данные для быстрого создания отчетов и импортировать дополнительные сведения, чтобы сделать отчеты еще более интересными. Вот ссылка:

Учебник 3. Создание отчетов Power View на основе карт

ТЕСТ

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

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

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

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

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

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

Вопрос 2. Верно ли приведенное утверждение? Можно создать связи между таблицами на основе уникального идентификатора, который создается с помощью формул DAX.

А. Да

B: ЛОЖЬ

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

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

б: В новом столбце Power Pivotf.

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

Г. A и B.

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

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

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

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

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

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

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