PowerPivot untuk Excel - Tabel Pencarian di Gudang Data

Salah satu hal yang paling saya cantumkan tentang PowerPivot untuk Excel adalah kemampuan untuk menambahkan tabel pencarian ke kumpulan data Anda. Seringkali, data yang Anda kerjakan tidak memiliki semua bidang yang Anda perlukan untuk analisis Anda. Misalnya, Anda mungkin memiliki bidang tanggal tetapi perlu mengelompokkan data Anda menurut kuartal. Anda bisa menulis rumus, tetapi lebih mudah untuk membuat tabel pencarian sederhana dalam lingkungan PowerPivot.

Anda juga dapat menggunakan tabel pencarian ini untuk pengelompokan lain seperti nama bulan dan paruh pertama / kedua tahun ini. Dalam istilah pergudangan data, Anda sebenarnya membuat tabel dimensi tanggal. Dalam artikel ini, saya akan memberikan Anda beberapa contoh tabel dimensi untuk meningkatkan proyek PowerPivot untuk Excel Anda.

Tabel Dimensi Teks Baru (Pencarian)

Mari kita pertimbangkan tabel dengan data pesanan (data Contoso dari Microsoft termasuk kumpulan data yang mirip dengan ini). Asumsikan tabel memiliki bidang untuk pelanggan, tanggal pesanan, total pesanan, dan jenis pesanan. Kami akan fokus pada bidang jenis pesanan. Asumsikan bidang jenis pesanan termasuk nilai-nilai seperti:

• Netbook
• Desktops
• Pengawas
• Proyektor
• Printer
• Pemindai
• Kamera digital
• Kamera Digital SLR
• Kamera Film
• Kamera perekam
• Telepon Kantor
• Ponsel pintar
• PDA
• Aksesori Telepon Seluler

Pada kenyataannya, Anda akan memiliki kode untuk ini tetapi untuk menjaga contoh ini sederhana, anggap ini adalah nilai-nilai aktual dalam tabel urutan.

Dengan menggunakan PowerPivot untuk Excel, Anda akan dengan mudah dapat mengelompokkan pesanan Anda berdasarkan jenis pesanan. Bagaimana jika Anda menginginkan pengelompokan yang berbeda? Misalnya, anggap Anda memerlukan pengelompokan "kategori" seperti komputer, kamera, dan ponsel. Tabel urutan tidak memiliki bidang "kategori", tetapi Anda dapat dengan mudah membuatnya sebagai tabel pencarian di PowerPivot untuk Excel.

Tabel lookup sampel lengkap ada di bawah pada Tabel 1 . Berikut langkahnya:

• Langkah 1: Anda perlu daftar yang berbeda dari kolom jenis untuk tabel pencarian Anda. Ini akan menjadi bidang pencarian Anda. Dari kumpulan data Anda, buat daftar nilai berbeda dari bidang jenis pesanan. Masukkan daftar berbeda dari "jenis" ke dalam buku kerja Excel. Label Jenis kolom.
• Langkah 2: Di kolom di samping kolom pencarian Anda (Ketik), tambahkan bidang baru yang ingin Anda kelompokkan. Dalam contoh kami, tambahkan kolom dengan label yang disebut Kategori.
• Langkah 3: Untuk setiap nilai dalam daftar nilai Anda yang berbeda (jenis dalam contoh ini), tambahkan nilai "Kategori" yang sesuai. Dalam contoh sederhana kami, masukkan Komputer, Kamera, atau Telepon ke kolom Kategori.
• Langkah 4: Salin tabel data Jenis dan Kategori ke clipboard Anda.
• Langkah 5: Buka buku kerja Excel dengan data pesanan di PowerPivot untuk Excel. Luncurkan Jendela PowerPivot. Klik Tempel yang akan membawa tabel pencarian Anda yang baru. Berikan nama pada tabel dan pastikan Anda mencentang "Gunakan baris pertama sebagai tajuk kolom." Klik OK. Anda telah membuat tabel pencarian di PowerPivot.
• Langkah 6: Buat hubungan antara kolom Jenis di tabel Pesanan dan bidang Kategori di tabel pencarian. Klik pada pita Desain dan pilih Buat Hubungan. Buat pilihan dalam dialog Buat Hubungan dan klik Buat.

Ketika Anda membuat PivotTable di Excel berdasarkan data PowerPivot, Anda akan dapat mengelompokkan menurut bidang Kategori baru Anda. Perlu diingat bahwa PowerPivot untuk Excel hanya mendukung Inner Join. Jika Anda memiliki "jenis pesanan" yang hilang dari tabel pencarian Anda, semua catatan terkait untuk jenis itu akan hilang dari PivotTable apa pun berdasarkan data PowerPivot. Anda perlu memeriksa ini dari waktu ke waktu.

Tabel Dimensi Tanggal (Pencarian)

Tabel pencarian tanggal kemungkinan besar akan diperlukan di sebagian besar proyek PowerPivot untuk Excel Anda. Sebagian besar set data memiliki beberapa jenis bidang tanggal. Ada fungsi untuk menghitung tahun dan bulan.

Namun, jika Anda membutuhkan teks bulan atau kuartal yang sebenarnya, Anda perlu menulis rumus yang rumit. Jauh lebih mudah untuk memasukkan tabel Tanggal dimensi (lookup) dan mencocokkannya dengan nomor bulan di kumpulan data utama Anda. Anda perlu menambahkan kolom ke tabel pesanan Anda untuk mewakili nomor bulan dari bidang tanggal pesanan. Rumus DAX untuk "bulan" dalam contoh kami adalah "= BULAN ([Tanggal Pesanan]). Ini akan mengembalikan angka antara 1 dan 12 untuk setiap catatan. Tabel dimensi kami akan memberikan nilai alternatif, yang menaut ke nomor bulan. Ini akan memberikan Anda fleksibilitas dalam analisis Anda. Tabel dimensi tanggal sampel lengkap adalah di bawah pada Tabel 2 .

Dimensi tanggal atau tabel pencarian akan menyertakan 12 rekaman. Kolom bulan akan memiliki nilai 1 - 12. Kolom lain akan mencakup teks bulan singkat, teks bulan penuh, kuartal, dll. Berikut adalah langkah-langkahnya:

• Langkah 1: Salin tabel dari Tabel 2 di bawah dan tempelkan ke PowerPivot. Anda bisa membuat tabel ini di Excel tetapi saya menghemat waktu Anda. Anda harus dapat menempel langsung dari data yang dipilih di bawah ini jika Anda menggunakan Internet Explorer. PowerPivot mengambil format tabel dalam pengujian saya. Jika Anda menggunakan browser lain, Anda mungkin harus menempelkan ke Excel terlebih dahulu dan menyalinnya dari Excel untuk mengambil format tabel.
• Langkah 2: Buka buku kerja Excel dengan data pesanan di PowerPivot untuk Excel. Luncurkan Jendela PowerPivot. Klik Tempel yang akan membawa tabel pencarian Anda disalin dari tabel di bawah atau dari Excel. Berikan nama pada tabel dan pastikan Anda mencentang "Gunakan baris pertama sebagai tajuk kolom." Klik OK. Anda telah membuat tabel pencarian tanggal di PowerPivot.

• Langkah 3 : Buat hubungan antara bidang Bulan di tabel Pesanan dan bidang MonthNumber di tabel pencarian. Klik pada pita Desain dan pilih Buat Hubungan. Buat pilihan dalam dialog Buat Hubungan dan klik Buat.

Sekali lagi, dengan penambahan dimensi tanggal, Anda akan dapat mengelompokkan data dalam PivotTable Anda menggunakan salah satu nilai yang berbeda dari tabel pencarian tanggal. Pengelompokan per kuartal atau nama bulan akan sangat cepat.

Tabel Dimensi Sampel (Pencarian)

Tabel 1

Meja 2