Dimensional Modeling: Advance Topics (chapt 11)

Chapter Objectives

• Examine the snowflake schema in detail
• Learn about aggregate tables and determine when to use them

Skema Snowflake

• Merupakan metode normalisasi tabel-tabel dimensi pada skema STAR.
• Asumsi :
• Terdapat 500.000 baris pada tabel dimensi produk.
• Produk memiliki 500 brand produk
• Brand produk terbagi menjadi 10 kategori produk.

Jika tabel dimensi produk tidak di-indeks pada kaegori produk, query yang dilakukan pada kategori produk akan melakukan pencarian pada sebanyak 500.000 baris.

Jika tabel dimensi produk dinormalisasi terpisah masing-masing menjadi tabel brand produk dan tabel kategori produk, maka query pada kategori produk hanya akan mencari pada 10 baris pada tabel kategori produk.

Metode untuk normalisasi tabel dimensi

• Lakukan normalisasi parsial hanya pada beberapa tabel dimensi, biarkan yang lain tetap.
• Lakukan normalisasi parsial atau keseluruhan hanya pada beberapa tabel dimensi, biarkan yang lain tetap.
• Lakukan normalisasi parsial pada setiap tabel dimensi.
• Lakukan normalisasi keseluruhan pada setiap tabel dimensi

Advantages and Disadvantages

• Small savings pada storage
• Struktur normalisasi lebih mudah di update dan maintain
• Simulasi :

500.000 baris tabel dimensi produk. Dengan adanya snowflake, akan berkurang sebanyak 500.000 20-byte nama kategori produk. Saat yang sama, menambahkan 4-byte id-kategori pada tabel dimensi.

Storage yang dihemat : 500.000 x 16 = 8 MB

Jika rata-rata 500.000 baris menghabiskan 200 MB, maka yang dihemat adalah sekitar 4% storage

• Schema kurang intuitif dan end-user diperlambat oleh kompleksitas
• Kemampuan mencari melalui konten sulit dilakukan karena banyak menggunakan indeksing(key)
• Penurunan performa query karena terdapat tambahan join.

Snowflaking is not generally recommended in a data warehouse environment

Kapan menggunakan Skema Snowflake

• City classification dipisahkan dari tabel Dimensi customer, karena kumpulan atribut berbeda secara granularitynya.
• Jika dimensi customer sangat besar, jutaan baris, maka penghematan storage akan sangat berpengaruh.

Tabel Fakta Agregasi

• Query 1: Total sales for customer number 12345678 during the first week of December 2008 for product Widget-1.
• Query 2: Total sales for customer number 12345678 during the first three months of 2009 for product Widget-1.
• Query 3: Total sales for all customers in the south-central territory for the first two quarters of 2009 for product category Bigtools

• Query 1: Total sales for customer number 12345678 during the first week of December 2008 for product Widget-1.

All fact table rows where the customer key relates to customer number 12345678, the product key relates to product Widget-1, and the time key relates to the seven days in the first week of December 2008. Assuming that a customer may make at most one purchase of a single product in a single day, only a maximum of seven fact table rows participate in the summation

• Query 2: Total sales for customer number 12345678 during the first three months of 2009 for product Widget-1.

All fact table rows where the customer key relates to customer number 12345678, the product key relates to product Widget-1, and the time key relates to about 90 days of the first quarter of 2009. Assuming that a customer may make at most one purchase of a single product in a single day, only about 90 fact table rows or less participate in the summation

• Query 3: Total sales for all customers in the south-central territory for the first two quarters of 2009 for product category Bigtools

All fact table rows where the customer key relates to all customers in the south-central territory, the product key relates to all products in the product category Bigtools, and the time key relates to about 180 days in the first two quarters of 2009. In this case, clearly a large number of fact table rows participate in the summation

Query 3 akan membutuhkan waktu yang lama, karena sejumlah besar baris pada tabel fakta yang perlu diproses.

Solusi : gunakan aggregate tabel

Ukuran tabel fakta

• Terdapat sekitar 2 milyar baris pada tabel fakta dengan granularity level terendah.
• Dimensi waktu : 5tahun x 365 hari = 1825
• Dimensi store : 300 store melaporkan penjulan harian (daily sales)
• Dimensi product : 40.000 product pada setiap store (sekitar 4000 penjualan pada setiap store per hari)
• Dimensi promosi : item yang terjual hanya terdapat pada sebuag promosi pada masing-masing store pada hari yang ditentukan
• Jumlah maksimum record tabel fakta : 1825 x 300 x 4000 x 1 = 2 milyar baris

Ukuran tabel fakta (cont)

• Estimasi tambahan baris data pada tabel fakta :
• Monitoring panggilan telepon
• Dimensi waktu : 5 tahun = 1825 hari
• Jumlah panggilan yang di-rack setiap hari = 150 juta
• Jumlah maksimum record tabel fakta = 274 milyar

Ukuran tabel fakta (cont)

• Estimasi tambahan baris data pada tabel fakta :
• Tracking transaksi kartu kredit :
• Dimensi waktu : 5 tahun = 60 bulan
• Jumlah akun kartu kredit = 150 juta
• Jumlah rata-rata transaksi bulanan per akun = 20
• Jumlah maksimum record tabel fakta = 180 milyar

• If you need detailed data at the lowest level of granularity in the base fact tables, how do you deal with summations of huge numbers of fact table rows to produce query result?
• Query :
• How did the three new stores in Wisconsin perform during the last three months compared to the national average?
• What is the effect of the latest holiday sales campaign on meat and poultry?
• How do the July 4th holiday sales by product categories compare to last year? to produce query results?

Untuk memproses queri-queri tersebut, diperlukan selection dan summation pada baris tabel fakta.

Pada query ke-3, diperlukan data harian pada dimensi waktu (tgl 4 juli), tapi

berdasarkan kategori produk.

Jika pada tabel fakta sudah terdapat total summary, query akan diproses lebih cepat.

Kebutuhan untuk Aggregate

• 300 store dengan asumsi 500 produk per brand. Dari 40.000 produk, diasumsikan setidaknya 1 penjualan per produk per store per minggu.
• Estimasi jumlah baris tabel fakta :
• Query melibatkan 1 produk, 1 store, 1 minggu 🡪 meretrieve/summarize hanya 1 baris tabel fakta
• Query melibatkan 1 produk, ALL store, 1 minggu 🡪 meretrieve/summarize 300 baris tabel fakta
• Query melibatkan 1 brand, 1 store, 1 minggu 🡪 meretrieve/summarize 500 baris tabel fakta
• Query melibatkan 1 brand, ALL store, 1 tahun🡪 meretrieve/summarize 7800.000 baris tabel fakta
• jika dilakukan/dibuat aggregate fact table untuk setiap row yang disummarized untuk total brand, per store, per minggu. Maka query ke-3 hanya me-retrieve 1 baris. Sedangkan query ke-4 hanya perlu meretrieve 15.600 baris.

Aggregating Tabel Fakta

​

• Setiap row pada tabel fakta menunjukkan angka penjualan unit dan nilai penjualan (dollar) pada 1 tanggal, 1 store, dan 1 product.
• One way aggregates : digunakan saat menuju ke level hirarki yang lebih tinggi pada dimensi yang satu dan menuju ke level hirarki yang lebih rendah pada dimensi lain.
• Product category by store by date
• Product department by store by date

Aggregating Tabel Fakta

• One way aggregates :

Digunakan saat mencapai ke level hirarki yang lebih tinggi pada dimensi yang satu dan mencapai ke level hirarki yang lebih rendah pada dimensi lain.

• Product category by store by date
• Product department by store by date
• All products by store by date
• Territory by product by date
• Region by product by date
• All stores by product by date
• Month by store by product
• Quarter by store by product
• Year by store by product

Aggregating Tabel Fakta

• Two way aggregates :

Digunakan saat mencapai ke level hirarki yang lebih tinggi pada dua dimensi dan mencapai ke level hirarki yang lebih rendah pada dimensi lain.

• Product category by territory by date
• Product category by region by date
• Product category by all stores by date
• Product category by month by store
• Product category by quarter by store
• Product category by year by store
• Product department by territory by date
• Product department by region by date
• Product department by all stores by date
• Product department by month by store
• Product department by quarter by store

Aggregating Tabel Fakta

• Three way aggregates :

Digunakan saat mencapai ke level hirarki yang lebih tinggi pada semua dimensi.

• Product category by territory by month
• Product department by territory by month
• All products by territory by month
• Product category by region by month
• Product department by region by month
• All products by region by month
• Product category by all stores by month
• Product department by all stores by month
• Product category by territory by quarter
• Product department by territory by quarter
• All products by territory by quarter
• Product category by region by quarter

• Aggregate fact table diturunkan dari fact table, yang kemudian di-joinkan dengan lebih dari 1 tabel dimensi turunan.
• Brand = 80
• Store = 300
• Time = 365 x 5 tahun = 1825
• Max number row dari tabel aggregate = 43.800.000 row.
• Jauh lebih sedikit dari 2milyar baris

Experienced data warehousing practitioners have a suggestion

• When you form aggregates, make sure that each aggregate table row summarizes at least 10 rows in the lower level table.
• If you increase this to 20 rows or more, it would be really remarkable.