Criar e gerenciar relações no Power BI Desktop

Artigo publicado no site da Microsoft

Quando você importa várias tabelas, é provável que você realize algumas análises usando dados de todas essas tabelas. Relações entre essas tabelas são necessárias para calcular os resultados com precisão e exibir as informações corretas em seus relatórios. O Power BI Desktop torna fácil a criação dessas relações. Na verdade, na maioria dos casos, você não precisará fazer nada - o recurso de Detecção Automática pode fazer isso por você. No entanto, em alguns casos, talvez você precise criar relações por conta própria, ou talvez seja necessário fazer algumas alterações em uma relação. De qualquer modo, é importante compreender as relações no Power BI Desktop e compreender como criá-las e editá-las.

Detectar automaticamente durante o carregamento

Se você consultar duas ou mais tabelas ao mesmo tempo, quando os dados forem carregados, o Power BI Desktop tentará localizar e criar relações para você. A cardinalidade, a direção do filtro cruzado e as propriedades de relação ativa são definidas automaticamente. O Power BI Desktop procura nomes de colunas nas tabelas que você está consultando para determinar se há quaisquer relações em potencial. Se houver, essas relações serão criadas automaticamente. Caso o Power BI Desktop não consiga determinar com um alto nível de confiança que há uma correspondência, ele não criará automaticamente a relação. Você ainda pode usar a caixa de diálogo Gerenciar Relações para criar ou editar relações.

Criar uma relação usando a Detecção Automática

Na guia Página Inicial, clique em Gerenciar Relações > Detectar Automaticamente.

📷

Criar uma relação manualmente

Na guia Página Inicial, clique em Gerenciar Relações > Nova.Na caixa de diálogo Criar Relação, na primeira lista suspensa de tabelas, selecione uma tabela e, em seguida, selecione a coluna que você deseja usar na relação.Na segunda lista suspensa de tabelas, selecione a outra tabela que você deseja na relação, em seguida a outra coluna que você deseja usar, e clique em OK.

📷

Por padrão, o Power BI Desktop configurará automaticamente a Cardinalidade (direção), a Direção do filtro cruzado e as Propriedades ativas para sua nova relação. No entanto, você pode alterar as configurações, se necessário. Para obter mais informações, consulte a seção Noções básicas sobre opções adicionais posteriormente neste artigo.

Você verá um erro informando que Uma das colunas deve ter valores exclusivos, caso nenhuma das tabelas selecionadas para a relação tenha valores exclusivos. Pelo menos uma tabela em uma relação precisa ter uma lista distinta e exclusiva de valores de chave, que é um requisito comum para todas as tecnologias de banco de dados relacional.

Se você encontrar esse erro, haverá duas maneiras de corrigir o problema:

Use "Remover Linhas Duplicadas" para criar uma coluna com valores exclusivos. A desvantagem dessa abordagem é que você perderá informações quando as linhas duplicadas forem removidas e, geralmente, há um bom motivo para que uma chave (linha) esteja duplicada.Adicione uma tabela intermediária composta pela lista de valores de chave distintos no modelo, que, em seguida, será vinculado às duas colunas originais na relação.

Para ver informações mais detalhadas, confira a postagem no blog.

Editar uma relação

Na guia Página Inicial , clique em Gerenciar Relações.Na caixa de diálogo Gerenciar Relações , selecione a relação e clique em Editar.

Configurar opções adicionais

Quando você cria ou edita uma relação, pode configurar opções adicionais. Por padrão, as opções adicionais são configuradas automaticamente com base em uma melhor estimativa, que pode ser diferente para cada relacionamento com base nos dados das colunas.

Cardinalidade

Muitos para Um (*:1) – o tipo padrão mais comum. Isso significa que a coluna em uma tabela pode ter mais de uma instância de um valor, enquanto a outra tabela relacionada, geralmente conhecida como a tabela de pesquisa, tem apenas uma instância de cada valor.

Um para Um (1:1) – a coluna em uma tabela inclui apenas uma instância de um valor específico, e a outra tabela relacionada inclui apenas uma instância de um valor específico.

Relações muitos-para-muitos: Com os modelos compostos, você pode estabelecer relações de Muitos para Muitos entre tabelas, o que remove os requisitos para valores exclusivos em tabelas. Ela também remove as soluções alternativas anteriores, como introduzir novas tabelas somente para estabelecer relações. Para obter informações detalhadas, confira Relações com uma cardinalidade muitos para muitos.

Consulte a seção Noções básicas sobre opções adicionais, mais adiante neste artigo, para obter mais detalhes sobre quando alterar a cardinalidade.

Direção do filtro cruzado

Ambas – Para fins de filtragem, ambas as tabelas são tratadas como se fossem uma única tabela. Ambas é ideal para uma única tabela que tem uma série de tabelas de pesquisa em torno dela. Um exemplo é uma tabela de dados reais de Vendas com uma tabela de pesquisa por departamento. Isso é frequentemente chamado de Configuração de esquema em estrela (uma tabela central com várias Tabelas de pesquisa). No entanto, se você tiver duas ou mais tabelas que também têm tabelas de pesquisa (com algumas em comum), não seria vantajoso para você usar a configuração “Ambas”. Para continuar o exemplo anterior, nesse caso, você também tem uma tabela de vendas de orçamento que registra o orçamento de destino para cada departamento. E a tabela de departamento é conectada tanto à tabela de vendas quanto à de alocação. Evite a configuração “Ambas” para esse tipo de configuração.

Única – A descrição padrão mais comum, o que significa que as opções de filtragem em tabelas conectadas funcionam na tabela na qual os valores estão sendo agregados. Se você importar uma tabela do Power Pivot no Excel 2013 ou um modelo de dados anterior, todas as relações terão uma única direção.

Consulte a seção de opções adicionais Noções básicas, mais adiante neste artigo, para obter mais detalhes sobre quando alterar a direção do filtro cruzado.

Ativar esta relação

Quando marcada, isso significa que a relação serve como a relação ativa, padrão. Em casos nos quais há mais de uma relação entre duas tabelas, a relação ativa fornece uma maneira para o Power BI Desktop criar automaticamente visualizações que incluem ambas as tabelas.

Veja a seção Noções básicas sobre opções adicionais mais adiante neste artigo para obter mais detalhes sobre quando tornar ativa uma relação específica.

Noções básicas sobre relações

Depois que você tiver conectado duas tabelas unindo-as com uma relação, você pode trabalhar com os dados em ambas as tabelas como se fossem uma única, não precisando mais se preocupar com detalhes da relação nem mesclar essas tabelas em uma única tabela antes de importá-las. Em muitas situações, o Power BI Desktop pode criar relações automaticamente para você, ou seja, talvez nem seja necessário criar essas relações por conta própria. No entanto, se o Power BI Desktop não puder determinar com alto grau de certeza que deve existir uma relação entre duas tabelas, ele não criará automaticamente a relação. Nesse caso, você deve criar a relação.

Vamos conferir um pequeno tutorial, para mostrar melhor como funcionam as relações no Power BI Desktop.

Dica

Você pode concluir esta lição por conta própria. Copie a tabela ProjectHours abaixo em uma planilha do Excel, selecione todas as células, clique em INSERIR > Tabela. Na caixa de diálogo Criar Tabela , clique em OK. Em seguida, em Nome da Tabela, digite ProjectHours. Faça o mesmo para a tabela CompanyProject. Em seguida, você pode importar os dados por meio de Obter Dados no Power BI Desktop. Selecione sua pasta de trabalho e tabelas como uma fonte de dados.

Esta primeira tabela, ProjectHours, é um registro de tíquetes de trabalho que gravam o número de horas que uma pessoa já trabalhou em um projeto específico.

ProjectHours

TicketSubmittedByHoursProjectDateSubmit1001Alan Brewer22Azul1/1/20131002Alan Brewer26Vermelho2/1/20131003Shu Ito34Amarelo12420121004Alan Brewer13Laranja1/2/20121005Eli Bowen29Roxo01/10/20131006Nuno Bento35Verde2/1/20131007David Hamilton10Amarelo01/10/20131008Mu Han28Laranja1/2/20121009Shu Ito22Roxo2/1/20131010Eli Bowen28Verde10120131011Eli Bowen9Azul10/15/2013

Essa segunda tabela, CompanyProject, é uma lista de projetos aos quais é atribuída uma prioridade, A, B ou C.

CompanyProject

ProjNamePriorityAzulAVermelhoBVerdeCAmareloCRoxoBLaranjaC

Observe que cada tabela tem uma coluna de projeto. Cada uma é nomeada de modo ligeiramente diferente, mas os valores parecem ser os mesmos. Isso é importante, e retornaremos a esse assunto em breve.

Agora que temos nossas duas tabelas importadas para um modelo, vamos criar um relatório. A primeira coisa que queremos ter é o número de horas enviadas por prioridade do projeto, então vamos selecionar Priority e Hours em Campos.

📷

Se observarmos nossa tabela na tela Relatório, você verá que o número de horas é 256,00 para cada projeto e é também o total. Obviamente isso não é correto. Por que? É porque não podemos calcular uma soma total dos valores de uma tabela (Hours na tabela Project), dividida por valores em outra tabela (Priority na tabela CompanyProject) sem que exista uma relação entre essas duas tabelas.

Portanto, vamos criar uma relação entre essas duas tabelas.

Lembre-se das colunas que vimos em ambas as tabelas com um nome de projeto, mas com valores parecidos? Vamos usar essas duas colunas para criar uma relação entre nossas tabelas.

Por que essas colunas? Bem, se observarmos a coluna Projeto na tabela ProjectHours, vemos valores como Azul, Vermelho, Amarelo, Laranja e assim por diante. Na verdade, podemos ver várias linhas que têm o mesmo valor. Assim, temos muitos valores de cor para Project.

Se observarmos a coluna ProjName na tabela CompanyProject, vemos que há somente um de cada um dos valores de cor para Project. Cada valor de cor nesta tabela é exclusivo e isso é importante, porque podemos criar uma relação entre essas duas tabelas. Nesse caso, uma relação de tipo muitos para um. Em uma relação muitos para um, pelo menos uma coluna em uma das tabelas deve conter valores exclusivos. Há algumas opções adicionais para determinadas relações, e vamos analisá-las posteriormente, mas por enquanto, vamos criar uma relação entre as colunas Project em cada uma de nossas duas tabelas.

Para criar a nova relação

Clique em Gerenciar Relacionamentos.Em Gerenciar Relações, clique em Novo para abrir a caixa de diálogo Criar Relação, na qual podemos selecionar tabelas, colunas e outras configurações desejadas para nossa relação.Na primeira tabela, selecione ProjectHours; em seguida, selecione a coluna Project . Este é o lado muitos de nossa relação.Na segunda tabela, selecione CompanyProject, depois selecione a coluna Project . Este é o lado um de nossa relação.Vá em frente e clique em OK em ambas as caixas de diálogo Criar Relação e Gerenciar Relações .

📷

Buscando uma divulgação completa, acabamos criando essa relação da maneira mais difícil. Você poderia simplesmente ter clicado no botão Detecção Automática, na caixa de diálogo Gerenciar Relações. Na verdade, a Detecção Automática já teria feito isso ao carregar os dados se ambas as colunas tivessem o mesmo nome. Mas qual é o desafio nisso?

Agora, vamos examinar novamente a tabela em nossa tela Relatório.

📷

Agora a aparência está muito melhor, não é?

Quando somamos as horas por prioridade, o Power BI Desktop procura cada instância dos valores de cor exclusivos na tabela de pesquisa CompanyProject, em seguida, procura todas as instâncias de cada um desses valores na tabela CompanyProject e, por fim, calcula uma soma total para cada valor exclusivo.

Foi bastante fácil. Na verdade, com a Detecção Automática, talvez você nem precise chegar a fazer tudo isso.

Noções básicas sobre opções adicionais

Quando uma relação é criada, seja com Detecção Automática ou manualmente por você, o Power BI Desktop configura opções adicionais automaticamente com base nos dados de suas tabelas. Você pode configurar essas propriedades de relação adicionais localizadas na parte inferior da caixa de diálogo Criar/Editar relação.

📷

Como dissemos, elas geralmente são definidas automaticamente, e você não precisará lidar com elas; no entanto, há várias situações em que você talvez queira configurar essas opções por conta própria.

Atualizações automáticas de relação

Você pode gerenciar como o Power BI trata e ajusta automaticamente as relações em seus relatórios e modelos. Para especificar como o Power BI trata as opções de relacionamentos, selecione Arquivo > Opções e configurações > Opções no Power BI Desktop, em seguida, selecione Carregamento de Dados, no painel esquerdo. Em seguida, você verá opções para relacionamentos.

📷

Há três opções disponíveis para selecionar e habilitar.

A primeira opção é Importar relacionamentos de fontes de dados, que é selecionada por padrão. Quando selecionada, o Power BI verifica as relações definidas na fonte de dados, como relações de chave estrangeira/chave primária no data warehouse. Se essas relações existirem, elas serão espelhadas no modelo de dados do Power BI quando você carregar os dados inicialmente. Essa opção permite que você comece a trabalhar rapidamente com seu modelo, em vez de exigir que você encontre ou defina essas relações por conta própria.

A segunda opção é Atualize ou exclua relacionamentos ao atualizar os dados, e ela aparece desativada por padrão. Quando selecionada (ativada marcando a caixa de seleção ao lado da opção), o Power BI verifica alterações nos relacionamentos da fonte de dados quando o conjunto de dados é atualizado. Quando essas relações são alteradas ou removidas, o Power BI espelha essas alterações no respectivo modelo de dados, atualizando-as ou excluindo-as para correspondência.

Aviso

Se estiver usando RLS (Segurança em Nível de Linha) com base em relacionamentos definidos, recomendamos não selecionar a segunda opção, Atualize ou exclua relacionamentos ao atualizar os dados. Se um relacionamento do qual se baseiam as configurações da RLS for removido, o modelo poderá se tornar menos seguro.

A terceira opção é Detectar automaticamente novos relacionamentos depois que os dados são carregados, descrita na seção Detectar automaticamente durante o carregamento anteriormente neste artigo.

Atualizações futuras dos dados exigem uma cardinalidade diferente

Normalmente, o Power BI Desktop pode determinar automaticamente a melhor cardinalidade para a relação. Se precisar substituir a configuração automática por saber que os dados serão alterados no futuro, você poderá selecioná-la no controle Cardinalidade. Vejamos um exemplo em que precisamos selecionar uma cardinalidade diferente.

A tabela CompanyProjectPriority abaixo é uma lista de todos os projetos da empresa, incluindo a prioridade de cada um. A tabela ProjectBudget é o conjunto de projetos para os quais o orçamento foi aprovado.

ProjectBudget

Approved ProjectsBudgetAllocationAllocationDateAzul40.0001212012Vermelho100.0001212012Verde50.0001212012

CompanyProjectPriority

ProjectPriorityAzulAVermelhoBVerdeCAmareloCRoxoBLaranjaC

Se criarmos uma relação entre a coluna Project na tabela CompanyProjectPriority e a coluna ApprovedProjects na tabela ProjectBudget, desse modo:

📷

A cardinalidade é definida automaticamente como Um Para Um (1:1) e a direção da filtragem cruzada como “Ambas” (conforme mostrado). Isso ocorre porque, para o Designer, a melhor combinação das duas tabelas agora tem essa aparência:

ProjectPriorityBudgetAllocationAllocationDateAzulA40.0001212012VermelhoB100.0001212012VerdeC50.0001212012AmareloC RoxoB LaranjaC

Há uma relação um para um entre nossas duas tabelas porque não há repetição de valores na coluna Project da tabela combinada. A coluna Project é exclusiva, porque cada valor ocorre apenas uma vez; portanto, as linhas das duas tabelas podem ser combinadas diretamente sem nenhuma duplicação.

Mas digamos que você saiba que os dados mudarão na próxima vez que você atualizá-los. Uma versão atualizada da tabela ProjectBudget agora tem linhas adicionais para Azul e Vermelho:

ProjectBudget

Approved ProjectsBudgetAllocationAllocationDateAzul40.0001212012Vermelho100.0001212012Verde50.0001212012Azul80,0006/1/2013Vermelho90,0006/1/2013

Isso significa que a melhor combinação das duas tabelas agora tem essa aparência:

ProjectPriorityBudgetAllocationAllocationDateAzulA40.0001212012VermelhoB100.0001212012VerdeC50.0001212012AmareloC RoxoB LaranjaC AzulA800006/1/2013VermelhoB900006/1/2013

Nessa nova tabela combinada, há repetição de valores na coluna Project. As duas tabelas originais não terão uma relação de tipo um para um depois que a tabela for atualizada. Nesse caso, por sabermos que as atualizações futuras farão com que a coluna de projeto tenha duplicatas, queremos definir a Cardinalidade como Muitos Para Um (*:1), com Muitos no lado de ProjectBudget e Um no lado de CompanyProjectPriority.

Ajuste da direção de filtro cruzado para um conjunto complexo de tabelas e relações

Para a maioria das relações, a direção da filtragem cruzada é definida como ‘Ambas’. No entanto, há algumas circunstâncias mais incomuns em que você talvez precise definir isso de modo diferente do padrão, como se você estivesse importando um modelo de uma versão anterior do PowerPivot, na qual cada relação é configurada para uma única direção.

A configuração “Ambas” habilita o Power BI Desktop a tratar todos os aspectos das tabelas conectadas como se elas fossem uma única tabela. Há algumas situações, porém, em que o Power BI Desktop não pode definir a direção de filtro cruzado da relação como ‘Ambas’ e, ao mesmo tempo, manter um conjunto inequívoco de padrões disponíveis para fins de relatório. Se a direção de filtro cruzado de uma relação não é definida como “Ambas”, isso geralmente ocorre porque tal configuração geraria ambiguidade. Se a configuração padrão de filtro cruzado não está funcionando para você, você pode defini-la em direção a uma tabela específica ou “Ambas”.

A filtragem cruzada em uma única direção funciona em muitas situações. Na verdade, se você importar um modelo do PowerPivot no Excel 2013 ou anterior, todas as relações estarão definidas para uma única direção. O uso de uma única direção significa que as opções de filtragem, em tabelas conectadas, funcionam na tabela na qual está ocorrendo trabalho de agregação de valores. Às vezes compreender a filtragem cruzada pode ser um pouco difícil, portanto, vamos examinar um exemplo.

📷

Com filtragem cruzada de direção única, se você criar um relatório que resume as horas de projeto, você poderá, em seguida, escolher resumir (ou filtrar) por CompanyProject, Priority ou CompanyEmployee, City. Se, no entanto, você quiser contar o número de funcionários por projeto (uma pergunta menos comum), isso não funcionará. Você obterá uma coluna de valores todos iguais. No exemplo a seguir, a direção da filtragem cruzada de ambas as relações está definida como uma única direção - em direção à tabela ProjectHours:

📷

A especificação de filtro fluirá de CompanyProject para CompanyEmployee (conforme mostrado na imagem abaixo), porém, o fluxo não chegará até CompanyEmployee. No entanto, se você definir a direção de filtragem cruzada como “Ambas”, ela funcionará. A configuração “Ambas” permite que a especificação de filtro flua até Employee.

📷

Com a direção da filtragem cruzada definida como “Ambas”, nosso relatório agora parece correto:

📷

A filtragem cruzada em ambos os trajetos funciona bem para um padrão de relações de tabela que se parecem com o padrão acima. Isso é mais comumente chamado de um esquema em estrela, como esse:

📷

A direção de filtragem cruzada não funciona bem com um padrão mais geral encontrado com frequência em bancos de dados, como nesse diagrama:

📷

Se você tiver um padrão de tabela como este, com loops, então a filtragem cruzada pode criar um conjunto ambíguo de relações. Por exemplo, se você realiza a soma de um campo da Tabela X e, em seguida, opta por filtrar por um campo na Tabela Y, não fica claro o percurso que o filtro deve fazer: pela tabela superior ou pela inferior. Um exemplo comum para esse tipo de padrão é que a Tabela X seja uma tabela Sales com os dados efetivos e que Tabela Y seja de dados de orçamento. Em seguida, as tabelas no meio são tabelas de pesquisa utilizadas por ambas as tabelas, como Divisão ou Região.

Assim como ocorre com relações ativas/inativas, o Power BI Desktop não permitirá que uma relação seja definida como “Ambas” se isso gerar ambiguidade em relatórios. Há várias maneiras diferentes de tratar disso. Estas são as duas mais comuns:

Excluir ou marcar relações como inativas para reduzir a ambiguidade. Em seguida, talvez você possa definir a filtragem cruzada de uma relação como “Ambas”.Inclua uma tabela duas vezes (com um nome diferente na segunda vez) para eliminar os loops. Isso torna o padrão de relações similar a um esquema em estrela. Com um esquema em estrela, todas as relações podem ser definidas como Ambas.

Relação ativa errada

Quando o Power BI Desktop cria relações automaticamente, ele às vezes encontra mais de uma relação entre duas tabelas. Quando isso acontece, apenas uma das relações é definida como ativa. A relação ativa serve como a relação padrão para que, quando você escolher campos de duas tabelas diferentes, o Power BI Desktop possa criar automaticamente uma visualização para você. No entanto, em alguns casos, a relação selecionada automaticamente pode estar errada. Você pode usar a caixa de diálogo Gerenciar Relações para definir uma relação como ativa ou inativa, ou então definir a relação ativa na caixa de diálogo Editar relação.

Para garantir que exista uma relação padrão, o Power BI Desktop permite apenas uma relação ativa entre duas tabelas em um determinado momento. Portanto, você deve primeiro definir a relação atual como inativa e, em seguida, definir como ativa a relação desejada.

Vejamos um exemplo. Esta primeira tabela é ProjectTickets, a tabela a seguir é EmployeeRole.

ProjectTickets

TicketOpenedBySubmittedByHoursProjectDateSubmit1001Tom PerhamAlan Brewer22Azul1/1/20131002Daniel RomanoAlan Brewer26Vermelho2/1/20131003Daniel RothShu Ito34Amarelo12420121004Tom PerhamAlan Brewer13Laranja1/2/20121005Daniel RomanoEli Bowen29Roxo01/10/20131006Daniel RothNuno Bento35Verde2/1/20131007Daniel RothDavid Hamilton10Amarelo01/10/20131008Tom PerhamMu Han28Laranja1/2/20121009Daniel RomanoShu Ito22Roxo2/1/20131010Daniel RothEli Bowen28Verde10120131011Tom PerhamEli Bowen9Azul10/15/2013

EmployeeRole

EmployeeFunçãoNuno BentoGerente de projetoEli BowenLíder de projetoAlan BrewerGerente de projetoDavid HamiltonLíder de projetoMu HanLíder de projetoShu ItoLíder de projetoTom PerhamPatrocinador de projetoDaniel RomanoPatrocinador de projetoDaniel RothPatrocinador de projeto

Na verdade, existem duas relações aqui. Uma é entre SubmittedBy na tabela ProjectTickets e Employee na tabela EmployeeRole, enquanto a outra é entre OpenedBy na tabela ProjectTickets e Employee na tabela EmployeeRole.

📷

Se adicionarmos ambas as relações no modelo (OpenedBy primeiro), a caixa de diálogo Gerenciar Relações mostrará em seguida que OpenedBy está ativa:

📷

Agora, se criarmos um relatório que usa os campos Role e Employee de EmployeeRole, e o campo Hours de ProjectTickets em uma visualização de tabela na tela Relatório, veremos apenas patrocinadores de projeto, porque eles são os únicos que têm um tíquete de projeto aberto.

📷

Podemos alterar a relação ativa e obter SubmittedBy em vez de OpenedBy. Em Gerenciar Relações, desmarcamos a relação entre ProjectTickets(OpenedBy) e EmployeeRole(Employee) e, em seguida, marcamos a relação entre Project Tickets(SubmittedBy) e EmployeeRole(Employee).

📷