1 / 32

Dimensional Modeling (Advance)

Dimensional Modeling (Advance). Update Pada Tabel Dimensi. Setiap terjadi transaksi penjualan maka pada penambahan baris data juga akan dilakukan pada tabel fakta Bandikan dengan tabel dimensi , Tabel dimensi lebih stabil dibandingkan dengan tabel fakta .

azura
Download Presentation

Dimensional Modeling (Advance)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dimensional Modeling(Advance)

  2. Update PadaTabelDimensi • Setiapterjaditransaksipenjualanmakapadapenambahanbaris data jugaakandilakukanpadatabelfakta • Bandikandengantabeldimensi, Tabeldimensilebihstabildibandingkandengantabelfakta. • Transaksi OLTP meningkat update tabelfakta • Update berdasarkanpeningkatan data/rows • Namunpadatabeldimensi update tidakhanyadisebabkanolehpeningkatan data saja, tapidiakibatkanolehperubahan data danatribut

  3. Tipe update data padatabeldimensi • Slowly Changing Dimensions • Rapidly Changing Dimensions

  4. Slowly Changing Dimensions (SCD) • Kebanyakandimensikonstan • Banyakdimensi yang tidakkonstan, tetapichange slowly • Prymary key(product key) tidakberubah • Deskripsidanatribut yang lain, change slowly

  5. Ada 3 macam SCD • SCD1 : Record barumengganti record lama. Hanyaadasatu record pada database current data • SCD2 : Record baruditambahkanpadatabeldimensi. Terdapatdua record pada database – current record dan record/data sebelumnya. • SCD3 : Data yang aslidimodifikasidenganmemasukkaninformasibarudidalamnya. Pada database terdapatsatu record yaitu data lama dantambahaninformasibarupadabaris yang sama

  6. SCD1 • Mempebaiki error padasistemsumbersepertikesalahan data nama. • Kesalahanpenulisan data  • Kristin Daniels menjadi Kristin Samuelson • Perubahan status  BelumkawinmenjadiKawin (dilakukanhanyajikaterjadikesalahan data)

  7. SCD1

  8. SCD1 • Ganti data padaatributdengan data baru • Data yang lama tidakperludisimpan • Perubahan lain tidakdilakukanpadatabeldimensi • Kuncitidakterpengaruholehperubahan • Mudahuntukdilakukan

  9. SCD 2

  10. SCD 2 • Tambahkanbarisbarupadadimensibeserta update data • Effective data field masukpadatabeldimensi • Tidakadaperubahanpadabarisdimensi yang asli • Kuncibarisaslitidakterpengaruh • Tambahkan surrogate key padabarisbaru yang ditambahkan.

  11. Surrogate key  kuncipengganti natural key. Merupakankunci unique untuksetiapbarisdandapatdigunakansebagaipengganti primary key. • Harus unique untuksetiapbaris. • Bergunakarena primary key dapatberubahdanmengakibatkan update data menjadisukar. • Selalu integer atau numeric

  12. SCD3

  13. SCD3 • Tambahkanatribut “old” padadimensi • Pindahkancurent data pada old atribut • Masukkan data update pada current atribut • Kuncitidakterpengaruholehperubahan

  14. Hybrid slowly changing dimension • Kombinasidari SCD1 dan SCD2 • Bidaterjadipadatabel, beberapakolom yang pentingdankitaperlumenyimpan data perubahannya.

  15. IsuSeputarDimensi • Large Dimension • Multiple Hierarchies • Rapidly Changing Dimensions • Junk Dimensions

  16. Large Dimension • Very deep  mempunyaibanyakbaris • Very Wide  mempunyaibanyakatribut • Menyebabkan DW lambatdantidakefisien • Solusi : • Lakukan index • Memecahdimensimenjadi mini dimensi • Laukanteknik-teknikoptimasilainnya

  17. Multiple Hierarchies

  18. Rapidly Changing Dimensions

  19. Junk Dimensions • Atribut yang mempunyaiartiuntukmenjaga constraint queriberdasarkan flag/kode text. • Menggabungkanatribut-atributtersebutmenjadi sub dimensidisbut – junk dimension

  20. AGGREGATE FACT TABLES

  21. Query 1: Total sales for customer number 12345678 during the first week of December 2000 for product Widget-1. • Query 2: Total sales for customer number 12345678 during the first three months of 2000 for product Widget-1. • Query 3: Total sales for all customers in the South-Central territory for the first two quarters of 2000 for product category Bigtools. • Query diatasakanmenghitung total sales padasetiapkasus. • Total akandihitungdenganmenambahkan sales quantitativesdan sales dollars

  22. Query 1 • Semua fact table dimana key berhubungandengancustumer number 12345678, key product berelasidengan product Widget-1, dan time key berelasidengan 7 haripadaminggupertamadecember 2000. • Denganasumsitersebutseorangcustumerbisamelakukantransaksipembelian : • sebuahproduk • padasatuhari, • Makamaksimal 7 barispadatabelfaktaakanikutdalampenghitungan

  23. Query 2 • Semuabarispadatabelfaktadimanakuncicustumeradalah 12345678. kunciprodukberhubungandengan product Widget-1 dankunciwaktuberhubungandengansekitar 90 haripada quarter pertamatahun 2000 • Dari asusmsitersebutmakaSeorangcustumerdapatmelakukanpembelian • sebuahprosuk • Padasatuhari • Dan maks 90 barispadatabelfaktayangakandiikutkandalamperhitungan

  24. Query 3 • Semuabarispadatabelfaktadimanakuncicustumerberhubungandengansemuacustumerpada South-Central territory, kunci product berelasidengansemua product padaprocut category, dankunciwaktuberelasidengansekitar 180 haripadadua quarter pertamatahun. • Sehingga query yang ketigaini paling lama dieksekusi

  25. Fact Table Sizes

  26. Fact Table Sizes

  27. Aggregating Fact Tables

  28. One-Way Aggregates • 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

  29. Two-Way Aggregates • 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 • Product department by year by store • All products by territory by date • All products by region by date • All products by all stores by date • All products by month by store • All products by quarter by store • All products by year by store • District by month by product • District by quarter by product • District by year by product • Territory by month by product • Territory by quarter by product • Territory by year by product • Region by month by product • Region by quarter by product • Region by year by product • All stores by month by product • All stores by quarter by product • All stores by year by product

  30. Three-Way Aggregates Product category by region by quarter Product department by region by quarter All products by region by quarter Product category by all stores by quarter Product department by all stores by quarter Product category by territory by year Product department by territory by year All products by territory by year Product category by region by year Product department by region by year All products by region by year Product category by all stores by year Product department by all stores by year All products by all stores by year • 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

More Related