As topologias dominantes da modelagem de Data Warehouse (Star, Snowflake) são projetadas tendo em mente relacionamentos um-para-muitos. A legibilidade, o desempenho e a estrutura da consulta são prejudicados severamente quando confrontados com um relacionamento muitos-para-muitos nesses esquemas de modelagem.
Quais são algumas maneiras de implementar uma relação muitos-para-muitos entre dimensões ou entre a tabela de fatos e uma dimensão em um data warehouse e quais compromissos eles infligem em relação à granularidade necessária e ao desempenho da consulta?
Na minha experiência, uma hierarquia recursiva é a maneira mais prática de lidar com isso. Oferece as seguintes vantagens:
Por outro lado, é necessária uma tabela extra para cada nível de junções "para muitos". Isso é codificado e difícil de manter em relação a atualizações de esquema.
Ao usar índices filtrados, uma grande tabela de junções hierárquicas pode ser executada em velocidade superior a tabelas dedicadas. O motivo é que cada junção é apenas "pai-filho" em comparação com "juntar tabela a tabela de dados". O último tem mais índices para processar e armazenar.
Venho tentando resolver esse problema há muitos anos. Recentemente, isso é o que eu vim com.
Alguns cenários para relacionamentos M:M em um modelo de data warehouse
A maioria dos servidores OLAP e sistemas ROLAP tem um meio de lidar com estruturas de dados M:M agora, mas há algumas ressalvas sobre isso que você precisa prestar atenção. Se você implementar relacionamentos M:M, precisará ficar de olho em sua camada de relatórios e em quais ferramentas deseja oferecer suporte.
Cenário 1: Dimensão M:M em uma tabela de fatos
Um exemplo disso pode ser vários drivers em uma política de motor. Se você adicionar ou remover um driver, a transação de ajuste de política pode ter um relacionamento com uma lista de drivers que muda com o ajuste.
Opção 1 - M:M driver-fact bridge table Esta tabela terá um volume de dados bastante grande, pois possui drivers x linhas de transações para uma determinada política. O SSAS pode consumir essa estrutura de dados diretamente, mas é mais lento consultar por meio de uma ferramenta ROLAP.
Se seu relacionamento M:M for baseado em entidades que são específicas para a linha de fatos (por exemplo, motoristas em um carro), isso também pode ser apropriado para uma ferramenta ROLAP, desde que sua ferramenta ROLAP suporte relacionamentos M:M (por exemplo, usando contextos em Business Objetos).
Opção 2 - Tabela de dimensão 'combinações' fictícia Se você estiver mapeando uma lista de códigos comuns para uma tabela de fatos (ou seja, as entidades vinculadas não são peculiares à linha de fatos), poderá adotar outra abordagem que reduzirá os volumes de dados. Um exemplo desse tipo de cenário são os códigos ICD em uma consulta de internação. Cada consulta de internação terá um ou mais diagnósticos e/ou procedimentos de CID listados. Os códigos da CID são globais.
Nesse caso, você pode fazer uma lista distinta das combinações de códigos em cada caso. Faça uma tabela de dimensão com uma linha para cada combinação distinta e tenha uma tabela de ligação entre as combinações e as tabelas de referência para os próprios códigos ICD.
A tabela de fatos pode ter uma chave de dimensão para a dimensão 'combinações' e a linha de dimensão tem uma lista de referências a códigos ICD reais. A maioria das ferramentas ROLAP pode consumir essa estrutura de dados. Se sua ferramenta funcionar apenas com um relacionamento M:M real, você poderá criar uma visualização que emule o relacionamento M:M entre o fato e a tabela de referência de codificação. Esta seria a abordagem preferida com SSAS.
Vantagens da opção 1: - As consultas adequadamente indexadas, baseadas na seleção de linhas da tabela de fatos com um determinado relacionamento por meio da tabela M:M, podem ser razoavelmente eficientes.
Vantagens da opção 2: - O armazenamento de dados é mais compacto
Cenário 2: relação M:M entre as dimensões:
Mais difícil pensar em um caso de uso, mas pode-se imaginar algo fora dos cuidados de saúde com códigos ICD novamente. Num sistema de análise de custos, a visita de internamento pode tornar-se uma dimensão e terá relações M:M entre a visita (ou consultor-episódio na linguagem do NHS) e as codificações.
Nesse caso, você pode configurar os relacionamentos M:M e, possivelmente, codificar uma renderização legível por humanos deles na dimensão base. As relações podem ser feitas por meio de tabelas de ligação M:M diretas ou por meio de uma tabela de 'combinações' de ponte, como antes. Essa estrutura de dados pode ser consultada corretamente por meio de objetos de negócios ou ferramentas ROLAP de melhor qualidade.
De cabeça, não consigo ver o SSAS sendo capaz de consumir isso sem levar o relacionamento direto para a tabela de fatos, então você precisaria apresentar uma visão do relacionamento M:M entre a codificação e a tabela de fatos linhas para usar o SSAS com esses dados.
Gostaria de saber exatamente que tipo de relacionamento muitos-para-muitos você tem em mente em seu modelo, seja no sistema transacional ou em qualquer modelo de dados em que esteja atualmente.
Normalmente, as relações muitos-para-muitos entre dimensões são fatos sobre dimensões. O fato de um cliente fazer pedidos de várias filiais que atendem a muitos clientes ou algo assim. Cada um deles é um fato. Teria uma data efetiva ou algo assim, mas o relacionamento poderia ser "sem fatos". O relacionamento em si pode ter outras dimensões além do cliente e da filial. Portanto, é um esquema em estrela típico com uma tabela de fatos (potencialmente sem fatos) no centro. Como esta estrela pode se relacionar com outras estrelas dimensionais no armazém obviamente dependerá. Sempre que combinar estrelas diferentes, você o fará nas chaves de negócios e terá que garantir que não esteja executando junções cruzadas inadvertidas.
Normalmente, não se relata essas tabelas de relacionamento de dimensão no mesmo grau que as tabelas de fatos maiores e, quando o fazem, nem sempre são tantos dados, portanto, isso não tende a afetar o desempenho. No caso acima, você pode observar a utilização do cliente/filial ao longo do tempo, mas dados melhores sobre as quantidades reais do pedido estariam disponíveis em sua tabela de fatos do pedido, que presumivelmente também teria dimensões para o cliente, filial etc. a maioria das pessoas consideraria muitos-para-muitos (embora um pedido possa ser considerado para definir uma relação muitos-para-muitos do cliente para a filial), portanto, são mais comuns em ambientes de data warehouse. Você só faria agregações numéricas em modelos muitos-para-muitos se tivesse acumulado informações resumidas para esse nível de relacionamento - ou seja, cliente, filial, mês,
Aqui estão alguns artigos relevantes de Kimball e outros que podem se aplicar à modelagem de uma determinada relação muitos-para-muitos proposta. Observe que um relacionamento muitos-para-muitos é um conceito apenas no domínio do problema/modelo lógico. Em um modelo OLTP normalizado, ainda seria tratado com uma tabela de links que é, obviamente, um para muitos em cada sentido. Em um modelo de armazém de dados Kimball não normalizado, há várias maneiras de fazer isso, uma das quais basicamente trata essa tabela de links como o fato no centro de uma estrela. Outra é como uma matriz de colunas de sinalizadores.
Em última análise, a escolha dependerá exatamente do que você está modelando, como está mudando e como deseja relatar isso. É aqui que a modelagem dimensional e o armazenamento de dados em geral divergem acentuadamente do modelo normalizado. O modelo normalizado concentra-se nas relações lógicas e teóricas nos dados, cujo armazenamento de dados sempre fica de olho nos casos de uso realistas e desnormaliza para fazê-los funcionar.
Modelando hierarquias alternativas usando uma tabela de ponte:
http://www.kimballgroup.com/wp-content/uploads/2012/05/DT62Alternative.pdf
Três opções para um relacionamento de muitos para muitos (vinculado a alocações de compartilhamento numérico - veja os comentários para algumas idas e vindas interessantes)
http://www.pythian.com/news/364/implementing-many-to-many-relationships-in-data-warehousing/
Infelizmente, muitos artigos da revista Information Week/DBMS de Kimball não têm mais bons links...
Uma maneira de resolver isso é ter uma tabela de fatos com apenas 2 colunas, de chave estrangeira das 2 dimensões tendo uma relação muitos para muitos.