Manipulasi Data MDX - PILIH

Artikel
05/23/2023

Mengambil data dari kubus tertentu.

Sintaks

  
[ WITH <SELECT WITH clause>   
   [ , <SELECT WITH clause>...n ]   
]   
SELECT   
     [ *   
    | ( <SELECT query axis clause>   
                  [ , <SELECT query axis clause>,...n ]   
            )   
            ]  
FROM   
   <SELECT subcube clause>   
      [ <SELECT slicer axis clause> ]  
      [ <SELECT cell property list clause> ]  
  
<SELECT WITH clause> ::=  
     ( CELL CALCULATION <CREATE CELL CALCULATION body clause> )   
   | ( [ CALCULATED ] MEMBER <CREATE MEMBER body clause>)   
   | ( SET <CREATE SET body clause>)  
   | ( MEASURE = <measure body clause> )  
  
<SELECT query axis clause> ::=  
   [ NON EMPTY ] Set_Expression  
   [ <SELECT dimension property list clause> ]   
      ON   
            Integer_Expression   
       | AXIS(Integer)   
       | COLUMNS   
       | ROWS   
       | PAGES   
       | SECTIONS   
       | CHAPTERS   
  
<SELECT subcube clause> ::=  
      Cube_Name   
   | [NON VISUAL] (SELECT   
                  [ *   
       | ( <SELECT query axis clause> [ ,   
           <SELECT query axis clause>,...n ] )   
         ]   
            FROM   
         <SELECT subcube clause>   
         <SELECT slicer axis clause> )  
  
<SELECT slicer axis clause> ::=   
      WHERE Tuple_Expression  
  
<SELECT cell property list clause> ::=   
   [ CELL ] PROPERTIES CellProperty_Name   
      [ , CellProperty_Name,...n ]  
  
<SELECT dimension property list clause> ::=  
   [DIMENSION] PROPERTIES   
      (DimensionProperty_Name   
         [,DimensionProperty_Name,...n ] )   
    | (LevelProperty_Name   
         [, LevelProperty_Name,...n ] )   
    | (MemberProperty_Name   
         [, MemberProperty_Name,...n ] )

Argumen

Set_Expression
Ekspresi Ekspresi Multidminsional (MDX) yang valid yang mengembalikan satu set.

Bilangan bulat
Bilangan bulat antara 0 dan 127.

Cube_Name
String valid yang menyediakan nama kubus.

Tuple_Expression
Ekspresi Ekspresi Multidmensional (MDX) yang valid yang mengembalikan tuple.

CellProperty_Name
String valid yang mewakili properti sel.

DimensionProperty_Name
String valid yang mewakili properti dimensi.

LevelProperty_Name
String valid yang mewakili properti tingkat.

MemberProperty_Name
String valid yang mewakili properti anggota.

Keterangan

<SELECT slicer axis clause> Ekspresi harus berisi anggota dalam dimensi dan hierarki selain yang direferensikan dalam ekspresi yang ditentukan<SELECT query axis clause>.

Jika atribut dalam kubus dihilangkan dari ekspresi dan <SELECT slicer axis clause> nilai yang ditentukan<SELECT query axis clause>, anggota default atribut secara implisit ditambahkan ke sumbu pemotong.

Opsi NON VISUAL dalam pernyataan subpilih memungkinkan Anda memfilter anggota sambil mempertahankan total yang sebenarnya alih-alih total yang difilter. Ini memungkinkan Anda untuk mengkueri sepuluh penjualan teratas (orang/produk/wilayah) dan mendapatkan total penjualan yang sebenarnya untuk semua anggota yang dikueri, alih-alih nilai total penjualan untuk sepuluh teratas yang dikembalikan. Lihat contoh di bawah ini untuk informasi selengkapnya.

Anggota terhitung dapat disertakan dalam <klausul> sumbu kueri SELECT setiap kali koneksi dibuka menggunakan subkueri parameter string koneksi=1; lihat Properti XMLA (XMLA) yang didukung dan ConnectionString untuk penggunaan parameter. Contoh disediakan pada anggota terhitung dalam subpilih.

Autoexists

Ketika dua atau beberapa atribut dimensi digunakan dalam pernyataan SELECT, Analysis Services mengevaluasi ekspresi atribut untuk memastikan bahwa anggota atribut tersebut dibatasi dengan benar untuk memenuhi kriteria semua atribut lainnya. Misalnya, Anda bekerja dengan atribut dari dimensi Geografi. Jika Anda memiliki satu ekspresi yang mengembalikan semua anggota dari atribut Kota, dan ekspresi lain yang membatasi anggota dari atribut Negara ke semua negara/wilayah di Eropa, maka ini akan mengakibatkan anggota Kota hanya terbatas pada kota-kota yang termasuk dalam negara/wilayah di Eropa. Karakteristik Analysis Services ini disebut Autoexists dan hanya berlaku untuk atribut dalam dimensi yang sama. Autoexists hanya berlaku untuk atribut dari dimensi yang sama karena mencoba mencegah rekaman dimensi yang dikecualikan dalam satu ekspresi atribut disertakan oleh ekspresi atribut lainnya. Autoexists juga dapat dipahami sebagai persimpangan yang dihasilkan dari ekspresi atribut yang berbeda atas rekaman dimensi. Lihat contoh berikut ini:

//Obtain the Top 10 best reseller selling products by Name

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

Kumpulan hasil yang diperoleh adalah:

Nama Model + Ukuran	Jumlah Penjualan Penjual	Jumlah Diskon	Diskon PCT
Gunung-200	$14.356.699,36	$19.012,71	0.13%
Jalan-250	$9.377.457,68	$4.032,47	0.04%
Gunung-100	$8.568.958,27	$139.393,27	1.63%
Jalan-650	$7.442.141,81	$39.698,30	0.53%
Touring-1000	$6.723.794,29	$166.144,17	2.47%
Road-550-W	$3.668.383,88	$1.901,97	0.05%
Road-350-W	$3.665.932,31	$20.946,50	0.57%
Bingkai Gunung HL	$3.365.069,27	$174,11	0.01%
Jalan-150	$2.363.805,16	$0,00	0.00%
Touring-3000	$2.046.508,26	$79.582,15	3.89%

Set produk yang diperoleh tampaknya sama dengan Preferred10Products; jadi, memverifikasi set Preferred10Products:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

Sesuai hasil berikut, kedua set (Top10SellingProducts, Preferred10Products) sama

Nama Model + Ukuran	Jumlah Penjualan Penjual	Jumlah Diskon	Diskon PCT
Gunung-200	$14.356.699,36	$19.012,71	0.13%
Jalan-250	$9.377.457,68	$4.032,47	0.04%
Gunung-100	$8.568.958,27	$139.393,27	1.63%
Jalan-650	$7.442.141,81	$39.698,30	0.53%
Touring-1000	$6.723.794,29	$166.144,17	2.47%
Road-550-W	$3.668.383,88	$1.901,97	0.05%
Road-350-W	$3.665.932,31	$20.946,50	0.57%
Bingkai Gunung HL	$3.365.069,27	$174,11	0.01%
Jalan-150	$2.363.805,16	$0,00	0.00%
Touring-3000	$2.046.508,26	$79.582,15	3.89%

Dalam contoh sebelumnya kita membuat dua set: satu sebagai ekspresi terhitung dan yang lainnya sebagai ekspresi konstanta. Contoh-contoh ini menggambarkan berbagai rasa Autoexists.

Autoexists dapat diterapkan secara mendalam atau dangkal ke ekspresi. Pengaturan default dalam. Contoh berikut akan mengilustrasikan konsep Autoexists yang mendalam. Dalam contoh, kami memfilter Top10SellingProducts oleh [Product]. Atribut [Product Line] untuk mereka yang berada di grup [Mountain]. Perhatikan bahwa kedua atribut (pemotong dan sumbu) milik dimensi yang sama, [Produk].

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

// Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

Menghasilkan tataan hasil berikut:

Nama Model + Ukuran	Jumlah Penjualan Penjual	Jumlah Diskon	Diskon PCT
Gunung-200	$14.356.699,36	$19.012,71	0.13%
Gunung-100	$8.568.958,27	$139.393,27	1.63%
Bingkai Gunung HL	$3.365.069,27	$174,11	0.01%
Gunung-300	$1.907.249,38	$876,95	0.05%
Gunung-500	$1.067.327,31	$17.266,09	1.62%
Mountain-400-W	$592.450,05	$303,49	0.05%
Bingkai Gunung LL	$521.864,42	$252,41	0.05%
ML Mountain Frame-W	$482.953,16	$206,95	0.04%
Bingkai Gunung ML	$343.785,29	$161,82	0.05%
Celana Pendek Gunung Wanita	$260,304,09	$6.675,56	2.56%

Dalam tataan hasil sebelumnya, kami memiliki tujuh pendatang baru ke daftar Top10SellingProducts dan Mountain-200, Mountain-100 dan HL Mountain Frame telah pindah ke bagian atas daftar. Dalam tataan hasil sebelumnya, ketiga nilai tersebut diselingi

Ini disebut Deep Autoexists, karena set Top10SellingProducts dievaluasi untuk memenuhi kondisi pemotongan kueri. Deep Autoexists berarti bahwa semua ekspresi akan dievaluasi untuk memenuhi ruang sedalam mungkin setelah menerapkan ekspresi pemotong, ekspresi sub-pilih di sumbu, dan sebagainya.

Namun, seseorang mungkin ingin dapat melakukan analisis melalui Top10SellingProducts setara dengan Preferred10Products, seperti dalam contoh berikut:

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

set Preferred10Products as '

{[Product].[Model Name].&[Mountain-200],

[Product].[Model Name].&[Road-250],

[Product].[Model Name].&[Mountain-100],

[Product].[Model Name].&[Road-650],

[Product].[Model Name].&[Touring-1000],

[Product].[Model Name].&[Road-550-W],

[Product].[Model Name].&[Road-350-W],

[Product].[Model Name].&[HL Mountain Frame],

[Product].[Model Name].&[Road-150],

[Product].[Model Name].&[Touring-3000]

}'

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Preferred10Products on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

Menghasilkan tataan hasil berikut:

Nama Model + Ukuran	Jumlah Penjualan Penjual	Jumlah Diskon	Diskon PCT
Gunung-200	$14.356.699,36	$19.012,71	0.13%
Gunung-100	$8.568.958,27	$139.393,27	1.63%
Bingkai Gunung HL	$3.365.069,27	$174,11	0.01%

Dalam hasil di atas, pemotongan memberikan hasil yang hanya berisi produk-produk tersebut dari Preferred10Products yang merupakan bagian dari grup [Mountain] di [Product]. [Product Line]; seperti yang diharapkan, karena Preferred10Products adalah ekspresi konstanta.

Tataan hasil ini juga dipahami sebagai Autoexists dangkal. Ini karena ekspresi dievaluasi sebelum klausa pemotongan. Dalam contoh sebelumnya, ekspresi adalah ekspresi konstan untuk tujuan ilustrasi untuk memperkenalkan konsep.

Perilaku autoexists dapat dimodifikasi pada tingkat sesi menggunakan properti string koneksi Autoexists . Contoh berikut dimulai dengan membuka sesi baru dan menambahkan properti Autoexists=3 ke string koneksi. Anda harus membuka koneksi baru untuk melakukan contoh. Setelah koneksi dibuat dengan pengaturan Autoexist, koneksi tersebut akan tetap berlaku hingga koneksi selesai.

with member [Measures].[PCT Discount] AS '[Measures].[Discount Amount]/[Measures].[Reseller Sales Amount]', FORMAT_STRING = 'Percent'

set Top10SellingProducts as 'topcount([Product].[Model Name].children, 10, [Measures].[Reseller Sales Amount])'

//Preferred10Products set removed for clarity

select {[Measures].[Reseller Sales Amount], [Measures].[Discount Amount], [Measures].[PCT Discount]} on 0,

Top10SellingProducts on 1

from [Adventure Works]

where [Product].[Product Line].[Mountain]

Tataan hasil berikut sekarang menunjukkan perilaku autoexists yang dangkal.

Nama Model + Ukuran	Jumlah Penjualan Penjual	Jumlah Diskon	Diskon PCT
Gunung-200	$14.356.699,36	$19.012,71	0.13%
Gunung-100	$8.568.958,27	$139.393,27	1.63%
Bingkai Gunung HL	$3.365.069,27	$174,11	0.01%

Perilaku autoexists dapat dimodifikasi dengan menggunakan parameter AUTOEXISTS=[1|2|3] dalam string koneksi; lihat Properti XMLA yang didukung (XMLA) dan ConnectionString untuk penggunaan parameter.

Contoh

Contoh berikut mengembalikan jumlah Measures.[Order Quantity] anggota, yang dikumpulkan selama delapan bulan pertama tahun kalender 2003 yang terkandung dalam Date dimensi, dari kubus Adventure Works .

WITH MEMBER [Date].[Calendar].[First8Months2003] AS  
    Aggregate(  
        PeriodsToDate(  
            [Date].[Calendar].[Calendar Year],   
            [Date].[Calendar].[Month].[August 2003]  
        )  
    )  
SELECT   
    [Date].[Calendar].[First8Months2003] ON COLUMNS,  
    [Product].[Category].Children ON ROWS  
FROM  
    [Adventure Works]  
WHERE  
    [Measures].[Order Quantity]

Untuk memahami NON VISUAL, contoh berikut adalah kueri [Adventure Works] untuk mendapatkan angka [Jumlah Penjualan Penjual] dalam tabel di mana kategori produk adalah kolom dan jenis bisnis penjual adalah baris. Perhatikan bahwa total diberikan untuk produk dan penjual.

Pernyataan SELECT berikut:

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

Menghasilkan hasil berikut:

Jenis Bisnis + Kategori	Lihat semua produk	Aksesori	Sepeda	Pakaian	Komponen
Semua Reseller	$80.450.596,98	$571.297,93	$66.302.381,56	$1.777.840,84	$11.799.076,66
Toko Sepeda Khusus	$6.756.166,18	$65.125,48	$6.080.117,73	$252.933,91	$357.989,07
Reseller Nilai Ditambahkan	$34.967.517,33	$175.002,81	$30.892.354,33	$592.385,71	$3.307.774,48
Gudang	$38.726.913,48	$331.169,64	$29.329.909,50	$932.521,23	$8.133.313,11

Untuk menghasilkan tabel dengan data hanya untuk produkAccessories dan Pakaian, Penjual Nilai Tambah dan pengecer Gudang, namun mempertahankan total keseluruhan dapat ditulis sebagai berikut menggunakan NON VISUAL:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,

{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1

from [Adventure Works])

where [Measures].[Reseller Sales Amount]

Menghasilkan hasil berikut:

Jenis Bisnis + Kategori	Lihat semua produk	Aksesori	Pakaian
Semua Reseller	$80.450.596,98	$571.297,93	$1.777.840,84
Reseller Nilai Ditambahkan	$34.967.517,33	$175.002,81	$592.385,71
Gudang	$38.726.913,48	$331.169,64	$932.521,23

Untuk menghasilkan tabel yang secara visual menjumlahkan kolom tetapi untuk total baris menghasilkan total yang sebenarnya dari semua [Kategori], kueri berikut harus dikeluarkan:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0

from ( Select {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 0

from [Adventure Works])

)

where [Measures].[Reseller Sales Amount]

Perhatikan bagaimana NON VISUAL hanya diterapkan ke [Kategori].

Kueri di atas menghasilkan hasil berikut:

Jenis Bisnis + Kategori	Lihat semua produk	Aksesori	Pakaian
Semua Reseller	$73.694.430,80	$506.172,45	$1.524.906,93
Pengecer Nilai Tambah	$34.967.517,33	$175.002,81	$592.385,71
Gudang	$38.726.913,48	$331.169,64	$932.521,23

Jika dibandingkan dengan hasil sebelumnya, Anda dapat mengamati bahwa baris [Semua Penjual] sekarang menambahkan hingga nilai yang ditampilkan untuk [Value Added Reseller] dan [Warehouse] tetapi kolom [Semua Produk] menunjukkan nilai total untuk semua produk, termasuk yang tidak ditampilkan.

Contoh berikut menunjukkan cara menggunakan anggota terhitung dalam subpilih untuk memfilternya. Untuk dapat mereprodusi sampel ini, koneksi harus dibuat menggunakan subkueri parameter string koneksi=1.

select Measures.allmembers on 0

from (

Select { [Measures].[Reseller Sales Amount]

, [Measures].[Reseller Total Product Cost]

, [Measures].[Reseller Gross Profit]

, [Measures].[Reseller Gross Profit Margin]

} on 0

from [Adventure Works]

)

Kueri di atas menghasilkan hasil berikut:

Jumlah Penjualan Penjual	Total Biaya Produk Penjual	Laba Kotor Penjual	Margin Laba Kotor Penjual
$80.450.596,98	$79.980.114,38	$470,482,60	0,58%

Lihat Juga

Konsep Utama dalam MDX (Analysis Services)
Pernyataan Manipulasi Data MDX (MDX)
Membatasi Kueri dengan Kueri dan Sumbu Pemotong (MDX)

Bagikan melalui

Manipulasi Data MDX - PILIH

Sintaks

Argumen

Keterangan

Autoexists

Contoh

Lihat Juga

Saran dan Komentar

Sumber Daya Tambahan: