Basis data dapat diimplementasikan menggunakan Excel, GSheet, atau menggunakan sistem ORM besar. Dalam praktik analisis bisnis saya, saya menemukan berbagai solusi. Dan karena saya datang ke analisis bisnis dari keuangan dan audit, setiap kali saya bertemu dengan sistem baru, saya bertanya pada diri sendiri - bagaimana mereka semua berbeda satu sama lain dan tugas apa yang mereka selesaikan? Menemukan beberapa jawaban. Artikel ini akan membahas dua tujuan utama dari database:
1 - akuntansi operasi,
2 - analisis data
Jenis tugas pertama diselesaikan oleh sistem OLTP: dari O n L ine T ransaction P rocessing. Tipe kedua diselesaikan oleh sistem OLAP: dari O n L ine A nalytical Processing
OLTP
Model penyimpanan OLTP dapat dibandingkan dengan entri buku telepon. Baris dalam tabel disajikan sebagai indeks dan indeks data yang sesuai: (indexN, data). Oleh karena itu, tabel seperti itu tidak dapat disebut tabel. Ini agak buku biasa, dengan garis-garis bernomor. Jika Anda perlu menulis operasi baru untuk buku, tambahkan baris, tetapkan indeks dan tutup buku. Label mencuat dari buku yang memungkinkan Anda dengan cepat O (log n), menemukan baris yang diinginkan dan melakukan CRUD.
Untuk keperluan akuntansi operasi, ini adalah tampilan yang ramah. Tapi itu tidak memusuhi analisis data, di mana bukan garis itu sendiri yang penting bagi kami, tetapi perhitungan berdasarkan pada isi dari garis-garis ini. Dan jika Anda membuat kueri analitik berdasarkan pada isi baris, mis. untuk bidang yang tidak diindeks, maka permintaan seperti itu akan bekerja lebih lambat.
Mengindeks semua catatan, seperti yang Anda tahu, bukan opsi. Meskipun buku menjadi seperti tabel, karena atribut menjadi tersedia untuk pencarian cepat, buku ini juga memperlambat pembuatan baris baru dan memperbarui yang ada. Karena operasi ini akan membutuhkan pengurutan ulang seluruh array.
Tradeoff antara OLAP dan OLTP
Dalam solusi 1C, kompromi diterapkan sebagai berikut. Peristiwa ketika menulis ke database ditulis ke beberapa tempat sekaligus. Di satu tempat, catatan memiliki beberapa indeks dan dioptimalkan untuk beban OLTP, di tempat lain, catatan diindeks oleh semua bidang dan disesuaikan untuk beban OLAP. Tabel tersebut disebut register akumulasi dan register informasi. Karena menulis ke beberapa tempat meningkatkan ruang yang ditempati beberapa kali, untuk menghemat tidak semua atribut transaksi termasuk dalam register, tetapi hanya yang dianggap penting untuk bagian akuntansi analitik ini. Kompromi seperti itu disebut model ROLAP, mis. pemetaan analitik relasional.
OLAP
Di SAP, mitra 1C Jerman melangkah lebih jauh. Model OLTP relasional dalam perangkat lunak ini dapat direplikasi ke model OLAP. SAP HANA mengimplementasikan struktur kolom penyimpanan. Ini berarti bahwa "tabel" disimpan di sana bukan sebagai satu set baris, tetapi sebagai satu set kolom.
Skema penyimpanan serupa diterapkan dalam solusi seperti Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
Perbedaan antara penyimpanan kolom dan penyimpanan baris
Jika dalam struktur baris-bijaksana, data disimpan dalam bentuk tupel "horizontal", yang masing-masing merupakan transaksi:
period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ...
Kemudian di kolom data tersebut disimpan "secara vertikal":
(Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...)
Pengulangan dapat dioptimalkan secara kondisional sebagai berikut:
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}...)
Jika ada kolom di mana optimasi tersebut tidak akan mengurangi volume awal, maka data disimpan dalam bentuk aslinya.
Mesin tabel kolom itu sendiri memilih urutan pengurutan kolom, tetapi jika Anda tahu data Anda dan mengurutkannya secara manual, ini sering meningkatkan kompresi dan memudahkan beban analitis. Kompresi tabel individual saya melebihi 300 kali. Dalam praktiknya, struktur penyimpanan data seperti itu:
- memungkinkan Anda untuk mengompres data ke level ketika mereka ditempatkan di RAM, mis. membuat komputasi dalam memori tersedia yang tidak sebanding dalam kecepatan dengan permintaan ke database relasional
- menetapkan aturannya sendiri untuk membangun model data, tidak lagi memerlukan normalisasi seperti di OLTP
- mendefinisikan semantiknya untuk membangun ekspresi analitik.
Spesifikasi ekspresi dijelaskan secara rinci:
di sini adalah untuk Google BigQuery.
di sini adalah untuk Microsoft DAX.
BI sebagai infrastruktur dasar kolom
BI adalah solusi yang melayani beban analitis. Dan mereka membuat hidup lebih mudah jika dibangun di atas basis data kolom. Ini bisa berupa sekelompok ClickHouse-Grafana-Python buatan sendiri atau tumpukan Google: Bigquery-Data Studio-Dataprep-Dataflow atau Power BI monolitik.
Kubus multidimensi adalah alternatif OLAP lain untuk penyimpanan kolom. Tetapi bagi saya, ekspresi MDX, bila dibandingkan dengan SQL dalam BQ atau DAX, berlebihan dan kompleks.