Eu e outro DBA em nossa empresa temos a tarefa de revisar um projeto de banco de dados que um fornecedor desenvolveu para nós. O fornecedor disse que usa Kimball como base para seu design. (NOTA: não estou procurando argumentos de Kimball vs Inmon, etc.) Eles projetaram um mercado com múltiplos fatos e dimensões.
Agora, com toda a justiça, nossa empresa nunca projetou um único mercado. Sempre tivemos os consultores fazendo isso. E nunca fomos mandados para aulas nem nada. Portanto, nosso conhecimento de warehousing/marts/modelagem dimensional etc. é baseado na pouca experiência que temos, no que podemos encontrar na Internet e na autoleitura (temos os livros de Inmon e Kimball e estamos tentando fazer o nosso caminho através deles) .
Agora que o palco está montado para o meu nível de conhecimento, chegamos ao desafio do design.
Existe uma tabela de fatos chamada "Estatísticas de sinistros" (para seguros). E eles estão tentando capturar tanto os pagamentos de sinistros (acumulados até um nível mensal) quanto o dinheiro nas reservas (como uma conta bancária para sinistros). Eles desejam ver os valores mensais para pagamentos (nada demais). Mas eles desejam ver o saldo atual da conta das reservas.
Vou dar um exemplo pictórico.
Digamos que definimos US$ 1.000 em reservas para uma reivindicação. Isso é reservado (portanto, em alguns aspectos, funciona como uma conta bancária).
Em outubro de 2014, ainda não pagamos nada. Então a empresa quer ver os pagamentos e o saldo da reserva no final de outubro.
-----------------------------------------------
- MONTH_YEAR - PAYMENTS - RESERVE_BALANCE -
-----------------------------------------------
- 102014 - 0.00 - 1000.00 -
-----------------------------------------------
Então chega novembro. Fazemos pagamentos de $ 100, $ 150 e $ 75 dólares. Eles desejam ver esses valores agregados e a reserva no saldo da seguinte forma:
-----------------------------------------------
- MONTH_YEAR - PAYMENTS - RESERVE_BALANCE -
-----------------------------------------------
- 102014 - 0.00 - 1000.00 -
-----------------------------------------------
- 112014 - 325.00 - 675.00 -
-----------------------------------------------
E então digamos que temos zero pagamentos em dezembro e mais $ 200 em janeiro do próximo ano.
-----------------------------------------------
- MONTH_YEAR - PAYMENTS - RESERVE_BALANCE -
-----------------------------------------------
- 102014 - 0.00 - 1000.00 -
-----------------------------------------------
- 112014 - 325.00 - 675.00 -
-----------------------------------------------
- 122014 - 0.00 - 675.00 -
-----------------------------------------------
- 12015 - 200.00 - 475.00 -
-----------------------------------------------
Aqui é onde eu luto. Meu entendimento é que a parte de pagamentos está correta. Eles são todos acumulados em um nível mensal dentro de cada registro. Assim, você pode acumular ainda mais, se quiser, para o ano, trimestre etc.
Mas o valor das reservas é diferente. É um equilíbrio. E a empresa quer ver quanto está no saldo a cada mês. Mas você não pode agregar neste campo. Se o fizesse, obteria alguns resultados instáveis.
De alguma forma, isso me parece errado. Mas não posso dizer com sinceridade que modelei o suficiente ou conheço o suficiente. Tudo o que posso dizer é o que sei. E pelo que sei, todos os valores em um Fato devem estar na mesma granularidade.
Ambos os números estão na mesma granularidade de um "mês", mas não são do ponto de vista do que representam. Um é dólares agregados dentro de um mês. O outro é apenas o equilíbrio.
Isso está correto? Eu tenho empurrado para trás neste design. Estou errado em fazer isso? Está tudo bem fazer isso de fato? Ou meu senso de "cheiro de código" de um design ruim é preciso?
Qualquer ajuda seria apreciada. NOTA: por favor, não diga apenas "Deveria ser o caminho X", explique por que deveria ser assim para que eu possa aprender com isso.
EDIT : Bem, aprendi que minha compreensão inicial do fato está errada. A granularidade NÃO é mensal. A granularidade é nível de transação. Isso significa que dentro de MONTH_YEAR (ou seja, realmente é o período do relatório financeiro) haverá várias transações de pagamento e recuperação. Esses serão postados por data ou data de transação. Mas por causa de um relatório anterior que a empresa vê, e também por causa de como os dados são armazenados no sistema legado de onde vem, eles queriam colocar os dados transacionais (uma linha por) e o saldo mensal de reserva (uma linha por mês ).
Depois que aprendi isso, percebi que o problema não era tanto aditivo versus não aditivo, ou mesmo semi-aditivo, mas era grão, que é o que eu suspeitava desde o início. Nossa equipe de DBA discutiu isso com a equipe do projeto e relatou que eles estão tentando colocar dois grãos diferentes no mesmo fato, e isso não foi correto. Que eles deveriam rolar as transações para um nível mensal, permitindo que eles tivessem os pagamentos, as recuperações e o saldo mensal da reserva (ou seja, um fato semi-aditivo) porque tudo seria em um grão mensal. Ou eles precisam encontrar uma maneira de dividir o saldo de reserva em transações para preservar o nível de granulação da transação. Ou eles precisam quebrar o fato em dois fatos. Um pode ser o nível mensal do saldo da reserva. O outro pode estar no nível da transação para os pagamentos e recuperações. (Não há razão para que eles também não possam colocar os pagamentos e recuperações em nível mensal no fato de nível mensal também. Depende apenas das necessidades do negócio.)
Dado o que aprendi, marcarei a resposta de Thomas como a correta. No entanto, sinto que a discussão que comecei com a pergunta original ainda é boa para os outros aprenderem, então deixarei a parte original da minha pergunta intacta. Também pretendo recompensar a resposta de nikadam, pois ela me ensinou muito sobre fatos aditivos, não aditivos e semiaditivos e corrigiu muitos mal-entendidos que eu tinha sobre modelagem dimensional.
Você está correto: " grãos diferentes não devem ser misturados na mesma tabela de fatos ".
Mas o saldo da reserva no final do mês e a soma dos pagamentos no final do mês são iguais. É apenas um dos fatos é semi-aditivo . Tipo de fato (aditivo ou não) não define a granulação da tabela.
Pelo que você descreveu, vejo seu grão como "instantâneo de reivindicação mensal", o que torna sua tabela de fatos " Tabela de fatos instantâneos periódicos ".
Neste artigo , Kimball tem um exemplo de fatos aditivos e semi-aditivos na mesma tabela de fatos.
Aqui está um exemplo de snapshot periódico com fatos semi-aditivos do The Data Warehouse Toolkit (página 116):
A melhor prática é ter uma tabela de fatos transacionais que reflita todas as mudanças na reserva (pagamentos e ajustes) no nível atômico mais baixo. Quando você lida com sinistros, muitas vezes o nível atômico não é sinistro, mas subsinistro (sua companhia de seguros pode ter seu próprio termo para isso). Geralmente, cada sub-reivindicação representará uma parte diferente da reivindicação e pagamentos/reservas para cada parte. Por exemplo, pode não haver pagamentos para o segurado, mas pagamentos para não segurados pela pessoa ferida de sua empresa e pagamentos para o hospital e advogado.
Dependendo do desempenho de sua ferramenta de BI, você pode usar a tabela de fatos transacionais diretamente para obter pagamentos e saldos mensais. Ou você pode atualizar a tabela de fatos instantâneos periódicos da transação diariamente ou no final do mês.
A capacidade de lidar com fatos semi-aditivos dependerá de qual camada de BI você está usando. Algumas ferramentas conseguem lidar facilmente com fatos semi-aditivos e outras não.
O livro principal de Kimball ( The Data Warehouse Toolkit ) tem um capítulo completo (16) sobre seguros.
Sua intuição de cheiro de código está bem aguçada.
Você está lidando com
reserveso que Kimball chama de "fato semi-aditivo". Não acumula bem para trimestre ou ano.A solução típica para isso é ter duas tabelas de fatos, uma para o fato aditivo (
paymentsno seu caso) e outra para o fato não aditivo. O fato não aditivo não precisa realmente ter um grão no nível do mês, você pode armazená-los até o dia e as coisas ainda funcionam.O fato não aditivo,
reserve, é questionado de forma diferente do outro fato. Há uma decisão de negócios que você precisa tomar: o que significareserveno nível do ano? É o último mês do ano, ou talvez a média dos meses do ano? Seja qual for a escolha que você fizer, você pode encontrar a solução para modelar isso nos livros de Kimball nos capítulos sobre fatos não aditivos.Observe que, se você usar um produto de cubo como o Analysis Services, é possível que os agregados "simplesmente funcionem" mesmo que você armazene tudo em uma tabela. No entanto, prefiro manter as coisas separadas para que as consultas relacionais sejam mais fáceis de escrever (e os fatos também sejam mais fáceis de carregar).