28 撰寫 MDX 查詢

MDX 是一種類似 SQL 的語言，可用來發出從 Essbase 擷取資料的查詢。MDX 也用於定義 ASO 立方體上的公式、查詢中繼資料、限定成員名稱，以及分隔資料或中繼資料的子集。學習 MDX 的最佳方式是撰寫查詢。

本節透過一系列練習針對 Sample Basic 立方體撰寫查詢，協助您瞭解 MDX。

撰寫 MDX 查詢的先決條件

要完成練習，您需要：

• 文字編輯器，寫入 MDX 查詢。

• 使用 Sample Basic 存取 Essbase 例項。

  如果您需要取得「基本範例」，請依照建立範例立方體以瀏覽大綱特性中的步驟進行 (只要執行匯入，然後略過設定大綱特性)。

• MaxL 用戶端：向 Essbase 發出查詢。

建立 MDX 查詢樣板

瞭解 MDX 查詢的基本格式，以便開始使用含有 Essbase 的 MDX。與 SQL 陳述式類似，MDX 查詢通常以 SELECT 開頭。

在本節中，您將建立一個範本，作為開發簡單 MDX 查詢的基礎。

大部份的查詢都可以建立在以下文法架構上 ：

SELECT
  {}
ON COLUMNS
FROM Sample.Basic

第 1 行中的 SELECT 是開始 MDX 陳述式主體的關鍵字。

第 2 行的大括號 { } 是集合的預留位置。在上述查詢中，集合是空的，但大括號仍是預留位置。

練習 1：建立 MDX 查詢範本

若要建立查詢範本：

1. 建立資料夾以儲存可在 Sample.Basic 立方體執行的範例查詢。

2. 使用文字編輯器，將下列程式碼輸入空白檔案：

   SELECT
     {}
   ON COLUMNS
   FROM Sample.Basic

3. 將檔案另存為 qry_blank.txt。

MDX 集和元組

MDX 集合包含元組，而 MDX 元組包含成員名稱。瞭解集合、元組和成員名稱之間的差異，以及它們如何融入 Essbase MDX 查詢。

撰寫您的第一個查詢，然後在 MaxL 從屬端中執行，以從「基本範例」立方體擷取部分資料。

集

MDX 集可以是空的，也可以是元組的集合或集合集合。

例如，以下是空的集合。

{ }

集合必須以大括號 {} 括住，但若集合以傳回集合的 MDX 函數表示 (稍後再有關函數)，則除外。

以下是由一個元組組成的集合。

  {[Cola]}

在下列查詢中，{([Cola], [Actual])} 也是由一個元組組成的集合，但在此情況下，元組具有多個成員名稱。

SELECT
  {([Cola], [Actual])}
ON COLUMNS
FROM Sample.Basic

{([Cola], [Actual])} 是由兩個維度 (Product 和 Scenario) 的兩個成員 (Cola 和 Actual) 所組成的元組。

維度規則

當集合有多個元組時，每個元組的成員必須以相同的順序代表相同的 Essbase 維度。亦即，所有元組的維度必須與其他的相同。

• 確定：下列集合包含兩個相同維度的元組：

  {(West, Feb), (East, Mar)}

• 不確定：下列集合會破壞維度規則，因為 Feb 和 Sales 來自不同的維度：

  {(West, Feb), (East, Sales)}

• 不確定：下列集合會破壞維度規則，因為雖然兩個元組包含相同的維度，但維度的順序會在第二個元組中反轉：

  {(West, Feb), (Mar, East)}

元組與成員名稱

元組是一種從任意數目的維度參照成員或成員組合的方式。例如，在 Sample.Basic 立方體中，這些都是有效的元組：

• Jan
• (Jan, Sales)
• ([Jan],[Sales],[Cola],[Utah],[Actual])

可以透過下列方式指定成員名稱：

• 指定實際名稱或別名，例如：

  • Cola

  • Actual

  • COGS

  • [100]

  如果成員名稱以數字開頭或包含空格，則應在方括號內；例如 [100] 或 [New York]。不過，建議所有成員名稱使用成員名稱括號，以供清晰度與程式碼可讀性使用。

  如果成員名稱開頭為 & 符號，則應在引號內；例如 ["&xyz"]。這是因為前置 & 符號是保留給替代變數 (請參閱 MDX 查詢中的變數 )。您也可以將它指定為 StrToMbr("&100")。

  對於屬性成員，應使用完整名稱 (用於唯一識別成員)；例如，[Ounces_12] 而非 [12]。

• 將維度名稱或任何一個祖代成員名稱指定為成員名稱的首碼，例如 [Product].[100-10] 和 [Diet].[100-10]。建議所有成員名稱使用此課堂練習，因為這樣可以消除不明確的情況，並讓您正確地參照共用成員。請參閱：大綱中的重複成員名稱中的「透過區別祖代來限定成員」。

  附註：

  請勿在成員名稱限定中使用多個祖代。如果包含多個祖代， Essbase 會傳回錯誤。例如，[Market].[New York] 和 [East].[New York] 是紐約的有效名稱；不過，[Market].[East].[New York] 會傳回錯誤。

• 指定在 WITH 區段中定義的計算成員名稱。

練習 2：執行您的第一個查詢

請回想一下，查詢範本第 2 行中的大括號 {} 是集合的預留位置。在本練習中，我們將新增一組至查詢並執行。

若要執行查詢：

1. 開啟 qry_blank.txt，這是您從建立 MDX 查詢樣板建立的查詢樣板。

2. 由於集合可以像一個元組一樣簡單，因此在保留集合的大括號 { } 中新增元組。

   在第 2 行的 { } 大括號內部輸入 [Jan]：

   SELECT 
     {[Jan]}
   ON COLUMNS
   FROM Sample.Basic

3. 將查詢另存為 qry_first.txt。

4. 確定 Essbase 正在執行中。

5. 啟動 MaxL 用戶端，並使用有效的使用者名稱和密碼登入。舉例而言：

   login admin1 my_Pa55w0rD on "https://myserver.example.com:9001/essbase/agent";

6. 將整個 SELECT 查詢複製並貼到 MaxL 從屬端，但尚未按 Enter 。

7. 在「基本」之後但按 Enter 之前的尾端輸入分號。(分號不是 MDX 需求，但 MaxL 從屬端要求此分號表示敘述句結束。

8. 按 Enter 鍵將查詢傳送至 Essbase 。

   結果應該與下列類似：

   Jan
     8024

含座標軸與立方體規格的 MDX 查詢版面配置

MDX 軸是一種指示，用來塑造 Essbase 立方體中查詢結果的方格版面配置。ON COLUMNS 和 ON ROWS 是描述結果顯示位置的軸關鍵字。立方體規格包含 FROM 關鍵字，並告訴 Essbase 要查詢的立方體。

MDX 軸

軸在選取後符合 MDX 查詢：

SELECT <axis> [, <axis>...]
FROM <database> 

在下列查詢中，座標軸規格為 {Jan} ON COLUMNS：

SELECT
  {Jan} ON COLUMNS
FROM Sample.Basic

至少必須在任何 MDX 查詢中指定一個軸。

可指示最多 64 個軸，從 AXIS (0) 開始並繼續 AXIS (1) ...AXIS (63) 使用三個以上的軸並不常見。軸的順序並不重要；不過，當指定一組 0 到 n 的軸時，不應略過 0 到 n 之間的軸。此外，維度不能出現在多個軸上。

前五個軸有關鍵字別名，如下表所列：

表格 28-1 軸關鍵字別名

座標軸關鍵字別名	座標軸
在欄上	可代替 AXIS (0)
在資料列上	可以取代 AXIS (1)
頁面上	可以取代 AXIS (2)
在章節上	可以取代 AXIS (3)
區段	可以取代 AXIS (4)

MDX 立方體規格

立方體規格是查詢的一部分，可決定要查詢哪些 Essbase 資料庫。立方體規格適用於 MDX 查詢，如下所示：

SELECT <axis> [, <axis>...]
FROM <cube> 

<cube> 區段位於 FROM 關鍵字之後，且應由分隔或非分隔的識別碼所組成，這些識別碼會先指定應用程式名稱，然後指定資料庫名稱；例如，下列規格有效：

• FROM Sample.Basic

• FROM [Sample.Basic]

• FROM [Sample].[Basic]

• FROM'Sample'.'Basic'

執行 3：執行雙軸查詢

若要執行雙軸查詢：

1. 開啟 qry_blank.txt，即您在建置 MDX 查詢樣板中建立的查詢樣板。

2. 在 ON COLUMNS 之後新增逗號；然後新增 ON ROWS 以新增第二個軸的預留位置：

   SELECT
     {}
   ON COLUMNS,
     {}
   ON ROWS
   FROM Sample.Basic

3. 將新的查詢樣板另存為 qry_blank_2ax.txt。

4. 作為欄軸的設定，輸入 Product 成員 100-10 和 100-20。舉例而言：

   SELECT
     {[100-10],[100-20]}
   ON COLUMNS, 
     {}
   ON ROWS
   FROM Sample.Basic

   因為這些成員名稱包含特殊字元，所以您必須使用方括號。此處使用的慣例，建議將所有成員名稱括在括號中，即使成員名稱不包含特殊字元也一樣。

5. 作為列軸的設定，輸入「年度」成員 Qtr1 到 Qtr4。

   SELECT
     {[100-10],[100-20]}
   ON COLUMNS,
     {[Qtr1],[Qtr2],[Qtr3],[Qtr4]}
   ON ROWS
   FROM Sample.Basic

6. 將查詢另存為 qry_2ax.txt。

7. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

查詢的結果應該如下所示：

表格 28-2 結果：執行雙軸查詢

	100-10	100-20
Qtr1	5096	1359
Qtr2	5892	1534
Qtr3	6583	1528
Qtr4	5206	1287

練習 4：查詢單一軸上的多個維度

若要查詢單一軸上的多個維度，請執行下列動作：

1. 開啟 qry_blank_2ax.txt，即您在上一個練習中建立的查詢範本。

2. 在欄軸上，指定兩個元組，每個元組都是成員組合，而不是單一成員。將每個元組括在括號中，因為每個元組中都會呈現多個成員。

   SELECT
     {([100-10],[East]), ([100-20],[East])}
   ON COLUMNS,
     {}
   ON ROWS
   FROM Sample.Basic

3. 在列軸上，指定四個兩個成員元組，以 Profit 為每一季巢狀：

   SELECT
     {([100-10],[East]), ([100-20],[East])}
   ON COLUMNS,
     {
     ([Qtr1],[Profit]), ([Qtr2],[Profit]),
     ([Qtr3],[Profit]), ([Qtr4],[Profit])
     }
   ON ROWS
   FROM Sample.Basic

4. 將查詢另存為 qry_1ax.txt。

5. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   結果應該與下列類似：

   表格 28-3 結果：查詢單一軸上的多個維度

   		100-10	100-20
   		East	East
   Qtr1	利潤	2461	212
   Qtr2	利潤	2490	303
   Qtr3	利潤	3298	312
   Qtr4	利潤	2430	287

使用 MDX 函數來建立集

您可以使用 MDX 函數在 Essbase 中繼資料或資料上作業。函數可以傳回成員、集合、值、元組或字串。無論您是使用 MDX 來分析、更新或匯出資料，它們都很有用。

嘗試使用練習，瞭解如何使用 MemberRange 與 CrossJoin 函數。

MDX 函數的簡介著重於一些產生集合的函數。您不需手動在 MDX 查詢中輸入個別成員或元組的集合，而是可以用簡單的函數表示式來取代此類列舉。MDX 函數可以傳回集合以及其他值。

例如，Children 是 set 函式。它會傳回輸入成員的子成員集合。因此，Children(Qtr1) 會傳回 {Jan, Feb, Mar}。

後續練習可協助您學習在簡單查詢中使用 MDX 功能。Essbase 支援的 MDX 函數完整參照列於 MDX 函數清單。

練習 5：使用 MemberRange 函數

MemberRange MDX 函數會傳回包含相同層代之兩個指定成員之間的成員範圍。其語法如下：

MemberRange (<member1>, <member2>, [,<layertype>])

其中您提供的第一個引數是開始範圍的成員，而第二個引數是結束範圍的成員。layertype 引數是選擇性的。

附註：

MemberRange 的替代語法是在兩個成員之間使用冒號，而不是使用函數名稱：member1 : member2.

若要使用 MemberRange 函數：

1. 開啟 qry_blank.txt，即您在建置 MDX 查詢樣板中建立的查詢樣板。

2. 刪除使用函數傳回集合時不需要的大括號 {}。

3. 使用冒號運算子來選取 Qtr1 到 Qtr4 的成員範圍：

   SELECT 
     [Qtr1]:[Qtr4]
   ON COLUMNS 
   FROM Sample.Basic

4. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   會傳回第 1 季、第 2 季、第 3 季及第 4 季。

5. 使用 MemberRange 函數來選取相同的成員範圍，即 Qtr1 到 Qtr4。

   SELECT
     MemberRange([Qtr1],[Qtr4])
   ON COLUMNS
   FROM Sample.Basic

6. 將查詢貼到 MaxL 從屬端並執行。

7. 將查詢另存為 gry_member_range_func.txt。

練習 6：使用 CrossJoin 函數

CrossJoin 函數會傳回來自不同 Essbase 維度之兩個集合的交叉乘積。其語法如下：

CrossJoin(set,set)

此函數採用兩個來自不同維度的集合作為輸入，並建立其交叉乘積的集合。這對於建立對稱報表非常有用。

若要使用 CrossJoin 函數：

1. 開啟 qry_blank.txt，即您在建置 MDX 查詢樣板中建立的查詢樣板。

2. 將資料欄軸的大括號 {} 取代為 CrossJoin()。

   SELECT
     CrossJoin () 
   ON COLUMNS, 
     {}
   ON ROWS
   FROM Sample.Basic

3. 在您要提供給 CrossJoin 函數的兩個 set 引數中，加上兩個以逗號分隔的大括號作為預留位置：

   SELECT
     CrossJoin ({}, {})
   ON COLUMNS,
     {}
   ON ROWS
   FROM Sample.Basic

4. 在第一個集合中，指定 Product 成員 [100-10]。在第二組中，指定「市場」成員 [East]、[West]、[South] 和 [Central]。

   SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
   ON COLUMNS,
     {}
   ON ROWS
   FROM Sample.Basic

5. 在列軸上，使用 CrossJoin 跨一組包含 Qtr1 的 Measures 成員：

   SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
   ON COLUMNS,
     CrossJoin ( 
       {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]} 
     ) 
   ON ROWS
   FROM Sample.Basic

6. 將查詢另存為 qry_crossjoin_func.txt。

7. 將查詢貼到 MaxL 從屬端並執行。

當您使用 CrossJoin 實驗時，請注意引數的順序會影響輸出中的元組順序。

查詢結果如下所示：

表格 28-4 結果：使用 CrossJoin 函數

		100-10	100-10	100-10	100-10
		East	西部	南部省	中央區
銷售管理系統	Qtr1	5731	3493	2296	3425
COGS	Qtr1	1783	1428	1010	1460
毛利百分比	Qtr1	66.803	59.118	56.01	57.372
利潤百分比	Qtr1	45.82	29.974	32.448	24.613

附註：

如果輸入集是基礎維度及其屬性維度，請考慮使用 CrossJoinAttribute。

練習 7：使用子項函數

「子項」函數會傳回指定成員的所有子項成員。使用此語法：

Children (member)

附註：

「子項」的替代語法是用來作為輸入成員的運算子，如下所示：member.Children。我們將在本練習中使用運算子語法。

若要使用 Children 函數在第一軸規格中介紹捷徑，請執行下列動作：

1. 開啟 qry_crossjoin_func.txt，這是您在上一個練習中建立的查詢。

2. 在資料欄軸規格的第二組中，以 [Market].Children 取代 [East],[West],[South],[Central]。

   SELECT 
     CrossJoin ({[100-10]}, {[Market].Children})
   ON COLUMNS,
     CrossJoin (
       {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
     )
   ON ROWS
   FROM Sample.Basic

3. 將查詢另存為 gry_children_func.txt。

4. 將查詢貼到 MaxL 從屬端並執行。

   您應該會看到和前一個 CrossJoin 練習傳回的結果相同的結果。

使用 MDX 的參考層級和層代

部分 MDX 函數會根據輸入 layer 引數執行 set 作業。圖層代表 Essbase 維度的層代或層級。瞭解如何使用「成員」函數來參照集合。

在 MDX 中，層的概念是指 Essbase 階層中的層代和層級。

在 Essbase 中，層代編號在維度名稱開始計算為 1；較高的層代編號是階層中最接近分葉成員的層代編號。

階層最下層部分的層級編號開頭為 0，而最高層級編號則是維度名稱。

您可以使用下列方式指定圖層引數：

• 層代或層次名稱；例如 States 或 Regions。

• 維度名稱以及層代或層級名稱；例如 Market.Regions 和 [Market].[States]。

• 「層次」可搭配維度與層次編號作為輸入。例如，[Year].Levels(0)。

• 「層次」函數與成員作為輸入。例如，[Qtr1].Level 會傳回 Sample.Basic 中的季度層次，即 Market 維度的層次 1。

• 「層代」可搭配維度與層代編號作為輸入。例如，[Year].Generations (3)。

• Generation 函數與成員作為輸入。例如，[Qtr1].Generation 會傳回 Sample.Basic 中季度的層代，即 Market 維度的層代 2。

附註：

在 Sample.Basic 資料庫中，Qtr1 和 Qtr4 位於同一層。這表示 Qtr1 與 Qtr4 也位於相同的世代。不過，在具有不規則階層的不同資料庫中，Qtr1 與 Qtr4 可能不一定位於相同的階層中，雖然它們位於相同的層代。例如，如果 Qtr1 的階層向下探鑽至週，而 Qtr4 的階層在月停止，則 Qtr1 是比 Qtr4 高一個層級，但仍位於同一個層級。

練習 8：使用成員函數

使用「成員」函數可傳回指定層代或層級的所有成員。與圖層引數搭配使用時，語法如下：

Members (layer)

其中 layer 引數指示要傳回的成員層代或層級。

附註：

「成員」的替代語法為 layer.Members。

若要使用「成員」函數，請執行下列動作：

1. 開啟 qry_blank.txt，即您在建置 MDX 查詢樣板中建立的查詢樣板。

2. 刪除大括號 {}，這是使用函數傳回集合時不需要的。

3. 使用「成員」函數與「層次」函數來選取 Sample.Basic 之 Market 維度中的所有層次 0 成員：

   SELECT
     Members(Market.levels(0))
   ON COLUMNS
   FROM Sample.Basic

4. 將查詢另存為 qry_members_func.txt。

5. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   結果：會傳回 Market 維度中的所有狀態。

使用切割器軸來設定 MDX 查詢檢視點

切片器軸是將 MDX 查詢限制為僅考慮 Essbase 立方體的特定區域的方法。試試範例練習，學習如何使用 WHERE 子句中的切片器。

切片器 (若有使用) 必須位於 MDX 查詢的 WHERE 區段中。此外，WHERE 區段必須為查詢的最後一個元件，並遵循立方體設定 (FROM 區段)：

SELECT {set}
ON axes
FROM cube
WHERE slicer

使用切片軸來設定查詢的相關資訊環境；通常是所有其他軸的預設相關資訊環境。

如果只要在 Sample.Basic 立方體中選取實際銷售 (不包括預算銷售)，WHERE 子句看起來可能如下：

WHERE ([Actual], [Sales])

由於 (實際，銷售) 是在切片軸中指定的，因此您不需要將它們包含在 ON AXIS (n) 集規格中。

附註：

相同的標註不能出現在其他軸和切片軸上。若要使用本身維度的準則來篩選軸，您可以使用子選取。

練習 9：使用切片器軸限制結果

若要使用切割器軸來限制結果，請執行下列動作：

1. 開啟 gry_crossjoin_func.txt，這是您在「練習 6」中建立的查詢，使用 MDX 函數來建置集。

2. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   請注意其中一個資料儲存格中的結果；例如，請注意，第一個元組 ([Cola],[East],[Sales],[Qtr1]) 的值為 5731。

3. 新增切片器軸，將傳回的資料限制為僅預算值。

   SELECT
     CrossJoin ({[100-10]}, {[East],[West],[South],[Central]})
   ON COLUMNS,
     CrossJoin (
       {[Sales],[COGS],[Margin %],[Profit %]}, {[Qtr1]}
     )
   ON ROWS
   FROM Sample.Basic
   WHERE (Budget)

4. 將查詢貼到 MaxL 從屬端並執行。

5. 請注意，元組 ([Cola],[East],[Sales],[Qtr1]) 的值現在是 5020。

6. 將查詢另存為 qry_slicer_axis.txt。

通用 MDX 關係功能

MDX 關係函式會根據 Essbase 立方體大綱中的階層成員關係傳回集合或成員。

下列 MDX 關係函數會傳回集合。

表格 28-5 傳回集的 MDX 關係功能清單

關係功能	描述
子系	傳回輸入成員的子項。
同層級	傳回輸入成員的同層級。
下一代	傳回具有不同選項的成員的子代。

下列 MDX 關係函式會傳回單一成員而非集合：

表格 28-6 傳回單一成員的 MDX 關係函數清單

關係功能	描述
Ancestor	傳回指定圖層的祖系。
堂、表兄弟姊妹	傳回與其他祖代成員位於相同位置的子項成員。
父項	傳回輸入成員的父項。
第一個子項	傳回輸入成員的第一個子項。
上下階	傳回輸入成員的最後一個子項。
第一階同層級	傳回輸入成員之父項的第一個子項。
最後同階	傳回輸入成員之父項的最後一個子項。

練習 10：從說明文件中嘗試一些關係功能範例

瞭解關係函數的運作方式：

1. 按一下上方其中一個表格中的函數連結，或按一下子項。

2. 閱讀範例，並將 SELECT 查詢複製到您的剪貼簿。

3. 將查詢貼到 MaxL 從屬端，新增右分號並執行查詢，如第一次練習 (在 MDX 集和元組中) 所述。

設定作業的 MDX 函數

您可以將這些 MDX 函數與 Essbase 搭配使用，以比較、結合、合併或減少集合：CrossJoin、CrossJoinAttribute、Distinct、Except、Generate、Head、Intersect、Subset、Tail 和 Union。

觀賞練習瞭解 Intersect 與 Union 函數之間的差異。

下列集合函數會在輸入集上作業，而不會從立方體衍生進一步的資訊：

表格 28-7 純集函數清單

純集功能	描述
CrossJoin 、 CrossJoinAttribute	傳回兩個不同維度集合的交叉區段。
不同	刪除某個集合中的重複元組。
例外	傳回內含兩個集合之間差異的子集。
產生	反覆函數。針對 set1 中的每個元組，傳回 set2。
標頭	傳回集合中第一個存在的 n 成員或元組。
交集	傳回兩個輸入集合的交集。
子集合	傳回某個集合的子集，其中的子集為以數值指定的元組範圍。
尾部	傳回集合中最後一個 n 成員或元組。
聯集	傳回兩個輸入集合的聯集。

練習 11：使用交集函數

MDX Intersect 函數會傳回兩個輸入集的交集 (選擇性地保留重複項目)。使用它透過尋找兩個集合中的元組來比較集合。

要遵循的語法為：

Intersect (set, set [,ALL])

1. 開啟 qry_blank.txt，即您在建置 MDX 查詢樣板中建立的查詢樣板。

2. 從軸刪除空白的集合大括號 {}，並以 Intersect() 取代。在 Intersect 大括號中保留一些空格，以加入更多程式碼。舉例而言：

   SELECT
      Intersect (
   
      )
   ON COLUMNS
   FROM Sample.Basic

3. 新增兩組以逗號分隔的大括號，作為您將提供給「交集」函數之兩個組引數的預留位置。舉例而言：

   SELECT
      Intersect ( 
      { },
      { }
      )
   ON COLUMNS
   FROM Sample.Basic

4. 指定 East 的子項作為第一個設定的引數。舉例而言：

   SELECT
      Intersect (
      { [East].children },
      { }
      )
   ON COLUMNS
   FROM Sample.Basic

5. 對於第二個 set 引數，請指定具有 "Major Market" 之 UDA 的 Market 維度的所有成員。舉例而言：

   SELECT
      Intersect (
      { [East].children },
      { UDA([Market], "Major Market") }
      )
   ON COLUMNS
   FROM Sample.Basic

6. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   系統會傳回 UDA 為主要市場的所有 East 子項。舉例而言：

   New York   Massachusetts   Florida
   
   8202       6172            5029

7. 將查詢另存為 qry_intersect_func.txt。

練習 12：使用聯集函數

MDX Union 函數結合兩個輸入集，選擇性地保留重複項目。使用它將兩個集合組合成一個集合。

要遵循的語法為：

Union (set, set [,ALL])

1. 開啟 qry_intersect_func.txt，即您在上一個練習中建立的查詢。

2. 將 Intersect 取代為 Union 。

3. 將查詢另存為 qry_union_func.txt。

4. 將查詢貼到 MaxL 從屬端並執行。

   當「交集」傳回的集合僅包含具有「主要市場」使用者定義屬性的 East 子系時，Union 會傳回較大的集合。它包含 East 的所有下階，以及具有主要市場使用者定義屬性的所有市場成員。

    (New York)      (Massachusetts) (Florida)       (Connecticut)   (New Hampshire) (East)          (California)    (Texas)         (Central)       (Illinois)      (Ohio)          (Colorado)
   +---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------
               8202            6712            5029            3093            1125           24161           12964    6425           38262           12577            4384            7227
   

可重複使用的集合和成員：MDX WITH 區段

在 MDX 查詢的 WITH 區段中定義成員和集合，可協助您篩選資料而不影響立方體。計算的成員是僅存在於查詢中的邏輯成員。指定的集合是只存在於查詢中的邏輯集合。試試範例練習 。

計算的成員和具名集是查詢中的邏輯實體，可在查詢生命週期多次使用。計算的成員和具名集可以節省撰寫程式碼行和執行時間的時間。MDX 查詢開頭的選擇性 WITH 區段是您定義計算的成員和 (或) 具名集的位置。

下列查詢使用計算的成員：

WITH
MEMBER [Measures].[Max Qtr2 Sales] AS
  'Max (
    {[Year].[Qtr2]},
    [Measures].[Sales]
  )'
SELECT
{ [Measures].[Max Qtr2 Sales] } on columns,
{ [Product].children } on rows
FROM Sample.Basic

下列查詢使用指定的集合：

WITH SET [NewSet] 
AS 'CrossJoin([Product].Children, [Market].Children)'
SELECT
   Filter([NewSet], NOT IsEmpty([NewSet].CurrentTuple)) 
ON COLUMNS
FROM Sample.Basic
WHERE
   {[Sales]}

計算的成員

計算的成員是查詢執行期間存在的假設性成員。計算的成員可啟用複雜分析，而不需要將實體成員新增至立方體大綱。計算的成員會儲存對實體成員執行的計算結果。

針對計算的成員名稱使用下列準則：

• 將計算的成員與維度建立關聯；例如，將成員 MyCalc 與 Measures 維度建立關聯，將它命名為 [Measures].[MyCalc]。

• 請勿使用實際成員名稱來命名計算的成員；例如，請勿命名計算的成員 [Measures].[Sales]，因為「計量」維度中已有 Sales。

當您使用多個計算的成員來建立比率或自訂總計時，建議為每個計算的成員設定解決順序。

已命名集

您可以在查詢的 SELECT 部分之前，使用 WITH SET 關鍵字定義具名集。這麼做非常有用，因為您可以在建立查詢的 SELECT 部分時，參照依名稱設定。

例如，命名的集 Best5Prods 會識別 12 月份的五項暢銷產品集合：

WITH
SET [Best5Prods]
  AS
  'Topcount (
    [Product].members,
    5,
    ([Measures].[Sales], [Scenario].[Actual], [Year].[Dec])
  )'
SELECT [Best5Prods] ON AXIS(0),
  {[Year].[Dec]} ON AXIS(1)
FROM Sample.Basic

練習 13：建立計算的成員

此練習使用最大值函數，這是計算的一般 MDX 函數。它會傳回集合元組中找到的最大值。

要遵循的語法為：

Max (set, numeric_value)

1. 開啟 qry_blank_2ax.txt，即您在 MDX 查詢版面配置 (含軸與立方體規格) 的「練習 3」中建立的查詢範本。

   SELECT
     {}
   ON COLUMNS,
     {}
   ON ROWS
   FROM Sample.Basic

2. 在列軸集上，指定 Product 的子項。舉例而言：

   SELECT
     {}
   ON COLUMNS,
     {[Product].children}
   ON ROWS
   FROM Sample.Basic

3. 在查詢開頭，為計算的成員規格新增預留位置。舉例而言：

   WITH MEMBER [].[]
    AS ''
   SELECT
     {}
   ON COLUMNS,
     {[Product].children}
   ON ROWS
   FROM Sample.Basic

4. 若要將計算的成員與 Measures 維度建立關聯，並將其命名為 Max Qtr2 Sales，請將此資訊新增至計算的成員規格。舉例而言：

   WITH MEMBER [Measures].[Max Qtr2 Sales]
    AS ''
   SELECT
     {}
   ON COLUMNS,
     {[Product].children}
   ON ROWS
   FROM Sample.Basic

5. 在 AS 關鍵字和單引號內，定義名為 Max Qtr2 Sales 之計算成員的邏輯。

   搭配集合使用 Max 函數以評估 (Qtr2) 為第一個引數，並將要評估 (Sales) 的計量作為第二個引數。舉例而言：

   WITH MEMBER [Measures].[Max Qtr2 Sales]
     AS '
     Max (
       {[Year].[Qtr2]},
       [Measures].[Sales]
     )'
   SELECT
     {}
   ON COLUMNS,
     {[Product].children}
   ON ROWS
   FROM Sample.Basic
   

6. 計算的成員「最大 Qtr2 銷售」是在 WITH 區段中定義。若要在查詢中使用它，請在查詢的 SELECT 部分的其中一個軸上參照它。舉例而言：

   WITH MEMBER [Measures].[Max Qtr2 Sales]
     AS '
     Max (
       {[Year].[Qtr2]},
       [Measures].[Sales]
     )'
   SELECT
     {[Measures].[Max Qtr2 Sales]}
   ON COLUMNS,
     {[Product].children}
   ON ROWS
   FROM Sample.Basic
   

7. 將查詢另存為 gry_calc_member.txt。

8. 將查詢貼到 MaxL 從屬端並執行，如第一個練習 (在 MDX 集和元組中) 所述。

   查詢結果如下所示：

   表格 28-8 結果：建立計算的成員

   	第 2 季銷售上限
   100	27187
   200	27401
   300	25736
   400	21355
   飲食	26787

附註：

MDX 參考文件中有更多範例。請參閱 MDX With Section 。

反覆 MDX 函數

反覆的 MDX 函數會對 Essbase 立方體中的資料集執行迴圈，以執行您指定的任何搜尋條件來調整結果。請參閱根據布林測試篩選資料的範例。

表 28-9 反覆 MDX 函數清單

函式	描述
篩選	傳回 set 中，搜尋條件值為 TRUE 的元組子集。
IIF	執行條件式測試，並傳回適當的數值表示式，或者根據測試評估為 TRUE 或 FALSE 而設定。
案例	執行條件式測試並傳回您指定的結果。
產生	針對 set1 中的每個元組，傳回 set2。

篩選函數範例

下列查詢使用 MDX 篩選函數來傳回表示式 IsChild([Market].CurrentMember,[East]) 傳回 TRUE 的所有 Market 維度成員。此查詢會傳回 East 的所有子項。

SELECT
  Filter([Market].Members,
    IsChild([Market].CurrentMember,[East])
  )
ON COLUMNS
FROM Sample.Basic

MDX 中的 Filter 函數與 Report Writer 中的 RESTRICT 指令相當。

使用 MDX 處理遺漏的資料

使用 MDX 查詢 Essbase 立方體時，您可以使用軸上的 NON EMPTY 關鍵字來隱藏不含值的儲存格。處理遺漏值的 MDX 函數包括 Avg、CoalesceEmpty、IsEmpty、NonEmptyCount 及 NonEmptySubset。NONEMPTYMEMBER 和 NONEMPTYTUPLE 特性可協助篩選出大型資料集的空白值。

在座標軸的設定設定前加上選擇性關鍵字 NON EMPTY 會導致該座標軸中將包含整個 #MISSING 值的片段隱藏。

以下為 NON EMPTY 的軸規格語法：

<axis_specification> ::=
  [NON EMPTY] <set> ON
  COLUMNS | ROWS | PAGES | CHAPTERS |
  SECTIONS | AXIS (<unsigned_integer>)

對於軸上的任何指定元組 (例如 (Qtr1, Actual))，磁碟片段是由結合此元組與所有其他座標軸的所有元組所產生的儲存格組成。如果所有的儲存格值都是 #MISSING，則 NON EMPTY 關鍵字會導致元組的刪除。

例如，如果資料列中甚至有一個值不是空的，就會傳回整個資料列。在列軸設定開頭包含 NON EMPTY 會排除查詢傳回之集合的下列列時段：

除了隱藏沒有 EMPTY 的遺漏值之外，您還可以使用下列 MDX 函數來處理 #MISSING 結果：

• CoalesceEmpty ，其會搜尋非 #MISSING 值的數值表示式

• IsEmpty ，如果輸入 numeric-value-expression 的值評估為 #MISSING，則傳回 TRUE

• 平均值：省略平均值遺漏的值，除非您使用選擇性的 IncludeEmpty 旗標

NonEmptyCount MDX 函數會傳回組中評估為非 #Missing 值之元組數目的計數。系統會評估每個元組，並將其納入此函數傳回的計數中。如果指定數值表示式，則會在每個元組的相關資訊環境中進行評估，並傳回非 #Missing 值的計數。

僅在聚總儲存立方體上，NonEmptyCount 函數已最佳化，因此只要掃描立方體一次，即可計算所有儲存格的相異計數。如果沒有這項最佳化，資料庫的掃描次數就會與相對應於相異計數的儲存格數目相同。當大綱成員公式具有下列語法時，會觸發 NonEmptyCount 最佳化：

NONEMPTYCOUNT(set, measure, exclude_missing)

exclude_missing 參數可藉由改善查詢執行不同計數計算的測量結果的查詢效能，來支援聚總資料庫的 NonEmptyCount 最佳化。

NONEMPTYMEMBER 和 NONEMPTYTUPLE 最佳化特性可讓 MDX 查詢大量成員或元組，同時略過只包含 #MISSING 資料之非促成值的公式執行。

• 在計算的成員或公式表示式開頭使用單一 NONEMPTYMEMBER 特性子句，以指示 Essbase 當 nonempty_member_list 中指定的任一成員空白時，公式或計算的成員值是空的。

• 在計算的成員或公式表示式開頭處使用單一 NONEMPTYTUPLE 特性子句，以指示 Essbase 在 nonempty_member_list 中所指定元組的儲存格值空白時，公式或計算的成員值是空的。

針對輸入集， NonEmptySubset MDX 函數會傳回該輸入集的子集，其中所有元組都會評估為非空白。可以指定非空白檢查的選擇性值表示式。此函數可協助最佳化以一組非空白組合已知為小值的大型組合為基礎的查詢。NonEmptySubset 會減少測量結果存在時設定的大小；例如，您可以針對特定「單位」要求子代的非空白子集。

MDX 查詢中的變數

您可以在 MDX 中使用預先定義的 Essbase 替代變數來參考經常變更的資訊，而不需變更查詢。若要參照 MDX 查詢或表示式中的變數，請輸入變數名稱前面加上 & 符號。

Essbase 中的替代變數會作為定期變更資訊的預留位置。您可以在 Essbase 立方體、應用程式或全域層級設定替代變數，然後為每個變數指定一個值。您可以隨時變更該值。您至少必須具備「資料庫管理員」角色，才能設定替代變數。請參閱導入變更資訊的變數。

若要在 MDX 表示式中使用替代變數，請考慮：

• 替代變數必須能夠從您查詢的應用程式和立方體存取。

• 替代變數有兩個元件：名稱和值。

• 變數名稱可以是英數字元組合，其大小上限是在名稱與相關物件限制中指定。請勿在 MDX 中使用的替代變數名稱中使用空格、標點符號或括號 ([ ])。

• 在您要使用變數的表示式中，輸入前面加上 & 符號的變數名稱；例如，其中 CurMonth 是伺服器上設定的替代變數名稱，在 MDX 表示式中包含 &CurMonth。

• 當您執行擷取時， Essbase 會將變數名稱取代為替代值，而 MDX 表示式會使用該值。

例如，撰寫的表示式顯示變數名稱 CurQtr 前面加上 &：

SELECT 
  {[&CurQtr]}
ON COLUMNS
FROM Sample.Basic

當評估表示式時，會使用目前值 (Qtr1) 取代變數名稱，如此可有效地執行表示式：

SELECT 
  {[Qtr1]}
ON COLUMNS
FROM Sample.Basic

查詢 MDX 中的特性

特性描述 Essbase 資料與中繼資料的特定特性。MDX 可讓您根據 Essbase 特性 (可以是內建或自訂) 來撰寫擷取和分析資料的查詢。您可以在 MDX 查詢軸或值表示式中呼叫特性。

內在和自訂特性

在 MDX 中，properties 描述資料和描述資料的特定特性。MDX 可讓您撰寫使用屬性來擷取和分析資料的查詢。屬性可以是內建的或自訂的。

內在特性

為所有維度中的成員定義內建屬性。為 Essbase 資料庫大綱中所有成員定義的內建成員特性為 MEMBER_NAME、MEMBER_ALIAS、LEVEL_NUMBER、GEN_NUMBER、IS_EXPENSE、COMMENTS 及 MEMBER_UNIQUE_NAME。

如需每個特性的描述，請參閱 MDX 內建特性。

自訂特性

Essbase 中的 MDX 支援兩種類型的自訂特性：屬性特性和 UDA 特性。屬性特性是由大綱中的屬性維度所定義。在 Sample.Basic 資料庫中，Pkg Type 屬性維度描述 Product 維度中成員的封裝特性。您可以使用特性名稱 [Pkg Type] 在 MDX 中查詢此資訊。

屬性特性僅針對特定維度定義，且僅針對每個維度中的特定層次定義。例如，在 Sample.Basic 大綱中，[Ounces] 是僅針對 Product 維度中的成員定義的屬性特性，且此特性只有 Product 維度的層級 0 成員具有有效值。其他維度 (例如 Market) 沒有 [Ounces] 特性。Product 維度中非層級 0 成員的 [Ounces] 特性為 NULL 值。大綱中的屬性特性是以該大綱中的屬性維度名稱來識別。

自訂特性也包含 UDA 。例如，[Major Market] 是在 Market 維度成員上定義的 UDA 特性。如果為成員定義 [ 主要市場 ] UDA，則傳回 TRUE 值，否則傳回 FALSE。

另請參閱 MDX 自訂特性。

呼叫查詢軸中的特性

您可以列出每個軸集的維度與屬性組合。執行查詢時，會評估指定維度中所有成員的指定屬性，並包含在結果集中。

例如，在欄軸上，下列查詢會傳回每個 Market 維度成員的 GEN_NUMBER 資訊。在列軸上，查詢會傳回每個 Product 維度成員的 MEMBER_ALIAS 資訊。

SELECT
  [Market].Members
    DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
  Filter ([Product].Members, Sales > 5000)
    DIMENSION PROPERTIES [Product].[MEMBER_ALIAS] on rows
FROM Sample.Basic

使用座標軸的 DIMENSION PROPERTIES 區段查詢成員特性時，可以使用維度名稱和特性名稱來識別特性，或使用特性名稱本身來識別特性。當特性名稱本身使用時，該特性會針對該座標軸上所有維度的所有成員傳回該特性資訊。

在下列查詢中，會針對 Year 和 Product 維度在列軸上評估 MEMBER_ALIAS 特性。

SELECT [Market].Members
  DIMENSION PROPERTIES [Market].[GEN_NUMBER] on columns,
  CrossJoin([Product].Children, Year.Children)
    DIMENSION PROPERTIES [MEMBER_ALIAS] on rows
FROM Sample.Basic

呼叫值表示式中的特性

特性可以在 MDX 查詢中的值表示式中使用。例如，您可以根據使用輸入集中成員特性的值表示式來篩選集合。

下列查詢會傳回所有包裝在罐中的咖啡因產品。

SELECT
   Filter([Product].levels(0).members,
    [Product].CurrentMember.Caffeinated and
    [Product].CurrentMember.[Pkg Type] = "Can")
       Dimension Properties
       [Caffeinated], [Pkg Type] on columns
FROM Sample.Basic

下列查詢會使用使用者定義屬性 (UDA) [ 主要市場 ]，根據目前市場是否為主要市場來計算值 [BudgetedExpenses]。

WITH
  MEMBER [Measures].[BudgetedExpenses] AS
    'IIF([Market].CurrentMember.[Major Market],
    [Marketing] * 1.2, [Marketing])'

SELECT  {[Measures].[BudgetedExpenses]} ON COLUMNS,
  [Market].Members ON ROWS
FROM Sample.Basic
WHERE ([Budget])

特性的值類型

Essbase 中 MDX 特性的值可以是數值、布林值或字串類型。MEMBER_NAME 與 MEMBER_ALIAS 屬性會傳回字串值。LEVEL_NUMBER 和 GEN_NUMBER 特性會傳回數值。

屬性特性會根據屬性維度類型傳回數值、布林值或字串值。例如，在 Sample.Basic 中，[Ounces] 屬性特性為數值特性。[Pkg Type] 屬性特性為字串特性。[Caffeinated] 屬性特性為布林值特性。

Essbase 允許具有日期類型的屬性維度。日期類型特性會被視為 MDX 中的數值特性。比較這些特性值與日期時，請使用 Todate 函數，在比較之前將日期字串轉換成數值。

下列查詢會傳回在日期 03/25/2018 導入的所有 Product 維度成員。因為屬性 [Intro Date] 是日期類型，所以 TODATE 函數必須用來將日期字串 "03-25-2018" 轉換成數字，才能進行比較。

SELECT
  Filter ([Product].Members,
    [Product].CurrentMember.[Intro Date] = 
    TODATE("mm-dd-yyyy","03-25-2018"))ON COLUMNS
FROM Sample.Basic

在值運算式中使用屬性時，您必須根據其值類型適當地使用該屬性：字串、數值或布林值。

您也可以查詢具有數值範圍的屬性維度。

下列查詢會擷取「小」、「中」及「大」植入範圍的銷售資料。

SELECT
  {Sales} ON COLUMNS,
  {Small, Medium, Large} ON ROWS
FROM Sample.Basic

當屬性用作值表示式中的特性時，您可以使用範圍成員來檢查成員的特性值是否落在指定的範圍內 (使用 IN 運算子)。

例如，下列查詢會傳回植入範圍為「中」的所有 Market 維度成員：

SELECT
  Filter(
    Market.Members, Market.CurrentMember.Population
    IN "Medium"
  )
ON AXIS(0)
FROM Sample.Basic

NULL 特性值

並非所有成員都具有指定特性名稱的有效值。例如，MEMBER_ALIAS 特性會傳回大綱中定義之指定成員的替代名稱；不過，並非所有成員都可能已定義別名。在這些情況下，會為沒有別名的成員傳回 NULL 值。

在下列查詢中，

SELECT
  [Year].Members
   DIMENSION PROPERTIES [MEMBER_ALIAS]
ON COLUMNS
FROM Sample.Basic

Year 維度中的成員都沒有為其定義的別名。因此，查詢會針對 Year 維度中的成員傳回 MEMBER_ALIAS 特性的 NULL 值。

屬性特性是針對特定維度的成員和該維度中的特定層次所定義。在 Sample.Basic 資料庫中，只有 Product 維度的層級 0 成員才定義 [Ounces] 特性。

因此，如果您從 Market 維度查詢成員的 [Ounces] 特性 (如下查詢所示)，將會發生語法錯誤：

SELECT
  Filter([Market].members,
    [Market].CurrentMember.[Ounces] = 32) ON COLUMNS
FROM Sample.Basic

此外，如果您查詢維度中非層級 0 成員的 [Ounces] 特性，則會得到 NULL 值。

在值運算式中使用屬性值時，您可以使用 IsValid() 函數來檢查 NULL 值。下列查詢會傳回 [Ounces] 特性值 12 的所有 Product 維度成員 (排除具有 NULL 值的成員)。

SELECT
  Filter([Product].Members,
    IsValid([Product].CurrentMember.[Ounces]) AND
    [Product].CurrentMember.[Ounces] = 12) 
ON COLUMNS
FROM Sample.Basic