Hubungan antar tabel dalam Model Data

Browser Anda tidak mendukung video. Instal Microsoft Silverlight, Adobe Flash Player, atau Internet Explorer 9.
Video: Hubungan di Power View dan Power Pivot

Tambahkan keunggulan ke analisis data Anda dengan membuat hubungan antar data dalam tabel yang berbeda. Hubungan adalah koneksi antara dua tabel data, berdasarkan pada satu kolom dalam setiap tabel. Untuk mengetahui mengapa hubungan berguna, bayangkan Anda melacak data untuk pesanan pelanggan di bisnis Anda. Anda bisa melacak semua data dalam satu tabel yang memiliki struktur seperti berikut:

IDPelanggan

Nama

Email

TarifDiskon

IDPesanan

TanggalPesanan

Produk

Kuantitas

1

Akbar

Cholid.Akbar@contoso.com

.05

256

07-01-2010

Kamera Digital

11

1

Akbar

Cholid.Akbar@contoso.com

.05

255

03-01-2010

Kamera SLR

15

2

Juniarta

malik.juniarta@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

[IDPelanggan]

Nama

Email

1

Akbar

Cholid.Akbar@contoso.com

2

Juniarta

malik.juniarta@contoso.com

DiskonPelanggan

[IDPelanggan]

TarifDiskon

1

.05

2

.10

Pesanan

[IDPelanggan]

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 pada Model Data yang Anda buat secara eksplisit, atau Excel membuat 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 dari database yang sama, Power Pivot dapat mendeteksi hubungan di antara tabel berdasarkan kolom dalam tanda [kurung siku] dan membuat ulang hubungan tersebut 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.

Atas Halaman

Di artikel ini

Kolom dan tombol

Tipe hubungan

Hubungan dan kinerja

Beberapa hubungan di antara tabel

Persyaratan untuk hubungan tabel

Hal yang tidak didukung dalam hubungan tabel

Tombol Komposit dan Kolom Pencarian

Hubungan Banyak ke Banyak

Swa-Gabung dan Pengulangan

Deteksi otomatis dan inferensi hubungan di Power Pivot

Deteksi Otomatis untuk Kumpulan Bernama

Inferensi Hubungan

Kolom dan kunci

Hubungan didasarkan pada kolom dalam setiap tabel yang berisi data yang sama. Misalnya, tabel Customers dan Orders bisa dihubungkan satu sama lain karena keduanya 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 tipe kunci, yang biasanya hanya 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.

Berikut adalah kunci yang paling menarik untuk tujuan kami:

  • Kunci utama: mengidentifikasi baris dalam tabel dengan unik, seperti IDPelanggan dan 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 dalam tabel lain, misalnya CustomerID dalam tabel Orders yang merujuk ke CustomerID dalam tabel Customers.

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. Di dalam contoh kami, hubungan akan ditentukan antara CustomerID dalam tabel Orders (kolom) dan CustomerID dalam tabel Customers (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 Customers dan Orders adalah hubungan satu ke banyak. Setiap pelanggan bisa mempunyai beberapa pesanan, tetapi suatu pesanan tidak bisa mempunyai beberapa pelanggan. Tipe hubungan lainnya adalah satu ke satu dan banyak ke banyak. Tabel CustomerDiscounts, yang menentukan satu tingkat diskon untuk setiap pelanggan, adalah hubungan satu ke satu dengan tabel Customers.

Tabel berikut menunjukkan hubungan di antara ketiga tabel:

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 semua hubungan dibuat, Excel biasanya harus menghitung ulang semua rumus yang menggunakan kolom dari tabel dalam hubungan yang baru dibuat. Pemrosesan bisa memakan waktu, bergantung pada jumlah data dan kompleksitas hubungan. Lihat Menghitung Ulang Rumus untuk detailnya.

Beberapa hubungan di antara tabel

Model Data bisa mempunyai beberapa hubungan antara dua tabel. Untuk membuat perhitungan yang akurat, Excel memerlukan jalur tunggal dari satu tabel ke tabel berikutnya. Oleh karena itu, hanya satu hubungan antara setiap pasang tabel yang aktif pada satu waktu. Yang lain tidak aktif, tetapi Anda bisa menentukan hubungan yang tidak aktif dalam rumus dan kueri. Di Tampilan Diagram hubungan yang aktif merupakan garis penuh dan yang tidak aktif merupakan garis putus-putus. Misalnya, di AdventureWorksDW2012, tabel DimDate berisi kolom, DateKey, yang berhubungan dengan tiga kolom yang 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.

Atas Halaman

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 mengenai tipe data, lihat Tipe Data yang didukung dalam Model Data.

Tidak didukung dalam hubungan tabel

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 secara unik mengidentifikasi setiap baris dalam tabel. Jika Anda mengimpor tabel yang memiliki hubungan yang sudah ada berdasarkan kunci komposit, Panduan Impor Tabel di Power Pivot akan mengabaikan hubungan tersebut karena hubungan tidak dapat dibuat dalam 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 hitung dalam 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 ke 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.

Atas Halaman

Deteksi otomatis dan inferensi hubungan di Power Pivot

Salah satu keunggulan mengimpor data menggunakan add-in Power Pivot adalah Power Pivot terkadang dapat mendeteksi hubungan dan membuat hubungan baru dalam Model Data yang dibuat di Excel.

Bila Anda mengimpor beberapa tabel, Power Pivot akan secara otomatis mendeteksi setiap hubungan yang ada di antara tabel tersebut. Bila 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 ModelData.

  • 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. Sebagai contoh, dalam pengaturan bisnis, Anda sering mempunyai variasi nama kolom yang berisi data yang sama: Emp ID, EmployeeID, Employee ID, EMP_ID,, dan 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.

Atas Halaman

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×