Поворотное преобразование

Область применения:SQL Server среда выполнения интеграции SSIS в Фабрика данных Azure

Преобразование «Pivot» преобразует нормализованный набор данных в менее нормализованную, но более компактную форму путём преобразования входных данных по значениям одного из столбцов. Например, нормализованный набор данных Orders , содержащий имя клиента, продукт и количество приобретенных единиц продукта, обычно содержит множество строк для клиента, купившего несколько наименований продуктов. Каждая строка содержит подробности о различных продуктах. Если выполнить сведение набора данных по столбцу продукта, преобразование Pivot может сформировать набор данных с одной строкой на каждого клиента. Эта одна строка содержит все покупки клиента, при этом названия продуктов указаны в качестве названий столбцов, а количество — в качестве значения в столбце соответствующего продукта. Так как не каждый клиент приобретает все виды продукции, многие столбцы могут содержать значения NULL.

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

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

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

  • Столбец определяет сведение. Значения в этом столбце связаны со столбцами в сводном наборе данных.

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

Это преобразование содержит один вход, один обычный вывод и один вывод ошибок.

Сортировка и повторяющиеся строки

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

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

Параметры в диалоговом окне Pivot

Операция поворота настраивается, если задать параметры в диалоговом окне Pivot. Чтобы открыть диалоговое окно Pivot, добавьте в пакет преобразование Pivot в SQL Server Data Tools (SSDT), а затем щелкните правой кнопкой мыши компонент и выберите Изменить.

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

Сводный ключ
Указывает столбец для использования в качестве значений верхней строки (строки заголовка) таблицы.

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

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

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

Также вы можете настроить преобразование для вывода значений, установив для пользовательского свойства PassThroughUnmatchedPivotKeys значение True.

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

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

  2. Запустите пакет.

  3. После успешного выполнения пакета щелкните вкладку Ход выполнения и найдите в журнале информационное сообщение от преобразования «Поворот», содержащее значения ключей поворота.

  4. Щелкните сообщение правой кнопкой мыши и выберите пункт Копировать текст сообщения.

  5. Выберите пункт Остановить отладку в меню Отладка , чтобы переключиться в режим конструктора.

  6. Щелкните правой кнопкой мыши преобразование "Сведение" и выберите команду Редактировать.

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

    [значение1],[значение2],[значение3]

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

Выходные столбцы отображаются в поле Существующие сводные выходные столбцы.

Существующие сводные столбцы вывода
Выводит список выходных столбцов для значений сводного ключа

В следующей таблице представлен набор данных до преобразования в сводную таблицу по столбцу Year.

Год Название продукта Итог
2004 Шина для велосипеда HL Mountain 1504884,15
2003 Камера для шоссейного велосипеда 35920.50
2004 Фляга для воды — 30 унций. 2805.00
2002 Шина для туристического велосипеда 62364.225

В следующей таблице показан набор данных после того, как данные были сведены по столбцу Year.

Название продукта 2002 2003 2004
Шина для велосипеда HL Mountain 141164.10 446297,775 1504884,15
Камера для шоссейного велосипеда 3592.05 35920.50 89801.25
Фляга для воды — 30 унций. NULL NULL 2805.00
Шина для туристического велосипеда 62364.225 375051.60 1041810.00

Для сведения данных по столбцу Год , как показано выше, в диалоговом окне Сведения задаются следующие параметры.

  • Значение «Год» выбирается в списке Сводный ключ.

  • Название продукта выбрано в списке Задать ключ.

  • В списке Значение сводной таблицы выбран пункт «Итог».

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

    [2002],[2003],[2004]

Настройка преобразования Pivot

Свойства могут быть заданы с помощью конструктора SSIS или программным путем.

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

Связанные материалы

  • Last updated on