Les bases de données peuvent être implémentées à l'aide d'Excel, de GSheet ou à l'aide de grands systèmes ORM. Dans ma pratique de l'analyse commerciale, je suis tombé sur différentes solutions. Et depuis que je suis arrivée à l'analyse commerciale de la finance et de l'audit, chaque fois que je rencontrais un nouveau système, je me posais des questions - en quoi diffèrent-elles les unes des autres et quelles tâches résolvent-elles? J'ai trouvé quelques réponses. Cet article couvrira deux objectifs principaux des bases de données:
1 - comptabilité des opérations,
2 - analyse des données
Le premier type de tâches est résolu par les systèmes OLTP: à partir du traitement en ligne des transactions. Le deuxième type est résolu par les systèmes OLAP: de O n L ine A nalytical Processing
OLTP
Le modèle de stockage OLTP peut être comparé aux entrées de l'annuaire téléphonique. La ligne du tableau est présentée sous la forme d'un index et de l'index de données correspondant: (indexN, données). Par conséquent, une telle table ne peut pas être appelée table. C'est plutôt un livre ordinaire, avec des lignes numérotées. Si vous devez écrire une nouvelle opération dans le livre, ajoutez une ligne, attribuez un index et fermez le livre. Des étiquettes qui sortent du livre qui vous permettent de rapidement O (log n), trouver la ligne souhaitée et faire CRUD.
Aux fins de la comptabilité des opérations, il s'agit d'un affichage convivial. Mais ce n'est pas hostile à l'analyse des données, dans laquelle ce ne sont pas les lignes elles-mêmes qui sont importantes pour nous, mais les calculs basés sur le contenu de ces lignes. Et si vous faites une requête analytique basée sur le contenu des lignes, c'est-à-dire pour les champs non indexés, ces requêtes fonctionneront plus lentement.
Comme vous le savez, l'indexation de tous les enregistrements n'est pas une option. Bien que le livre devienne comme un tableau, à mesure que les attributs deviennent disponibles pour une recherche rapide, il ralentit également la création de nouvelles lignes et la mise à jour de lignes existantes. Parce que ces opérations nécessiteront un nouveau tri de l'ensemble du tableau.
Le compromis entre OLAP et OLTP
Dans les solutions 1C, un compromis est implémenté comme suit. Les événements lors de l'écriture dans la base de données sont écrits à plusieurs endroits à la fois. À un endroit, les enregistrements ont peu d'index et sont optimisés pour les charges OLTP; à un autre endroit, les enregistrements sont indexés par tous les champs et adaptés aux charges OLAP. Ces tables sont appelées registres d'accumulation et registres d'information. Étant donné que l'enregistrement à plusieurs endroits augmente l'espace occupé de plusieurs fois, par souci d'économie, tous les attributs de transaction ne tombent pas dans les registres, mais seulement ceux qui sont considérés comme importants pour cette section de la comptabilité analytique. Un tel compromis est appelé le modèle ROLAP, c'est-à-dire cartographie analytique relationnelle.
OLAP
Dans SAP, l'homologue allemand 1C est allé plus loin. Le modèle OLTP relationnel de ce logiciel peut être répliqué sur le modèle OLAP. SAP HANA implémente une structure de colonnes de stockage. Cela signifie que les "tables" y sont stockées non pas comme un ensemble de lignes, mais comme un ensemble de colonnes.
Un schéma de stockage similaire est mis en œuvre dans des solutions telles que Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
La différence entre le stockage en colonne et le stockage en ligne
Si dans une structure en ligne, les données sont stockées sous la forme de tuples "horizontaux", dont chacun est une transaction:
period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ...
Ensuite, dans la colonne, ces données sont stockées "verticalement":
(Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...)
Les répétitions peuvent être optimisées conditionnellement comme suit:
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}...)
S'il existe une colonne pour laquelle une telle optimisation ne réduira pas le volume initial, les données sont stockées dans leur forme d'origine.
Le moteur de table de colonnes lui-même sélectionne la séquence de tri des colonnes, mais si vous connaissez vos données et les triez manuellement, cela augmente souvent la compression et allège les charges de travail analytiques. Ma compression de tables individuelles a dépassé 300 fois. En pratique, une telle structure de stockage de données:
- vous permet de compresser les données au niveau lorsqu'elles sont placées dans la RAM, c'est-à-dire rendre disponibles des calculs en mémoire dont la vitesse n'est pas comparable aux requêtes vers des bases de données relationnelles
- définit ses propres règles pour la construction d'un modèle de données, ne nécessitant plus une normalisation comme dans OLTP
- définit sa sémantique pour construire des expressions analytiques.
Les spécificités des expressions sont décrites en détail:
voici pour Google BigQuery.
voici pour Microsoft DAX.
BI en tant qu'infrastructure de base de colonne
BI est une solution au service de la charge analytique. Et ils facilitent la vie s'ils sont construits sur des bases de données de colonnes. Cela peut être un groupe ClickHouse-Grafana-Python maison ou un ensemble de piles Google: Bigquery-Data Studio-Dataprep-Dataflow ou Power BI monolithique.
Les cubes multidimensionnels sont une autre alternative OLAP au stockage sur colonne. Mais pour moi, les expressions MDX, par rapport à SQL en BQ ou DAX, sont redondantes et complexes.