Tutorial: Expandir relações de Modelos de Dados através do Excel, do Power Pivot e de DAX

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

Adicionar uma relação utilizando a Vista de Diagrama no Power Pivot

Expandir o Modelo de Dados com colunas calculadas

Criar uma hierarquia

Utilizar hierarquias em Tabelas Dinâmicas

Ponto de Verificação e Questionário

No final deste tutorial encontrará um questionário que pode utilizar para testar a sua aprendizagem.

Esta série de tutoriais utiliza dados descritivos das Medalhas Olímpicas, países/regiões anfitriões e diversos eventos desportivos olímpicos. Os tutoriais nesta série são os seguintes:

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

2. Expandir relações de modelo de dados através do Excel, Power Pivote DAX

3. Criar Relatórios do Power View baseados em Mapas

4. Incorporar Dados da Internet e Definir Predefinições de Relatórios do Power View

5. Ajuda do Power Pivot

6. Criar Relatórios Extraordinários do Power View - Parte 2

Recomendamos que respeite a ordem dos mesmos.

Nestes tutoriais utilizam o Excel 2013 com Power Pivot ativado. Para mais informações sobre o Excel 2013, clique aqui. Para obter orientações sobre como ativar Power Pivot, clique aqui.

Adicionar uma relação utilizando a Vista de Diagrama no Power Pivot

Nesta secção utiliza o suplemento Microsoft Office Power Pivot no Excel 2013 para expandir o modelo. A Vista de Diagrama no Microsoft SQL Server Power Pivot para Excel facilita a criação de relações. Primeiro, tem de se certificar de que tem o suplemento Power Pivot ativado.

Nota: O suplemento PowerPivot no Microsoft Excel 2013 faz parte do Office Professional Plus. Para mais informações, consulte Iniciar o Power Pivot no suplemento do Microsoft Excel 2013 .

Adicione Power Pivot ao Friso do Excel ao ativar o suplemento Power Pivot

Quando o Power Pivot está ativado, vê um separador no friso no Excel 2013 designado POWER PIVOT. Para ativar o Power Pivot, siga estes passos.

1. Aceda a FICHEIRO > Opções > Suplementos.

2. Na caixa Gerir perto do fundo, clique em Suplementos COM > Ir.

3. Selecione a caixa Microsoft Office PowerPivot no Microsoft Excel 2013 e, em seguida, clique em OK.

O friso do Excel tem agora um separador POWERPIVOT.

Adicionar uma relação utilizando a vista de diagrama no Power Pivot

O livro do Excel inclui uma tabela designada Anfitriões. Importámos os Anfitriões ao copiar e colá-los no Excel e, em seguida, formatámos os dados como uma tabela. Para adicionar a tabela Anfitriões ao Modelo de Dados, temos de estabelecer uma relação. Vamos usar o Power Pivot para representar visualmente as relações no Modelo de Dados e, em seguida, criar a relação.

1. No Excel, clique no separador Anfitriões para o tornar a folha ativa.

2. No friso, selecione POWERPIVOT > Tabelas > Adicionar ao Modelo de Dados. Este passo adiciona a tabela Anfitriões ao Modelo de Dados. Também abre o suplemento Power Pivot, que irá utilizar para executar os passos restantes nesta tarefa.

3. Repare que a janela de Power Pivot mostra todas as tabelas no modelo, incluindo anfitriões. Clique nas duas tabelas. Na Power Pivot pode ver todos os dados que contém o seu modelo, mesmo se estes não são apresentados quaisquer folhas de cálculo no Excel, como o disciplinas, eventose dados das medalhas abaixo, bem como S_Teams,W_ Equipas, e desportos.
   
   

4. Na janela do Power Pivot, na secção Ver, clique em Vista de Diagrama.

5. Utilize a barra de deslocamento para redimensionar o diagrama para que possa ver todos os objetos no diagrama. Reorganize as tabelas ao arrastar os respetivos barra de título, para que estejam visíveis e posicionado junto a um do outro. Repare que quatro tabelas estão não relacionadas para o resto das tabelas: anfitriões, eventos, W_Teamse S_Teams.
   
   

6. Repare que as tabelas Medalhas e Provas incluem um campo designado ProvaDeModalidade. Após uma análise mais aprofundada, conclui que o campo ProvaDeModalidade na tabela Provas consistente em valores exclusivos, não repetidos.

1. Crie uma relação entre a tabela Medalhas e a tabela Provas. Na Vista de Diagrama, arraste o campo ProvaDeModalidade da tabela Provas para o campo ProvaDeModalidade em Medalhas. Aparece uma linha entre as mesmas, que indica que foi estabelecida uma relação.

2. Clique na linha que liga-se eventos e medalhas. Os campos realçados definem a relação, conforme mostrado no ecrã seguinte.
   
   
   

3. Para ligar Anfitriões ao Modelo de Dados, precisamos de um campo com valores que identificam cada linha da tabela Anfitriões de maneira exclusiva. Em seguida, podemos procurar o nosso Modelo de Dados para ver se existem os mesmos dados noutra tabela. A Vista de Diagrama não nos permite fazer isto. Com Anfitriões selecionado, mude para a Vista de Dados.

4. Após examinar as colunas, podemos aperceba-se que o anfitriões não tem uma coluna de valores exclusivos. Irá temos criá-lo com uma coluna calculada e expressões de análise de dados (DAX).

É bom quando os dados no Modelo de Dados incluem todos os campos necessários para criar relações e combinar dados para visualizar no Power View ou em Tabelas Dinâmicas. No entanto, as tabelas não são sempre fáceis de usar, pelo que a secção seguinte descreve como criar uma nova coluna através de DAX para que possa ser usada para criar uma relação entre tabelas.

Expandir o Modelo de Dados com colunas calculadas

Para estabelecer uma relação entre a tabela Anfitriões e o Modelo de Dados e assim expandir o Modelo de Dados de modo a incluir a tabela Anfitriões, Anfitriões tem de ter um campo que identifica cada linha exclusivamente. Para além disso, o campo tem de corresponder a um campo do Modelo de Dados. Os campos correspondentes - um em cada tabela - são o que permite a associação dos dados da tabela.

Uma vez que a tabela de anfitriões não têm esse campo, tem de criá-lo. Para preservar a integridade do modelo de dados, é possível utilizar Power Pivot para editar ou eliminar dados existentes. No entanto, pode criar novas colunas utilizando campos calculados baseados nos dados existentes.

Ao analisar a tabela Anfitriões e as outras tabelas do Modelo de Dados, vemos um bom candidato para um campo exclusivo que poderíamos criar em Anfitriões e, em seguida, associar a uma tabela no Modelo de Dados. Ambas as tabelas precisam de uma nova coluna calculada para cumprir os requisitos necessários para estabelecer uma relação.

Em Anfitriões, podemos criar uma coluna calculada exclusiva ao combinar o campo Edição (o ano da prova dos Jogos Olímpicos) e o campo Época (Verão ou Inverno). A tabela Medalhas também inclui um campo Edição e um campo Época, pelo que se criarmos uma coluna calculada em cada tabela que combina com os campos Edição e Época, conseguimos estabelecer uma relação entre as tabelas Anfitriões e Medalhas. O ecrã seguinte apresenta a tabela Anfitriões, com os campos Edição e Época selecionados

Criar colunas calculadas através de DAX

Comecemos pela configuração da tabela Anfitriões. O objetivo é criar uma coluna calculada na tabela Anfitriões e, em seguida, na tabela Medalhas, que pode ser usada para estabelecer uma relação entre as mesmas.

No Power Pivot, pode utilizar DAX (Data Analysis Expressions) para criar cálculos. DAX é uma linguagem de fórmula para o Power Pivot e Tabelas Dinâmicas, criada para dados relacionais e análise contextual disponível no Power Pivot. Pode criar fórmulas do DAX numa nova coluna do Power Pivot e na Área de Cálculo no Power Pivot.

1. No Power Pivot, selecione BASE > Ver > Vista de Dados para se certificar de que a Vista de Dados está selecionada em vez de estar na Vista de Diagrama.

2. Selecione a tabela de anfitriões na Power Pivot. Adjacentes às colunas existentes são uma coluna vazia intitulada Adicionar coluna. Power Pivot fornece dessa coluna como um marcador de posição. Existem várias formas de adicionar uma nova coluna a uma tabela no Power Pivot, um dos quais é simplesmente selecione a coluna vazia que tem o título Adicionar coluna.
   
   
   

3. Na barra de fórmulas, escreva a seguinte fórmula do DAX. A função CONCATENAR combina dois ou mais campos numa só. À medida que escreve, conclusão automática ajuda-o a escreva os nomes de colunas e tabelas completamente qualificados e lista as funções que estão disponíveis. Utilize o separador para selecionar as sugestões de conclusão automática. Pode também clicar na coluna enquanto escrever a sua fórmula bem como Power Pivot insere o nome da coluna na sua fórmula.
   
   =CONCATENATE([Edition],[Season])

4. Quando terminar de criar a fórmula, prima ENTER para a aceitar.

5. Os valores são preenchidos nas linhas da coluna calculada. Se deslocar para baixo pela tabela, verá que cada linha é exclusiva - então, criámos um campo que identifica exclusivamente cada linha na tabela Anfitriões. Estes campos chamam-se chave primária.

6. Vamos mudar o nome da coluna calculada para IDDaEdição. Pode mudar o nome de qualquer coluna ao clicar na mesma duas vezes ou clicar com o botão direito na coluna e selecionar Mudar o Nome da Coluna. Quando concluída, a tabela Anfitriões no Power Pivot assemelha-se ao seguinte ecrã.
   
   

A tabela Anfitriões está pronta. Em seguida, vamos criar uma coluna calculada em Medalhas, que corresponde ao formato da coluna IDDaEdição que criámos em Anfitriões para que possamos criar uma relação entre as mesmas.

1. Comece por criar uma nova coluna na tabela Medalhas, tal como fizemos para Anfitriões. No Power Pivot selecione a tabela Medalhas e clique em Estrutura > Colunas > Adicionar. Repare que a opção Adicionar Coluna está selecionada. Tem o mesmo efeito que selecionar Adicionar Coluna.

2. A coluna Edição na tabela Medalhas tem um formato diferente do da coluna Edição na tabela Anfitriões. Antes de combinar ou concatenar a coluna Edição com a coluna Época para criar a coluna IDDaEdição, temos de criar um campo intermediário que aplica o formato certo a Edição. Na barra de fórmulas acima da tabela, escreva a seguinte fórmula DAX.

   = YEAR([Edition])
   

3. Quando terminar de criar a fórmula, prima ENTER. Os valores são preenchidos nas linhas da coluna calculada, com base na fórmula que introduziu. Se comparar esta coluna à coluna Edição na tabela Anfitriões, verá que estas colunas têm o mesmo formato.

4. Mude o nome à coluna ao clicar com o botão direito do rato em ColunaCalculada1 e selecionar Mudar o Nome da Coluna. Escreva Ano e, em seguida, prima ENTER.

5. Quando criou uma nova coluna, o Power Pivot adicionou outra coluna marcadora de posição chamada Adicionar Coluna. Em seguida, vamos criar a coluna calculada IDDaEdição, pelo que deve selecionar Adicionar Coluna. Na barra de fórmulas, escreva a seguinte fórmula DAX e prima ENTER.
   
   =CONCATENATE([Year],[Season])

6. Mude o nome da coluna ao clicar duas vezes na ColunaCalculada1 e ao escrever IDDaEdição.

7. Ordene a coluna por ordem ascendente. A tabela de medalhas na Power Pivot aspeto agora o ecrã seguinte.
   
   
   

Repare que muitos valores do campo IDDaEdição da tabela Medalhas se repetem. Isso é normal e esperado, visto que durante cada edição dos Jogos Olímpicos (agora representada pelo valor IDDaEdição) foram ganhas muitas medalhas. Cada medalha ganha é exclusiva na tabela Medalhas. O identificador exclusivo de cada registo na tabela Medalhas e a chave principal designada é o campo ChaveDaMedalha.

O passo seguinte é criar uma relação entre as tabelas Anfitriões e Medalhas.

Criar uma relação através de colunas calculadas

Em seguida, vamos usar as colunas calculadas que criámos para estabelecer uma relação entre as tabelas Anfitriões e Medalhas.

1. Na janela do Power Pivot, selecione base > vista > vista de diagrama no Friso. Também pode alternar entre a vista de grelha e vista de diagrama utilizando os botões na parte inferior da janela do PowerView, conforme mostrado no ecrã seguinte.
   
   

2. Expanda anfitriões para que possa ver todos os campos. Vamos criado a coluna Iddaedição para funcionar como a chave de primária de tabela de anfitriões (campo de exclusivo e não repetidos) e criada uma coluna de Iddaedição na tabela de medalhas para ativar a criação de uma relação entre elas. É necessário localizar ambos e criar uma relação. Power Pivot fornece uma funcionalidade Localizar no Friso, para que possa procurar um modelo de dados para campos correspondentes. O ecrã seguinte mostra a janela Localizar metadados , com Iddaedição introduzida no campo Localizar.
   

3. Mova a tabela Anfitriões de modo a que fique ao lado da tabela Medalhas.

4. Arraste a coluna de Iddaedição no medalhas para a coluna Iddaedição na anfitriões. 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 secção, aprendeu uma nova técnica para adicionar novas colunas, criadas uma coluna calculada através de DAX e utilizado essa coluna para estabelecer numa nova relação entre tabelas. A tabela de anfitriões agora está integrada no modelo de dados e os respetivos dados estão disponíveis para a tabela dinâmica na Folha1. Também pode utilizar os dados associados para criar adicionais tabelas dinâmicas, gráficos dinâmicos, relatórios do Power View e muito mais.

Criar uma hierarquia

A maioria dos Modelos de Dados incluem dados que são hierárquicos por natureza. Os exemplos comuns incluem dados de calendário, dados geográficos e categorias de produtos. A criação de hierarquias no Power Pivot é útil porque lhe permite arrastar um item para um relatório - a hierarquia - em vez de ter de agrupar e ordenar os mesmos campos repetidamente.

Os dados dos Jogos Olímpicos também são hierárquicos. É útil compreender a hierarquia dos Jogos Olímpicos relativamente a desportos, modalidades e provas. Para cada desporto, existem uma ou mais modalidades associadas (por vezes há várias). Para cada modalidade existem uma ou mais provas (por vezes há várias provas para cada modalidade). A imagem seguinte ilustra a hierarquia.

Nesta secção cria duas hierarquias nos dados dos Jogos Olímpicos que tem estado a utilizar neste tutorial. Em seguida, pode utilizar estas hierarquias para ver como as hierarquias facilitam a organização de dados em Tabelas Dinâmicas e, num tutorial seguinte, no Power View.

Criar uma hierarquia de Desporto

1. No Power Pivot, mude para a Vista de Diagrama. Expanda a tabela Provas para poder ver mais facilmente todos os seus campos.

2. Mantenha premida a tecla CTRL e clique nos campos Desporto, Modalidade e Provas. Com os três campos selecionados, clique com o botão direito e selecione Criar Hierarquia. É criado um nó hierárquico principal na parte inferior da tabela denominado Hierarquia 1 e as colunas selecionadas são copiadas sob a hierarquia como nós subordinados. Certifique-se de que Desporto aparece em primeiro lugar na hierarquia, seguido de Modalidade e Prova.

3. Clique duas vezes no título Hierarquia1 e escreva DMP para mudar o nome da sua nova hierarquia. Agora tem uma hierarquia que inclui Desporto, Modalidade e Prova. A tabela Provas assemelha-se agora ao seguinte ecrã.
   
   

Criar uma hierarquia de Localização

1. Ainda na vista de diagrama no Power Pivot, selecione a tabela de anfitriões e clique no botão Criar hierarquia no cabeçalho de tabela, conforme mostrado no ecrã seguinte.
   
   
   Um nó de elemento principal de hierarquia vazia é apresentada na parte inferior da tabela.

2. Escreva Localizações como nome para a nova hierarquia.

3. Existem várias maneiras de adicionar colunas a uma hierarquia. Arraste os campos Época, Cidade e NOC_PaísRegião para o nome da hierarquia (neste caso, Localizações) até o nome da hierarquia estar realçada e, em seguida, liberte para os adicionar.

4. Clique com o botão direito do rato em IDDaEdição e selecione Adicionar à Hierarquia. Selecione Localizações.

5. Certifique-se de que o seu nós de subordinados da hierarquia estão na ordem. De cima para baixo, a ordem deve ser: época, NOC, Localidade, Iddaedição. Se os nós subordinados forem fora de ordem, simplesmente arraste-os para a ordenação adequado na hierarquia. A tabela deverá ter o aspeto, como o ecrã seguinte.
   

O seu Modelo de Dados agora possui hierarquias que podem ser utilizadas nos relatórios. Na próxima secção, irá aprender como estas hierarquias podem tornar a criação de relatórios mais rápida e mais consistente.

Utilizar hierarquias em Tabelas Dinâmicas

Agora que temos uma hierarquia de Desportos e Localizações, podemos adicioná-los às Tabelas Dinâmicas ou ao Power View e obter rapidamente resultados que incluam agrupamentos úteis de dados. Antes da criação de hierarquias, teve de adicionar campos individuais à Tabela Dinâmica e organizar esses campos da forma que pretende que sejam visualizados.

Nesta secção utiliza as hierarquias que criou na secção anterior para refinar rapidamente a sua Tabela Dinâmica. Em seguida, crie a mesma vista de Tabela Dinâmica ao utilizar os campos individuais na hierarquia, para que possa comparar a utilização de hierarquias e a utilização de campos individuais.

1. Regresse ao Excel.

2. Na Folha1, remova os campos da área LINHAS dos Campos da Tabela Dinâmica e, em seguida, remova todos os campos da área COLUNAS. Certifique-se de que a Tabela Dinâmica está selecionada (que agora é muito pequena, pelo que pode selecionar a célula A1 para se certificar de que a Tabela Dinâmica está selecionada). Os campos restantes nos campos da Tabela Dinâmica são Medalha na área FILTROS e Contagem de Medalhas na área VALORES. A sua Tabela Dinâmica quase vazia deverá ter o aspeto do ecrã seguinte.

   

3. A partir de área de campos da tabela dinâmica, arraste SDE na tabela eventos para a área linhas. Em seguida, arraste localizações a partir da tabela de anfitriões para a área de colunas . Ao arrastar estas duas hierarquias, a sua tabela dinâmica é povoada com muitos dados, as quais estiverem dispostos na hierarquia de definidos nos passos anteriores. Ecrã deverá ter o aspeto, como o ecrã seguinte.
   

4. Vamos filtrar que dados por bit e apenas ver as linhas pela primeira vez dez de eventos. Na tabela dinâmica, clique na seta em Rótulos de linha, clique em (Selecionar tudo) para remover todas as seleções, em seguida, clique nas caixas junto as dez primeiros desportos. A tabela dinâmica aspeto agora o ecrã seguinte.
   

5. Pode expandir qualquer um desses desportos da tabela dinâmica, que é o nível superior da hierarquia SDE, e ver as informações no nível seguinte para baixo na hierarquia (disciplina). Se existir um nível na hierarquia de inferior para esse disciplina, pode expandir a disciplina para ver os seus eventos. Pode fazer o mesmo para a hierarquia de localização, nível superior da qual é época, que é apresentada como Verão e Inverno na tabela dinâmica. Quando o podemos expandir o desporto Aquatics, podemos ver todos os respetivos elementos de disciplina subordinado e os respetivos dados. Quando o podemos expandir a disciplina Escafandros para em Aquatics, vemos demasiado, conforme mostrado no ecrã seguinte seus eventos de subordinados. Que possamos faça o mesmo para água Polo e consulte o artigo que tem apenas um evento.
   

Ao arrastar estas duas hierarquias, criou rapidamente uma Tabela Dinâmica com dados interessantes e estruturados que pode agregar, filtrar e organizar.

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

1. Na área Tabela Dinâmica , removemos Localizações da área COLUNAS. Em seguida, remova DMP da área LINHAS. Está de volta a uma Tabela Dinâmica básica.

2. A partir da tabela Anfitriões, arraste Época, Cidade, NOC_PaísRegião e IDDaEdição para a área COLUNAS e organize por essa ordem, de cima para baixo.

3. A partir da tabela Provas, arraste Desporto, Modalidade e Prova para a área LINHAS e organize por essa ordem, de cima para baixo.

4. Na Tabela Dinâmica, filtre Etiquetas de Linhas para os dez primeiros Desportos.

5. Fechar todas as linhas e colunas, em seguida, expanda Aquatics, em seguida, Escafandros para e água Polo. O livro aspeto o ecrã seguinte.
   

O ecrã parece semelhante, exceto que arrastou sete campos individuais para as áreas Campos da Tabela Dinâmica, em vez de arrastar simplesmente duas hierarquias. Se é a única pessoa a criar Tabelas Dinâmicas ou relatórios do Power View com bases nestes dados, criar hierarquias pode parecer conveniente. No entanto, quando várias pessoas estão a criar relatórios e têm descobrir a ordenação adequada dos campos para obter as vistas corretas, as hierarquias tornam-se rapidamente num aumento de produtividade e permitem manter a consistência.

Noutro tutorial, aprendeu como utilizar hierarquias e outros campos em relatórios visualmente envolventes criados com o Power View.

Ponto de verificação e Questionário

Rever o que aprendeu

O seu livro do Excel tem agora um Modelo de Dados que inclui dados de várias origens, relacionados através de campos existentes e colunas calculadas. Também tem hierarquias que refletem a estrutura dos dados nas suas tabelas, o que torna a criação de relatórios apelativos um processo rápido, consistente e fácil.

Aprendeu que criar hierarquias lhe permite especificar estruturas inerentes nos seus dados e utilizar dados hierárquicos rapidamente nos seus relatórios.

No próximo tutorial da série, cria relatórios apelativos sobre Medalhas Olímpicas no Power View. Também efetua mais cálculos, otimiza dados para criar relatórios mais rapidamente e importa dados adicionais para tornar os relatórios mais interessantes. Aqui está uma ligação:

Tutorial 3: Criar relatórios do Power View baseados em Mapas

QUESTIONÁRIO

Pretende ver se ainda se lembra do que aprendeu? Eis a sua oportunidade. O questionário seguinte destaca as funcionalidades, capacidades ou requisitos aprendidos neste tutorial. Encontrará as respostas na parte inferior da página. Boa sorte!

Pergunta 1: Qual das vistas seguintes lhe permite criar relações entre duas tabelas?

A: Cria relações entre tabelas no Power View.

B: Cria relações entre tabelas na Vista de Estrutura no Power Pivot.

C: Cria relações entre tabelas na Vista de Grelha no Power Pivot.

D: Todas as respostas acima

Pergunta 2: VERDADEIRO ou FALSO: Pode estabelecer relações entre tabelas com base num identificador exclusivo criado utilizando fórmulas DAX.

A: VERDADEIRO

B: FALSO

Pergunta 3: Em qual das seguintes áreas pode criar uma fórmula DAX?

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

B: Numa nova coluna no Power Pivot.

C: Em qualquer célula no Excel 2013.

D: A e B.

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

A: Ao criar uma hierarquia, os campos incluídos já não se encontram disponíveis individualmente.

B: Ao criar uma hierarquia, os campos incluídos e a respetiva hierarquia podem ser utilizados nas ferramentas de cliente ao arrastar simplesmente a hierarquia para uma área do Power View ou da Tabela Dinâmica.

C: Ao criar uma hierarquia, os dados subjacentes no Modelo de Dados são combinados num único campo.

D: Não é possível 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