.::: Sample Data Warehouse in SQL Server :::.

Untuk menjadikan query ini sebagai skema data warehouse, tabel-tabel operasional tersebut perlu diubah menjadi tabel fakta dan tabel dimensi.

Skema Data Warehouse
Tabel Dimensi
Dimensi Barang: Mengandung detail tentang barang.
Dimensi Suplier: Mengandung detail tentang suplier.
Dimensi Customer: Mengandung detail tentang customer.
Dimensi Waktu: Mengandung detail waktu untuk analisis.
Tabel Fakta
Fakta Transaksi: Menggabungkan data dari pembelian dan pasok untuk menyimpan fakta transaksi.

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query.html

A. Struktur Tabel Dimensi dan Fakta
1. Dimensi Barang

CREATE TABLE dim_barang (
    barang_id INT IDENTITY(1,1) PRIMARY KEY,
    kode_barang CHAR(6) UNIQUE,
    nama_barang VARCHAR(25),
    satuan_barang VARCHAR(20)
);
2. Dimensi Suplier

CREATE TABLE dim_suplier (
    suplier_id INT IDENTITY(1,1) PRIMARY KEY,
    kode_suplier CHAR(5) UNIQUE,
    nama_suplier VARCHAR(30),
    kota_suplier VARCHAR(15)
);

3. Dimensi Customer

CREATE TABLE dim_customer (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    kode_customer CHAR(6) UNIQUE,
    nama_customer VARCHAR(30),
    kota_customer VARCHAR(15)
);

4. Dimensi Waktu

CREATE TABLE dim_waktu (
    waktu_id INT IDENTITY(1,1) PRIMARY KEY,
    tanggal DATE UNIQUE,
    tahun INT,
    bulan INT,
    hari INT,
    minggu INT,
    hari_nama VARCHAR(15)
);

5. Fakta Transaksi

CREATE TABLE fact_transaksi (
    transaksi_id INT IDENTITY(1,1) PRIMARY KEY,
    barang_id INT FOREIGN KEY REFERENCES dim_barang(barang_id),
    suplier_id INT FOREIGN KEY REFERENCES dim_suplier(suplier_id),
    customer_id INT FOREIGN KEY REFERENCES dim_customer(customer_id),
    waktu_id INT FOREIGN KEY REFERENCES dim_waktu(waktu_id),
    jenis_transaksi VARCHAR(10), -- 'Pasok' atau 'Pembelian'
    jumlah DECIMAL(10,2)
);

B. Query Pengisian Data

1. Isi Data ke Tabel Dimensi Barang

INSERT INTO dim_barang (kode_barang, nama_barang, satuan_barang)
SELECT KODE_BARANG, NAMA_BARANG, SATUAN_BARANG FROM teguhth..barang;

2. Isi Data ke Tabel Dimensi Suplier

INSERT INTO dim_suplier (kode_suplier, nama_suplier, kota_suplier)
SELECT KODE_SUPLIER, NAMA_SUPLIER, KOTA_SUPLIER FROM teguhth..suplier;

3. Isi Data ke Tabel Dimensi Customer

INSERT INTO dim_customer (kode_customer, nama_customer, kota_customer)
SELECT KODE_CUSTOMER, NAMA_CUSTOMER, KOTA_CUSTOMER FROM teguhth..customer;

4. Isi Data ke Tabel Dimensi Waktu

INSERT INTO dim_waktu (tanggal, tahun, bulan, hari, minggu, hari_nama)
SELECT DISTINCT
    TANGGAL_PASOK AS tanggal,
    YEAR(TANGGAL_PASOK) AS tahun,
    MONTH(TANGGAL_PASOK) AS bulan,
    DAY(TANGGAL_PASOK) AS hari,
    DATEPART(WEEK, TANGGAL_PASOK) AS minggu,
    DATENAME(WEEKDAY, TANGGAL_PASOK) AS hari_nama
FROM teguhth..pasok
UNION
SELECT DISTINCT
    TANGGAL_PEMBELIAN AS tanggal,
    YEAR(TANGGAL_PEMBELIAN) AS tahun,
    MONTH(TANGGAL_PEMBELIAN) AS bulan,
    DAY(TANGGAL_PEMBELIAN) AS hari,
    DATEPART(WEEK, TANGGAL_PEMBELIAN) AS minggu,
    DATENAME(WEEKDAY, TANGGAL_PEMBELIAN) AS hari_nama
FROM teguhth..pembelian;

5. Isi Data ke Tabel Fakta Transaksi
a. Data dari Pasok

INSERT INTO fact_transaksi (barang_id, suplier_id, waktu_id, jenis_transaksi, jumlah)
SELECT
    db.barang_id,
    ds.suplier_id,
    dw.waktu_id,
    'Pasok' AS jenis_transaksi,
    p.JUMLAH_PASOK
FROM teguhth..pasok p
JOIN dim_barang db ON p.KODE_BARANG = db.kode_barang
JOIN dim_suplier ds ON p.KODE_SUPLIER = ds.kode_suplier
JOIN dim_waktu dw ON p.TANGGAL_PASOK = dw.tanggal;

b. Data dari Pembelian

INSERT INTO fact_transaksi (barang_id, customer_id, waktu_id, jenis_transaksi, jumlah)
SELECT
    db.barang_id,
    dc.customer_id,
    dw.waktu_id,
    'Pembelian' AS jenis_transaksi,
    pb.JUMLAH_PEMBELIAN
FROM teguhth..pembelian pb
JOIN dim_barang db ON pb.KODE_BARANG = db.kode_barang
JOIN dim_customer dc ON pb.KODE_CUSTOMER = dc.kode_customer
JOIN dim_waktu dw ON pb.TANGGAL_PEMBELIAN = dw.tanggal;

C. Analisis Data Warehouse
Setelah data diatur dalam skema data warehouse, Anda dapat membuat query analisis seperti:

1. Total Penjualan dan Pasok per Barang

SELECT
    db.nama_barang,
    SUM(CASE WHEN ft.jenis_transaksi = 'Pasok' THEN ft.jumlah ELSE 0 END) AS total_pasok,
    SUM(CASE WHEN ft.jenis_transaksi = 'Pembelian' THEN ft.jumlah ELSE 0 END) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_barang db ON ft.barang_id = db.barang_id
GROUP BY db.nama_barang;

2. Penjualan Berdasarkan Kota Customer

SELECT
    dc.kota_customer,
    SUM(ft.jumlah) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_customer dc ON ft.customer_id = dc.customer_id
WHERE ft.jenis_transaksi = 'Pembelian'
GROUP BY dc.kota_customer;
 

3. Total Transaksi Berdasarkan Jenis (Pasok atau Pembelian) Per Tahun

SELECT
    dw.tahun,
    ft.jenis_transaksi,
    SUM(ft.jumlah) AS total_jumlah
FROM fact_transaksi ft
JOIN dim_waktu dw ON ft.waktu_id = dw.waktu_id
GROUP BY dw.tahun, ft.jenis_transaksi
ORDER BY dw.tahun, ft.jenis_transaksi;
 

4. Barang Paling Laris Berdasarkan Penjualan

SELECT
    db.nama_barang,
    SUM(ft.jumlah) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_barang db ON ft.barang_id = db.barang_id
WHERE ft.jenis_transaksi = 'Pembelian'
GROUP BY db.nama_barang
ORDER BY total_penjualan DESC;

5. Rata-rata Penjualan dan Pasok per Bulan

SELECT
    dw.bulan,
    ft.jenis_transaksi,
    AVG(ft.jumlah) AS rata_rata_jumlah
FROM fact_transaksi ft
JOIN dim_waktu dw ON ft.waktu_id = dw.waktu_id
GROUP BY dw.bulan, ft.jenis_transaksi
ORDER BY dw.bulan, ft.jenis_transaksi;

6. Suplier dengan Total Pasok Terbesar

SELECT
    ds.nama_suplier,
    SUM(ft.jumlah) AS total_pasok
FROM fact_transaksi ft
JOIN dim_suplier ds ON ft.suplier_id = ds.suplier_id
WHERE ft.jenis_transaksi = 'Pasok'
GROUP BY ds.nama_suplier
ORDER BY total_pasok DESC;
 

7. Kota Customer dengan Penjualan Terbanyak

SELECT
    dc.kota_customer,
    SUM(ft.jumlah) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_customer dc ON ft.customer_id = dc.customer_id
WHERE ft.jenis_transaksi = 'Pembelian'
GROUP BY dc.kota_customer
ORDER BY total_penjualan DESC;

8. Penjualan Berdasarkan Tahun dan Kota Customer

SELECT
    dw.tahun,
    dc.kota_customer,
    SUM(ft.jumlah) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_waktu dw ON ft.waktu_id = dw.waktu_id
JOIN dim_customer dc ON ft.customer_id = dc.customer_id
WHERE ft.jenis_transaksi = 'Pembelian'
GROUP BY dw.tahun, dc.kota_customer
ORDER BY dw.tahun, dc.kota_customer;
 

9. Barang dengan Tren Penjualan Paling Meningkat

SELECT
    db.nama_barang,
    dw.tahun,
    SUM(ft.jumlah) AS total_penjualan
FROM fact_transaksi ft
JOIN dim_barang db ON ft.barang_id = db.barang_id
JOIN dim_waktu dw ON ft.waktu_id = dw.waktu_id
WHERE ft.jenis_transaksi = 'Pembelian'
GROUP BY db.nama_barang, dw.tahun
ORDER BY db.nama_barang, dw.tahun;