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 do Modelo de Dados com o Excel, Power Pivot e 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.

Estes tutoriais utilizam o Excel 2013 com o Power Pivot ativado. Para obter mais informações sobre o Excel 2013, clique aqui. Para obter orientações sobre como ativar o 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. Consulte o artigo Iniciar o suplemento Power Pivot in Microsoft Excel 2013 para obter mais informações.

Adicionar 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 utilizar 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 Power Pivot mostra todas as tabelas no modelo, incluindo Anfitriões. Clique em algumas tabelas. No Power Pivot pode ver todos os dados que o seu modelo contém, mesmo que não sejam apresentados em folhas de cálculo no Excel, como os dados Disciplinas, Provas e Medalhas abaixo, bem como S_Teams, W_Teamse Desporto.
   
   

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

5. Utilize a barra de deslize para redimensionar o diagrama de modo a poder ver todos os objetos existentes no diagrama. Reorganize as tabelas ao arrastar a barra de título, para que fiquem visíveis e posicionadas junto umas às outras. Repare que quatro tabelas não estão relacionadas com as restantes tabelas: Anfitriões, Eventos, W_Teams e 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 Provas e Medalhas. Os campos destacados definem a relação, tal como apresentado 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. Procurar na Vista de Diagrama não nos permite fazê-lo. Com Anfitriões selecionado, mude para a Vista de Dados.

4. Depois de examinar as colunas, percebemos que anfitriões não têm uma coluna de valores exclusivos. Teremos de criá-la com uma coluna calculada e o DAX (Data Analysis Expressions).

É bom quando os dados no seu Modelo de Dados têm todos os campos necessários para criar relações e combinar dados para visualizar no Power View ou tabelas dinâmicas. No entanto, as tabelas nem sempre são tão cooperativas, pelo que a secção seguinte descreve como criar uma nova coluna, utilizando DAX, que pode ser utilizada 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. Esses campos correspondentes, um em cada tabela, são o que permite que os dados das tabelas sejam associados.

Uma vez que a tabela Anfitriões não tem esse campo, tem de criá-lo. Para preservar a integridade do Modelo de Dados, não pode utilizar Power Pivot para editar ou eliminar dados existentes. Contudo, pode criar novas colunas ao utilizar campos calculados com base em 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

Vamos começar com a 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 Anfitriões noPower Pivot. Existe uma coluna vazia designada Adicionar Coluna adjacente às colunas existentes. O Power Pivot disponibiliza essa coluna como um marcador de posição. Existem diversas maneiras de adicionar uma nova coluna a uma tabela no Power Pivot, uma das quais é simplesmente selecionar a coluna vazia que tem o título Adicionar Coluna.
   
   
   

3. Na barra de fórmulas, escreva a seguinte fórmula DAX. A função CONCATENAR combina dois ou mais campos num só. À medida que escreve, a Conclusão Automática ajuda-o a introduzir os nomes totalmente qualificados das colunas e tabelas e apresenta as funções disponíveis. Utilize a tecla de TABULAÇÃO para selecionar as sugestões da Conclusão Automática. Também pode clicar simplesmente na coluna ao escrever 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, prima ENTER para a aceitar.

5. Os valores são preenchidos nas linhas da coluna calculada. Se se deslocar para baixo na tabela, verá que cada linha é exclusiva, pelo que criámos com êxito 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 corresponda 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 com a coluna Edição em 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 crescente. A tabela Medalhas no Power Pivot assemelha-se agora ao seguinte ecrã.
   
   
   

Repare que muitos valores do campo IDDaEdição da tabela Medalhas se repetem. Tudo bem e esperado, uma vez que durante cada edição dos Jogos Olímpicos (agora representado pelo valor IDDaEdição) foram atribuídas 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 utilizar as colunas calculadas que criámos para estabelecer uma relação entre Anfitriões e Medalhas.

1. Na janela do Power Pivot, selecione Base > Ver > Vista de Diagrama a partir do friso. Também pode alternar entre a Vista de grelha e a Vista de diagrama com os botões na parte inferior da janela do PowerView, tal como apresentado no ecrã seguinte.
   
   

2. Expanda Anfitriões de maneira a ver todos os campos. Criámos a coluna IDDaEdição de modo a agir como a chave principal (campo exclusivo e não repetido) da tabela Anfitriões e criámos uma coluna IDDaEdição na tabela Medalhas para permitir estabelecer uma relação entre as duas. Precisamos de encontrar ambas e de criar uma relação. O Power Pivot disponibiliza uma funcionalidade Localizar no friso, pelo que pode procurar campos correspondentes no seu Modelo de Dados. O ecrã seguinte apresenta a janela Localizar Metadados com IDDaEdição introduzido no campo Localizar.
   

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

4. Arraste a coluna IDDaEdição na tabela Medalhas para a coluna IDDaEdição na tabela Anfitriões. O Power Pivot cria uma relação entre as tabelas com base na coluna IDDaEdição e desenha uma linha entre as suas colunas, que indica a relação das mesmas.
   
   

Nesta secção, aprendeu uma nova técnica para adicionar novas colunas, criou uma coluna calculada através de DAX e utilizou essa coluna para estabelecer uma nova relação entre tabelas. A tabela Anfitriões está agora 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 mais 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, em termos de desporto, disciplinas e eventos. 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, vai criar duas hierarquias nos dados olímpicos que tem utilizado 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 Anfitriões e clique no botão Criar Hierarquia no cabeçalho da tabela, tal como apresentado no ecrã seguinte.
   
   
   É apresentado um nó principal de hierarquia vazio 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 os nós subordinados da hierarquia estão ordenados. De cima para baixo, a encomenda deve ser: Season, NOC, City, EditionID. Se os nós subordinados estiverem desordenados, basta arrastá-los para a ordem adequada na hierarquia. A sua tabela deve ter o aspeto do 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 da área Campos da Tabela Dinâmica, arraste DMP da tabela Provas para a área LINHAS. Em seguida, arraste Localizações da tabela Anfitriões para a área COLUNAS. Ao arrastar essas duas hierarquias, a sua Tabela Dinâmica é preenchida com muitos dados que são organizados na hierarquia que definiu nos passos anteriores. O seu ecrã deverá assemelhar-se ao ecrã seguinte.
   

4. Vamos filtrar um pouco esses dados e ver apenas as primeiras dez linhas de eventos. Na Tabela Dinâmica, clique na seta nas Etiquetas de Linha, clique em (Selecionar Tudo) para remover todas as seleções e, em seguida, clique nas caixas ao lado dos primeiros dez Desportos. A sua Tabela Dinâmica tem agora o aspeto do ecrã seguinte.
   

5. Pode expandir qualquer um destes Desportos na Tabela Dinâmica, que é nível superior da hierarquia DMP e veja as informações no nível seguinte abaixo na hierarquia (modalidade). Se existir um nível inferior na hierarquia para essa modalidade, pode expandi-la para ver as suas provas. Pode fazer o mesmo para a hierarquia de Localização, cujo nível superior é Época, que aparece como Verão e Inverno na Tabela Dinâmica. Quando expandimos os desportos Aquáticos , vemos todos os seus elementos de modalidades e dados subordinados. Quando expandimos a modalidade Mergulho em Aquáticos, vemos as provas subordinadas, como é apresentado no ecrã seguinte. Podemos fazer o mesmo para Polo Aquático e ver que tem apenas uma prova.
   

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 das 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. Feche todas as linhas e colunas e, em seguida, expanda Desportos Aquáticos, Mergulho e Polo Aquático. O seu livro tem o aspeto do 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 for a única pessoa a criar tabelas dinâmicas ou relatórios do Power View com base nesses dados, a criação de hierarquias poderá 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. Eis 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? Aqui está a sua oportunidade. O questionário seguinte destaca as funcionalidades, capacidades ou requisitos aprendidos neste tutorial. Na parte inferior da página, encontrará as respostas. 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