PowerPivot untuk Excel - Lookup Table di Data Warehouse

Salah satu perkara yang paling saya list mengenai PowerPivot untuk Excel adalah keupayaan untuk menambah jadual carian pada set data anda. Kebanyakan masa, data yang anda bekerjasama tidak mempunyai setiap bidang yang anda perlukan untuk analisis anda. Sebagai contoh, anda mungkin mempunyai medan tarikh tetapi perlu mengumpulkan data anda mengikut suku. Anda boleh menulis formula, tetapi lebih mudah untuk membuat jadual carian mudah dalam persekitaran PowerPivot.

Anda juga boleh menggunakan jadual carian ini untuk kumpulan lain seperti nama bulan dan separuh pertama / kedua tahun ini. Dalam istilah pergudangan data, anda sebenarnya membuat jadual dimensi tarikh. Dalam artikel ini, saya akan memberikan anda beberapa contoh jadual dimensi untuk meningkatkan projek PowerPivot untuk Excel anda.

Jadual Teks Dimensi (Lookup)

Mari kita pertimbangkan satu jadual dengan data pesanan (data Contoso dari Microsoft merangkumi satu set data simile untuk ini). Anggapkan jadual mempunyai bidang untuk pelanggan, tarikh pesanan, jumlah tempahan, dan jenis pesanan. Kami akan menumpukan pada bidang jenis pesanan. Anggapkan medan jenis pesanan termasuk nilai-nilai seperti:

• Netbook
• Desktop
• Monitor
• Projektor
• Pencetak
• Pengimbas
• Kamera Digital
• Kamera Digital SLR
• Kamera Filem
• Camcorder
• Telefon Pejabat
• Telefon pintar
• PDA
• Aksesori Telefon Bimbit

Pada hakikatnya, anda akan mempunyai kod untuk ini tetapi untuk memastikan contoh ini mudah, anggap ini adalah nilai sebenar dalam jadual pesanan.

Menggunakan PowerPivot untuk Excel, anda dengan mudah dapat mengelompokkan pesanan anda mengikut jenis pesanan. Bagaimana jika anda mahukan kumpulan yang berbeza? Sebagai contoh, andaikan anda memerlukan kumpulan "kategori" seperti komputer, kamera, dan telefon. Jadual pesanan tidak mempunyai medan "kategori", tetapi anda boleh dengan mudah membuatnya sebagai jadual carian dalam PowerPivot untuk Excel.

Jadual carian sampel lengkap adalah di bawah dalam Jadual 1 . Berikut adalah langkah-langkah berikut:

• Langkah 1: Anda memerlukan senarai yang berbeza dari medan jenis untuk jadual carian anda. Ini akan menjadi bidang pencarian anda. Dari set data anda, buat senarai nilai yang berbeza dari medan jenis pesanan. Masukkan senarai "jenis" yang berbeza ke dalam buku kerja Excel. Labelkan Jenis lajur.
• Langkah 2: Dalam lajur di sebelah lajur carian anda (Jenis), tambahkan medan baru yang ingin anda keluarkan. Dalam contoh kami, tambah lajur dengan label yang dipanggil Kategori.
• Langkah 3: Untuk setiap nilai dalam senarai nilai anda yang berbeza (jenis dalam contoh ini), tambahkan nilai "Kategori" yang sepadan. Dalam contoh mudah kita, masukkan sama ada Komputer, Kamera atau Telefon ke dalam lajur Kategori.
• Langkah 4: Salin jadual data Jenis dan Kategori ke dalam clipboard anda.
• Langkah 5: Buka buku kerja Excel dengan data pesanan dalam PowerPivot untuk Excel. Lancarkan tetingkap PowerPivot. Klik Tampal yang akan membawa jadual carian baharu anda. Beri jadual nama dan pastikan anda menyemak "Gunakan baris pertama sebagai tajuk lajur." Klik OK. Anda telah membuat jadual carian dalam PowerPivot.
• Langkah 6: Buat hubungan antara medan Jenis dalam jadual Pesanan dan medan Kategori dalam jadual carian. Klik pada reben Rekabentuk dan pilih Cipta Hubungan. Buat pilihan dalam dialog Buat Perhubungan dan klik Buat.

Apabila anda membuat PivotTable dalam Excel berdasarkan data PowerPivot, anda akan dapat dikelompokkan oleh medan Kategori baru anda. Perlu diingat bahawa PowerPivot untuk Excel hanya menyokong Inner Joins. Jika anda mempunyai "jenis pesanan" yang hilang dari jadual carian anda, semua rekod yang sepadan untuk jenis tersebut akan hilang dari sebarang PivotTable berdasarkan data PowerPivot. Anda perlu menyemak ini dari semasa ke semasa.

Jadual Dimensi (Lookup)

Jadual carian Jadual kemungkinan besar diperlukan dalam kebanyakan projek PowerPivot untuk Excel anda. Kebanyakan set data mempunyai beberapa jenis medan tarikh. Terdapat fungsi untuk mengira tahun dan bulan.

Walau bagaimanapun, jika anda memerlukan teks bulan atau suku tahun yang sebenar, anda perlu menulis formula rumit. Lebih mudah memasukkan jadual dimensi (lookup) Tarikh dan sepadan dengan nombor bulan dalam set data utama anda. Anda perlu menambah lajur pada jadual pesanan anda untuk mewakili nombor bulan dari medan tarikh pesanan. Formula DAX untuk "bulan" dalam contoh kami ialah "= BULAN ([Tarikh Pesanan]) Ini akan memulangkan nombor antara 1 dan 12 untuk setiap rekod Jadual meja dimensi kami akan memberikan nilai alternatif yang menghubungkan dengan nombor bulan. akan memberikan anda kelonggaran dalam analisis anda. Jadual dimensi tarikh sampel lengkap adalah di bawah dalam Jadual 2 .

Tarikh dimensi atau jadual carian akan mengandungi 12 rekod. Lajur bulan akan mempunyai nilai 1 - 12. Lajur yang lain akan termasuk teks bulan yang disingkat, teks bulan penuh, suku, dan lain-lain Berikut adalah langkah-langkah:

• Langkah 1: Salin jadual dari Jadual 2 di bawah dan tampal ke PowerPivot. Anda boleh membuat jadual ini dalam Excel tetapi saya menjimatkan masa anda. Anda harus dapat menyalin secara langsung dari data yang dipilih di bawah jika anda menggunakan Internet Explorer. PowerPivot mengambil pemformatan meja dalam ujian saya. Sekiranya anda menggunakan penyemak imbas lain, anda perlu terlebih dahulu menempel ke Excel dan menyalinnya dari Excel untuk memilih pemformatan meja.
• Langkah 2: Buka buku kerja Excel dengan data pesanan dalam PowerPivot untuk Excel. Lancarkan tetingkap PowerPivot. Klik Tampal yang akan membawa jadual carian anda disalin dari jadual di bawah atau dari Excel. Beri jadual nama dan pastikan anda menyemak "Gunakan baris pertama sebagai tajuk lajur." Klik OK. Anda telah membuat jadual carian tarikh dalam PowerPivot.

• Langkah 3 : Buat hubungan antara medan Bulan dalam jadual Pesanan dan medan Bulan Bulan dalam jadual carian. Klik pada reben Rekabentuk dan pilih Cipta Hubungan. Buat pilihan dalam dialog Buat Perhubungan dan klik Buat.

Sekali lagi, dengan penambahan dimensi tarikh, anda akan dapat mengumpulkan data dalam PivotTable anda menggunakan mana-mana nilai yang berbeza dari jadual carian tarikh. Pengkumpulan mengikut suku atau nama bulan akan menjadi sekejap.

Jadual Dimensi (Lookup)

Jadual 1

Jadual 2