Eu construí um SSIS ETL para integrar várias fontes de dados (uma do MySQL, duas do SQL Server) em um único banco de dados SQL Server relacional e normalizado, que chamei de [NDS].
O SSIS ETL lida com atualizações do tipo 2 e, portanto, o [NDS] gera chaves substitutas e as tabelas SCD incluem um registro de data e hora [_EffectiveFrom] e uma coluna anulável [_EffectiveTo], e há restrições para as chaves naturais e belas chaves estrangeiras que vinculam todas as dados juntos.
Agora, eu queria construir um banco de dados dimensional SSAS a partir dele e não demorou muito para perceber que estava me preparando para um esquema de floco de neve:
Portanto, estou pensando em adicionar um novo banco de dados [DDS] (relacional) para criar a dimensão real e as tabelas de fatos que alimentarão os DSVs para o banco de dados SSAS.
Este banco de dados [DDS] seria tão desnormalizado quanto humanamente possível, de modo a "achatar" os fatos e dimensões (como, [OrderHeaders]+[OrderDetails] em uma tabela de fatos [Orders] e [CustomerStores]+[Customers]+ [SalesReps] em alguma tabela de dimensão [Clientes]) - fazer isso não deve apenas facilitar a construção das hierarquias de dimensão no SSAS, mas também facilitar a criação de um esquema em estrela real.
Eu tenho algumas perguntas embora:
- Posso reutilizar um subconjunto de minhas chaves substitutas existentes? Estou pensando em pegar a chave existente para o nível mais granular e torná-la a chave de dimensão. Essa é uma boa abordagem ou devo simplesmente ignorar as chaves substitutas [NDS] e fazer com que o [DDS] (db relacional) gere um novo conjunto de chaves substitutas?
- Como lidar com SCD? Por exemplo, "Materiais" e "Fornecedores" irão gerar novos registros em [NDS] quando alguns campos específicos mudarem no sistema de origem... Acho que terei que projetar o SSIS ETL para carregar apenas os registros de "última imagem" no banco de dados [DDS] e, em seguida, reimplemente as atualizações do tipo 2 nesse processo, ou seja, trate o [NDS] como um "sistema de origem" que mantém o histórico, enquanto duplica tudo neste banco de dados [DDS]. Mas então, por que eu precisaria manter o histórico no [NDS] e no [DDS]? Claramente algo não está certo.
Estou me preparando para um Big Mess™ ou estou no caminho certo?
Como você pode ver, um dos benefícios do Kimball Dimensional Modeling é que o projeto do data warehouse é essencialmente o seu projeto SSAS. Embora sempre haja exceções, normalmente você pode selecionar uma tabela no DSV e mover imediatamente para o design de hierarquia, relacionamentos de cubo etc.
Eu recomendaria mudar para o DDS com a ressalva de que você eliminaria gradualmente o NDS. Pelo simples motivo que você mencionou com o SCD Tipo II - não há motivo para duplicar todos esses dados, ETL e base de código. Manter ambos leva a uma solução excessivamente complexa com muita manutenção e risco - este é o principal Big Mess™ que você precisa evitar.
Eis a justificativa:
O design alternativo que eu sugeriria apenas se você tivesse pequenas quantidades de dados, nenhuma expectativa significativa de aumentos e nenhuma capacidade de mudar para o data warehouse. Essa sugestão inclui muito do trabalho de design que, de outra forma, iria para o DW e, como tal, é semelhante a manter o NDS e criar o DDS:
Você pode criar seus cubos com consultas manifestadas como Visualizações ou codificadas no DSV.
Parece que você superou muitos obstáculos e criou uma ótima solução funcional com o NDS. Infelizmente, o que ele não faz são duas coisas importantes que a modelagem dimensional fornece: padrões de consulta simples e tradução fácil em análises multidimensionais. Felizmente, muito do design de ETL provavelmente é útil como modelo ou ponto de partida para carregar uma estrutura de tabela diferente.