оба выбранных столбца содержат повторяющиеся значения чтобы можно было создать связь
Глава 22. Сложные связи модели данных Power Pivot
Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.
В DAX связи очень важны. Но некоторые связи, как бы это сказать, сложнее, чем другие))
Несколько связей между двумя таблицами
Рассмотрим таблицы Sales и Calendar. Они связаны Calendar[Date] –> Sales[OrderDate]. Файл примера – ch22A_ComplicatedRelationships.xlsx.
Рис. 22.1. Могут ли эти две таблицы иметь более одной связи?
Скачать заметку в формате Word или pdf
Дата заказа [OrderDate] – не единственное поле даты в таблице продаж Sales. Есть еще дата доставки. Что делать, если мы иногда хотим проанализировать наши данные о продажах по дате доставки? Как мы с этим справимся? Создадим ли мы еще одну связь между этими двумя таблицами? Это вообще сработает?
Рис. 22.2. Попытка создать связь Calendar[Date] –> Sales[ShipDate]; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Возможно, вы ожидали ошибки при нажатии кнопки Ok в диалоговом окне выше. Но на самом деле это работает. На приведенной ниже диаграмме можно заметить, что вновь созданная связь представлена пунктирной линией, а не сплошной.
Рис. 22.3. Двойные свидания не всегда проходят гладко))
Легче понять, что происходит, если в окне Power Pivot перейти на вкладку Конструктор и кликнуть на копке Управление связями (рис. 22.4) Обратите внимание на столбец Активно для связей между таблицами продаж Sales и календарем Calendar. В один и тот же момент может быть активна только одна связь между двумя таблицами.
Рис. 22.4. В любой момент времени может быть активна только одна связь между двумя таблицами
Если нам нужно проанализировать данные о продажах по дате доставки, то можем перевернуть активные связи, нажав на кнопку Изменить (см. верхнюю часть рис. 22.4). Если у вас есть сводная таблица, показывающая меру [Total Sales], вот как она изменится при изменении активной связи:
Рис. 22.5. Мера [Total Sales] показывает объем продаж на основе даты заказа или даты доставки, в зависимости от того, какая связь активна
Это неудобно, поэтому давайте рассмотрим несколько более элегантных способов сделать то же самое.
Создание связанных таблиц
Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу t_sales. Но вся прелесть Power Pivot в том, что с его помощью можно производить расчёты, комбинируя данные из нескольких таблиц. По моему личному мнению, если бы даже Powe Pivot не имел встроенного движка функций DAX, одна только способность связывания таблиц, уже оправдывала бы его существование.
Создание связей
Так как же создаются связи между таблицами? Всё очень просто. Заходим в окно Power Pivot и в правом нижнем углу, нажимаем на иконку со всплывающей надписью «Диаграмма».
Либо по кнопке «Представление диаграммы» на вкладке «Главная».
Откроется окно представления диаграммы, в котором все таблицы нашей модели данных показаны в виде отдельных окошек, со списком колонок.
Как видим, эти таблицы между собою пока никак не связаны. Чтобы создать между ними связь, нам сначала нужно определить идентичные колонки. Идентичными колонками, называют колонки содержащие одинаковые данные. К примеру, и в таблице t_sales и в таблице t_products есть колонки КодПродукта, содержащие одинаковые данные (при этом не обязательно, чтобы названия колонок в обоих таблицах были одинаковыми). Свяжем эти две таблицы между собою кликнув по названию колонки КодПродукта в t_sales и удерживая левую кнопку мыши нажатой, перетащим эту колонку к другой колонке КодПродукта в t_products.
Точно также, связь между таблицами можно создать через команду «Создание связи» на вкладке «Конструктор».
Но можно ли создавать связь между таблицами по любым идентичным столбцам? Например столбцы «ЦенаЗаШтуку» в t_sales и «Цена» в t_products содержат одинаковые данные. Попробуем создать между ними связь путём перетаскивания. Power Pivot выдаст ошибку: «Не удалось создать связь, поскольку в каждом столбце содержатся повторяющиеся значения. Выберите по крайней мере один столбец, содержащий только уникальные значения.»
То есть для того, чтобы установить связь между таблицами, один из связывающих столбцов должен содержать только уникальные, не повторяющиеся значения. К примеру, цена у нескольких продуктов может быть одинаковой (повторяться), поэтому использовать эти столбцы для создания связи между таблицами не получится. А вот «КодПродукта» в t_products содержит только уникальные значения, поэтому мы и смогли использовать его для создания связи.
Таблицы, содержащие столбцы с уникальными значениями, по которым устанавливается связывание, называются «таблицами поиска» (lookup tables).
Ниже представлена сводная таблица на основе данных таблицы t_sales.
Как это работает
Фильтры, применённые к таблице поиска передаются через установленную связь основной таблице.
Однако, фильтры применённые к основной таблице, не передаются таблице поиска.
Функция CALCULATE () и связанные таблицы
Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.
В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении каждого клиента. Создадим меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детьми:
[ПродажиРодителям]=
CALCULATE ([ИтогоПродаж],
t_clients[КоличествоДетейНаПопечении]>0)
Работа с повторяющимися значениями
Вы можете работать с повторяющимися наборами значений с помощью преобразований, которые могут удалять дубликаты из данных или фильтровать данные для отображения только дубликатов, что позволяет сосредоточиться на них.
Power Query учитывает регистр. При работе с повторяющимися значениями Power Query учитывает регистр текста, что может привести к нежелательным результатам. В качестве обходного решения пользователи могут применить преобразование в верхнем или нижнем регистре перед удалением дубликатов.
Удаление дубликатов
Одной из операций, которые можно выполнить, является удаление повторяющихся значений из таблицы.
Выберите столбцы, содержащие дублирующиеся значения.
В группе сокращение строк выберите Удалить строки.
В раскрывающемся меню выберите Удалить дубликаты.
Нет никакой гарантии, что первый экземпляр в наборе дубликатов будет выбран при удалении дубликатов.
Удалить дубликаты из нескольких столбцов
В этом примере требуется выявление и удаление дубликатов с помощью всех столбцов таблицы.
У вас есть четыре строки, которые дублируются. Ваша цель состоит в том, чтобы удалить эти повторяющиеся строки, чтобы в таблице были только уникальные строки. Выберите все столбцы из таблицы, а затем выберите Удалить дубликаты.
В результате этой операции вы получите таблицу, которую вы ищете.
Эта операция также может быть выполнена с подмножеством столбцов.
Удалить дубликаты из одного столбца
В этом примере необходимо найти и удалить дубликаты, используя только столбец Category из таблицы.
В результате этой операции вы получите таблицу, которую вы ищете.
Синхронизировать дубликаты
Другой операцией, которую можно выполнить с помощью дубликатов, является возможность сохранения только дубликатов, найденных в таблице.
Выберите столбцы, содержащие дублирующиеся значения.
В группе сокращение строк выберите параметр не учитывать строки.
В раскрывающемся меню выберите пункт не повторять .
Удерживать дубликаты из нескольких столбцов
В этом примере необходимо найти и удержать дубликаты, используя все столбцы из таблицы.
У вас есть четыре строки, которые дублируются. В этом примере ваша цель состоит в том, чтобы в таблице сохранятся только те строки, которые дублируются. Выберите все столбцы в таблице, а затем выберите вариант сохранения дубликатов.
В результате этой операции вы получите таблицу, которую вы ищете.
Удерживать дубликаты из одного столбца
В этом примере необходимо найти и удержать дубликаты, используя только столбец идентификаторов из таблицы.
В результате этой операции вы получите таблицу, которую вы ищете.
Создание связей в представлении схемы Power Pivot
Работа с несколькими таблицами позволяет сделать данные более интересными и релевантными для сводных таблиц и отчетов, которые их используют. При работе с данными в надстройке Power Pivot вы можете использовать представление диаграммы для создания подключений между импортированными таблицами и управления ими.
Создание связей между таблицами необходимо для того, чтобы каждая таблица имела столбец, содержащий совпадающие значения. Например, при соединении разделов «Клиенты» и «Заказы» каждая запись раздела «Заказ» должна будет содержать код клиента или идентификатор, разрешенные для одного клиента.
В окне Power Pivot выберите Представление диаграммы. Макет электронной таблицы «Представление данных» изменится на макет визуальной диаграммы, а все таблицы будут автоматически упорядочены на основе их связей.
Щелкните правой кнопкой диаграмму таблицы и выберите пункт Создание связи. Откроется диалоговое окно «Создание связи».
Если таблица из реляционной базы данных, то столбец будет предустановлен. Если не выбран ни один столбец, выберите один из таблицы, содержащей данные, которые будут использоваться для корреляции строк в каждой таблице.
В поле Связанная таблица подстановки выберите таблицу, содержащую хотя бы один столбец данных, связанный с таблицей, выбранной в поле Таблица.
В поле Столбец выберите столбец, содержащий данные, относящиеся к столбцу в поле Связанный столбец подстановки.
Нажмите кнопку Создать.
Примечание: Хотя Excel проверяет соответствие типов данных между каждым столбцом, он не проверяет наличие в столбцах соответствующих данных и создает отношение, даже если значения не соответствуют. Для проверки связи создайте сводную таблицу, содержащую поля из обеих таблиц. Если данные неправильные (например, пустые ячейки или одинаковые значения повторяются в каждой строке), необходимо выбрать разные поля и, возможно, разные таблицы.
Найдите связанный столбец
Если модели данных содержат много таблиц или таблицы содержат большое количество полей, может быть сложно выбрать столбцы для использования в связях таблицы. Одним из способов нахождения связанного столбца является нахождение его в модели. Этот метод удобен, если известно какой столбец (или ключ) необходимо использовать, но вы не уверены, включает ли в себя столбец другие таблицы. Например, таблицы фактов в хранилище данных обычно содержат много ключей. Можно начать с ключа в этой таблице и затем приступить к поиску модели для таблиц, содержащих этот же ключ. Любую таблицу, содержащую соответствующий ключ, можно использовать в связях для таблицы.
В окне Power Pivot нажмите кнопку Найти.
В окне функции Найти введите ключ или столбец в качестве условия поиска. Элементы поиска должны состоять из имени поля. Нельзя выполнять поиск по характеристикам столбца или типам данных, содержащихся в них.
Щелкните поле Показать скрытые поля во время поиска метаданных. Если ключ был скрыт для уменьшения помех в модели, он, возможно, не отобразится в окне функции «Представление диаграммы».
Нажмите кнопку Найти далее. Если совпадение найдено, столбец в диаграмме таблицы будет выделен. Сейчас известно, какая таблица содержит совпадающий столбец, который может быть использован в связях таблицы.
Изменение активной связи
Таблицы могут иметь несколько связей, но только одна может быть активной. Активные связи используются по умолчанию в вычислениях DAX и навигации по сводному отчету. Неактивные связи могут быть использованы в вычислениях DAX посредством функции USERELATIONSHIP. Дополнительные сведения см. в записи функции USERELATIONSHIP (DAX).
Многочисленные связи существуют, если таблицы были импортированы способом, при котором в исходном источнике данных были заданы многочисленные отношения для этой таблицы, или если вручную были созданы дополнительные связи для поддержки вычислений DAX.
Для изменения активной связи используйте неактивное отношение. Текущая активная связь автоматически станет неактивной.
Наведите указатель на линию связей между таблицами. Неактивная связь отобразится в виде пунктирной линии. (Связь неактивна, потому что между двумя столбцами уже существует косвенная связь.)
Щелкните правой кнопкой линию и выберите функцию Пометить как активную.
Примечание: Активировать отношение можно, только если нет других отношений между двумя таблицами. Если таблицы уже связаны, но нужно изменить режим соотношения, необходимо сначала пометить текущую связь как неактивную, а затем активировать новую.
Размещение таблицы в представлении диаграммы.
Чтобы увидеть все таблицы на экране, щелкните значок По размеру экрана, находящийся в правом верхнем углу представления диаграммы.
Для настройки удобного отображение используйте элемент управления Перетащите для увеличения и мини-карту и перетащите таблицы в необходимый макет. Для прокрутки экрана также можно использовать полосы прокрутки и колесо мыши.
Создание отношений списков с помощью уникальных столбцов и столбцов подстановки
Связи между списками можно создавать с помощью сочетания уникальных столбцов, столбцов под поисков и применения отношений (каскадное и ограничить удаление), что повышает возможности создания более сложных бизнес-решений и сохранения целостности данных.
Общие сведения о связях со списками
В следующих разделах содержится важная фоновая информация о создании связей между исходными и целевыми списками, создании уникальных столбцов и столбцов подытогов, а также об обеспечении связи между списками. Вы увидите, как добавлять столбцы основного и дополнительного подсмотра, просматривать, изменять и удалять элементы в связях между списками, а также управлять связями между списками, если списков много.
Назначение книг курсам с помощью уникальных столбцов и столбцов подсмотра
Исходный список называется Список для чтения, а целевой — Курсы.
Каждый список имеет уникальные столбцы для обеспечения совпадения данных.
Основной столбец подступа в списке источников (список для чтения) «ищет» столбец «Код» в целевом списке (курсы) на основе совпадающих значений.
Дополнительный столбец подставки в списке источников автоматически вставляет столбец Название курса из целевого списка.
При создании столбца можно указать, что он должен содержать уникальные значения. Это означает, что в списке не могут быть повторяющиеся значения в этом столбце. Вы не можете добавить элемент списка, содержащий повторяющиеся значения, изменить существующий элемент списка, который приведет к созданию повторяемого значения, или восстановить элемент из корзины, если это приведет к дублированию. Более того, если вы создали столбец подытогов в списке и определили, что он уникален, столбец в целевом списке также не должен содержать повторяющиеся значения.
Примечание: Уникальность нечувствительна к делу. Например, ord-231 и ord-231 считаются повторяютми значениями.
Уникальный столбец также должен иметь индекс. При создании уникального столбца может появиться запрос на создание индекса, который автоматически создается при нажатии кнопки ОК. Если у уникального столбца есть индекс, его невозможно удалить из него, если только вы не переопределите его, чтобы разрешить повторяющиеся значения.
В следующей таблице 2010 2016 2016 2016 2016 2013 2013 2016 2016 2013 2013
Поддерживаемые типы столбцов
Неподдерживаемые типы столбцов
Выбор (одного значения)
Выбор (нескольких значений)
Гиперссылка или рисунок
Подытая (одно значение)
Подсмотр (несколько значений)
Человек (одно значение)
Человек (несколько значений)
Чтобы создать связь между двумя списками, создайте в списке источников столбец подыскания, который извлекает (или «ищет») одно или несколько совпадающих значений из целевого списка. После создания этого столбца подытого (основного) можно добавить дополнительные столбцы (дополнительные столбцы) из целевого списка в исходный список. Дополнительные дополнительные столбцы должны иметь поддерживаемый тип данных, описанный ниже.
В следующей таблице 2010 2016 2016 2016 2016 2016 2013 2016 2016 2016 2013 2013 2013 2
Поддерживаемые типы столбцов
Неподдерживаемые типы столбцов
Человек или группа
Гиперссылка или рисунок
Существует два типа столбцов подытогов, которые можно создать: столбец подытогов с принудительной связью и столбец подзагонка с неиспользаемой связью.
В столбце подохранения с принудительной связью (также называемой целостностью данных) можно искать отдельные значения и поддерживать целостность данных в целевом списке одним из двух способов:
Каскадное удаление При удалении элемента в целевом списке также удаляется связанный элемент или элементы из списка-источника. В этом случае необходимо убедиться, что все связанные элементы удаляются в рамках одной транзакции базы данных. Например, при удалении заказа в целевом списке можно убедиться, что соответствующие элементы заказа в списке-источнике также будут удалены. Вы не хотите, чтобы элементы заказов были заказана без соответствующего порядка, так как это приведет к созданию потерянных элементов порядка, что может привести к путанице в дальнейшем.
Ограничить удаление При попытке удалить элемент в целевом списке, который имеет один или несколько связанных элементов в списке источников, удалить его из целевого списка будет нельзя. В этом случае необходимо сохранить связанный элемент в целевом списке. Например, если кто-то пытается удалить заказ из целевого списка, у клиента может по-прежнему отложенный заказ и вы хотите предотвратить удаление до обработки заказа. Вы не хотите, чтобы заказ клиента случайно удалялся, так как это повлияет на вашу способность завершить заказ.
Каскадное удаление обеспечивает удаление всех связанных элементов в одной транзакции базы данных. Например, если вы удалили элемент в списке «Курсы» с 1-м ИД списка, он также удаляет элементы из списка для чтения, на которые ссылается удаленный элемент.
Операция ограничить удаление предотвращает удаление элементов из целевого списка, если существуют связанные исходные элементы списка. Поэтому при удалении элемента из списка «Курсы» с номером 3 вы не сможете сделать это, так как на него по-прежнему будет ссылаться хотя бы один элемент списка для чтения.
Принудительное применение отношения между списками помогает поддерживать актуальность данных и предотвращать несоответствия, которые могут привести к проблемам в будущем.
Примечание: Чтобы создать или изменить принудительное отношение, необходимо разрешение на управление списками в списке.
В столбце подытовки, который обеспечивает связь, также должен быть индекс. При создании столбца под вопросом, который обеспечивает связь, может быть предложено создать индекс. Когда вы наберетсяе ОК,индекс будет создан автоматически. После того как в столбце под вопросом, который применяет связь, имеется индекс, удалить индекс из этого столбца будет нельзя, если только не удалить принудительное отношение.
Столбец подытов с неукрепленным отношением
В столбце подзагонков с неиспользаемой связью можно искать одно или несколько значений из списка исходных значений в целевом списке. Если у вас есть разрешения на удаление элемента в списке источников, в целевом списке не действуют дополнительные ограничения на удаление или удаление операций.
Одно из преимуществ создания связей в списке — отображение и изменение столбцов из двух или более списков на одной странице. При создании столбца под уточняется исходный список, в основном столбце отображаются значения из целевого столбца списка, но вы также можете указать, что дополнительные столбцы из целевого списка также отображаются вместе с основным столбцом и другими столбцами из списка.
Примечание: При создании основного столбца следует указать, что нужно принять несколько значений и дополнительные столбцы, связанные с основным столбцом. Хотя вы можете переименовать столбец из списка, другие функции добавить нельзя. Чтобы изменить несколько значений, добавить столбцы или другие параметры после создания списка, перейдите к Параметры> параметры списка.
Значением основного столбца является ссылка, которую можно выбрать для отображения всех значений столбцов этого элемента в целевом списке. Значение каждого дополнительного столбца в целевом списке отображается как текст в столбце исходных списков. При создании столбца подытогов с несколькими значениями в каждом столбце целевого списка (первичного или дополнительного) отображаются соответствующие им значения с запайлами. Для обработки этих основных и дополнительных столбцов в представлении списка можно использовать те же функции, что и для столбцов из списка-источника, например для добавления, удаления, фильтрации и сортировки.