The Way Of Life: June 2022

Kali ini saya akan berbagi tips untuk membuat query / SQL dengan SQL Server agar data yang kita ambil bisa untuk digunakan dalam membuat Visualisasi Data dengan Google Data Studio.

Contoh yang saya gunakan adalah menggunakan database dari program yang dibuat oleh pihak lain bukan database dari program yang kita buat sendiri, meskipun tips ini juga bisa digunakan untuk database yang kita buat sendiri. Dan tentunya akan lebih mudah karena kita akan sangat paham dengan desain database yang kita buat.

Langkah Pertama yaitu Tentukan data apa saja yang diperlukan.

Contoh : Kita akan membuat visualisasi data untuk grafik nilai pelajaran tiap siswa per kelas dalam 1 tahun ajaran ( Semester 1 dan Semester 2 ) tahun 2021 s/d 2022. Kemudian data apa saja yang perlu kita tampilkan, misal kita memerlukan data nama siswa, nilai, kelas, dan kapan ( tanggal ) nilai tersebut diberikan.

Langkah kedua yaitu Mapping Data.

Pada sebagian besar program atau aplikasi yang menggunakan database akan menggunakan Relational Database, yang akan menggunakan field primary key untuk menghubungkan data dari tabel satu dengan tabel lainnya. Contoh : pada database program untuk system informasi sekolah kita mempunyai table Siswa yang mempunyai field primary key : kodesiswa, dan kodesiswa akan menjadi foreign key pada table lainnya dan biasanya berulang. Seperti gambar di bawah ini:

Primary Key dan Foreign Key

Temukan dan Catat tabel dan field yang diperlukan.

Tips untuk mencari field (primary key) dengan cepat pada database SQL Server anda bisa menggunakan Query / SQL di bawah ini:

SELECT Table_Name, Column_Name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = ‘NamaDatabase’

AND COLUMN_NAME LIKE '%NamaField%'

Jika tabel dalam database menggunakan schema maka anda perlu membuat Query lagi dengan mencari tabel yang sudah anda temukan pada langkah pertama. Seperti dibawah ini:

SELECT SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,

T.NAME AS TABLE_NAME,

T.CREATE_DATE,

T.MODIFY_DATE

FROM SYS.TABLES T

WHERE T.NAME LIKE '%NamaTabel%'

ORDER BY TABLE_NAME;

Setelah selesai menemukan daftar tabel dan field yang anda perlukan adalah:

Langkah ketiga : Membuat Query / SQL

Sebelum anda membuat query / SQL anda perlu mengetahui bagaimana sebuah perintah SQL / Query dijalankan, agar anda dapat membuat query dengan benar dan menghasilkan data yang tepat.

Berikut ini adalah urutan Proses Query :

Mengambil Data (FROM, JOIN)
Filter Data (WHERE)
Grouping Data(GROUP BY)
Group Filter (HAVING)
Memilih data yang akan ditampilkan (SELECT)
Mengurutkan Data & Memilah/Membagi data (ORDER BY & LIMIT )

Dari urutan di atas, maka sebuah query akan mengambil data terlebih dahulu dengan mengeksekusi perintah FROM dan JOIN ( jika ada), kemudian mem-filter data yang diambil dengan perintah WHERE dan seterusnya. Artinya memilih tabel kemudian field yang benar adalah hal penting dan utama dalam membuat query, karena jika salah tabel kemudian salah memilih field yang tentu saja pasti salah, maka data yang dihasilkan hanya merupakan data sampah.

Untuk perintah JOIN, anda juga harus paham dengan berbagai tipe JOIN(INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN), karena akan menghasilkan hasil yang berbeda. Anda bisa mempelajarinya di sini dahulu. Karena jika anda tidak paham, ketika ada kesalahan hasil data, anda akan sulit memperbaiki perintah SQL-nya. Anda bisa membayangkan, proses mengambil data ini seperti menambang "emas" dari tumpukan data.

Pada program atau datatabase yang dibuat oleh pihak lain, meskipun anda diberi akses untuk menjalankan query / SQL biasanya anda tidak diberi akses untuk membuat view atau tabel temporary. Tetapi jika database ini adalah buatan anda sendiri tentu tidak menjadi masalah untuk membuat query yang lebih kompleks. Oleh karena itu yang akan saya bagikan disini adalah membuat query dengan teknik yang sederhana yaitu dengan perintah SELECT, INNER JOIN, WHERE, GROUP BY, CASE. Selain lebih mudah dipahami oleh diri kita sendiri, juga mudah dipahami oleh teman yang lain.

Misal pada Langkah kedua kita sudah mendapatkan tabel dan field yang kita perlukan, misal Tabel : SISWA ( field : KodeSiswa, NamaSiswa ), PELAJARAN( KodePelajaran, NamaPelajaran), TRANSAKSINILAISISWA ( KodeSiswa, , KodePelajaran, Nilai, Tanggal). Gunakan INNER JOIN atau perintah JOIN yang lain untuk menghubungkan antara primary key dan foreign key antar tabel. Untuk ini anda harus paham bagaimana dan dimana aplikasi menyimpan data di tabel dan menggunakan field apa saja. Misal ada field untuk menyimpan status siswa tersebut aktif atau tidak , ini tentu saja harus anda tambahkan dalam query setelah perintah WHERE: misal StatusSiwsa = 1 ( True ).

SELECT NamaSiswa, NamaPelajaran,Nilai, Tanggal

FROM Siswa AA

INNER JOIN TransaksiNilaiSiswa BB on BB.KodeSiswa = AA.KodeSiswa

INNER JOIN Pelajaran CC ON CC.KodePelajaran = BB.KodePelajaran

WHERE BB.Tanggal BETWEEN ‘2021-mm-dd’ AND ‘2022-mm-dd’

Hasil Query di atas akan menghasilkan data seperti di bawah ini , yang bisa digunakan untuk Visualisasi Data pada Goole Data Studio

Hasil Query

Salah satu masalah yang sering muncul pada query / SQL menggunakan INNER JOIN adalah munculnya data double. Tentu saja anda harus memahami cara kerja query dan paham koneksi antar data. Solusinya biasanya anda harus mengurangi pengkoneksian data dengan INNER JOIN yang berulang menggunakan primary key atau foreign key yang sama pada tabel yang berbeda, karena akan memunculkan data yang sama lebih dari satu.

Dan dari sini anda akan mengetahui apakah desain database tersebut dibuat menggunakan teknik normalisasi data atau tidak, karena jika tidak akan timbul banyak redudansi data sehingga andapun akan kesulitan dalam membuat query untuk menghasilkan data yang valid.

Langkah keempat: Cek apakah data hasil dari query sudah valid, untuk langkah ini anda bisa mengecek dengan metode sampling ataupun random cek. Apalagi untuk database yang dibuat oleh pihak lain, anda harus yakin dahulu data yang dihasilkan sudah valid, sehingga anda tidak menarik data sampah dari query yang anda buat. Karena data yang salah akan menghasilkan analisa dan keputusan yang salah.

Pada prakteknya tentu anda harus menggunakan sedikit improvisasi, tetapi menggunakan metode yang sama. Jadi anda harus menguasai inti dari metode ini, sehingga bisa diaplikasikan di berbagai kasus yang berbeda. Di bawah ini saya mencontohkan dengan data yang real sehingga bisa menjadi gambaran yang lebih jelas.