BI perusahaan otomatis

Contoh ini adalah tentang cara melakukan pemuatan bertahap dalam alur ekstrak, muat, dan transformasi (ELT ). Ini menggunakan Azure Data Factory untuk mengotomatiskan alur ELT. Alur secara bertahap memindahkan data OLTP terbaru dari database SQL Server lokal ke Azure Synapse. Data transaksional diubah menjadi model tabular untuk analisis.

Sistem

Unduh file Visio arsitektur ini.

Arsitektur ini dibangun berdasarkan arsitektur yang ditampilkan di Enterprise BI dengan Azure Synapse, namun menambahkan beberapa fitur yang penting untuk skenario pergudangan data perusahaan.

• Otomatisasi alur menggunakan Data Factory.
• Pemuatan secara bertahap.
• Mengintegrasikan beberapa sumber data.
• Memuat data biner seperti data geospasial dan gambar.

Alur kerja

Arsitektur terdiri dari layanan dan komponen berikut.

Sumber data

SQL Server lokal. Data sumber terletak di database SQL Server lokal. Untuk mensimulasikan lingkungan lokal. Database sampel OLTP Importir Seluruh Dunia digunakan sebagai database sumber.

Data eksternal. Skenario umum untuk gudang data adalah mengintegrasikan beberapa sumber data. Arsitektur referensi ini memuat himpunan data eksternal yang berisi populasi kota menurut tahun, dan mengintegrasikannya dengan data dari database OLTP. Anda dapat menggunakan data ini untuk wawasan seperti: "Apakah pertumbuhan penjualan di setiap wilayah cocok atau melebihi pertumbuhan populasi?"

Penyerapan dan penyimpanan data

Blob Storage. Penyimpanan blob digunakan sebagai area penahapan untuk data sumber sebelum memuatnya ke Azure Synapse.

Azure Synapse. Azure Synapse adalah sistem terdistribusi yang dirancang untuk melakukan analitik pada data besar. Ini mendukung pemrosesan paralel besar-besaran (MPP), yang membuatnya cocok untuk menjalankan analitik berperforma tinggi.

Azure Data Factory. Data Factory adalah layanan terkelola yang mengatur dan mengotomatiskan pergerakan data dan transformasi data. Dalam arsitektur ini, layanan mengkoordinasikan berbagai tahapan proses ELT.

Analisis dan pelaporan

Azure Analysis Services. Analysis Services adalah layanan terkelola sepenuhnya yang menyediakan kemampuan pemodelan data. Model semantik dimuat ke dalam Analysis Services.

Power BI. Power BI adalah seperangkat alat analisis bisnis untuk menganalisis data sebagai wawasan bisnis. Dalam arsitektur ini, ia mengkueri model semantik yang disimpan di Analysis Services.

Autentikasi

MICROSOFT Entra ID mengautentikasi pengguna yang tersambung ke server Analysis Services melalui Power BI.

Data Factory juga dapat menggunakan ID Microsoft Entra untuk mengautentikasi ke Azure Synapse, dengan menggunakan perwakilan layanan atau Identitas Layanan Terkelola (MSI).

Komponen

• Penyimpanan Blob Azure
• Azure Synapse Analytics
• Azure Data Factory
• Azure Analysis Services
• Power BI
• Microsoft Entra ID

Detail skenario

Pipa data

Di Azure Data Factory, alur adalah pengelompokan logis dari aktivitas yang digunakan untuk mengoordinasikan tugas — dalam hal ini, memuat dan mengubah data ke Azure Synapse.

Arsitektur referensi ini mendefinisikan alur induk yang menjalankan urutan alur anak. Setiap alur anak memuat data ke dalam satu atau beberapa tabel gudang data.

Rekomendasi

Pemuatan secara bertahap

Saat Anda menjalankan proses ETL atau ELT otomatis, yang paling efisien adalah memuat hanya data yang berubah sejak proses sebelumnya. Ini disebut beban bertambah bertahap, berbeda dengan beban penuh yang memuat semua data. Untuk melakukan beban bertambah bertahap, Anda memerlukan cara untuk mengidentifikasi data mana yang telah berubah. Pendekatan yang paling umum adalah menggunakan nilai tanda air tinggi, yang berarti melacak nilai terbaru dari beberapa kolom di tabel sumber, baik kolom tanggal waktu atau kolom bilangan bulat unik.

Dimulai dengan SQL Server 2016, Anda dapat menggunakan tabel temporal. Ini adalah tabel versi sistem yang menyimpan riwayat lengkap perubahan data. Mesin database secara otomatis merekam riwayat setiap perubahan dalam tabel riwayat terpisah. Anda dapat mengkueri data historis dengan menambahkan klausul FOR SYSTEM_TIME ke kueri. Secara internal, mesin database melakukan kueri tabel riwayat, tetapi ini transparan untuk aplikasi.

Tabel temporal berguna untuk data dimensi, yang dapat berubah seiring waktu. Tabel fakta biasanya mewakili transaksi yang tidak dapat diubah seperti penjualan, dalam hal ini menjaga riwayat versi sistem adalah hal yang tidak masuk akal. Sebaliknya, transaksi biasanya memiliki kolom yang mewakili tanggal transaksi, yang dapat digunakan sebagai nilai marka air. Misalnya, dalam database OLTP Importir Dunia Luas, tabel Sales.Invoices dan Sales.InvoiceLines memiliki bidang LastEditedWhen yang defaultnya adalah sysdatetime().

Berikut adalah alur umum untuk alur ELT:

1. Untuk setiap tabel di database sumber, lacak waktu cutoff saat pekerjaan ELT terakhir dijalankan. Simpan informasi ini di gudang data. (Pada pengaturan awal, semua waktu diatur ke '1-1-1900'.)

2. Selama langkah ekspor data, waktu cutoff dilewatkan sebagai parameter ke set prosedur tersimpan di database sumber. Prosedur tersimpan ini meminta rekaman apa pun yang diubah atau dibuat setelah waktu cutoff. Untuk tabel fakta Penjualan, kolom LastEditedWhen digunakan. Untuk data dimensi, tabel temporal versi sistem digunakan.

3. Saat migrasi data selesai, perbarui tabel yang menyimpan waktu cutoff.

Ini juga berguna untuk merekam silsilah data untuk setiap proses ELT. Untuk rekaman tertentu, silsilah data mengaitkan rekaman tersebut dengan proses ELT yang menghasilkan data. Untuk setiap proses ETL, rekaman eksekusi alur baru dibuat untuk setiap tabel, yang menunjukkan waktu muat mulai dan berakhir. Kunci silsilah data untuk setiap rekaman disimpan dalam tabel dimensi dan fakta.

Setelah kumpulan data baru dimuat ke dalam gudang, refresh model tabel Analysis Services. Lihat Refresh asinkron dengan REST API.

Pembersihan data

Pembersihan data harus menjadi bagian dari proses ELT. Dalam arsitektur referensi ini, salah satu sumber data buruk adalah tabel populasi kota, di mana beberapa kota memiliki populasi nol, mungkin karena tidak ada data yang tersedia. Selama pemrosesan, alur ELT menghapus kota-kota tersebut dari tabel populasi kota. Lakukan pembersihan data pada tabel penahapan, bukan tabel eksternal.

Sumber data eksternal

Gudang data sering mengonsolidasikan data dari berbagai sumber. Misalnya, sumber data eksternal yang berisi data demografis. Himpunan data ini tersedia di penyimpanan blob Azure sebagai bagian dari sampel WorldWideImportersDW.

Azure Data Factory dapat menyalin langsung dari penyimpanan blob, menggunakan konektor penyimpanan blob. Namun, konektor memerlukan string koneksi atau tanda tangan akses bersama, sehingga tidak dapat digunakan untuk menyalin blob dengan akses baca publik. Sebagai solusinya, Anda bisa menggunakan PolyBase untuk membuat tabel eksternal melalui penyimpanan Blob lalu menyalin tabel eksternal ke Azure Synapse.

Menangani data biner besar

Misalnya, dalam database sumber, tabel Kota memiliki kolom Lokasi yang menyimpan jenis data spasial geografi . Azure Synapse tidak mendukung jenis geografi secara asli, jadi bidang ini dikonversi ke jenis varbinary selama pemuatan. (Lihat Solusi untuk jenis data yang tidak didukung.)

Namun, PolyBase mendukung ukuran kolom maksimum varbinary(8000), yang berarti beberapa data dapat terpotong. Solusi untuk masalah ini adalah memecah data menjadi beberapa bagian selama ekspor, lalu merakit kembali bagian tersebut, sebagai berikut:

1. Buat tabel penahapan sementara untuk kolom Lokasi.

2. Untuk setiap kota, bagi data lokasi menjadi potongan 8000-byte, menghasilkan 1 – N baris untuk setiap kota.

3. Untuk memasang kembali gugus, gunakan operator PIVOT T-SQL untuk mengonversi baris menjadi kolom, lalu menggabungkan nilai kolom untuk setiap kota.

Tantangannya adalah setiap kota akan dibagi menjadi beberapa baris yang berbeda, tergantung ukuran data geografi. Agar operator PIVOT berfungsi, setiap kota harus memiliki jumlah baris yang sama. Untuk membuat ini berfungsi, kueri T-SQL melakukan beberapa trik untuk mengalihkan baris dengan nilai kosong, sehingga setiap kota memiliki jumlah kolom yang sama setelah pivot. Kueri yang dihasilkan ternyata jauh lebih cepat daripada mengulang baris satu per satu.

Pendekatan yang sama digunakan untuk data gambar.

Dimensi yang berubah secara perlahan

Data dimensi relatif statis, tetapi dapat berubah. Misalnya, suatu produk mungkin akan dipindahkan ke kategori produk yang berbeda. Ada beberapa pendekatan untuk menangani dimensi yang berubah secara perlahan. Teknik umum, yang disebut Jenis 2, adalah menambahkan rekaman baru setiap kali dimensi berubah.

Untuk menerapkan pendekatan Jenis 2, tabel dimensi memerlukan kolom tambahan yang menentukan rentang tanggal efektif untuk catatan tertentu. Selain itu, kunci primer dari database sumber akan diduplikasi, sehingga tabel dimensi harus memiliki kunci primer buatan.

Misalnya, gambar berikut menunjukkan tabel Dimension.City. Kolom WWI City ID adalah kunci primer dari database sumber. Kolom City Key adalah kunci buatan yang dihasilkan selama alur ETL. Perhatikan juga bahwa tabel memiliki kolom Valid From dan Valid To, yang menentukan rentang ketika setiap baris valid. Nilai saat ini memiliki Valid To sama dengan '9999-12-31'.

Keuntungan dari pendekatan ini adalah mempertahankan data historis, yang dapat berharga untuk analisis. Namun, itu juga berarti akan ada beberapa baris untuk entitas yang sama. Misalnya, berikut adalah rekaman yang cocok dengan WWI City ID = 28561:

Untuk setiap fakta Penjualan, Anda ingin mengaitkan fakta tersebut dengan satu baris di tabel dimensi Kota, yang sesuai dengan tanggal faktur.

Pertimbangan

Pertimbangan ini mengimplementasikan pilar Azure Well-Architected Framework, yang merupakan serangkaian tenet panduan yang dapat digunakan untuk meningkatkan kualitas beban kerja. Untuk informasi selengkapnya, lihat Microsoft Azure Well-Architected Framework.

Keamanan

Keamanan memberikan jaminan terhadap serangan yang disukai dan penyalahgunaan data dan sistem berharga Anda. Untuk informasi selengkapnya, lihat Gambaran Umum pilar keamanan.

Untuk keamanan tambahan, Anda dapat menggunakan titik akhir layanan Virtual Network untuk mengamankan sumber daya layanan Azure hanya ke jaringan virtual Anda. Ini sepenuhnya menghapus akses Internet publik ke sumber daya tersebut, yang memungkinkan lalu lintas hanya dari jaringan virtual Anda.

Dengan pendekatan ini, Anda membuat VNet di Azure dan kemudian membuat titik akhir layanan privat untuk layanan Azure. Layanan tersebut kemudian dibatasi untuk lalu lintas dari jaringan virtual tersebut. Anda juga dapat menjangkaunya dari jaringan lokal Anda melalui gateway.

Ketahui batasan berikut:

• Jika titik akhir layanan diaktifkan untuk Azure Storage, PolyBase tidak dapat menyalin data dari Storage ke Azure Synapse. Ada mitigasi untuk masalah ini. Untuk informasi selengkapnya, lihat Dampak penggunaan Titik Akhir Layanan VNet dengan penyimpanan Azure.

• Untuk memindahkan data dari lokal ke Azure Storage, Anda harus mengizinkan alamat IP publik dari lokal atau ExpressRoute Anda. Untuk mengetahui detailnya, lihat Mengamankan layanan Azure ke jaringan virtual.

• Untuk mengaktifkan Analysis Services agar membaca data dari Azure Synapse, sebarkan VM Windows ke jaringan virtual yang berisi titik akhir layanan Azure Synapse. Instal Gateway Data Lokal Azure di VM ini. Kemudian sambungkan layanan Azure Analysis Anda ke gateway data.

DevOps

• Buat grup sumber daya terpisah untuk lingkungan produksi, pengembangan, dan pengujian. Grup sumber daya yang terpisah akan mempermudah pengelolaan penyebaran, penghapusan penyebaran pengujian, dan penetapan hak akses.

• Tempatkan setiap beban kerja dalam template penyebaran terpisah dan simpan sumber daya dalam sistem kontrol sumber. Anda dapat menerapkan template secara bersama-sama atau sendiri-sendiri sebagai bagian dari proses CI/CD, sehingga proses otomatisasi menjadi lebih mudah.

  Dalam arsitektur ini, ada tiga beban kerja utama:

  • Server gudang data, Analysis Services, dan sumber daya terkait.
  • Azure Data Factory.
  • Skenario simulasi lokal ke cloud.

  Setiap beban kerja memiliki template penyebarannya masing-masing.

  Server gudang data disiapkan dan dikonfigurasi menggunakan perintah Azure CLI yang mengikuti pendekatan imperatif dari praktik IaC. Pertimbangkan untuk menggunakan skrip penyebaran dan integrasikan dalam proses otomatisasi.

• Pertimbangkan untuk melakukan pentahapan beban kerja. Sebarkan ke berbagai tahap dan jalankan pemeriksaan validasi di setiap tahap sebelum melanjutkan ke tahap berikutnya. Dengan begitu, Anda dapat menerapkan pembaruan ke lingkungan produksi dengan cara yang sangat terkontrol dan meminimalkan masalah penyebaran yang tidak terduga. Gunakan strategi Penyebaran biru-hijau dan rilis Canary untuk memperbarui lingkungan produksi secara langsung.

  Miliki strategi putar kembali yang baik untuk menangani penyebaran yang gagal. Misalnya, Anda dapat secara otomatis menerapkan ulang penyebaran sebelumnya yang berhasil dari riwayat penyebaran Anda. Lihat parameter flag --rollback-on-error di Azure CLI.

• Azure Monitor adalah opsi yang disarankan untuk menganalisis performa gudang data Anda serta keseluruhan platform analitik Azure untuk pengalaman pemantauan yang terintegrasi. Azure Synapse Analytics menyediakan pengalaman pemantauan dalam portal Microsoft Azure untuk menampilkan wawasan ke beban kerja gudang data Anda. Portal Microsoft Azure adalah alat yang direkomendasikan saat memantau gudang data Anda karena menyediakan periode retensi yang dapat dikonfigurasi, peringatan, rekomendasi, dan bagan serta dasbor yang dapat disesuaikan untuk metrik dan log.

Untuk informasi selengkapnya, lihat bagian DevOps di Microsoft Azure Well-Architected Framework.

Pengoptimalan biaya

Optimalisasi biaya adalah tentang mencari cara untuk mengurangi pengeluaran yang tidak perlu dan meningkatkan efisiensi operasional. Untuk informasi selengkapnya, lihat Gambaran umum pilar pengoptimalan biaya.

Gunakan kalkulator harga Azure untuk memperkirakan biaya. Berikut adalah beberapa pertimbangan untuk layanan yang digunakan dalam arsitektur referensi ini.

Azure Data Factory

Azure Data Factory mengotomatiskan alur ELT. Alur memindahkan data dari database SQL Server lokal ke Azure Synapse. Data kemudian diubah menjadi model tabular untuk analisis. Untuk skenario ini, penetapan harga mulai dari $0,001 aktivitas berjalan per bulan yang mencakup aktivitas, pemicu, dan proses debug. Harga itu adalah biaya dasar hanya untuk orkestrasi. Anda juga dikenakan biaya untuk aktivitas eksekusi, seperti menyalin data, pencarian, dan aktivitas eksternal. Setiap aktivitas diberi harga secara individual. Anda juga dikenai biaya untuk alur tanpa pemicu terkait atau berjalan dalam bulan tersebut. Semua kegiatan disebarkan per menit dan dibulatkan.

Contoh analisis biaya

Pertimbangkan kasus penggunaan di mana ada dua kegiatan pencarian dari dua sumber yang berbeda. Satu membutuhkan waktu 1 menit dan 2 detik (dibulatkan hingga 2 menit) dan yang lainnya membutuhkan waktu 1 menit sehingga total waktu 3 menit. Satu aktivitas salinan data membutuhkan waktu 10 menit. Satu aktivitas prosedur yang disimpan membutuhkan waktu 2 menit. Total aktivitas berjalan selama 4 menit. Biaya dihitung sebagai berikut:

Aktivitas berjalan: 4 * $0,001 = $0,004

Pencarian: 3 * ($0,005 / 60) = $0,00025

Prosedur tersimpan: 2 * ($0,00025 / 60) = $0,000008

Salinan data: 10 * ($0,25 / 60) * 4 unit integrasi data (DIU) = $0,167

• Total biaya per alur berjalan: $0,17.
• Jalankan sekali per hari selama 30 hari: $5,1 bulan.
• Jalankan sekali per hari per 100 tabel selama 30 hari: $510

Setiap aktivitas memiliki biaya yang terkait. Pahami model penetapan harga dan gunakan kalkulator harga ADF untuk mendapatkan solusi yang dioptimalkan tidak hanya untuk performa tetapi juga untuk biaya. Kelola biaya Anda dengan memulai, menghentikan, menjeda, dan menskalakan layanan Anda.

Azure Synapse

Azure Synapse sangat ideal untuk beban kerja intensif dengan performa kueri yang lebih tinggi dan kebutuhan skalabilitas komputasi. Anda dapat memilih model bayar sesuai pemakaian atau menggunakan paket yang dipesan untuk satu tahun (hemat 37%) atau 3 tahun (hemat 65%).

Penyimpanan data dikenakan biaya secara terpisah. Layanan lain seperti pemulihan bencana dan deteksi ancaman juga dikenakan biaya secara terpisah.

Untuk informasi selengkapnya, baca Harga Azure Synapse.

Analysis Services

Harga untuk Azure Analysis Services tergantung tingkatannya. Penerapan referensi arsitektur ini menggunakan tingkat Pengembang, yang direkomendasikan untuk skenario evaluasi, pengembangan, dan pengujian. Tingkatan lainnya termasuk, tingkat Dasar yang direkomendasikan untuk lingkungan produksi kecil; tingkat Standar untuk aplikasi produksi yang sangat penting. Untuk informasi selengkapnya, baca Tingkat yang tepat saat Anda membutuhkannya.

Tidak ada biaya yang dikenakan saat Anda menjeda instans.

Untuk informasi selengkapnya, lihat Harga Azure Analysis Services.

Penyimpanan Blob

Pertimbangkan untuk menggunakan fitur kapasitas penyimpanan Azure Storage untuk menurunkan biaya penyimpanan. Dengan model ini, Anda mendapatkan diskon jika Anda dapat berkomitmen untuk reservasi kapasitas penyimpanan tetap selama satu atau tiga tahun. Untuk informasi selengkapnya, lihat Mengoptimalkan biaya untuk penyimpanan Blob dengan kapasitas terpesan.

Untuk informasi selengkapnya, lihat bagian Biaya di Microsoft Azure Well-Architected Framework.

Langkah berikutnya

• Pengantar Azure Synapse Analytics
• Mulai menggunakan Azure Synapse Analytics
• Pengenalan Azure Data Factory
• Apa itu Azure Data Factory?
• Tutorial Azure Data Factory

Sumber daya terkait

Anda mungkin ingin meninjau skenario contoh Azure berikut yang menunjukkan solusi spesifik menggunakan beberapa teknologi yang sama:

• Pergudangan data dan analitik untuk penjualan dan pemasaran
• Enterprise BI di Azure dengan Azure Synapse.