Tutorial: Estender relacionamentos de Modelo de Dados usando o Excel, o Power Pivot e DAX

As seções deste tutorial são as seguintes:

• Adicionar uma relação usando o Modo de Exibição de Diagrama no Power Pivot

• Estender o Modelo de Dados usando colunas calculadas

• Criar uma hierarquia

• Usar hierarquias em Tabelas Dinâmicas

• Ponto de verificação e questionário

No final deste tutorial, você encontrará um questionário que pode ser usado para testar seu aprendizado.

Esta série utiliza dados que descrevem as Medalhas Olímpicas, países anfitriões e vários eventos olímpicos. Nesta série, os tutoriais são os seguintes:

1. Importar dados para o Excel e criar um modelo de dados

2. Estender relações de modelos de dados usando o ExcelPower Pivote Dax

3. Criar relatórios do Power View baseados em mapas

4. Incorporar dados da Internet e definir padrões para os relatórios do Power View

5. Ajuda do Power Pivot

6. Criar relatórios incríveis do Power View - Parte 2

Sugerimos que você veja os tutoriais na ordem.

Esses tutoriais usam o Excel 2013 com o Power Pivot habilitado. Para obter mais informações sobre o Excel 2013, clique aqui. Para obter orientação sobre como habilitar o Power Pivot, clique aqui.

Adicionar uma relação usando o Modo de Exibição de Diagrama no Power Pivot

Nesta seção, use o Microsoft Office Power Pivot no suplemento do Excel 2013 para estender o modelo. O uso do Modo de Exibição de Diagrama no Microsoft SQL Server Power Pivot para Excel facilita a criação de relações. Primeiro, você precisa se certificar de que possui o suplemento Power Pivot habilitado.

Observação: o suplemento de Power Pivot no Microsoft Excel 2013 faz parte do Office Professional Plus. Consulte Iniciar Power Pivot in Microsoft Excel 2013 suplemento para obter mais informações.

Adicionar Power Pivot à faixa de opções do Excel habilitando o suplemento de Power Pivot

Quando o Power Pivot está habilitado, é possível ver uma guia da faixa de opções no Excel 2013 chamada de POWER PIVOT. Para habilitar Power Pivot, execute estas etapas.

1. Acesse ARQUIVO > Opções > Suplementos.

2. Na caixa Gerenciar próxima à parte inferior, clique em Suplementos de COM > Ir.

3. Marque a caixa Microsoft Office Power Pivot no Microsoft Excel 2013 e clique em OK.

A faixa de opções do Excel agora tem uma guia POWER PIVOT.

Adicionar uma relação usando o Modo de Exibição de Diagrama no Power Pivot

A pasta de trabalho do Excel inclui uma tabela chamada Hosts. Importamos Hosts copiando e colando no Excel e formatamos os dados como uma tabela. Para adicionar a tabela Hosts ao Modelo de Dados, precisamos estabelecer uma relação. Vamos usar o Power Pivot para representar visualmente as relações no Modelo de Dados e criar a relação.

1. No Excel, clique na guia Hosts para torná-la a planilha ativa.

2. Na faixa de opções, selecione POWER PIVOT > Tabelas > Adicionar ao Modelo de Dados. Esta etapa adiciona a tabela Hosts ao Modelo de dados. Ela também abre o suplemento Power Pivot, que você usa para realizar as etapas restantes desta tarefa.

3. Observe que a janela Power Pivot mostra todas as tabelas no modelo, incluindo hosts. Clique em algumas tabelas. Em Power Pivot você pode exibir todos os dados que o modelo contém, mesmo se eles não forem exibidos em nenhuma planilha do Excel, como os dados disciplinas, eventose medalhas abaixo, bem como S_Teams,W_Teamse esportes.
   
   

4. Na janela do Power Pivot, na seção Modo de Exibição, clique em Modo de Exibição de Diagrama.

5. Use a barra de controle deslizante para redimensionar o diagrama de forma que você possa ver todos os objetos no diagrama. Reorganize as tabelas arrastando a barra de título, para que elas fiquem visíveis e posicionadas ao lado uma da outra. Observe que quatro tabelas não estão relacionadas ao restante das tabelas: hosts, eventos, W_Teamse S_Teams.
   
   

6. Perceba que as tabelas Medalhas e Eventos têm um campo chamado EventoDisciplina. Após outra inspeção, você determina que o campo EventoDisciplina da tabela Eventos é composto de valores exclusivos e não repetidos.

1. Crie uma relação entre as tabelas Medalhas e Eventos. No Modo de Exibição de Diagrama, arraste o campo EventoDisciplina da tabela Eventos para o campo EventoDisciplina em Medalhas. Aparece uma linha entre eles, indicando que uma relação foi estabelecida.

2. Clique na linha que conecta Eventos e Medalhas. Os campos realçados definem a relação, conforme exibido na imagem a seguir.
   
   
    

3. Para conectar Hosts ao Modelo de Dados, precisamos de um campo com valores que identificam exclusivamente cada linha na tabela Hosts. Em seguida, podemos procurar nosso Modelo de dados para ver se esses mesmos dados existem em outra tabela. Analisar o Modo de Exibição de Diagrama não permite que façamos isto. Com Hosts selecionado, alterne de volta para o Modo de Exibição de Dados.

4. Depois de examinar as colunas, percebemos que Hosts não tem uma coluna de valores exclusivos. Teremos que criá-la usando uma coluna calculada e expressões DAX (Expressões de Análise de Dados).

É bom quando os dados em seu Modelo de dados têm todos os campos necessários para criar relacionamentos e combinar dados no Power View ou Tabelas dinâmicas. Mas as tabelas nem sempre são tão cooperativas, portanto a próxima seção descreve como criar uma nova coluna, usando DAX, que pode ser usado para criar uma relação entre tabelas.

Estender o Modelo de Dados usando colunas calculadas

Para estabelecer uma relação entre a tabela Hosts e o Modelo de Dados, e estender nosso Modelo de Dados para incluir a tabela Hosts, Hosts precisa ter um campo que identifique exclusivamente cada linha. Além disso, esse campo deve corresponder a um campo no Modelo de dados. Os campos correspondentes, um em cada tabela, são os que permitem que os dados das tabelas sejam associados.

Como a tabela Hosts não tem esse campo, você precisa criá-lo. Para preservar a integridade do Modelo de Dados, você não pode usar o Power Pivot para editar ou excluir dados já existentes. Você pode, no entanto, criar novas colunas usando campos calculados com base nos dados existentes.

Analisando a tabela Hosts e, em seguida, analisando outras tabelas do Modelo de Dados, encontramos um bom candidato para um campo exclusivo que podemos criar em Hosts e depois associamos a uma tabela no Modelo de dados. Ambas as tabelas exigirão uma nova coluna calculada a fim de satisfazer aos requisitos necessários para estabelecer uma relação.

Em Hosts, podemos criar uma única coluna calculada combinando o campo Edição (o ano do evento Olimpíadas) e o campo Estação (Verão ou Inverno). Na tabela Medalhas, há também um campo Edição e um campo Estação, então se criarmos uma coluna calculada em cada uma dessas tabelas que combine os campos Edição e Estação, poderemos estabelecer uma relação entre Hosts e Medalhas. A tela a seguir mostra a tabela Hosts, com seus campos Edição e Estação selecionados

Criar colunas calculadas usando DAX

Vamos começar com a tabela Hosts. O objetivo é criar uma coluna calculada na tabela Hosts e, em seguida, na tabela Medalhas, que pode ser usada para estabelecer uma relação entre elas.

No Power Pivot, é possível usar DAX (Expressões de Análise de Dados) para criar cálculos. DAX é uma linguagem de fórmula para Power Pivot e Tabelas Dinâmicas, projetadas para os dados relacionais e análise contextual disponíveis em Power Pivot. Você pode criar fórmulas DAX em uma nova coluna do Power Pivot e na Área de Cálculo em Power Pivot.

1. No Power Pivot, selecione PÁGINA INICIAL > Exibir > Exibição de Dados para verificar se o Modo de Exibição de Dados está selecionado, em vez do Modo de Exibição de Diagrama.

2. Selecione a tabela Hosts no Power Pivot. Adjacente às colunas existentes está uma coluna vazia chamada Adicionar Coluna. O Power Pivot fornece essa coluna como um espaço reservado. Existem muitas maneiras de adicionar uma nova coluna a uma tabela no Power Pivot, uma delas é simplesmente selecionar a coluna vazia que tem o título Adicionar Coluna.
   
   
   

3. Na barra de fórmulas, digite a fórmula DAX a seguir. A função CONCATENATE combina dois ou mais campos em um. À medida que você digita, o Preenchimento Automático ajuda a digitar os nomes plenamente qualificados de colunas e tabelas, e mostra uma lista das funções disponíveis. Use tab para selecionar sugestões do Preenchimento Automático. Você também pode apenas clicar na coluna ao digitar a fórmula e o Power Pivot insere o nome da coluna na sua fórmula.
   
   =CONCATENATE([Edition],[Season])

4. Quando terminar de criar a fórmula, pressione Enter para aceitá-la.

5. Os valores são preenchidos para todas as linhas na coluna calculada. Se você rolar a tabela para baixo, verá que cada linha é exclusiva - portanto criamos com sucesso um campo que identifica exclusivamente cada linha na tabela Hosts. Tais campos são chamados de chave primária.

6. Vamos renomear a coluna calculada para IDdaEdição. Você pode renomear qualquer coluna clicando duas vezes ou clicando com o botão direito do mouse na coluna e escolhendo Renomear Coluna. Quando concluída, a tabela Hosts no Power Pivot parece a tela a seguir.
   
   

A tabela Hosts está pronta. Em seguida, vamos criar uma coluna calculada em Medalhas que corresponda ao formato da coluna IDdaEdição que criamos em Hosts, para podermos criar uma relação entre elas.

1. Comece criando uma nova coluna na tabela Medalhas, como fizemos para Hosts. No Power Pivot selecione a tabela Medalhas e clique em Design > Colunas > Adicionar. Perceba que Adicionar Coluna está selecionado. Isso tem o mesmo efeito que simplesmente selecionar Adicionar Coluna.

2. A coluna Edição em Medalhas tem um formato diferente do que a coluna Edição em Hosts. Antes de combinar, ou concatenar, a coluna Edição com a coluna Estação para criar a coluna IDdaEdição, precisamos criar um campo intermediário que coloque a Edição no formato certo. Na barra de fórmulas acima da tabela, digite a fórmula DAX a seguir.

   = YEAR([Edition])
   

3. Quando terminar de criar a fórmula, pressione Enter. Os valores são preenchidos para todas as linhas na coluna calculada, com base na fórmula inserida. Se você comparar esta coluna com a coluna Edição em Hosts, você verá que essas colunas têm o mesmo formato.

4. Renomeie a coluna clicando com o botão direito do mouse em ColunaCalculada1 e selecionando Renomear Coluna. Digite Ano e pressione Enter.

5. Quando uma nova coluna é criada, o Power Pivot acrescenta outra coluna de espaço reservado chamada Adicionar Coluna. Em seguida, queremos criar a coluna calculada IDdaEdição, sendo assim, selecione Adicionar Coluna. Na barra de fórmulas, digite a seguinte fórmula DAX e pressione Enter.
   
   =CONCATENATE([Year],[Season])

6. Renomeie a coluna clicando duas vezes em ColunaCalculada1 e digitando IDdaEdição.

7. Classificar a coluna em ordem crescente. A tabela Medalhas no Power Pivot parece com a tela a seguir.
   
   
    

Observe que muitos valores são repetidos no campo IDdaEdição da tabela Medalhas. Isso é normal e esperado, uma vez que durante cada edição dos Jogos Olímpicos (agora representado pelo valor IDdaEdição) muitas medalhas são entregues. O que é único na tabela Medalhas é cada medalha entregue. O identificador exclusivo de cada registro na tabela Medalhas e sua chave primária designada é o campo ChaveMedalha.

A próxima etapa é criar uma relação entre Hosts e Medalhas.

Criar uma relação usando colunas calculadas

Em seguida, vamos usar as colunas calculadas que criamos para estabelecer uma relação entre Hosts e Medalhas.

1. Na janela Power Pivot, selecione Página Inicial >Exibição > Modo de Exibição de Diagrama na faixa de opções. Você também pode alternar entre o modo de exibição de Grade e o modo de exibição de Diagrama usando os botões na parte inferior da janela PowerView, conforme exibido na imagem a seguir.
   
   

2. Expanda Hosts para que você possa ver todos os seus campos. Criamos a coluna IDdaEdição para atuar como chave primária da tabela Hosts (campo exclusivo e não repetido) e criamos uma coluna IDdaEdição na tabela Medalhas para permitir o estabelecimento de uma relação entre elas. Precisamos encontrar ambas e criar uma relação. O Power Pivot fornece um recurso Localizar na faixa de opções, de modo que você possa procurar seu Modelo de Dados dos campos correspondentes. A tela a seguir mostra a janela Localizar Metadados, com a IDdaEdição inserida no campo Localizar.
   

3. Posicione a tabela Hosts para que fique ao lado de Medalhas.

4. Arraste a coluna IDdaEdição em Medalhas para a coluna IDdaEdição em Hosts. O Power Pivot cria uma relação entre as tabelas com base na coluna IDdaEdição e desenha uma linha entre as duas colunas, indicando a relação.
   
   

Nesta seção, você aprendeu uma nova técnica para adicionar novas colunas, criou uma coluna calculada usando DAX e usou essa coluna para estabelecer uma nova relação entre as tabelas. A tabela Hosts agora é integrada ao Modelo de Dados e seus dados ficam disponíveis para a Tabela Dinâmica na Planilha1. Você também pode usar os dados associados ao criar Tabelas dinâmicas adicionais, Gráficos dinâmicos, relatórios do Power View e muito mais.

Criar uma hierarquia

A maioria dos Modelos de dados inclui dados que são inerentemente hierárquicos. Exemplos comuns incluem dados de calendário, dados geográficos e categorias de produtos. Criar hierarquias no Power Pivot é útil porque você pode arrastar um item até um relatório (a hierarquia) em vez de ter que montar e ordenar os mesmos campos todas as vezes.

Os dados olímpicos também são hierárquicos. É útil compreender a hierarquia dos Jogos Olímpicos em termos de esportes, disciplinas e eventos. Para cada esporte, há uma ou mais disciplinas associadas (às vezes há muitas). E para cada disciplina, há um ou mais eventos (novamente, às vezes há muitos eventos em cada disciplina). A imagem a seguir ilustra a hierarquia.

Nesta seção, crie duas hierarquias dentro dos dados Olímpicos que você está usando neste tutorial. Em seguida, use essas hierarquias para ver como as hierarquias facilitam a organização de dados em Tabelas dinâmicas e, em um tutorial posterior, no Power View.

Criar uma hierarquia de esporte

1. No Power Pivot, alterne para Modo de Exibição de Diagrama. Expanda a tabela Eventos para visualizar com maior facilidade todos os seus campos.

2. Mantenha pressionada a tecla Ctrl e clique nos campos Esporte, Disciplina e Evento. Com esses três campos selecionados, clique com o botão direito do mouse e selecione Criar Hierarquia. Um nó de hierarquia pai, Hierarquia 1, é criado na parte inferior da tabela e as colunas selecionadas são copiadas sob a hierarquia como nós filho. Verifique se Esporte aparece primeiro na hierarquia, em seguida Disciplina e Evento.

3. Clique duas vezes no título, Hierarquia1 e digite SDE para renomear sua nova hierarquia. Agora você tem uma hierarquia que inclui Esporte, Disciplina e Evento. Sua tabela Eventos agora se parece com a seguinte tela.
   
   

Criar uma hierarquia de localização

1. Ainda no Modo de Exibição de Diagrama no Power Pivot, selecione a tabela Hosts e clique no botão Criar Hierarquia no cabeçalho da tabela, conforme exibido na tela a seguir.
   
   
   Um nó pai de hierarquia vazio aparece na parte inferior da tabela.

2. Digite Locais como o nome de sua nova hierarquia.

3. Há muitas maneiras de adicionar colunas a uma hierarquia. Arraste os campos Estação, Cidade e NOC_PaísRegião até o nome da hierarquia (neste caso, Locais), até o nome da hierarquia ser realçado e, em seguida, solte para adicioná-los.

4. Clique com o botão direito do mouse em IDdaEdição e selecione Adicionar à Hierarquia. Escolha Locais.

5. Certifique-se de que os nós filho da hierarquia estejam em ordem. De cima para baixo, o pedido deve ser: temporada, NOC, cidade, Ediçãoid. Se os nós filhos estiverem fora de ordem, simplesmente arraste-os para a ordem adequada na hierarquia. Sua tabela deve parecer com a tela a seguir.
   

Seu Modelo de dados agora tem hierarquias que podem ser usadas de forma útil em relatórios. Na próxima seção, você aprenderá como essas hierarquias podem agilizar a criação de relatórios e torná-la mais consistente.

Usar hierarquias em Tabelas Dinâmicas

Agora que temos uma hierarquia de Esportes e uma hierarquia de Locais, podemos adicioná-los às Tabelas dinâmicas ou Power View e rapidamente obter resultados que incluem agrupamentos úteis de dados. Antes da criação de hierarquias, era necessário adicionar campos individuais à Tabela Dinâmica e organizar esses campos da forma como você deseja exibi-los.

Nesta seção, use as hierarquias criadas na seção anterior para refinar rapidamente sua Tabela Dinâmica. Em seguida, crie o mesmo modo de exibição de Tabela Dinâmica usando os campos individuais na hierarquia, só assim você pode comparar usando hierarquias em vez de usar campos individuais.

1. Volte para o Excel.

2. Em Planilha1, remova os campos da área LINHAS dos Campos da Tabela Dinâmica e, em seguida, remova todos os campos da área COLUNAS. Selecione a Tabela Dinâmica (que agora é muito pequena, então escolha a célula A1 para ter certeza de que sua Tabela Dinâmica esteja selecionada). Os únicos campos restantes nos campos da Tabela Dinâmica são Medalha na área FILTROS e Contagem de Medalhas na área VALORES. Sua Tabela Dinâmica quase vazia deverá parecer com a tela a seguir.

   

3. Na área Campos da Tabela Dinâmica, arraste SDE da tabela Eventos para a área LINHAS. Em seguida, arraste Locais da tabela Hosts para a área COLUNAS. Apenas arrastando as duas hierarquias, sua Tabela Dinâmica é preenchida com muitos dados, todos organizados na hierarquia definida nas etapas anteriores. Sua tabela deve parecer com a tela a seguir.
   

4. Vamos filtrar esses dados um pouco e ver apenas as dez primeiras linhas de eventos. Na Tabela Dinâmica, clique na seta em Rótulos de Linha, clique em (Selecionar Tudo) para remover todas as seleções e clique nas caixas ao lado dos dez primeiros Esportes. Sua Tabela Dinâmica agora parece com a tela a seguir.
   

5. Você pode expandir qualquer um dos Esportes na Tabela Dinâmica, que é o nível superior da hierarquia de SDE, e consulte as informações no próximo nível abaixo na hierarquia (disciplina). Se houver um nível inferior na hierarquia existir para essa disciplina, você pode expandir a disciplina para ver seus eventos. Você pode fazer o mesmo para a Hierarquia Local, sendo que o nível superior é Estação, que aparece como Verão e Inverno na Tabela Dinâmica. Quando expandimos o esporte Aquáticos, vemos que todos seus elementos de disciplina filhos e seus dados. Quando expandimos a disciplina Mergulho sob Aquáticos, vemos seus eventos filhos também, conforme exibido na imagem a seguir. Podemos fazer o mesmo para o Polo Aquático e ver que ele tem apenas um evento.
   

Ao arrastar essas duas hierarquias, você criou rapidamente uma Tabela Dinâmica com dados estruturados e interessantes nos quais é possível realizar uma busca detalhada, filtrar e organizar.

Agora vamos criar a mesma Tabela Dinâmica, sem o benefício de hierarquias.

1. Na área Campos da Tabela Dinâmica, remova Locais da área COLUNAS. Então remova a SDE da área LINHAS. Você está de volta a uma Tabela Dinâmica básica.

2. Na tabela Hosts, arraste Estação, Cidade, NOC_PaísRegião e IDdaEdição para a área COLUNAS e organize-os nessa ordem, de cima para baixo.

3. Na tabela Eventos, arraste Esporte, Disciplina e Evento para a área LINHAS e organize-os nessa ordem, de cima para baixo.

4. Na Tabela Dinâmica, filtre os Rótulos de Linha para os 10 principais Esportes.

5. Recolha todas as linhas e colunas e expanda Esportes aquáticos, Mergulho e Pólo aquático. Sua pasta de trabalho se parece com a seguinte tela.
   

A tela é semelhante, exceto pelo fato de que você arrastou sete campos individuais para as áreas Campos da Tabela Dinâmica, em vez de simplesmente arrastar duas hierarquias. Se você é a única pessoa criando relatórios de Tabelas dinâmicas ou do Power View com base nesses dados, a criação de hierarquias pode apenas parecer conveniente. Mas, quando muitas pessoas estão criando relatórios e devem descobrir a ordem correta dos campos a fim de obter os modos de exibição corretos, as hierarquias tornam-se rapidamente um meio de aprimoramento da produtividade e permitem a consistência.

Em um outro tutorial, você aprende a usar hierarquias e outros campos em relatórios visualmente envolventes criados com o Power View.

Ponto de verificação e Questionário

Revise o que você aprendeu

Agora, sua pasta de trabalho do Excel tem um Modelo de dados que inclui dados de várias fontes, relacionadas por meio do uso de campos existentes e colunas calculadas. Você também tem hierarquias que refletem a estrutura de dados em suas tabelas, tornando a criação de relatórios convincentes fácil, rápida e consistente.

Você aprendeu que a criação de hierarquias permite especificar a estrutura inerente aos seus dados e usar rapidamente os dados hierárquicos em seus relatórios.

No próximo tutorial desta série, crie relatórios visualmente atraentes sobre medalhas olímpicas, usando o Power View. Você também fazer mais cálculos, otimizar dados para criação rápida de relatórios e importar dados adicionais para tornar esses relatórios ainda mais interessantes. Veja um link:

Tutorial 3: criar relatórios do Power View baseados em mapas

QUESTIONÁRIO

Quer ver o quanto você se lembra do que aprendeu? Aqui está sua chance. O questionário a seguir destaca recursos, capacidades ou requisitos sobre os quais você aprendeu neste tutorial. Na parte inferior da página, você encontrará as respostas. Boa sorte!

Pergunta 1: Qual dos modos de exibição a seguir permite a criação de relações entre duas tabelas?

A: Você cria relações entre tabelas no Power View.

B: Você cria relações entre tabelas usando o Modo de Exibição de Design no Power Pivot.

C: Você cria relações entre tabelas usando o Modo de Exibição de Grade no Power Pivot.

D: Todas as anteriores

Pergunta 2: VERDADEIRO ou falso: você pode estabelecer relações entre tabelas com base em um identificador exclusivo que é criado usando fórmulas DAX.

A: VERDADEIRO

B: FALSO

Pergunta 3: Em qual das seguintes opções você pode criar uma fórmula DAX?

A: Na Área de Cálculo do Power Pivot.

B: Em uma nova coluna no Power Pivotf.

C: Em qualquer célula no Excel 2013.

D: A e B.

Pergunta 4: Qual das seguintes afirmações é verdadeira sobre hierarquias?

A: Quando você cria uma hierarquia, os campos incluídos já não ficam mais disponíveis individualmente.

B: Quando você cria uma hierarquia, os campos incluídos, incluindo a hierarquia, podem ser usados em ferramentas de cliente simplesmente arrastando a hierarquia para uma área do Power View ou da Tabela Dinâmica.

C: Quando você cria uma hierarquia, os dados subjacentes no Modelo de dados são combinados em um campo.

D: Você não pode criar hierarquias no Power Pivot.

Respostas do questionário

1. Resposta correta: D

2. Resposta correta: A

3. Resposta correta: D

4. Resposta correta: B