Las bases de datos se pueden implementar usando Excel, GSheet o usando sistemas ORM grandes. En mi práctica de análisis de negocios, encontré diferentes soluciones. Y desde que llegué al análisis de negocios de finanzas y auditoría, cada vez que conocí un nuevo sistema, me hacía preguntas: ¿en qué se diferencian entre sí y qué tareas resuelven? Encontré algunas respuestas. Este artículo cubrirá dos propósitos principales de las bases de datos:
1 - contabilidad de operaciones,
2 - análisis de datos
El primer tipo de tareas lo resuelven los sistemas OLTP: desde el procesamiento de transacciones en línea. El segundo tipo lo resuelven los sistemas OLAP: desde el procesamiento analítico on line
OLTP
El modelo de almacenamiento OLTP se puede comparar con las entradas de la guía telefónica. La fila de la tabla se presenta como un índice y el índice de datos correspondiente: (indexN, data). Por lo tanto, dicha tabla no puede llamarse tabla. Es más bien un libro ordinario, con líneas numeradas. Si necesita escribir una nueva operación en el libro, agregue una línea, asigne un índice y cierre el libro. Etiquetas que sobresalen del libro que le permiten rápidamente O (log n), encontrar la línea deseada y hacer CRUD.
Para fines de contabilidad de operaciones, esta es una presentación amigable. Pero no es hostil al análisis de datos, en el que no estamos interesados en las líneas en sí, sino en los cálculos basados en el contenido de estas líneas. Y si realiza una consulta analítica basada en el contenido de las filas, es decir para campos no indexados, estas consultas funcionarán más lentamente.
La indexación de todos los registros, como sabes, no es una opción. Aunque el libro se convierte en una tabla, a medida que los atributos están disponibles para la búsqueda rápida, también ralentiza la creación de filas nuevas y actualizadas. Debido a que estas operaciones requerirán volver a ordenar toda la matriz.
La compensación entre OLAP y OLTP
En las soluciones 1C, se implementa un compromiso de la siguiente manera. Los eventos al escribir en la base de datos se escriben en varios lugares a la vez. En un lugar, los registros tienen pocos índices y están optimizados para cargas OLTP; en otro lugar, todos los campos indexan los registros y se adaptan para cargas OLAP. Dichas tablas se denominan registros de acumulación y registros de información. Dado que escribir en varios lugares aumenta el espacio ocupado varias veces, para guardar no todos los atributos de transacción caen en los registros, sino solo aquellos que se consideran importantes para esta sección de contabilidad analítica. Tal compromiso se llama modelo ROLAP, es decir mapeo analítico relacional.
OLAP
En SAP, la contraparte alemana 1C fue más allá. El modelo OLTP relacional en este software se puede replicar al modelo OLAP. SAP HANA implementa una estructura de columnas de almacenamiento. Esto significa que las "tablas" se almacenan allí no como un conjunto de filas, sino como un conjunto de columnas.
Se implementa un esquema de almacenamiento similar en soluciones como Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
La diferencia entre el almacenamiento de columnas y el almacenamiento de filas
Si en una estructura en filas, los datos se almacenan en forma de tuplas "horizontales", cada una de las cuales es una transacción:
period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ...
Luego, en la columna, dichos datos se almacenan "verticalmente":
(Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...)
Las repeticiones se pueden optimizar condicionalmente de la siguiente manera:
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}...)
Si hay una columna para la cual dicha optimización no reducirá el volumen inicial, entonces los datos se almacenan en su forma original.
El propio motor de tabla de columnas selecciona la secuencia de clasificación de columnas, pero si conoce sus datos y los ordena manualmente, esto a menudo aumenta la compresión y facilita las cargas de trabajo analíticas. Mi compresión de tablas individuales superó las 300 veces. En la práctica, dicha estructura de almacenamiento de datos:
- le permite comprimir datos al nivel cuando se colocan en la RAM, es decir Poner a disposición cálculos en memoria que no son comparables en velocidad con consultas a bases de datos relacionales
- establece sus propias reglas para construir un modelo de datos, ya no requiere tal normalización como en OLTP
- define su semántica para construir expresiones analíticas.
Los detalles de las expresiones se describen en detalle:
aquí está para Google BigQuery.
aquí es para Microsoft DAX.
BI como infraestructura base de columna
BI es una solución que sirve a la carga analítica. Y hacen la vida mucho más fácil si se construyen sobre bases de datos de columna. Esto puede ser un grupo casero de ClickHouse-Grafana-Python o un paquete de pila de Google: Bigquery-Data Studio-Dataprep-Dataflow o monolítico Power BI.
Los cubos multidimensionales son otra alternativa OLAP al almacenamiento de columnas. Pero para mí, las expresiones MDX, en comparación con SQL en BQ o DAX, son redundantes y complejas.