MDXクエリーの記述

この項の内容:

はじめに

クエリーの要素の理解

関数を使用したセットの作成

レベルおよび世代の処理

スライサ軸を使用したクエリーの視点の設定

共通関係関数

セット演算の実行

名前付きセットおよび計算済メンバーの作成と使用

反復関数の使用

欠落データの処理

MDXクエリーでの代替変数の使用

プロパティのクエリー

はじめに

MDXは、Essbaseのデータ操作言語で、XML for Analysis設立メンバーの共同仕様です。

Sample.Basicデータベースに基づいている、この章の演習を行うには、MaxLシェルを使用します。先に進む前に、Essbaseを起動してMaxLシェルにログオンします。また、この章に紹介されているとおりにサンプル・クエリーを作成するためにテキスト・エディタを用意してください。

注:

MaxLシェルのかわりに、管理サービス・コンソールでMDXスクリプト・エディタを使用できます。ただし、この章の説明では、MaxLシェルを使用しています。

クエリーの要素の理解

この項では、簡単なクエリーを作成するための基礎として使用するテンプレートを作成します。

ほとんどのクエリーは、次の構文フレームワークに基づいて作成できます:

      SELECT
  {}
ON COLUMNS
FROM Sample.Basic
   

1行目のSELECTは、すべてのMDXステートメントの本体を開始するキーワードです。

2行目の中カッコ{ }は、セットのプレースホルダです。前のクエリーでは、セットは空ですが中カッコはプレースホルダのままです。

  クエリー・テンプレートを作成するには:

  1. Sample.Basicデータベースに対して実行できるサンプル・クエリーを保管するフォルダを作成します。

    たとえば、EssbaseインストールのARBORPATH/app/Sample/Basicディレクトリに「queries」というフォルダを作成します。

  2. テキスト・エディタを使用して、空のファイルに次のコードを入力します:

                SELECT
      {}
    ON COLUMNS
    FROM Sample.Basic
             
  3. ファイルをqry_blank.txtという名前でqueriesフォルダに保存します。

    注:

    テキスト・エディタではなく、Administration ServicesのMDXスクリプト・エディタを使用している場合は、MDXスクリプト・エディタからクエリーをqry_blank.MDXという名前で保存します。

セットおよびタプルの概要

セットとは、同じ次元性を持つ1つ以上のタプルの、順序付けられた集合のことです(次元性の詳細は、セットの指定のルールを参照してください)。

タプルとは、任意の数の次元からメンバーまたはメンバーの組合せを参照する方法です。たとえば、Sample.Basicデータベースでは、Janがタプルで、(Jan, Sales)、([Jan],[Sales],[Cola],[Utah],[Actual])のように使用します。

メンバー名は、次の方法で指定できます:

  • 実際の名前または別名を指定します。次に例を示します:

    • Cola

    • Actual

    • COGS

    • [100]

    メンバー名が数字で始まる場合、またはメンバー名にスペースが入っている場合、[100]のように大カッコで囲む必要があります。明確にしてコードを読みやすくするため、すべてのメンバー名で大カッコを使用することをお薦めします。

    メンバー名がアンパサンド(&)で始まる場合、["&xyz"]のように引用符で囲む必要があります。これは、先頭のアンパサンドが代替変数用に予約されるためです(MDXクエリーでの代替変数の使用を参照)。また、StrToMbr("&100")として指定することもできます。

    属性メンバーの場合は、(メンバー名を一意に識別できる)ロング名を使用します(例: [12]ではなく、[Ounces_12])。

  • メンバー名の接頭辞として、次元名、またはいずれかの祖先メンバー名を指定します(例: [Product].[100-10]および[Diet].[100-10])。この方法ではあいまいさがなくなり、共有メンバーを正しく参照できるため、すべてのメンバー名にこの方法を使用することをお薦めします。

    注:

    メンバー名の修飾には複数の祖先を使用しないでください。複数の祖先を使用した場合、Essbaseによってエラーが戻されます。たとえば、[Market].[New York]および[East].[New York]は、有効なNew Yorkの名前ですが、[Market].[East].[New York]を指定すると、エラーが戻されます。

  • WITHセクションで定義された計算済メンバーの名前を指定します。

演習: 初めてのクエリーの実行

クエリー・テンプレートの2行目の中カッコ{ }は、セットのプレースホルダです。この演習では、クエリーにセットを追加して、実行します。

  クエリーを実行するには:

  1. qry_blank.txtを開きます。

  2. セットは1つのタプルと同じくらい簡単なので、セットとしてJanをクエリー・テンプレートに追加します。中カッコはそのままにします(中カッコは、関数呼出しによって生成されるセットを除き、すべてのセット指定に必要です)。

    2行目の中カッコの内側にJanと入力します:

                SELECT 
      {
                Jan
                }
    ON COLUMNS
    FROM Sample.Basic
             
  3. クエリーをqry_first.txtという名前で保存します。

  4. Essbaseが起動していること(essbase.exeプロセスが実行中であること)を確認します。

  5. EssbaseにMDXステートメントを渡すには、MaxLシェルまたはAdministration ServicesのMDXスクリプト・エディタを使用して、ステートメントをEssbaseに渡します。この章の例では、MaxLシェルを使用します。

    MaxLシェルを起動して、有効なユーザー名とパスワードでログオンします。たとえば、

                essmsh -l admin passwd
             
  6. SELECTクエリー全体をコピーしてMaxLシェルに貼り付けますが、まだ「[Enter]」キーは押さないでください。

  7. Basicの後で、「[Enter]」キーを押す前に、最後にセミコロンを入力します。セミコロンは、MDX構文の要件ではありませんが、MaxLシェルでは、実行する準備ができているステートメントの終わりを示すために必要です。

    注:

    Administration ServicesのMDXスクリプト・エディタを使用している場合は、末尾にセミコロンを入力しないでください。

  8. 「[Enter]」キーを押します。

結果: 結果は次のようになります:

      Jan
  8024
   

セットの指定のルール

前述のとおり、セットは、1つ以上のタプルの順序付けられた集合です。

たとえば、次のクエリーでは、{[100-10]}は1つのタプルで構成されたセットです。

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

次のクエリー{([100-10], [Actual])}も1つのタプルで構成されたセットですが、この場合のタプルは1つのメンバー名ではありません。([100-10], [Actual])は、2つの次元(製品とシナリオ)のメンバーで構成されたタプルを表しています。

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

セットに複数のタプルが含まれる場合は、次のルールが適用されます: セットの各タプルのメンバーは、セットの他のタプルのメンバーと同じ次元を表す必要があります。さらに、次元は同じ順序で表す必要があります。つまり、セットの各タプルには同じ次元性が必要です。例:

  • 次のセットは、同じ次元性を持つ2つのタプルで構成されています:

                {(West, Feb), (East, Mar)}
             
  • 次のセットでは、FebとSalesが異なる次元のメンバーであるため、次元性ルールが守られていません:

                {(West, Feb), (East, Sales)}
             
  • 次のセットでは、2つのタプルには同じ次元が含まれていますが、2番目のタプルの次元の順序が逆であるため、次元性ルールが守られていません:

  •             {(West, Feb), (Mar, East)}
             
  • セットは、セットの集合または空(タプルが含まれていない)であることが可能です。

  • セットは、中カッコ{}で囲まれている必要があります(セットを戻すMDX関数でセットが表される場合を除く)。

軸の指定の概要

軸とは、データベースからのクエリー結果のレイアウトの指定です。軸は、次のようにMDXクエリーに指定します:

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

少なくとも1つの軸をMDXクエリーに指定する必要があります。

最大64個の軸を指定でき、AXIS(0)で始まり、AXIS(1)...AXIS(63)と続きます。一般的に、4個以上の軸を使用することはありません。軸の順序は重要ではありませんが、0からnまでの軸のセットを指定するときに、0とnの間の軸をスキップしないでください。また、次元は複数の軸上に表示できません。

表110に示すように、最初の5個の軸にはキーワードの別名があります:

表 110. 軸のキーワード別名

軸のキーワード別名

ON COLUMNS

AXIS(0)のかわりに使用可能

ON ROWS

AXIS(1)を置換え可能

ON PAGES

AXIS(2)を置換え可能

ON CHAPTERS

AXIS(3)を置換え可能

ON SECTIONS

AXIS(4)を置換え可能

たとえば、次のクエリーでは、軸の指定は{Jan}ON COLUMNSです:

      SELECT
  {Jan} ON COLUMNS
FROM Sample.Basic
   

演習: 2軸クエリーの実行

  2軸クエリーを実行するには:

  1. qry_blank.txtを開きます。

  2. ON COLUMNSの後ろにカンマを追加してから、ON ROWSを追加することで2番目の軸のプレースホルダを追加します:

                SELECT
      {}
    ON COLUMNS,
    
                  {}
                
    
                ON ROWS
                
    FROM Sample.Basic
             
  3. 新しいクエリー・テンプレートをqry_blank_2ax.txtという名前で保存します。

  4. 列軸のセットの指定として、製品メンバー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シェルに貼り付けて、実行します。

クエリーの結果を表111に示します:

表 111. 結果: 2軸クエリーの実行

 

100-10

100-20

Qtr1

5096

1359

Qtr2

5892

1534

Qtr3

6583

1528

Qtr4

5206

1287

演習: 単一軸上での複数の次元のクエリー

  単一軸上で複数の次元をクエリーするには:

  1. qry_blank_2ax.txtを開きます。

  2. 列軸で、2つのタプルを指定します。各タプルは、1つのメンバーではなく、メンバーの組合せです。各タプルで複数のメンバーを表しているので、各タプルを丸カッコで囲みます。

                SELECT
      {
                ([100-10],[East]), ([100-20],[East])
                }
    ON COLUMNS,
      {}
    ON ROWS
    FROM Sample.Basic
             
  3. 行軸で、2つのメンバーを持つタプルを4つ指定します。各Quarterを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シェルに貼り付けて、実行します。

クエリーの結果を表112に示します:

表 112. 結果: 単一軸上での複数の次元のクエリー

  

100-10

100-20

  

East

East

Qtr1

Profit

2461

212

Qtr2

Profit

2490

303

Qtr3

Profit

3298

312

Qtr4

Profit

2430

287

キューブの指定

キューブの指定は、クエリー対象のデータベースを決定するクエリーの一部です。キューブの指定は、次のようにMDXクエリーに配置します:

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

      FROM <database>
       
   

<database>セクションは、FROMキーワードの後に置き、最初にアプリケーション名を指定し次にデータベース名を指定する、区切られたまたは区切られていない識別子で構成する必要があります。たとえば、次の指定は有効です:

  • FROM Sample.Basic

  • FROM [Sample.Basic]

  • FROM [Sample].[Basic]

  • FROM'Sample'.'Basic'

関数を使用したセットの作成

メンバーごとまたはタプルごとにセットを作成するのではなく、セットを戻す関数を使用できます。MDXには、セットを戻す関数および他の値を戻す関数が用意されています。EssbaseでサポートされるMDX関数の詳細は、『Oracle Essbaseテクニカル・リファレンス』のMaxLに関する項を参照してください。

演習: MemberRange関数の使用

MemberRange関数では、同世代の指定された2つのメンバーの間(これら2つのメンバーを含む)の範囲のメンバーが戻されます。この構文は次のとおりです:

      MemberRange (
      member1
      , 
      member2
      , [,
      layertype
      ])
   

指定する最初の引数は、範囲の始まりとなるメンバーで、2番目の引数は、範囲の終わりとなるメンバーです。layertype引数はオプションです。『Oracle Essbaseテクニカル・リファレンス』を参照してください。

注:

MemberRangeのもう1つの構文として、関数名を使用するかわりに、member1 : member2のように2つのメンバー間にコロンを使用する方法があります。

  MemberRange関数を使用するには:

  1. qry_blank.txtを開きます。

  2. 中カッコ{}を削除します。セットを戻す関数を使用するときには必要ありません。

  3. コロン演算子を使用して、次のようにQtr1からQtr4までのメンバー範囲を選択します:

                SELECT 
      
                [Qtr1]:[Qtr4]
                
    ON COLUMNS 
    FROM Sample.Basic
             
  4. 演習: 初めてのクエリーの実行の説明のとおりに、クエリーをMaxLシェルに貼り付けて、実行します。

    Qtr1、Qtr2、Qtr3およびQtr4が戻されます。

  5. MemberRange関数を使用して、同じメンバー範囲であるQtr1からQtr4までを選択します。

                SELECT
      
                MemberRange([Qtr1],[Qtr4])
                
    ON COLUMNS
    FROM Sample.Basic
             
  6. クエリーをgry_member_range_func.txtという名前で保存します。

  7. 演習: 初めてのクエリーの実行の説明のとおりに、クエリーをMaxLシェルに貼り付けて、実行します。

結果: 手順3および手順5で示されたクエリーの実行時に、同じ結果が戻されるはずです。

演習: CrossJoin関数の使用

CrossJoin関数では、異なる次元の2つのセットのクロス積が戻されます。この構文は次のとおりです:

      CrossJoin(
      set
      ,
      set
      )
   

CrossJoin関数では、異なる次元の2つのセットを入力として、2つの入力セットのクロス積であるセットが作成されます。これは、対称型レポートを作成するときに便利です。

  CrossJoin関数を使用するには:

  1. qry_blank_2ax.txtを開きます。

  2. 列軸の中カッコ{}CrossJoin()に置き換えます。

                SELECT
      
                CrossJoin ()
                 
    ON COLUMNS
                ,
                 
      {}
    ON ROWS
    FROM Sample.Basic
             
  3. CrossJoin関数に指定する2つのセット引数のプレースホルダとして、中カッコ2つのペアをカンマで区切って追加します:

                SELECT
      CrossJoin (
                {}, {}
                )
    ON COLUMNS,
      {}
    ON ROWS
    FROM Sample.Basic
             
  4. 最初のセットでは、製品メンバー[100-10]を指定します。2番目のセットでは、市場メンバー[East][West][South]および[Central]を指定します。

                SELECT
      CrossJoin ({
                [100-10]
                }, {
                [East],[West],[South],[Central]
                })
    ON COLUMNS,
      {}
    ON ROWS
    FROM Sample.Basic
             
  5. 行軸でCrossJoinを使用して、メジャーのメンバーのセットとQtr1が含まれるセットをクロスします:

                   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を使用する場合は、引数の順序が、出力のタプルの順序に影響を与えます。

クエリーの結果を表113に示します:

表 113. 結果: CrossJoin関数の使用

  

100-10

100-10

100-10

100-10

  

East

West

South

Central

Sales

Qtr1

5731

3493

2296

3425

COGS

Qtr1

1783

1428

1010

1460

Margin %

Qtr1

66.803

59.118

56.01

57.372

Profit %

Qtr1

45.82

29.974

32.448

24.613

演習: Children関数の使用

Children関数では、特定のメンバーのすべての子メンバーのセットが戻されます。次の構文を使用します:

      Children (member)
   

注:

Childrenのもう1つの構文として、member.Childrenのように、Childrenを入力メンバーで演算子のように使用する方法があります。この演習では、この演算子構文を使用します。

  Children関数を使用して、最初の軸の指定にショートカットを取り入れるには、次の手順に従います:

  1. qry_crossjoin_func.txtを開きます。

  2. 列軸の指定の2番目のセットで、[East],[West],[South],[Central][Market].Childrenに置き換えます。

                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シェルに貼り付けて、実行します。

結果: 表113で戻された結果と同じ結果が戻されるはずです。

レベルおよび世代の処理

MDXでは、レイヤーは、Essbase階層の世代とレベルを意味します。

Essbaseでは、世代番号のカウントは、次元名を1として開始され、世代番号が大きいほど階層内のリーフ・メンバーに近くなります。

レベル番号は、階層の最もリーフ側に近い部分の0から始まります。最上位のレベル番号は次元名です。

数多くのMDX関数では、指定したレイヤーが入力引数として受け取られ、レイヤー引数で示された世代またはレベルに基づいて、セット操作が実行されます。

レイヤー引数を指定する方法は、次のとおりです:

  • 世代名またはレベル名(例: StatesRegions)。

  • 世代名またはレベル名を付けた次元名(例: Market.Regions[Market].[States])。

  • 入力として次元とレベル番号を使用するLevels関数たとえば、[Year].Levels(0)

  • 入力としてメンバーを使用するLevel関数。たとえば、[Qtr1].Levelによって、Sample.Basicの四半期のレベルが戻されます。それは、市場次元のレベル1です。

  • 入力として次元と世代番号を使用するGenerations関数(例: [Year].Generations (3))。

  • 入力としてメンバーを使用するGeneration関数。たとえば、[Qtr1].Generationによって、Sample.Basicの四半期の世代が戻されます。それは、市場次元の世代2です。

注:

Sample.Basicデータベースでは、Qtr1とQtr4は同じレイヤーにあります。つまり、Qtr1とQtr4は同じ世代に属します。ただし、不規則階層を持つ別のデータベースでは、Qtr1とQtr4は、同じ世代に属していても、必ずしも同じレベルにあるとは限りません。たとえば、Qtr1の階層は週にドリル・ダウンし、Qtr4の階層は月で止まることがあります。Qtr1は、Qtr4よりも1つレベルが高いですが、この2つは同じレイヤーにあります。

演習: Members関数の使用

Members関数を使用して、指定した世代またはレベルのすべてのメンバーを戻すことができます。この構文をlayer引数とともに使用すると、次のようになります:

Members ( layer )

指定するlayer引数は、戻されるメンバーの世代またはレベルを示します。

注:

Membersのもう1つの構文は、layer.Membersです。

  Members関数を使用するには:

  1. qry_blank.txtを開きます。

  2. 中カッコ{}を削除します。

  3. Members関数とLevels関数を使用して、Sample.Basicの市場次元のすべてのレベル0メンバーを選択します:

                SELECT
      
                Members(Market.levels(0))
                
    ON COLUMNS
    FROM Sample.Basic
             
  4. クエリーをqry_members_func.txtという名前で保存します。

  5. 演習: 初めてのクエリーの実行の説明のとおりに、クエリーをMaxLシェルに貼り付けて、実行します。

結果: 市場次元のすべての州が戻されます。

スライサ軸を使用したクエリーの視点の設定

スライサ軸は、データベースの特定の領域のみに適用されるようにクエリーを制限する方法です。オプションのスライサを使用する場合は、MDXクエリーのWHEREセクションで指定する必要があります。さらに、WHEREセクションは、キューブの指定(FROMセクション)の後に置き、クエリーの最後の構成要素にする必要があります:

      SELECT {
      set
      }
ON 
      axes
      
FROM 
      database
      
WHERE slicer
   

スライサ軸を使用して、クエリーのコンテキストを設定します。通常は、他のすべての軸のデフォルトのコンテキストです。

たとえば、クエリーでSample.Basicデータベース内の売上高実績のみ(売上高予算を除く)を選択する場合、WHERE句は次のようになります:

      WHERE ([Actual], [Sales])
   

スライサ軸に(Actual, Sales)が指定されているため、ON AXIS(n)のセットの指定に含める必要はありません。

演習: スライサ軸を使用した結果の制限

  スライサ軸を使用して結果を制限するには:

  1. gry_crossjoin_func.txtを開きます。

  2. 演習: 初めてのクエリーの実行の説明のとおりに、クエリーをMaxLシェルに貼り付けて、実行します。

    データ・セルの1つに表示される結果に注意してください。たとえば、最初のタプル([100-10],[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. クエリーをqry_slicer_axis.txtという名前で保存します。

結果: 手順1および手順3で示されたクエリーに対して異なる結果が表示されるはずです。

共通関係関数

表114に、データベース・アウトライン内のメンバーの関係に基づいてセットを戻す関係関数をリストします:

表 114. セットを戻す関係関数のリスト

関係関数

説明

Children

入力メンバーの子が戻されます。

Siblings

入力メンバーの兄弟が戻されます。

Descendants

メンバーの子孫が戻されます。異なるオプションが使用できます。

表115に、セットではなく単一メンバーを戻す関係関数をリストします:

表 115. 単一メンバーを戻す関係関数のリスト

  

関係関数

説明

Ancestor

指定したレイヤーに存在する祖先が戻されます。

Cousin

別の祖先のメンバーと同じ位置の子メンバーが戻されます。

Parent

入力メンバーの親が戻されます。

FirstChild

入力メンバーの最初の子が戻されます。

LastChild

入力メンバーの最後の子が戻されます。

FirstSibling

入力メンバーの親の最初の子が戻されます。

LastSibling

入力メンバーの親の最後の子が戻されます。

関係関数を使用する例は、『Oracle Essbaseテクニカル・リファレンス』のMaxLに関する項に記載されているMDXの例を参照してください。

セット演算の実行

表116に、データベースから追加情報を抽出せずに、入力セットに作用するセット関数をリストします:

表 116. 純粋なセット関数のリスト

  

純粋なセット関数

説明

CrossJoin

複数の次元のセット2つがクロス(交差)する部分が戻されます。

Distinct

セットから重複するタプルが削除されます。

Except

2つのセットの相違を含むサブセットが戻されます。

Generate

反復関数です。set1内のタプルごとにset2が戻されます。

Head

セットの先頭からn個のメンバーまたはタプルが戻されます。

Intersect

2つの入力セットの交差が戻されます。

Subset

セットからサブセットが戻されます。戻されるサブセットは、数値で指定されたタプルの範囲です。

Tail

セットの最後からn個のメンバーまたはタプルが戻されます。

Union

2つの入力セットの和集合が戻されます。

演習: Intersect関数の使用

Intersect関数では、2つの入力セットの交差が戻されます。必要に応じて、重複が保持されます。構文:

      Intersect (
      set
      , 
      set
       [,ALL])
   

Intersect関数を使用すると、両方のセットに存在するタプルを検索して、セットを比較できます。

  Intersect関数を使用するには:

  1. qry_blank.txtを開きます。

  2. 軸から中カッコ{}を削除し、かわりにIntersect()を入力します。例:

                SELECT
       
                Intersect (
    
       )
                
    ON COLUMNS
    FROM Sample.Basic
             
  3. Intersect関数に指定する2つのセット引数のプレースホルダとして使用するために、中カッコ2つのペアをカンマで区切って追加します。例:

                SELECT
       Intersect ( 
       
                { },
       { }
                
       )
    ON COLUMNS
    FROM Sample.Basic
             
  4. 最初のセット引数としてEastの子を指定します。例:

                SELECT
       Intersect ( 
       { 
                [East].children
                 },
       { }
       )
    ON COLUMNS
    FROM Sample.Basic
             
  5. 2番目のセット引数として、Major MarketのUDAを持つ市場次元のすべてのメンバーを指定します。例:

                SELECT
       Intersect (
       { [East].children },
       { 
                UDA([Market], "Major Market")
                 }
       )
    ON COLUMNS
    FROM Sample.Basic
             
  6. クエリーをqry_intersect_func.txtという名前で保存します。

  7. 演習: 初めてのクエリーの実行の説明のとおりに、クエリーをMaxLシェルに貼り付けて、実行します。

結果: 「Major Market」のUDAを持つEastのすべての子が戻されます。例:

      
New York   Massachusetts   Florida

   

      
8202       6172            5029
   

演習: Union関数の使用

Union関数では、2つの入力セットが結合されます。必要に応じて、重複が保持されます。構文:

      Union (set, set [,ALL])
   

Union関数を使用すると、2つのセットを1つのセットにまとめることができます。

  Union関数を使用するには:

  1. qry_intersect_func.txtを開きます。

  2. IntersectをUnionに置き換えます。

  3. クエリーをqry_union_func.txtという名前で保存します。

  4. クエリーをMaxLシェルに貼り付けて実行します。

結果: これらのクエリーの結果とIntersect関数を使用した結果(演習: Intersect関数の使用を参照)を比較すると、Intersectでは、「Major Market」のUDAを持つ、Eastの子のみが含まれているセットが戻されるのに対して、Unionでは、(Eastのすべての子) + (「Major Market」のUDAを持つ市場のすべてのメンバー)が戻されることがわかります。

純粋なセット演算関数のその他の例は、『Oracle Essbaseテクニカル・リファレンス』を参照してください。

名前付きセットおよび計算済メンバーの作成と使用

計算済メンバーと名前付きセットは、クエリーの存続期間中に複数回使用できる、クエリー内の論理エンティティです。計算済メンバーと名前付きセットによって、記述されたコードの行および実行時間で時間を節約できます。MDXクエリーの先頭に置くオプションのWITHセクションでは、計算済メンバーまたは名前付きセット、あるいはその両方を定義できます。

計算済メンバー

計算済メンバーとは、クエリーの実行期間中に存在する仮想メンバーです。計算済メンバーを使用すれば、新しいメンバーをデータベース・アウトラインに追加する必要なく、複雑な分析を行えます。計算済メンバーは、実際のメンバーに対して実行された計算結果の格納場所です。

計算済メンバーの名前には次のガイドラインを使用します:

  • 計算済メンバーは次元に関連付けます。たとえば、メンバーMyCalcをメジャー次元に関連付けるには、[Measures].[MyCalc]という名前を付けます。

  • 実際のメンバーの名前を使用して、計算済メンバーに名前を付けないでください。たとえば、Salesはすでにメジャー次元に存在するため、計算済メンバーに[Measures].[Sales]という名前を付けないでください。

複数の計算済メンバーを使用して、比率またはカスタム合計を作成するときには、各計算済メンバーに解決順を設定することをお薦めします。解決順の詳細は、『Oracle Essbaseテクニカル・リファレンス』のMDXに関する項を参照してください。

演習: 計算済メンバーの作成

この演習では、計算の共通関数であるMax関数を使用します。Max関数では、セットのタプル内で見つかった最大値が戻されます。この構文は次のとおりです:

      Max (
      set
      , 
      numeric_value
      )
   

  計算済メンバーを作成するには:

  1. qry_blank_2ax.txtを開きます。

  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. 計算済メンバーをメジャー次元に関連付け、メンバー名を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関数で、セットを使用して(Qtr)を最初の引数として評価し、メジャーを使用して(Sales)を2番目の引数として評価します。例:

                WITH MEMBER [Measures].[Max Qtr2 Sales]
      AS '
      
                Max (
                
    
                    {[Year].[Qtr2]},
                
    
                    [Measures].[Sales]
                
    
                  )
                '
    SELECT
      {}
    ON COLUMNS,
      {[Product].children}
    ON ROWS
    FROM Sample.Basic
    
             
  6. 計算済メンバーMax Qtr2 Salesは、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シェルに貼り付けて、実行します。

クエリーの結果を表117に示します:

表 117. 結果: 計算済メンバーの作成

 

Max Qtr2 Sales

100

27187

200

27401

300

25736

400

21355

Diet

26787

名前付きセット

名前付きセットは、クエリーのWITHセクションに定義します。定義すると、クエリーのSELECTセクションを作成するときにセットを名前で参照できるので便利です。

たとえば、名前付きセットBest5Prodsは、12月に最もよく売れている5つの製品のセットを識別します:

      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
   

反復関数の使用

表118に、データのセットがループされて、指定した検索条件と結果が実行される関数をリストします:

表 118. 反復関数のリスト

関数

説明

Filter

検索条件の値がTRUEであるセットの、タプルのサブセットが戻されます。

IIF

条件付きテストが実行されます。テストがTRUEまたはFALSEのいずれに評価されるかに応じて、適切な数値式またはセットが戻されます。

Case

条件付きテストが実行され、指定した結果が戻されます。

Generate

反復関数です。set1内のタプルごとにset2が戻されます。

Filter関数の例

次のクエリーでは、式IsChild([Market].CurrentMember,[East])によってTRUEが戻される市場次元のすべてのメンバーが戻されます。つまり、このクエリーでは、Eastのすべての子が戻されます。

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

MDX内のFilter関数は、レポート・ライターのRESTRICTコマンドと互換性があります。

Filter関数および他の反復関数のその他の例は、『Oracle Essbaseテクニカル・リファレンス』を参照してください。

欠落データの処理

データベースをクエリーする場合、軸の指定の先頭にNON EMPTYキーワードを使用すると、値を含まないセルをクエリー結果から除外できます。

NON EMPTYを含む軸の指定の構文は、次のとおりです:

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

軸でのセットの指定の前にオプションのキーワードNON EMPTYを指定すると、この軸で#MISSING値全体を含むスライスが抑制されます。

軸上の特定のタプル((Qtr1, Actual)など)の場合、スライスは、このタプルと他のすべての軸のすべてのタプルの組合せからなるセルで構成されます。これらのセルの値がすべて#MISSINGの場合、NON EMPTYキーワードによってそのタプルが削除されます。

たとえば、行のいずれの値も空でない場合は、その行全体が戻されます。行軸指定の先頭にNON EMPTYを挿入すると、クエリーで戻されるセットから次の行スライスが削除されます:

Qtr1

実績

#MISSING

#MISSING

#MISSING

#MISSING

#MISSING

NON EMPTYを使用して欠落した値を抑制する他にも、#MISSINGの結果を処理する関数として、次のMDX関数があります:

  • CoalesceEmptyは、数値式から#MISSING値以外を探します。

  • IsEmptyは、入力の数値式の評価結果の値が#MISSINGの場合に、TRUEを戻します。

  • Avgは、オプションのIncludeEmptyフラグが使用されていないかぎり、欠落した値を平均から除外します。

NonEmptyCount MDX関数は、セットのタプルのうち、値が#Missing値でないと評価されたタプル数のカウントを戻します。タプルごとに評価され、この関数によって戻されるカウントに含まれます。数値式が指定されている場合は、すべてのタプルとの関連で式が評価され、#Missingでない値のカウントが戻されます。

集約ストレージ・データベースの場合のみ、NonEmptyCount MDX関数が最適化されるため、データベースを一度スキャンするだけですべてのセルの個別カウントを計算できます。この最適化がない場合、個別カウントに対応するセルの数と同じ回数データベースがスキャンされます。アウトライン・メンバー式に次の構文がある場合、NonEmptyCount最適化がトリガーされます:

      NONEMPTYCOUNT(
      set
      , 
      measure
      , exclude_missing)
   

exclude_missingパラメータは、個別カウント計算を実行するメトリックを問い合せるクエリーのパフォーマンスを向上させることで、集約データベースでのNonEmptyCount最適化をサポートします。

NONEMPTYMEMBERおよびNONEMPTYTUPLEプロパティを使用すると、EssbaseのMDXでは、メンバーまたはタプルの大きなセットでクエリーを実行でき、一方、#MISSINGデータのみが含まれた、使用しない値での式の実行はスキップできます。

  • nonempty_member_listで指定されたメンバーのいずれかが空のとき、式または計算済メンバーの値が空であることをEssbaseに通知するには、計算済メンバーまたは式の先頭でNONEMPTYMEMBERプロパティ句を単独で使用します。

  • nonempty_member_listで指定されたタプルのセル値が空のとき、式または計算済メンバーの値が空であることをEssbaseに通知するには、計算済メンバーまたは式の先頭でNONEMPTYTUPLEプロパティ句を単独で使用します。

入力セットが指定されている場合、NonEmptySubset MDX関数では、すべてのタプルが空でないと評価される入力セットのサブセットが戻されます。空でないことの確認に対してオプションの値式を指定できます。この関数は、空でない組合せのセットが小規模であることがわかっている大規模なセットに基づいたクエリーを最適化する際に役立つことがあります。NonEmptySubsetを使用すると、メトリックが存在する場合にセットのサイズが減少します; たとえば、特定のUnitsに対して空でない子孫のサブセットを要求できます。

詳細および例は、『Oracle Essbaseテクニカル・リファレンス』のMDXに関する項を参照してください。

MDXクエリーでの代替変数の使用

代替変数は、定期的に変化する情報のグローバル・プレースホルダとして機能します。Administration Services、MaxLまたはESSCMDを使用して、代替変数をサーバーに設定し、各変数に値を割り当てます。その値はいつでも変更できます。代替変数を設定するにはデータベース・マネージャ以上の役割が必要です。代替変数の使用を参照してください。

  MDX式で代替変数を使用する場合は、次の点に注意してください:

  • 代替変数は、クエリーを実行するアプリケーションやデータベースからアクセス可能でなければなりません。

  • 代替変数は、名前と値の2つのコンポーネントで構成されます。

  • 変数名は英数字の組合せであり、最大サイズは制限。で指定されています。MDXで使用する代替変数名にスペース、句読点、大カッコ([ ])を使用しないでください。

  • 式の中では変数を使用する場所で、変数名の前にアンパサンド(&)を付けて指定します。たとえば、サーバー上に設定した代替変数の名前がCurMonthである場合は、MDX式では、&CurMonthと指定します。

  • 取得を実行すると、Essbaseによって変数名が代替値に置換されて、MDX式ではこの値が使用されます。

たとえば、次のように変数名CurQtrの先頭に&を付けた式を作成します:

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

この式を実行するときは、変数名が現在の値(Qtr1)に置換されるので、次の式が実行されます:

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

プロパティのクエリー

MDXでは、プロパティでデータとメタデータの特性を記述します。MDXでは、プロパティを使用してデータを取得および分析するクエリーを作成できます。プロパティは、固有かカスタムかのいずれかです。

組込みプロパティは、すべての次元のメンバーに対して定義されます。Essbaseのデータベース・アウトラインのすべてのメンバーに対して定義されている組込みメンバー・プロパティは、MEMBER_NAME、MEMBER_ALIAS、LEVEL_NUMBERおよびGEN_NUMBERです。

EssbaseのMDXでは、属性プロパティとUDAプロパティという2種類のカスタム・プロパティをサポートします。属性プロパティは、アウトラインの属性次元によって定義されます。Sample.Basicデータベースのパッケージ・タイプ属性次元は、製品次元のメンバーの容器特性を表しています。この情報は、プロパティ名[Pkg Type]を使用してMDXでクエリーできます。

属性プロパティは、特定の次元に対してのみ、および各次元の特定のレベルに対してのみ定義されます。たとえば、Sample.Basicアウトラインでは、[Ounces]は、製品次元のメンバーに対してのみ定義された属性プロパティで、このプロパティは、製品次元のレベル0メンバーにのみ有効な値です。[Ounces]プロパティは、市場などの他の次元にはありません。製品次元の非レベル0メンバーの[Ounces]プロパティは、NULL値です。アウトラインの属性プロパティは、そのアウトラインにある属性次元の名前によって識別されます。

カスタム・プロパティにはUDAも含まれます。たとえば、[Major Market]は、市場次元のメンバーに対して定義されたUDAプロパティです。[Major Market] UDAがメンバーに対して定義されている場合はTRUE値が戻され、それ以外の場合はFALSEが戻されます。

メンバー・プロパティのクエリー

MDXクエリー内部でプロパティを使用するには、2つの方法があります。

  • 軸セットごとに次元とプロパティの組合せをリストできます。クエリーを実行すると、指定したプロパティが、指定した次元のすべてのメンバーに対して評価され、結果セットに追加されます。

    たとえば、次のクエリーによって、列軸では市場次元のすべてのメンバーのGEN_NUMBER情報が戻されます。行軸では、クエリーによって各製品次元メンバーに対して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セクションを使用してメンバー・プロパティをクエリーするときに、プロパティは、次元名とプロパティ名によって識別するか、プロパティ名のみを使用して識別できます。プロパティ名が単独で使用される場合、そのプロパティが適用される、対象軸上のすべての次元からのすべてのメンバーに対してプロパティ情報が戻されます。次のクエリーでは、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")
           xDimension Properties
           [Caffeinated], [Pkg Type] on columns
    FROM Sample.Basic
             

    次のクエリーでは、UDA [Major Market]を使用し、現在のMarketが主要な市場であるかどうかに基づいて値[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関数を使用して、比較する前に日付文字列を数値に変換します。

次のクエリーでは、2007年3月25日に発売された製品次元のすべてのメンバーが戻されます。プロパティ[Intro Date]は日付型であるため、比較を行う前にTODATE関数を使用して、日付の文字列「03-25-2007」を数値に変換する必要があります。

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

プロパティを値式で使用する場合は、その値の型(文字列、数値またはブール)に基づいて適切にプロパティを使用する必要があります。

数値の範囲を持つ属性次元をクエリーすることもできます。

次のクエリーでは、Small、MediumおよびLargeという人口範囲のSalesデータが取得されます。

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

値式で属性をプロパティとして使用する場合は、範囲メンバーを使用して、メンバーのプロパティ値が特定の範囲内であるかどうかを確認できます。この場合はIN演算子を使用します。

たとえば、次のクエリーでは、人口範囲がMediumである市場次元のすべてのメンバーが戻されます:

      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
   

年次元のどのメンバーにも別名が定義されていません。したがって、クエリーでは、年次元のメンバーのMEMBER_ALIASプロパティについてNULL値が戻されます。

属性プロパティは、特定の次元のメンバーおよびその次元の特定のレベルに対して定義されます。Sample.Basicデータベースでは、[Ounces]プロパティは、製品次元のレベル0メンバーに対してのみ定義されています。

したがって、次のクエリーに示すように、市場次元のメンバーの[Ounces]プロパティをクエリーすると、構文エラーとなります:

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

その次元の非レベル0メンバーの[Ounces]プロパティをクエリーすると、NULL値が戻されます。

値式でプロパティ値を使用する場合、関数IsValid()を使用して、NULL値があるかどうかを確認できます。次のクエリーでは、NULL値を持つメンバーが削除されてから、[Ounces]プロパティ値12を持つ製品次元のすべてのメンバーが戻されます。

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