Tambahkan lebih banyak kekuatan pada analisis data Anda dengan membuat hubungan di antara tabel yang berbeda. Hubungan adalah koneksi antara dua tabel yang berisi data: satu kolom di setiap tabel adalah dasar untuk hubungan tersebut. Untuk mengetahui mengapa hubungan berguna, bayangkan Anda melacak data untuk pesanan pelanggan di bisnis Anda. Anda dapat melacak semua data dalam satu tabel yang memiliki struktur seperti ini:
| ID Pelanggan | Nama | TarifDiskon | IDPesanan | TanggalPesanan | Produk | Kuantitas | |
|---|---|---|---|---|---|---|---|
| 1 | Akbar | chris.ashton@contoso.com | .05 | 256 | 07-01-2010 | Kamera Digital | 11 |
| 1 | Akbar | chris.ashton@contoso.com | .05 | 255 | 03-01-2010 | Kamera SLR | 15 |
| 2 | Juniarta | michal.jaworski@contoso.com | .10 | 254 | 03-01-2010 | Budget Movie-Maker | 27 |
Pendekatan ini bisa berhasil, tetapi melibatkan penyimpanan banyak data berulang, misalnya alamat email pelanggan untuk setiap pesanan. Penyimpanan itu murah, tetapi jika alamat email berubah Anda harus memastikan Anda memperbarui setiap baris untuk pelanggan tersebut. Satu solusi untuk masalah ini adalah memisahkan data ke dalam beberapa tabel dan menentukan hubungan antar tabel tersebut. Ini adalah pendekatan yang digunakan dalam database relasional seperti SQL Server. Sebagai contoh, database yang Anda impor bisa mewakili data pesanan dengan menggunakan tiga tabel yang berkaitan:
Pelanggan
| [ID Pelanggan] | Nama | |
|---|---|---|
| 1 | Akbar | chris.ashton@contoso.com |
| 2 | Juniarta | michal.jaworski@contoso.com |
DiskonPelanggan
| [ID Pelanggan] | TarifDiskon |
|---|---|
| 1 | .05 |
| 2 | .10 |
Pesanan
| [ID Pelanggan] | IDPesanan | TanggalPesanan | Produk | Kuantitas |
|---|---|---|---|---|
| 1 | 256 | 07-01-2010 | Kamera Digital | 11 |
| 1 | 255 | 03-01-2010 | Kamera SLR | 15 |
| 2 | 254 | 03-01-2010 | Budget Movie-Maker | 27 |
Hubungan ada dalam Model Data—hubungan yang Anda buat secara eksplisit, atau yang dibuat secara otomatis Excel atas nama Anda saat Anda mengimpor beberapa tabel secara bersamaan. Anda juga dapat menggunakan add-in Power Pivot untuk membuat atau mengelola model. Lihat Membuat Model Data di Excel untuk selengkapnya.
Jika Anda menggunakan add-in Power Pivot untuk mengimpor tabel dari database yang sama, Power Pivot dapat mendeteksi hubungan antar tabel berdasarkan kolom yang ada dalam [tanda kurung siku], dan dapat mereproduksi hubungan ini dalam Model Data yang dibuat di belakang layar. Untuk informasi lebih lanjut, lihat Deteksi Otomatis dan Inferensi Hubungan dalam artikel ini. Jika tabel diimpor dari beberapa sumber, Anda dapat membuat hubungan secara manual seperti dijelaskan dalam Membuat hubungan antara dua tabel.
Kolom dan kunci
Hubungan didasarkan pada kolom dalam setiap tabel yang berisi data yang sama. Misalnya, Anda dapat menghubungkan tabel Pelanggan dengan tabel Pesanan jika masing-masing berisi kolom yang menyimpan ID Pelanggan. Dalam contoh tersebut, nama kolom sama, tetapi ini bukan persyaratan. Yang satu bisa menjadi CustomerID dan yang lain CustomerNumber, sepanjang semua baris di tabel Orders berisi sebuah ID yang juga tersimpan dalam tabel Customers.
Dalam database relasional, ada beberapa jenis kunci. Kunci biasanya berupa kolom dengan properti khusus. Memahami tujuan setiap kunci bisa membantu Anda mengelola beberapa tabel Model Data yang menyediakan data ke laporan PivotTable, PivotChart, atau Power View.
Meskipun ada banyak tipe kunci, ini adalah yang paling penting untuk tujuan kami di sini:
- Kunci utama: mengidentifikasi baris dalam tabel secara unik, seperti CustomerID dalam tabel Pelanggan .
- Kunci alternatif (atau kunci kandidat): kolom selain kunci utama yang unik. Sebagai contoh, tabel Karyawan mungkin menyimpan ID karyawan dan nomor jaminan sosial, keduanya unik.
- Kunci asing: kolom yang merujuk ke kolom unik di tabel lain, seperti CustomerID dalam tabel Pesanan , yang mengacu ke CustomerID di tabel Pelanggan.
Dalam Model Data, kunci utama atau kunci alternatif disebut sebagai kolom terkait. Jika tabel mempunyai baik kunci utama maupun kunci alternatif, Anda bisa menggunakan salah satunya sebagai dasar hubungan tabel. Kunci asing disebut sebagai kolom sumber atau cukup kolom. Dalam contoh kami, hubungan akan ditentukan antara CustomerID dalam tabel Pesanan (kolom) dan CustomerID dalam tabel Pelanggan (kolom pencarian). Jika Anda mengimpor data dari database relasional, secara default Excel memilih kunci asing dari satu tabel dan kunci terkait utama dari tabel lain. Namun, Anda bisa menggunakan kolom mana pun yang mempunyai nilai unik untuk kolom pencarian.
Tipe hubungan
Hubungan antara pelanggan dan pesanan adalah hubungan satu-ke-banyak. Setiap pelanggan bisa mempunyai beberapa pesanan, tetapi suatu pesanan tidak bisa mempunyai beberapa pelanggan. Hubungan tabel penting lainnya adalah satu-ke-satu. Dalam contoh kami di sini, tabel CustomerDiscounts , yang menentukan satu tingkat diskon untuk setiap pelanggan, memiliki hubungan satu lawan satu dengan tabel Pelanggan.
Tabel ini memperlihatkan hubungan antara tiga tabel (Pelanggan, CustomerDiscounts, dan Pesanan):
| Hubungan | Tipe | kolom pencarian | Kolom |
|---|---|---|---|
| Pelanggan-DiskonPelanggan | satu-lawan-satu | Pelanggan.IDPelanggan | DiskonPelanggan.IDPelanggan |
| Pelanggan-Pesanan | satu-ke-banyak | Pelanggan.IDPelanggan | Pesanan.IDPelanggan |
Catatan
Hubungan banyak ke banyak tidak didukung di Model Data. Contoh hubungan banyak ke banyak adalah hubungan langsung antara Products dan Customers, di mana pelanggan bisa membeli banyak produk dan produk yang sama bisa dibeli oleh banyak pelanggan.
Hubungan dan kinerja
Setelah hubungan dibuat, Excel biasanya harus menghitung ulang rumus apa pun yang menggunakan kolom dari tabel dalam hubungan yang baru dibuat. Pemrosesan bisa memakan waktu, bergantung pada jumlah data dan kompleksitas hubungan. Untuk detail selengkapnya, lihat Menghitung ulang Rumus.
Beberapa hubungan di antara tabel
Model Data bisa mempunyai beberapa hubungan antara dua tabel. Untuk membuat penghitungan yang akurat, Excel memerlukan satu jalur dari satu tabel ke tabel berikutnya. Oleh karena itu, hanya satu hubungan antara setiap pasang tabel yang aktif pada satu waktu. Meskipun yang lain tidak aktif, Anda dapat menentukan hubungan tidak aktif dalam rumus dan kueri.
Dalam Tampilan Diagram, hubungan aktif adalah garis solid dan yang tidak aktif adalah garis putus-putus. Misalnya, di AdventureWorksDW2012, tabel DimDate berisi kolom, DateKey, yang terkait dengan tiga kolom berbeda dalam tabel FactInternetSales: OrderDate, DueDate, dan ShipDate. Jika hubungan aktif adalah antara KunciTanggal dan OrderDate, itulah hubungan default dalam rumus kecuali Anda menentukan lain.
Persyaratan untuk hubungan tabel
Hubungan dapat dibuat bila persyaratan berikut terpenuhi:
| Kriteria | Deskripsi |
|---|---|
| Pengidentifikasi unik untuk setiap tabel | Setiap tabel harus mempunyai satu kolom yang mengidentifikasi setiap baris secara unik dalam tabel tersebut. Kolom ini sering disebut sebagai kunci utama. |
| Kolom pencarian unik | Nilai data dalam kolom pencarian harus unik. Dengan kata lain, kolom tersebut tidak bisa berisi duplikat. Di Model Data, null dan string kosong sama dengan kosong, yang merupakan nilai data yang berbeda. Ini berarti bahwa Anda tidak bisa memiliki beberapa null dalam kolom lookup. |
| Tipe data yang kompatibel | Tipe data di kolom sumber dan kolom pencarian harus kompatibel. Untuk informasi selengkapnya tentang tipe data, lihat Tipe data yang didukung dalam Model Data. |
Fitur database yang tidak didukung di Model Data Excel
Di Model Data, Anda tidak bisa membuat hubungan tabel jika kuncinya adalah kunci komposit. Anda juga dibatasi untuk membuat hubungan satu ke satu dan satu ke banyak. Tipe hubungan lainnya tidak didukung.
Kunci Komposit dan Kolom Pencarian
Kunci komposit terdiri atas beberapa kolom. Model Data tidak dapat menggunakan kunci komposit: tabel harus selalu memiliki tepat satu kolom yang mengidentifikasi setiap baris dalam tabel secara unik. Jika Anda mengimpor tabel yang memiliki hubungan yang sudah ada berdasarkan kunci komposit, Panduan Impor Tabel di Power Pivot akan mengabaikan hubungan tersebut karena tidak dapat dibuat di model.
Untuk membuat hubungan antara dua tabel yang memiliki beberapa kolom yang mendefinisikan kunci primer dan kunci asing, kombinasikan nilai terlebih dulu untuk membuat kolom kunci tunggal sebelum membuat hubungan. Anda dapat melakukannya sebelum mengimpor data, atau dengan membuat kolom terhitung di Model Data menggunakan add-in Power Pivot.
Hubungan Banyak ke Banyak
Model Data tidak bisa mempunyai hubungan banyak ke banyak. Anda tidak bisa menambahkan tabel sambungan dalam model. Namun, Anda bisa menggunakan fungsi DAX untuk membuat model hubungan banyak ke banyak.
Penggabungan Otomatis dan Pengulangan
Penggabungan otomatis tidak diizinkan dalam Model Data. Penggabungan otomatis adalah hubungan berulang antara satu tabel dengan tabel itu sendiri. Penggabungan otomatis sering digunakan untuk menentukan hierarki induk/turunan. Sebagai contoh, Anda bisa menggabungkan tabel Karyawan ke tabel itu sendiri untuk menghasilkan hierarki yang memperlihatkan rantai manajemen di sebuah bisnis.
Excel tidak mengizinkan pembuatan pengulangan di antara hubungan dalam sebuah buku kerja. Dengan kata lain, kumpulan hubungan berikut dilarang.
Tabel 1, kolom a hingga Tabel 2, kolom f
Tabel 2, kolom f ke Tabel 3, kolom n
Tabel 3, kolom n ke Tabel 1, kolom a
Jika Anda mencoba membuat hubungan yang menghasilkan pengulangan, maka pesan kesalahan akan ditampilkan.
Deteksi otomatis dan inferensi hubungan di Power Pivot
Salah satu keuntungan mengimpor data menggunakan add-in Power Pivot adalah Power Pivot terkadang dapat mendeteksi hubungan dan membuat hubungan baru dalam Model Data yang dibuatnya di Excel.
Saat Anda mengimpor beberapa tabel, Power Pivot secara otomatis mendeteksi hubungan yang ada di antara tabel. Selain itu, saat Anda membuat PivotTable, Power Pivot akan menganalisis data dalam tabel. Excel mendeteksi kemungkinan hubungan yang belum didefinisikan dan menyarankan kolom yang sesuai untuk disertakan dalam hubungan tersebut.
Algoritma deteksi menggunakan data statistik tentang nilai dan metadata kolom untuk membuat inferensi tentang probabilitas hubungan.
- Tipe data di semua kolom terkait harus kompatibel. Untuk deteksi otomatis, hanya tipe data bilangan bulat dan teks yang didukung. Untuk informasi selengkapnya mengenai tipe data, lihat Tipe Data yang didukung dalam Model Data.
- Agar hubungan berhasil dideteksi, jumlah kunci unik dalam kolom pencarian harus lebih besar daripada nilai dalam tabel pada sisi banyak. Dengan kata lain, kolom kunci pada sisi banyak di hubungan tersebut tidak boleh berisi nilai apa pun yang tidak ada di kolom kunci pada tabel pencarian. Sebagai contoh, anggap Anda mempunyai tabel yang mencantumkan daftar produk dengan ID-nya (tabel pencarian) dan tabel penjualan yang mencantumkan daftar penjualan untuk setiap produk (sisi banyak dari hubungan). Jika catatan penjualan Anda berisi ID produk yang tidak mempunyai ID terkait dalam tabel Products, hubungan tidak bisa dibuat secara otomatis, tetapi Anda mungkin bisa membuatnya secara manual. Agar Excel mendeteksi hubungan tersebut, Anda harus terlebih dahulu memperbarui tabel pencarian Product dengan ID dari produk yang hilang.
- Pastikan bahwa nama kolom kunci pada sisi banyak mirip dengan nama kolom kunci di tabel pencarian. Nama tersebut tidak harus sama persis. Misalnya, dalam lingkungan bisnis, Anda sering memiliki variasi pada nama kolom yang pada dasarnya berisi data yang sama: Emp ID, EmployeeID, ID Karyawan, EMP_ID, dan lain sebagainya. Algoritma mendeteksi nama yang mirip dan menetapkan probabilitas yang lebih tinggi pada kolom yang mempunyai nama yang serupa atau persis sama. Oleh karena itu, untuk meningkatkan probabilitas pembuatan hubungan, Anda bisa mencoba mengganti nama kolom dalam data yang Anda impor menjadi nama yang serupa dengan kolom di tabel yang sudah ada. Jika Excel menemukan beberapa hubungan yang mungkin, maka Excel tidak membuat hubungan.
Informasi ini mungkin membantu Anda memahami mengapa tidak semua hubungan terdeteksi, atau bagaimana perubahan dalam metadata--seperti nama bidang dan tipe data-- bisa meningkatkan hasil deteksi hubungan otomatis. Untuk informasi selengkapnya, lihat Memecahkan Masalah Hubungan.
Deteksi Otomatis untuk Kumpulan Bernama
Hubungan antara Kumpulan Nama dan bidang terkait dalam PivotTable tidak secara otomatis dideteksi. Anda bisa membuat hubungan ini secara manual. Jika Anda ingin menggunakan deteksi hubungan otomatis, hapus setiap Kumpulan Nama dan tambahkan bidang individual dari Kumpulan Nama langsung ke PivotTable.
Inferensi Hubungan
Dalam beberapa kasus, hubungan antar tabel secara otomatis dikaitkan. Sebagai contoh, jika Anda membuat hubungan di antara dua kumpulan tabel pertama di bawah ini, maka hubungan akan disimpulkan ada di antara kedua tabel lainnya dan hubungan dibuat secara otomatis.
Products dan Category -- dibuat secara manual
Category dan SubCategory -- dibuat secara manual
Products dan SubCategory -- hubungan disimpulkan
Agar hubungan dikaitkan secara otomatis, hubungan harus berjalan dalam satu arah, seperti yang diperlihatkan di atas. Jika hubungan awalnya adalah antara, misalnya, Sales dan Products, dan Sales dan Customers, maka hubungan tidak disimpulkan. Ini karena hubungan antara Products dan Customers adalah hubungan banyak ke banyak.