Os bancos de dados podem ser implementados usando o Excel, GSheet ou usando grandes sistemas ORM. Na minha prática de análise de negócios, me deparei com diferentes soluções. E desde que cheguei à análise de negócios de finanças e auditoria, cada vez que conhecia um novo sistema, me fazia perguntas - como elas diferem umas das outras e que tarefas resolvem? Encontrei algumas respostas. Este artigo abordará dois objetivos principais dos bancos de dados:
1 - contabilidade das operações,
2 - análise de dados
O primeiro tipo de tarefa é resolvido pelos sistemas OLTP: a partir do processo de transação final. O segundo tipo é resolvido pelos sistemas OLAP: a partir do Processamento nalítico
OLTP
O modelo de armazenamento OLTP pode ser comparado às entradas da lista telefônica. A linha da tabela é apresentada como um índice e o índice de dados correspondente: (indexN, data). Portanto, essa tabela não pode ser chamada de tabela. É um livro comum, com linhas numeradas. Se você precisar escrever uma nova operação no livro, adicione uma linha, atribua um índice e feche o livro. Etiquetas destacadas do livro que permitem que você rapidamente O (log n), encontre a linha desejada e faça CRUD.
Para fins de contabilidade de operações, esta é uma exibição amigável. Mas não é hostil à análise de dados, na qual não são as próprias linhas que são importantes para nós, mas os cálculos baseados no conteúdo dessas linhas. E se você fizer uma consulta analítica com base no conteúdo das linhas, ou seja, para campos não indexados, essas consultas funcionarão mais lentamente.
A indexação de todos os registros, como você sabe, não é uma opção. Embora o livro se torne uma tabela, à medida que os atributos se tornam disponíveis para pesquisa rápida, também diminui a criação de linhas novas e atualizadas. Porque essas operações exigirão uma nova classificação de toda a matriz.
A troca entre OLAP e OLTP
Nas soluções 1C, um compromisso é implementado da seguinte maneira. Os eventos ao gravar no banco de dados são gravados em vários locais ao mesmo tempo. Em um local, os registros possuem poucos índices e são otimizados para cargas OLTP; em outro local, os registros são indexados por todos os campos e adaptados para cargas OLAP. Essas tabelas são chamadas registros de acumulação e registros de informações. Como a gravação em vários locais aumenta o espaço ocupado em várias vezes, por uma questão de economia, nem todos os atributos da transação caem nos registros, mas apenas aqueles que são considerados importantes para esta seção da contabilidade analítica. Esse compromisso é chamado de modelo ROLAP, ou seja, mapeamento analítico relacional.
OLAP
Na SAP, a contraparte alemã 1C foi além. O modelo OLTP relacional neste software pode ser replicado para o modelo OLAP. O SAP HANA implementa uma estrutura de coluna de armazenamento. Isso significa que as "tabelas" são armazenadas lá, não como um conjunto de linhas, mas como um conjunto de colunas.
Um esquema de armazenamento semelhante é implementado em soluções como Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
A diferença entre armazenamento de coluna e armazenamento de linha
Se, em uma estrutura de linhas, os dados são armazenados na forma de tuplas "horizontais", cada uma das quais é uma transação:
period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ...
Então, na coluna, esses dados são armazenados "verticalmente":
(Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...)
As repetições podem ser otimizadas condicionalmente da seguinte maneira:
period = (Q1, {start: 0, count: n}, Q2, {start: n+1; count: m}, ...) product = (SKU1, {start: 0, count: 1}, SKU2, {start: 1; count: 1}, SKU1, {start: 2; count: m}, ...) department = (1,{start:0, count:m}...)
Se houver uma coluna para a qual essa otimização não reduzirá o volume inicial, os dados serão armazenados em sua forma original.
O mecanismo da própria tabela de colunas escolhe a sequência de classificação das colunas, mas se você conhece seus dados e os classifica manualmente, isso geralmente aumenta a compactação e facilita a carga analítica. Minha compactação de tabelas individuais excedeu 300 vezes. Na prática, essa estrutura de armazenamento de dados:
- permite compactar dados até o nível em que são colocados na RAM, ou seja, disponibilizar cálculos na memória que não são comparáveis em velocidade com consultas a bancos de dados relacionais
- define suas próprias regras para a construção de um modelo de dados, não exigindo mais a normalização como no OLTP
- define sua semântica para construir expressões analíticas.
Os detalhes das expressões são descritos em detalhes:
aqui é para o Google BigQuery.
aqui é para o Microsoft DAX.
BI como uma infraestrutura base de coluna
BI é uma solução que atende a carga analítica. E eles tornam a vida muito mais fácil se construídos sobre bancos de dados de colunas. Pode ser um monte caseiro ClickHouse-Grafana-Python ou um pacote da pilha do Google: Bigquery-Data Studio-Dataprep-Dataflow ou Power BI monolítico.
Cubos multidimensionais são outra alternativa OLAP ao armazenamento de colunas. Mas para mim, as expressões MDX, quando comparadas ao SQL no BQ ou DAX, são redundantes e complexas.