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

  • 發行項

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

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

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

不支援下列作業：

  • 針對表格式模型進行 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：

  • 若要建立可用於這些範例的 Cube，請遵循 Analysis Services 教學課程，直到第 4 課：建立 OLAP Cube \(部分機器翻譯\)

  • 下載現有的 Cube 作為備份，並將它還原到 Analysis Services 的執行個體。 例如，此網站會以壓縮格式提供完整處理的 Cube：Adventure Works 多維度模型 SQL 2014。 將檔案解壓縮，然後將它還原到您的 SSAS 執行個體。 如需詳細資訊，請參閱備份與還原 \(部分機器翻譯\) 或 Restore-ASDatabase Cmdlet。

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]
  • 在資料行上，您可以指定多個量值作為以逗號區隔字串的元素。
  • [資料列] 座標軸會使用「產品線」維度的所有可能值 (所有 MEMBERS)。
  • 此查詢會傳回內含三個資料行的資料表，其中包含所有國家/地區之網際網路銷售量的彙總摘要。
  • 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 Countr,y].[Australia]

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

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

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

列出所指定連線上的可用 Cube

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

重要

最終結果不是 Cube；TRUE 僅表示中繼資料作業成功。 如果引數無效，則會擲回錯誤。

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs)
結果
Analysis Services 教學課程
網際網路銷售
轉售商銷售
銷售摘要
[1] TRUE

取得 Cube 維度清單

若要檢視 Cube 或檢視方塊中的所有維度，請指定 Cube 或檢視方塊名稱。

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs \<- OlapConnection(cnnstr)
explore(ocs, "Sales")
結果
客戶
日期
區域

傳回所指定維度和階層的所有成員

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

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs, "Analysis Services Tutorial", "Product", "Product Categories", "Category")
結果
Accessories
Bikes
Clothing
Components
-> 組件元件
-> 組件元件

另請參閱

在 R 中使用 OLAP Cube 的資料