Cara membuat kueri MDX di R menggunakan olapR

Artikel
05/30/2023

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru

OlapR dalam paket SQL Server Machine Learning Services mendukung kueri MDX terhadap kubus yang dihosting di SQL Server Analysis Services. Anda dapat membuat kueri terhadap kubus yang ada, menjelajahi dimensi dan objek kubus lainnya, dan menempelkan kueri MDX yang ada untuk mengambil data.

Artikel ini menjelaskan dua kegunaan utama paket olapR :

Buat kueri MDX dari R, menggunakan konstruktor yang disediakan dalam paket olapR
Menjalankan kueri MDX yang sudah ada dan valid menggunakan olapR dan penyedia OLAP

Operasi berikut tidak didukung:

Kueri DAX terhadap model tabular
Pembuatan objek OLAP baru
Tulis balik ke partisi, termasuk ukuran atau jumlah

Menyusun kueri MDX dari R

Tentukan string koneksi yang menentukan sumber data OLAP (instans SSAS), dan penyedia MSOLAP.
Gunakan fungsi OlapConnection(connectionString) untuk membuat handel untuk kueri MDX dan meneruskan string koneksi.
Query() Gunakan konstruktor untuk membuat instans objek kueri.
Gunakan fungsi pembantu berikut untuk memberikan detail selengkapnya tentang dimensi dan pengukuran yang akan disertakan dalam kueri MDX:
- cube() Tentukan nama database SSAS. Jika menyambungkan ke instans bernama, berikan nama komputer dan nama instans.
- columns() Berikan nama pengukuran yang akan digunakan dalam argumen ON COLUMNS .
- rows() Berikan nama pengukuran yang akan digunakan dalam argumen ON ROWS .
- slicers() Tentukan bidang atau anggota untuk digunakan sebagai pemotong. Pemotong seperti filter yang diterapkan ke semua data kueri MDX.
- axis() Tentukan nama sumbu tambahan yang akan digunakan dalam kueri.
  
  Kubus OLAP dapat berisi hingga 128 sumbu kueri. Umumnya, empat sumbu pertama disebut sebagai Kolom, Baris, Halaman, dan Bab.
  
  Jika kueri Anda relatif sederhana, Anda bisa menggunakan fungsi columns, , rowsdll. untuk membangun kueri Anda. Namun, Anda juga dapat menggunakan axis() fungsi dengan nilai indeks bukan nol untuk membangun kueri MDX dengan banyak kualifikasi, atau untuk menambahkan dimensi tambahan sebagai kualifikasi.
Teruskan gagang, dan kueri MDX yang telah selesai, ke salah satu fungsi berikut, tergantung pada bentuk hasilnya:

executeMD Mengembalikan array multi-dimensi
execute2D Mengembalikan bingkai data dua dimensi (tabular)

Menjalankan kueri MDX yang valid dari R

Tentukan string koneksi yang menentukan sumber data OLAP (instans SSAS), dan penyedia MSOLAP.
Gunakan fungsi OlapConnection(connectionString) untuk membuat handel untuk kueri MDX dan meneruskan string koneksi.
Tentukan variabel R untuk menyimpan teks kueri MDX.
Teruskan handel dan variabel yang berisi kueri MDX ke dalam fungsi executeMD atau execute2D, tergantung pada bentuk hasilnya.
- executeMD Mengembalikan array multi-dimensi
- execute2D Mengembalikan bingkai data dua dimensi (tabular)

Contoh

Contoh berikut didasarkan pada proyek mart data dan kubus AdventureWorks, karena proyek tersebut tersedia secara luas, dalam beberapa versi, termasuk file cadangan yang dapat dengan mudah dipulihkan ke Analysis Services. Jika Anda tidak memiliki kubus yang sudah ada, dapatkan kubus sampel menggunakan salah satu opsi berikut:

Buat kubus yang digunakan dalam contoh ini dengan mengikuti tutorial Analysis Services hingga Pelajaran 4: Membuat kubus OLAP
Unduh kubus yang ada sebagai cadangan, dan pulihkan ke instans Analysis Services. Misalnya, situs ini menyediakan kubus yang sepenuhnya diproses dalam format zip: Adventure Works Multidimensional Model SQL 2014. Ekstrak file, lalu pulihkan ke instans SSAS Anda. Untuk informasi selengkapnya, lihat Pencadangan dan pemulihan, atau Cmdlet Restore-ASDatabase.

1. MDX dasar dengan pemotong

Kueri MDX ini memilih ukuran untuk hitungan dan jumlah jumlah penjualan Internet dan jumlah penjualan, dan menempatkannya di sumbu Kolom. Ini menambahkan anggota dimensi SalesTerritory sebagai pemotong, untuk memfilter kueri sehingga hanya penjualan dari Australia yang digunakan dalam perhitungan.

SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, 
{[Product].[Product Line].[Product Line].MEMBERS} ON ROWS 
FROM [Analysis Services Tutorial] 
WHERE [Sales Territory].[Sales Territory Country].[Australia]

Pada kolom, Anda dapat menentukan beberapa pengukuran sebagai elemen string yang dipisahkan koma.
Sumbu Baris menggunakan semua nilai yang mungkin (semua ANGGOTA) dari dimensi "Baris Produk".
Kueri ini akan mengembalikan tabel dengan tiga kolom, yang berisi ringkasan rollup penjualan Internet dari semua negara/wilayah.
Klausa WHERE menentukan sumbu pemotong. Dalam contoh ini, pemotong menggunakan anggota dimensi SalesTerritory untuk memfilter kueri sehingga hanya penjualan dari Australia yang digunakan dalam perhitungan.

Untuk menyusun kueri ini menggunakan fungsi yang disediakan dalam olapR

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

qry <- Query()
cube(qry) <- "[Analysis Services Tutorial]"
columns(qry) <- c("[Measures].[Internet Sales Count]", "[Measures].[Internet Sales-Sales Amount]")
rows(qry) <- c("[Product].[Product Line].[Product Line].MEMBERS") 
slicers(qry) <- c("[Sales Territory].[Sales Territory Country].[Australia]")

result1 <- executeMD(ocs, qry)

Untuk instans bernama, pastikan untuk menghindari karakter apa pun yang dapat dianggap sebagai karakter kontrol di R. Misalnya, string koneksi berikut mereferensikan instans OLAP01, di server bernama ContosoHQ:

cnnstr <- "Data Source=ContosoHQ\\OLAP01; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"

Untuk menjalankan kueri ini sebagai string MDX yang telah ditentukan sebelumnya

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

mdx <- "SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, {[Product].[Product Line].[Product Line].MEMBERS} ON ROWS FROM [Analysis Services Tutorial] WHERE [Sales Territory].[Sales Territory Country].[Australia]"

result2 <- execute2D(ocs, mdx)

Jika Anda menentukan kueri dengan menggunakan penyusun MDX di SQL Server Management Studio lalu menyimpan string MDX, itu akan memberi nomor sumbu mulai dari 0, seperti yang ditunjukkan di sini:

SELECT {[Measures].[Internet Sales Count], [Measures].[Internet Sales-Sales Amount]} ON AXIS(0), 
   {[Product].[Product Line].[Product Line].MEMBERS} ON AXIS(1) 
   FROM [Analysis Services Tutorial] 
   WHERE [Sales Territory].[Sales Territory Countr,y].[Australia]

Anda masih dapat menjalankan kueri ini sebagai string MDX yang telah ditentukan sebelumnya. Namun, untuk membangun kueri yang sama menggunakan R menggunakan axis() fungsi , Anda harus menamai ulang sumbu mulai dari 1.

2. Jelajahi kubus dan bidangnya pada instans SSAS

Anda dapat menggunakan explore fungsi untuk mengembalikan daftar kubus, dimensi, atau anggota untuk digunakan dalam membuat kueri Anda. Ini berguna jika Anda tidak memiliki akses ke alat penjelajahan OLAP lainnya, atau jika Anda ingin memanipulasi atau membuat kueri MDX secara terprogram.

Untuk mencantumkan kubus yang tersedia pada koneksi yang ditentukan

Untuk melihat semua kubus atau perspektif pada instans yang memiliki izin untuk Anda lihat, berikan handel sebagai argumen untuk explore.

Penting

Hasil akhir bukan kubus; TRUE hanya menunjukkan bahwa operasi metadata berhasil. Kesalahan dilemparkan jika argumen tidak valid.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs)

Hasil
Analysis Services Tutorial
Penjualan Internet
Penjualan Penjual
Ringkasan Penjualan
[1] BENAR

Untuk mendapatkan daftar dimensi kubus

Untuk melihat semua dimensi dalam kubus atau perspektif, tentukan kubus atau nama perspektif.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs \<- OlapConnection(cnnstr)
explore(ocs, "Sales")

Hasil
Pelanggan
Tanggal
Wilayah

Untuk mengembalikan semua anggota dimensi dan hierarki yang ditentukan

Setelah menentukan sumber dan membuat handel, tentukan kubus, dimensi, dan hierarki yang akan dikembalikan. Dalam hasil pengembalian, item yang diawali dengan -> mewakili turunan dari anggota sebelumnya.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs, "Analysis Services Tutorial", "Product", "Product Categories", "Category")

Hasil
Aksesoris
Sepeda
Pakaian
Komponen
-> Komponen Rakitan
-> Komponen Rakitan

Lihat juga

Menggunakan data dari kubus OLAP di R