如何在 R 中使用 olapR 建立 MDX 查詢

適用於： SQL Server 2016 (13.x) 和更新版本

SQL Server 機器學習 Services 套件中的 olapR 可讓您對裝載在 SQL Server Analysis Services 中的 Cube 執行 MDX 查詢。 您可以針對現有的 Cube 建置查詢、瀏覽維度和其他 Cube 物件，並貼上現有的 MDX 查詢來取出資料。

本文將說明 olapR 套件的兩個主要用法：

• 使用 olapR 套件中提供的建構函式，從 R 建置 MDX 查詢
• 使用 olapR 和 OLAP 提供者來執行現有的有效 MDX 查詢

以下操作不支援：

• 針對表格式模型進行 DAX 查詢
• 建立新的 OLAP 物件
• 回寫至分割區，包括量值或總和

從 R 建置 MDX 查詢

1. 定義可指定 OLAP 資料來源 (SSAS 執行個體) 和 MSOLAP 提供者的連接字串。

2. 使用 OlapConnection(connectionString) 函式建立 MDX 查詢的控制代碼，並傳遞連接字串。

3. 使用 Query() 建構函式具現化查詢物件。

4. 使用下列 helper 函式，以提供要包含在 MDX 查詢中之維度和量值的更多詳細資訊︰

   • cube()請指定SSAS資料庫名稱。 如果要連線到具名執行個體，請提供電腦名稱和執行個體名稱。

   • columns()：提供在 ON COLUMNS 參數中使用的測度名稱。

   • rows()： 提供在 ON ROWS 參數中使用的測量名稱。

   • slicers()：指定一個欄位或成員作為切片器。 交叉分析篩選器就像套用於所有 MDX 查詢資料上的篩選器。

   • axis()：指定查詢中使用額外軸的名稱。

     OLAP Cube 最多可以包含 128 個查詢座標軸。 前四個軸通常稱為資料行、資料列、頁面和章節。

     如果您的查詢相當簡單，您可以使用 columns、 rows等函式來建置查詢。 不過，您也可以使用索引值非零的 axis() 函式來建置具有許多限定詞的 MDX 查詢，或將額外維度新增為限定詞。

5. 根據結果的結構，將控制代碼和已完成的 MDX 查詢傳入下列其中一個函式：

• executeMD回傳一個多維陣列
• execute2D： 回傳一個二維（表格）資料框架

從 R 執行有效的 MDX 查詢

1. 定義可指定 OLAP 資料來源 (SSAS 執行個體) 和 MSOLAP 提供者的連接字串。

2. 使用 OlapConnection(connectionString) 函式建立 MDX 查詢的控制代碼，並傳遞連接字串。

3. 定義 R 變數來儲存 MDX 查詢的文字。

4. 視結果的形式而定，將控制代碼和包含 MDX 查詢的變數傳遞給函式 executeMD 或 execute2D。

   • executeMD回傳一個多維陣列
   • execute2D： 回傳一個二維（表格）資料框架

範例

下列範例會以 AdventureWorks 資料超市和 Cube 專案為基礎，因為該專案可在多個版本中廣泛使用，包括能夠輕鬆還原到 Analysis Services 的備份檔案。 如果您沒有現有的 Cube，可透過以下任一方式取得範例 Cube：

• 建立這些範例中使用的立方體，請依照 Analysis Services 教學直到第四課：建立 OLAP 立方體。

• 下載現有的 Cube 作為備份，並將它還原到 Analysis Services 的執行個體。 例如，此網站提供一個已完整處理且採用 ZIP 壓縮格式的 Cube：Adventure Works Multidimensional Model SQL Server 2014。 將檔案解壓縮，然後將它還原到您的 SSAS 執行個體。 欲了解更多資訊，請參閱 備份與還原，或 Restore-ASDatabase。

1. 基本 MDX 與切片器

這個 MDX 查詢會選取 量值，即網際網路銷售筆數和銷售金額，並將它們放在資料行座標軸上。 它會將 SalesTerritory 維度的成員新增為交叉篩選器，以篩選查詢，使計算時僅使用來自澳洲的銷售資料。

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]

• 在資料行上，您可以指定多個量值作為以逗號區隔字串的元素。
• [資料列] 軸會使用「產品線」維度的所有可能值（所有成員）。
• 此查詢會傳回內含三個資料行的資料表，其中包含所有國家/地區之網際網路銷售量的彙總摘要。
• WHERE 子句指定 交叉分析篩選軸。 在此範例中，交叉分析篩選器會使用 SalesTerritory 維度成員來篩選查詢，因此，計算中僅會使用來自澳洲的銷售量。

請使用 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)

若為具名執行個體，請務必逸出任何在 R 中可能被視為控制字元的字元。例如，下列連接字串會參考名為 ContosoHQ 之伺服器上的執行個體 OLAP01：

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

將此查詢以預設的 mdx 字串執行

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)

如果你在 SQL Server Management Studio 中使用 MDX 建構器定義查詢，然後儲存 MDX 字串，它會將軸編號為 0，如下範例所示：

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 Country].[Australia]

您還是可以執行這個查詢作為預先定義的 MDX 字串。 不過，若要利用 R 使用 axis() 函式來建置相同查詢，就必須從 1 開始對軸進行編號。

2. 探索 SSAS 執行個體上的 Cube 及其欄位

您可以使用 explore 函式傳回 Cube、維度或成員清單，以用於建立查詢。 如果您無法存取其他 OLAP 瀏覽工具，或您想要以程式設計方式操作或建構 MDX 查詢，則這十分方便使用。

列出指定連接上可用的方塊

若要檢視該執行個體上您具有檢視權限的所有 Cube 或視角，請將控制代碼作為引數提供給 explore。

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

取得立方體尺寸清單

若要檢視多維度資料集或透視中的所有維度，請指定多維度資料集或透視的名稱。

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

返回指定維度與層級的所有成員

定義來源並建立控制代碼之後，請指定要傳回的立方體、維度和階層。 在傳回結果中，具有前置詞 > 的項目代表前一個成員的子系。

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

相關內容

• 在 R 中使用 OLAP Cube 的資料