Visão geral de relações
Este tópico apresenta as relações que podem ser definidas entre tabelas no PowerPivot para Excel. O tópico inclui as seguintes seções:
O que é uma relação?
Requisitos de relações
Detecção automática e interferência de relações
Depois de ler este tópico, você deve entender o que é uma relação, quais são os requisitos para definir uma relação e como o PowerPivot para Excel pode detectar as relações para você automaticamente. No percurso, você aprenderá parte da terminologia que os profissionais de banco de dados usam para descrever relações.
O que é uma relação?
Relação é uma conexão entre duas tabelas de dados, baseada em uma ou mais colunas em cada tabela (exatamente uma coluna em cada tabela para o PowerPivot). Para saber por que as relações são úteis, imagine que você acompanhe dados para pedidos de clientes na empresa. Você poderia acompanhar todos os dados em uma única tabela com uma estrutura semelhante à seguinte:
CustomerID |
Nome |
DiscountRate |
OrderID |
OrderDate |
Produto |
Quantidade |
|
|---|---|---|---|---|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
256 |
2010-01-07 |
Compact Digital |
11 |
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
255 |
2010-01-03 |
SLR Camera |
15 |
2 |
Jaworski |
michal.jaworski@contoso.com |
.10 |
254 |
2010-01-03 |
Budget Movie-Maker |
27 |
Esta abordagem pode funcionar, mas envolve o armazenamento de muitos dados redundantes, como o endereço de email do cliente para todos os pedidos. Embora o armazenamento seja barato, você deverá ter certeza de que atualizou todas as linhas desse cliente se o endereço de email for alterado. Uma solução para esse problema é dividir os dados em várias tabelas e definir relações entre essas tabelas. Essa é a abordagem usada em bancos de dados relacionais como o SQL Server. Por exemplo, um banco de dados importado para o PowerPivot para Excel pode representar dados de pedidos usando três tabelas relacionadas:
Customers
[CustomerID] |
Name |
|
|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
2 |
Jaworski |
michal.jaworski@contoso.com |
CustomerDiscounts
[CustomerID] |
DiscountRate |
|---|---|
1 |
.05 |
2 |
.10 |
Orders
[CustomerID] |
OrderID |
OrderDate |
Product |
Quantity |
|---|---|---|---|---|
1 |
256 |
2010-01-07 |
Compact Digital |
11 |
1 |
255 |
2010-01-03 |
SLR Camera |
15 |
2 |
254 |
2010-01-03 |
Budget Movie-Maker |
27 |
Se você importar essas tabelas do mesmo banco de dados, o PowerPivot poderá detectar as relações entre as tabelas com base nas colunas que estão entre [colchetes], e poderá reproduzir essas relações na janela do PowerPivot. Para obter mais informações, consulte Detecção automática e inferência de relações neste tópico. Se você importar tabelas de várias fontes, poderá criar manualmente relações como as descritas em Criar uma relação entre duas tabelas.
Colunas e chaves
As relações se baseiam em colunas de cada tabela que contenham os mesmos dados. Por exemplo, as tabelas Customers e Orders podem ser relacionadas porque ambas contêm uma coluna que armazena uma ID de cliente. No exemplo, os nomes de coluna são os mesmos, mas isso não é um requisito. Um nome pode ser CustomerID e outro CustomerNumber, desde que todas as linhas da tabela Orders contenham uma ID que também esteja armazenada na tabela Customers.
Em um banco de dados relacional, há vários tipos de chaves que normalmente são apenas colunas com propriedades especiais. Os quatro tipos de chaves a seguir são as mais interessantes para nossos objetivos:
Chave primária: identifica exclusivamente uma linha de uma tabela, como CustomerID na tabela Customers.
Chave alternativa (ou chave candidata): uma coluna diferente da chave primária que é exclusiva. Por exemplo, uma tabela Employees pode armazenar uma ID de funcionário e um CIC, ambos exclusivos.
Chave estrangeira: uma coluna que se refere a uma coluna exclusiva em outra tabela, como CustomerID na tabela Orders, que faz referência a CustomerID na tabela Customers.
Chave composta: uma chave composta de mais de uma coluna. Não há suporte para chaves compostas no PowerPivot para Excel. Para obter mais informações, consulte "Chaves compostas e colunas de pesquisa" neste tópico.
No PowerPivot para Excel, a chave primária ou a chave alternativa é conhecida como a coluna de pesquisa relacionada ou apenas coluna de pesquisa. Se uma tabela tiver uma chave primária e outra alternativa, você poderá usar uma delas como a coluna de pesquisa. A chave estrangeira é referenciada como a coluna de origem ou apenas como coluna. Em nosso exemplo, uma relação seria definida entre CustomerID na tabela Orders (a coluna) e CustomerID (a coluna de pesquisa) na tabela Customers. Se você importar dados de um banco de dados relacional, por padrão, o PowerPivot para Excel escolherá a chave estrangeira de uma tabela e a chave primária correspondente de outra tabela. Entretanto, você pode usar qualquer coluna com valores exclusivos como a coluna de pesquisa.
Tipos de relações
A relação entre Customers e Orders é uma relação um para muitos. Todo cliente pode ter várias ordens, mas uma ordem não pode ter vários clientes. Os outros tipos de relação são um para um e muitos para muitos. A tabela CustomerDiscounts que define uma única taxa de desconto para cada cliente, está em uma relação um para um com a tabela Customers. Um exemplo de uma relação muitos para muitos é uma relação direta entre Products e Customers, na qual um cliente pode comprar muitos produtos e o mesmo produto pode ser comprado por muitos clientes. O PowerPivot para Excel não dá suporte a relações muitos para muitos na interface do usuário. Para obter mais informações, consulte "Relações muitos para muitos" neste tópico.
A tabela a seguir mostra as relações entre as três tabelas:
Relação |
Tipo |
Coluna de pesquisa |
Coluna |
|---|---|---|---|
Customers-CustomerDiscounts |
um para um |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers-Orders |
um-para-muitos |
Customers.CustomerID |
Orders.CustomerID |
Relações e desempenho
Após a criação de uma relação, o PowerPivot para Excel normalmente deve recalcular todas as fórmulas que usam colunas de tabelas na relação recém-criada. O processamento pode ser demorado, dependendo da quantidade de dados e da complexidade das relações. Para obter mais informações, consulte Recalcular fórmulas.
Requisitos de relações
O PowerPivot para Excel tem vários requisitos que devem ser seguidos durante a criação de relações:
Relação única entre tabelas
Várias relações podem resultar em dependências ambíguas entre as tabelas. Para criar cálculos exatos, você precisa de um único caminho de uma tabela para a próxima. Por isso, pode haver apenas uma relação entre cada par de tabelas. Por exemplo, em AdventureWorksDW2012 , a tabela DimDate contém uma coluna DateKey que está relacionada a três colunas diferentes na tabela FactInternetSales: OrderDate, DueDate e ShipDate. Se você tentar importar essas tabelas, a primeira relação será criada com êxito, mas você receberá o seguinte erro em relações sucessivas que envolvam a mesma coluna:
* Relação: table[column 1]-> table[column 2] - Status: error - Motivo: uma relação não pode ser criada entre as tabelas <table 1> e <table 2>. Só pode existir uma relação direta ou indireta entre duas tabelas.
Se tiver duas tabelas e várias relações entre elas, você precisará importar várias cópias da tabela que contém a coluna de pesquisa e criar uma relação entre cada par de tabelas.
Uma relação para cada coluna de origem
Uma coluna de origem não pode participar de várias relações. Se você já usou uma coluna como coluna de origem em uma relação, mas deseja usar essa coluna para conectar a outra coluna de pesquisa relacionada em uma tabela diferente, você poderá criar uma cópia da coluna e usá-la para a nova relação.
É fácil criar uma cópia de uma coluna que tenha exatamente os mesmos valores usando uma fórmula DAX em uma coluna calculada. Para obter mais informações, consulte Colunas calculadas.
Identificador exclusivo para cada tabela
Cada tabela deve ter uma única coluna que identifica exclusivamente cada linha nessa tabela. Essa coluna geralmente é chamada de chave primária.
Colunas de pesquisa exclusivas
Os valores de dados na coluna de pesquisa devem ser exclusivos. Em outras palavras, a coluna não pode conter duplicatas. No PowerPivot para Excel, as cadeias de caracteres nulas e vazias equivalem a um espaço em branco, que é um valor de dados distinto. Isso significa que não pode haver vários nulos na coluna de pesquisa.
Tipos de dados compatíveis
Os tipos de dados da coluna de origem e da coluna de pesquisa devem ser compatíveis. Para obter mais informações sobre tipos de dados, consulte Tipos de dados com suporte em pastas de trabalho PowerPivot.
Chaves compostas e colunas de pesquisa
Não é possível usar chaves compostas em uma pasta de trabalho do PowerPivot; você deve sempre ter exatamente uma coluna que identifique exclusivamente cada linha da tabela. Se você tentar importar tabelas que tenham uma relação existente com base em uma chave composta, o Assistente de Importação de Tabela ignorará essa relação porque ela não pode ser criada no PowerPivot.
Se quiser criar uma relação entre duas tabelas no PowerPivot e houver várias colunas que definam as chaves primária e estrangeira, você deverá combinar os valores para criar uma coluna de chave única antes de criar a relação. Isso pode ser feito antes de você importar os dados, ou no PowerPivot com a criação de uma coluna calculada.
Relações muitos para muitos
O PowerPivot para Excel não dá suporte a relações muitos para muitos, e não é possível simplesmente adicionar tabelas de junção no PowerPivot. No entanto, você pode usar funções de DAX para modelar relações muitos para muitos.
Autojunções e loops
Não são permitidas autojunções em tabelas do PowerPivot. Uma autojunção é uma relação recursiva entre uma tabela e ela mesma. Autojunções costumam ser usadas para definir hierarquias pai-filho. Por exemplo, você pode unir uma tabela Employees a ela própria para produzir uma hierarquia que mostra a cadeia de gerenciamento em uma empresa.
O PowerPivot para Excel não permite criar loops entre relações em uma pasta de trabalho. Em outras palavras, o conjunto de relações a seguir é proibido.
Tabela 1, coluna a a Tabela 2, coluna f
Tabela 2, coluna f a Tabela 3, coluna n
Tabela 3, coluna n a Tabela 1, coluna a
Se você tentar criar uma relação que resulte na criação de um loop, será gerado um erro.
Detecção automática e inferência de relações
Quando você importa dados para a janela do PowerPivot, o Assistente de Importação de Tabela detecta automaticamente todas as relações existentes entre as tabelas. Além disso, quando você cria uma Tabela Dinâmica, o PowerPivot para Excel analisa os dados nas tabelas. Ele detecta possíveis relações que não foram definidas e sugere colunas apropriadas a serem incluídas nessas relações.
O algoritmo de detecção usa dados estatísticos sobre os valores e metadados de colunas para criar inferências sobre a probabilidade das relações.
Os tipos de dados em todas as colunas relacionadas devem ser compatíveis. Para a detecção automática, apenas os tipos de dados de número inteiro e de texto têm suporte. Para obter mais informações sobre tipos de dados, consulte Tipos de dados com suporte em pastas de trabalho PowerPivot.
Para que a relação seja detectada com êxito, o número de chaves exclusivas na coluna de pesquisa deve ser maior que os valores na tabela no lado muitos. Em outras palavras, a coluna de chave no lado muitos da relação não deve conter valores que não constem na coluna de chave da tabela de pesquisa. Por exemplo, digamos que você tenha uma tabela que liste produtos com suas IDs (a tabela de pesquisa) e uma tabela de vendas que liste vendas para cada produto (o lado muitos da relação). Se seus registros de vendas contiverem a ID de um produto sem uma ID correspondente na tabela Products, a relação não poderá ser criada automaticamente, mas você poderá criá-la manualmente. Para que o PowerPivot para Excel detecte a relação, primeiro atualize a tabela de pesquisa Product com as IDs dos produtos ausentes.
Verifique se o nome da coluna de chave no lado muitos é semelhante ao nome da coluna de chave na tabela de pesquisa. Os nomes não precisam ser exatamente iguais. Por exemplo, em uma configuração de negócio, costumam existir variações nos nomes das colunas contendo essencialmente os mesmos dados: Emp ID, EmployeeID, Employee ID, EMP_ID e assim por diante. O algoritmo detecta nomes semelhantes e atribui uma probabilidade maior às colunas com nomes semelhantes ou exatamente iguais. Por isso, para aumentar a probabilidade de criar uma relação, experimente renomear as colunas nos dados importados com nomes semelhantes aos das colunas nas tabelas existentes. Se o PowerPivot para Excel encontrar várias relações possíveis, ele não criará uma relação.
Essas informações podem ajudar a entender por que nem todas as relações são detectadas ou como as alterações feitas nos metadados – como nome de campo e os tipos de dados – podem melhorar os resultados da detecção automática de relações. Para obter mais informações, consulte Solucionar problemas de relações e Nos bastidores da detecção automática de relacionamento do PowerPivot.
Detecção automática para conjuntos nomeados
As relações não são detectadas automaticamente entre Conjuntos Nomeados e campos relacionados em uma Tabela Dinâmica. Você pode criar essas relações manualmente. Se você desejar usar a detecção automática de relações, remove cada Conjunto Nomeado e adicione os campos individuais do Conjunto Nomeado diretamente à Tabela Dinâmica.
Inferência de relações
Em alguns casos, as relações entre as tabelas são encadeadas automaticamente. Por exemplo, se você criar uma relação entre os dois primeiros conjuntos de tabelas abaixo, uma relação será inferida como existente entre as outras duas tabelas, e uma relação será estabelecida automaticamente.
Products e Category -- criadas manualmente
Category e SubCategory -- criadas manualmente
Products e SubCategory -- a relação é inferida
Para que sejam encadeadas automaticamente, as relações devem seguir em uma direção, conforme mostrado acima. Se as relações iniciais fossem entre, por exemplo, Sales e Products e Sales e Customers, uma relação não seria inferida. Isso ocorre porque a relação entre Products e Customers é uma relação muitos para muitos.