Tenho lido sobre os índices columnstore do Microsoft SQL Server recentemente e fiquei preocupado que minha experiência não corresponda à do setor. Os índices columnstore são projetados para tornar os agregados fáceis. É dito com muita frequência que, por esse motivo, eles são ideais para tabelas de fatos.
Isso me deixa desconfortável. Na minha experiência, tabelas de fatos não são agregadas . A lista de colunas para cada tabela de fatos que já fiz tem o formato "chave primária seguida por várias colunas de dimensão, seguidas por cada bit de informação que armazenamos sobre a chave primária". Elas então são despejadas em alguma ferramenta de BI como PowerBI ou ThoughtSpot, para que pessoas não técnicas possam filtrar com base nas dimensões e descobrir o que quiserem saber. Elas podem agregar de tempos em tempos, mas esse certamente não é o uso principal da tabela. Espero que essas tabelas tenham bem mais de 100 colunas.
O design da tabela e o padrão de acesso que estou descrevendo são o que se espera de uma tabela de fatos? Ou estou descrevendo um tipo de tabela totalmente diferente, cujo nome desconheço?
Não, esta é apenas uma tabela desnormalizada.
As tabelas de fatos e as tabelas de dimensões devem ser separadas em um data warehouse modelado corretamente que segue o padrão de design de esquema Star clássico. As tabelas de dimensões geralmente são uma lista exclusiva de informações descritivas relacionadas aos seus objetos de dados (particularmente as medidas de suas tabelas de fatos), também conhecidas como dados qualitativos. As tabelas de fatos são os dados quantitativos, também conhecidas como as próprias medidas, que geralmente são dados numéricos para fins analíticos.
Geralmente os fatos são armazenados desagregados (em um grau razoável) para que possam ser agregados em diferentes graus para diferentes combinações de dimensões. A pré-agregação dos fatos torna impossível executar consultas analíticas e responder perguntas em granularidades de nível mais baixo. Aqui estão mais alguns detalhes e um exemplo.
Não tenho certeza de como seu design atual lida com múltiplas combinações de dimensões. Talvez você tenha linhas repetidas para a mesma dimensão com cada uma de suas combinações para outras dimensões na mesma tabela?
Sim, isso é verdade na maior parte. Eles ajudam a fazer com que agregações e consultas analíticas tenham melhor desempenho ao compactar dados em um nível colunar e, talvez ainda mais importante, ao poder utilizar operações em modo batch para atender a essas consultas.