計算メジャーおよび計算メンバの作成
ここでは、Business Intelligence で計算メジャーと計算メンバを作成して使用する方法を説明します。
キューブには、あらゆるクエリで使用できる追加の計算メンバが含まれることがあります。"InterSystems Business Intelligence のモデルの定義" を参照してください。
"BI サンプルのアクセス方法" も参照してください。
計算メジャーおよび計算メンバの概要
MDX では、計算メンバを作成できます。この計算メンバは、他のメンバに基づくメンバです。メジャーとメジャー以外の 2 種類の計算メンバを定義できます (メジャーが、MEASURES ディメンジョンのメンバと見なされることを忘れないでください)。
-
計算メジャーは、他のメジャーに基づきます。例えば、あるメジャーは第 3 のメジャーで除算した第 2 のメジャーとして定義できます。
計算メジャーという語句は、MDX の標準的な語句ではありません。このドキュメントでは、わかりやすくするためにこの語句を使用します。
-
一般に、非メジャーの計算メンバは、他の非メジャーのメンバと結合します。他の非メジャーのメンバと同様、この計算メンバはファクト・テーブル内のレコードのグループです。
例えば、メンバ A がファクト・テーブル内の 150 個のレコードを参照し、メンバ B がファクト・テーブル内の 300 個のレコードを参照するとします。また、A と B を結合させるメンバ C を作成するとします。そうすると、メンバ C は、ファクト・テーブル内の該当する 450 個のレコードを参照します。
計算メンバの作成
クエリ内で 1 つ以上の計算メンバを作成するには、以下のような構文を使用します。
WITH with_clause1 with_clause2 ... SELECT query_details
節と節の間にコンマを入れないことに注意してください。
以下はその説明です。
-
各式 with_clause1、with_clause2 (以降同様) の構文は以下のとおりです。
MEMBER MEASURES.[new_measure_name] AS 'value_expression'
このページの後続のセクションでは、value_expression について説明します。
-
query_details は MDX クエリです。
このようにすると、他のメンバを使用できるあらゆる場所で、名前を指定してその計算メンバを参照することができます。
以下はその例です。
WITH MEMBER MEASURES.avgage AS 'MEASURES.[age]/MEASURES.[%COUNT]'
SELECT MEASURES.avgage ON 0, diagd.diagnoses.members ON 1 FROM demomdx
avgage
1 None 33.24
2 asthma 34.79
3 CHD 67.49
4 diabetes 57.24
5 osteoporosis 79.46
この計算メンバは、クエリを範囲とする計算メンバであり、その範囲はクエリです。 セッションを範囲とする計算メンバの詳細は、"InterSystems MDX リファレンス" の "CREATE MEMBER 文" を参照してください。
計算メジャーの MDX レシピ
このセクションでは、広く必要とされるいくつかの計算メジャーの MDX 式を作成する方法について説明します。
他のメジャーの組み合わせ
計算メジャーでは、値式の形式は、メジャー式を組み合わせた数式であることがよくあります。以下はその例です。
(MEASURES.[measure A] + MEASURES.[measure B]) * 100
または、以下のようになります。
(MEASURES.[measure A] + MEASURES.[measure B])/MEASURES.[measure C]
さらに正式には、この式では以下の要素を使用できます。
-
メジャーへの参照。
-
数値リテラル。例えば、37 です。
-
パーセンテージ・リテラル。例えば、10% です。
数値とパーセント記号の間にスペースを入れてはいけません。
-
算術演算子。InterSystems IRIS Business Intelligence では、+ (加算)、- (減算)、/ (除算)、および * (乗算) の標準的な算術演算子がサポートされます。また、+ (正)、および - (負) の標準単項演算子もサポートされます。
優先順位を制御する括弧も使用できます。
例えば、MEASURES.[%COUNT] / 100 です。
-
AVG、MAX、COUNT など、数値を返す MDX 関数。
既に説明した関数に加えて、Business Intelligence では、いくつかのスカラ関数 (SQRT、LOG、および POWER) もサポートされます。
MDX 関数 IIF は、このような式でよく役に立ちます。これは、条件を評価して、条件に応じて 2 つの値のいずれかを返します。この関数を使用すると、例えば、0 で除算することがなくなります。
集約値のパーセンテージ
合計レコード数のパーセンテージや他の集約値のパーセンテージの計算が必要な場合も多くあります。このような場合は、インターシステムズによる拡張機能の %MDX 関数を使用できます。この関数は、単一の値を返す MDX クエリを実行し、関数を実行したコンテキストの影響を受けない値を返します。このため、以下のような値式で定義されたメジャーを使用してパーセンテージを計算できます。
100 * MEASURES.[measure A] / %MDX("SELECT FROM mycube")
例えば、以下のように表示されます。
WITH MEMBER MEASURES.PercentOfAll AS '100 * MEASURES.[%COUNT]/%MDX("SELECT FROM demomdx")'
SELECT MEASURES.PercentOfAll ON 0, diagd.MEMBERS ON 1 FROM demomdx
PercentOfAll
1 None 84.56
2 asthma 6.85
3 CHD 3.18
4 diabetes 4.89
5 osteoporosis 2.21
個別のメンバ数
場合によっては、特定のセルに対して、特定のレベルの個別のメンバ数のカウントが必要になります。例えば、DocD ディメンジョンにレベル Doctor が含まれているとします。この場合、指定した一連の患者の一次診療医である一意の医者の数をカウントすることが可能です。そのためには、以下の value_expression を使用する計算メジャーを定義します。
COUNT([docd].[h1].[doctor].MEMBERS,EXCLUDEEMPTY)
このメジャーは、クエリで以下のように使用できます。
WITH MEMBER MEASURES.[distinct doctor count] AS 'COUNT(docd.doctor.MEMBERS,EXCLUDEEMPTY)'
SELECT MEASURES.[distinct doctor count] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
distinct doctor co
1 0 to 9 38
2 10 to 19 38
3 20 to 29 38
4 30 to 39 40
5 40 to 49 41
6 50 to 59 40
7 60 to 69 33
8 70 to 79 31
9 80+ 28
準加法メジャー
準加法メジャーは、すべてではなくほとんどのディメンジョンを集約するメジャーです。例えば、銀行残高は特定の時点のスナップショットであるため、顧客の銀行残高を経時的に加算することはできません。このようなメジャーを作成するには、インターシステムズによる MDX への拡張機能である %LAST 関数を使用できます。
以下のメジャーについて考えてみます。
-
Balance はソース・プロパティ CurrentBalance に基づき、合計によって集約されます。
このメジャーの経時的な集計は不適切な結果が生じるため、回避する必要があります。このためこのメジャーの使用は行または列に時間レベルが含まれるピボット・テーブルのみに限定する必要があります。
-
Transactions は、ソース・プロパティ TxCount に基づき、合計によって集約されます。
LastBalance と呼ばれる計算メジャーを定義して、以下の value_expression を使用します。
%LAST(Date.Day.Members,Measures.Balance)
%LAST 関数は、指定セットの各メンバに対して評価されたメジャーの最終的な欠落のない値を返します。この場合は、値が存在する最後の日が検索され、その値が返されます。
フィルタ処理メジャー (タプル・メジャー)
通常のメジャーでは、ファクト・テーブル内でソース値が NULL でないすべてのレコードが考慮されます。状況によっては、以下のように動作するフィルタ処理メジャーの定義が必要になります。
-
特定のレコードでメジャーが NULL である。
-
その他のレコードについては、そのメジャーに値が存在する。
フィルタ処理メジャー (非公式にはタプル・メジャーとも呼ばれます) には、以下のような value_expression を使用します。
([MEASURES].[my measure],[DIMD].[HIER].[LEVEL].[member name])
この場合、value_expression は、以下のとおりのタプル式です。
-
[MEASURES].[my measure] は、基礎として使用されるメジャーです。
-
[DIMD].[HIER].[LEVEL].[member name] は、メジャー値が非 NULL のメンバです。
例えば、Avg Test Score メジャーがあるとします。これは、テストの値が NULL でないすべての患者の平均テスト・スコアです。Avg Test Score メジャーに加え、冠動脈性心疾患 (CHD 診断) を持つ患者の平均テスト・スコアのみを示す列も表示する必要があるとします。このため、メジャー Avg Test Score - CHD が必要になります。この場合、以下の value_expression を指定した計算メジャーを作成できます。
(MEASURES.[avg test score],diagd.h1.diagnoses.chd)
例えば、以下のように表示されます。
WITH MEMBER MEASURES.[avg test score - chd] AS
'(MEASURES.[avg test score],diagd.h1.diagnoses.chd)'
SELECT MEASURES.[avg test score - chd] ON 0, aged.[age bucket].MEMBERS ON 1 FROM demomdx
avg test score - c
1 0 to 9 *
2 10 to 19 *
3 20 to 29 *
4 30 to 39 *
5 40 to 49 78.00
6 50 to 59 75.75
7 60 to 69 80.71
8 70 to 79 83.33
9 80+ 55.25
別の期間のメジャー
多くの場合、以前の期間の指定のメジャーの値を表示しながら、以降の期間を表示すると便利です。例えば、UnitsSoldPreviousPeriod という名前の計算メジャーを定義して、以下の value_expression を使用できます。
([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])
このメジャーは、その定義方法により、クエリの他の軸に DateOfSale ディメンジョンを使用する場合にのみ意味があります。以下はその例です。
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold DateOfSale
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
...
2 番目の列のキャプションが、定義した計算メンバの名前ではなく値式内で使用されるディメンジョンに基づいていることに注意してください。%LABEL 関数を使用すると、より適したキャプションを指定できます。以下はその例です。
WITH MEMBER [MEASURES].[UnitsSoldPreviousPeriod] AS
'([DateOfsale].[Actual].CurrentMember.PrevMember ,MEASURES.[units sold])'
SELECT {[Measures].[Units Sold],%LABEL([MEASURES].[UNITSSOLDPREVIOUSPERIOD],"Units (Prv Pd)","")} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HoleFoods]
Units Sold Units (Prv Pd)
1 Jan-2009 15 *
2 Feb-2009 10 15
3 Mar-2009 13 10
4 Apr-2009 15 13
5 May-2009 22 15
6 Jun-2009 17 22
7 Jul-2009 24 17
8 Aug-2009 30 24
...
これらの例は、時間ベースのレベルを使用します。これは、この種の分析は時間レベルに対して一般的であるためです。ただし、同じ手法をデータ・レベルに対しても使用できます。
他のセルを参照するメジャー
多くの場合、ピボット・テーブルの別のセルの値を参照すると便利です。このためには、%CELL 関数および %CELLZERO 関数を使用できます。これらの関数はそれぞれ、ピボット・テーブルの別のセルの値を位置別に返します。指定された呼び出しに値がない場合、%CELL は NULL を返します。これに対して、%CELLZERO はゼロを返します。
これらの関数には多くの用途があります。1 つの例として、ある時点までの合計を計算するために %CELL を使用できます (この例では、インチ単位の積算降雨量です)。
SELECT {MEASURES.[Rainfall Inches],%CELL(-1,0)+%CELL(0,-1)} ON 0, {dated.year.1960:1970} ON 1 FROM cityrainfall
Rainfall Inches Expression
1 1960 177.83 177.83
2 1961 173.42 351.25
3 1962 168.11 519.36
4 1963 188.30 707.66
5 1964 167.58 875.24
6 1965 175.23 1,050.47
7 1966 182.50 1,232.97
8 1967 154.44 1,387.41
9 1968 163.97 1,551.38
10 1969 184.84 1,736.22
11 1970 178.31 1,914.53
メジャー以外の計算メンバの MDX レシピ
ここでは、一般的なシナリオのいくつかにおけるメジャー以外の計算メンバのレシピについて説明します。
年齢メンバの定義
年齢別にレコードをグループ化したメンバを用意すると役に立つことが普通です。このようなメンバを定義するには、既存の時間レベルと専用の NOW メンバを使用します。 例えば、HoleFoods サンプルの、MonthSold レベルを考えてみましょう。以下の value_expression を使用して、3 Months Ago という名前の計算メンバを定義することができます。
[dateofsale].[actual].[monthsold].[now-3]
例えば、以下のように表示されます。
WITH MEMBER CalcD.[3 months ago] as '[dateofsale].[actual].[monthsold].[now-3]'
SELECT calcd.[3 months ago] ON 0, {MEASURES.[units sold], MEASURES.target} ON1 FROM holefoods
3 months ago
1 Units Sold 37
2 Target 254.00
ハードコードされたメンバの組み合わせの定義
多くの場合、同じレベルの複数のメンバを結合する広い範囲のグループ化を定義すると便利です。そのためには、以下の形式の value_expression を持つ、メジャー以外の計算メンバを作成します。
%OR({member_expression, member_expression,...})
例えば、以下のように作成できます。
%OR({colord.red,colord.blue,colord.yellow})
メジャー以外のメンバのそれぞれが一連のレコードを参照します。%OR 関数を使用するメンバを作成する際は、そのコンポーネント・メンバが使用するすべてのレコードを参照するメンバを新規作成します。
以下はその例です。
WITH MEMBER CalcD.[primary colors] as '%OR({colord.red,colord.blue,colord.yellow})'
SELECT calcd.[primary colors] ON 0,
{MEASURES.[%COUNT], MEASURES.[avg test score]} ON 1 FROM demomdx
条件リストにより定義されるメンバの組み合わせの定義
用語リストにより、プログラミングせずに Business Intelligence モデルをカスタマイズする方法が提供されます。用語リストは、キーと値のペアの単純なリスト (ただし、拡張可能) です。用語リストは複数の方法で使用できます。1 つはメンバ・セットを作成する方法で、一般的にフィルタで使用します。
この場合、%TERMLIST 関数と %OR 関数を使用します。以下の形式の value_expression を持つ、メジャー以外の計算メンバを作成します。
%OR(%TERMLIST(term_list_name))
term_list_name は、用語リストの名前に評価される文字列です。
以下はその例です。
%OR(%TERMLIST("My Term List"))
この式は、用語リストで示されたメンバに属するすべてのレコードを参照します (%OR は複数のメンバを単一のメンバに結合することを思い出してください)。
%TERMLIST 関数には、オプションの 2 番目の引数があります。"EXCLUDE" をこの引数に指定すると、関数はそのレベルで用語リストにないすべてのメンバのセットを返します。
日付範囲の集約
%OR によって集約されるメンバの範囲を使用する形式も便利です。
%OR(member_expression_1:member_expression_n)
式 member_expression_1:member_expression_n は、member_expression_1 から member_expression_n までのすべてのメンバを返します (範囲の端点も含みます)。この形式を使用すると、日付範囲を簡潔な形式で表現できるため、特に時間レベルで役立ちます。
時間レベルでは、特殊な NOW メンバも使用できます。以下の式は、90 日前から本日までの売上レコードを集計します。
%OR(DateOfSale.DaySold.[NOW-90]:DateOfSale.DaySold.[NOW])
または、これに相当する以下の形式を使用します。
%OR(DateOfSale.DaySold.[NOW-90]:[NOW])
日付範囲の取得には、PERIODSTODATE 関数も使用できます。例えば、以下の式は、現在の年の初めから本日までの日付範囲を取得し、これらの日数を集約します。
%OR(PERIODSTODATE(DateOfSale.YearSold,DateOfSale.DaySold.[NOW]))
他のメンバの共通部分としてのメンバの定義
場合によっては (特にフィルタを定義する場合)、メンバの共通部分であるメンバを定義すると便利です。以下のような場合 (リテラル構文を表示しない) にフィルタが必要だとします。
Status = "discharged" and ERvisit = "yes" and PatientClass="infant"
また、このフィルタを多くの場所で使用する必要があるとします。
フィルタ式を繰り返し定義するのではなく、計算メンバを定義して使用できます。この計算メンバに、以下のような式を指定します。
%OR({member_expression,member_expression,...}
以下はその例です。
%OR({birthd.year.NOW,allersevd.[003 LIFE-THREATENING]}
式 (birthd.year.NOW,allersevd.[003 LIFE-THREATENING]) はタプル式で、メンバ birthd.year.NOW とメンバ allersevd.[003 LIFE-THREATENING] の共通部分、つまり今年生まれた、生死にかかわるアレルギーを持つすべての患者を示します。