Transformação de Pivot

Aplica-se a:SQL Server SSIS Integration Runtime no Azure Data Factory

A transformação Pivot transforma um conjunto de dados normalizado em uma versão menos normalizada, porém mais compacta, pivotando os dados de entrada com base no valor de uma coluna. Por exemplo, um conjunto de dados Orders normalizado que lista nome de cliente, produto e quantidade adquirida normalmente tem várias linhas para qualquer cliente que tenha comprado diversos produtos, sendo que cada linha daquele cliente apresenta detalhes do pedido para um produto diferente. Ao pivotar o conjunto de dados com base na coluna de produto, a transformação Pivot pode gerar um conjunto de dados com uma única linha por cliente. Aquela única linha lista todas as compras realizadas pelo cliente, com os nomes de produto mostrados como nomes de coluna, e a quantidade exibida como um valor na coluna de produto. Como nem todo cliente compra todos os produtos, muitas colunas podem conter valores nulos.

Quando um conjunto de dados é dinamizado, as colunas de entrada executam funções diferentes no processo de dinamização. Uma coluna pode participar dos seguintes modos:

• A coluna é passada inalterada até a saída. Como muitas linhas de entrada só podem resultar em uma linha de saída, a transformação copia só o primeiro valor de entrada para a coluna.

• A coluna age como a chave ou parte da chave que identifica um conjunto de registros.

• A coluna define o pivô. Os valores nesta coluna estão associados a colunas do conjunto de dados pivotado.

• A coluna contém valores que são inseridos nas colunas criadas pela tabela dinâmica.

Essa transformação tem uma entrada, uma saída comum e uma saída de erro.

Classificar e duplicar linhas

Para pivotar os dados com eficiência, o que significa criar o menor número possível de registros no conjunto de dados de saída, os dados de entrada devem estar ordenados pela coluna usada para pivotar. Se os dados não estiverem classificados, a transformação Pivot poderá gerar vários registros para cada valor na chave do conjunto, que é a coluna que define o pertencimento ao conjunto. Por exemplo, se um conjunto de dados for pivotado com base na coluna Name, mas os nomes não estiverem ordenados, o conjunto de dados de saída poderá ter mais de uma linha para cada cliente, porque ocorre uma pivoteação toda vez que o valor em Name muda.

Os dados de entrada podem conter linhas duplicadas, o que fará com que a transformação Pivot falhe. “Linhas duplicadas” refere-se a linhas que têm os mesmos valores nas colunas do conjunto de chaves e nas colunas de pivô. Para evitar uma falha, você pode configurar a transformação para redirecionar linhas de erro para uma saída de erro ou pode pré-agregar valores para ter certeza de que não existem linhas duplicadas.

Opções na caixa de diálogo Pivot

Você configura a operação de pivot definindo as opções na caixa de diálogo Pivot. Para abrir a caixa de diálogo Pivot, adicione a transformação Pivot ao pacote no SQL Server Data Tools (SSDT) e, em seguida, clique com o botão direito do mouse no componente e clique em Editar.

A lista a seguir descreve as opções na caixa de diálogo Pivot.

Chave de Pivot
Especifica a coluna a ser usada para obter valores na linha superior (linha de cabeçalho) da tabela.

Definir Chave
Especifica a coluna a ser usada para obter valores na coluna esquerda da tabela. A data de entrada deve ser classificada nesta coluna.

Valor Dinâmico
Especifica a coluna a ser usada para obter os valores da tabela, que não sejam os valores da linha do cabeçalho e da coluna esquerda.

Ignorar valores de Pivot Key sem correspondência e informá-los após a execução do DataFlow
Selecione essa opção para configurar a transformação Dinâmica para ignorar as linhas que contêm valores não reconhecidos na coluna Chave Dinâmica e gerar todos os valores de chave dinâmica para uma mensagem de log, quando o pacote é executado.

Você também pode configurar a transformação para gerar os valores definindo a propriedade personalizada PassThroughUnmatchedPivotKeys como True.

Gerar colunas de saída de pivô a partir de valores
Digite os valores da chave de pivot nesta caixa para permitir que a transformação Pivot crie colunas de saída para cada valor. Você pode inserir os valores antes de executar o pacote ou fazer o seguinte:

1. Selecione a opção Ignorar valores de chave Pivot não correspondentes e informá-los após a execução do DataFlow e clique em OK na caixa de diálogo Pivot para salvar as alterações na transformação Pivot.

2. Execute o pacote.

3. Quando o pacote for executado com êxito, clique na aba Progress e procure a mensagem de log informativa da transformação Pivot que contém os valores da chave de pivot.

4. Clique com o botão direito do mouse na mensagem e clique em Copiar Texto de Mensagem.

5. Clique em Parar Depuração no menu Depurar para alternar para o modo de design.

6. Clique com o botão direito do mouse na transformação Pivot e, em seguida, clique em Editar.

7. Desmarque a opção Ignorar valores de chave dinâmica não correspondentes e relatá-los após a execução do DataFlow e, em seguida, cole os valores da chave dinâmica na caixa Gerar colunas de saída dinâmica a partir dos valores usando o seguinte formato.

   [valor1],[valor2],[valor3]

Gerar Colunas Agora
Clique para criar uma coluna de saída para cada valor de chave dinâmica listada na caixa Gerar colunas de saída dinâmicas a partir dos valores .

As colunas de saída aparecem na caixa Colunas de saída pivotadas existentes.

Colunas de saída pivotadas existentes
Lista as colunas de saída dos valores de chave pivot.

A tabela a seguir mostra um conjunto de dados antes de os dados serem dinamizados na coluna Ano.

A tabela a seguir mostra um conjunto de dados depois que os dados foram pivotados pela coluna Ano.

Para dinamizar os dados na coluna Ano , como mostrado acima, as opções a seguir são definidas na caixa de diálogo Dinâmica .

• O ano é selecionado na caixa de listagem Chave Dinâmica.

• O nome do produto é selecionado na caixa de listagem Definir Chave .

• Total está selecionado na caixa de listagem Pivot Value.

• Os valores a seguir são inseridos na caixa Gerar colunas de saída dinâmicas a partir dos valores .

  [2002],[2003],[2004]

Configuração da Transformação Pivot

Você pode definir propriedades pelo Designer do SSIS ou programaticamente.

Para obter mais informações sobre as propriedades que podem ser definidas na caixa de diálogo Editor Avançado , clique em um dos seguintes tópicos:

• Propriedades comuns

• Propriedades personalizadas de Transformação

Conteúdo relacionado

• Definir as propriedades de um componente de fluxo de dados
• Transformação Unpivot
• Fluxo de Dados
• Transformações do Integration Services