SQL Server 2008 Analysis Services における MDX のパフォーマンスの向上
このバージョンの Analysis Services では、多次元式 (MDX) 計算の実行パフォーマンスの向上に重点が置かれており、そのためにエンジンのアーキテクチャにいくつかの重要な変更が加えられています。ただし、このようなパフォーマンスの向上を得るには MDX コードを最適化する必要があります。
このドキュメントでは、既存の MDX コードのどの部分でパフォーマンスの向上を妨げられる可能性が生じるのかを説明し、新しい MDX コードを作成する際にそのような問題を回避するためのアドバイスを提供します。このドキュメントには、パフォーマンスが向上する関数の一覧も含まれています。
MDX のパフォーマンスを最大限に高めるためのコードの見直し
コードを見直す際には、以下に示すシナリオやコーディング状況を避けるようにしてください。これらは、SQL Server 2008 Analysis Services (SSAS) で期待される MDX ステートメントのパフォーマンスの向上を妨げる可能性があります。ただし、コードを変更してこれらの状況を避けるのが現実的に無理な場合でも、SQL Server 2005 Analysis Services (SSAS) と同レベルのパフォーマンスは得られます。
用語の定義
領域
式の評価対象となるセルのセット。
任意図形
複数のセットのクロス結合として表現できない領域。たとえば、領域 {(Drink, USA), (Food, Canada)} は、{Drink, Food} * {USA, Canada} = {(Drink, USA), (Drink, Canada), (Food, USA), (Food, Canada)} のクロス結合のサブセットなので、任意図形を表します。
静的な式
式が計算される領域で変化しない場合、その式は静的な式と呼ばれます。
たとえば、CrossJoin(Product.Members, Customer.Members) の領域では、次の式は変化しません。
1 (定数式)
Product.Members.Count
動的な式
計算される領域のセルごとに異なる値に解決される式は、動的な式と呼ばれます。
たとえば、CrossJoin(Product.Members, Customer.Members) の領域では、次の式は動的です。
- Sales (Sales はメジャーであるため、その値は領域のセルごとに異なります)
変化する属性
変化する属性は式が評価される方法を左右し、式がそれに依存するようになります。たとえば、式 Customer.Geography.CurrentMember は、Geography 階層の属性に依存します。
通常は、変化する属性により、式が評価される領域が縮小されます。たとえば、次のような式があるとします。
with member measures.x as Customers.Geography.currentmember.uniquename
Select Customers.Geography.City.members on 0,
Product.members on 1
From sales
Where measures.x
この式で、Customers.Geography は静的な式です。currentmember 関数は、City 属性への依存関係を導入しているため、変化する属性です。Uniquename の場合は、currentmember に 1:1 のリレーションシップでバインドされているため、変化する属性は追加されません。したがって、uniquename は各顧客について 1 回だけ評価され、Product ごとに繰り返し評価されることはありません。その結果、式全体の領域が、変化する属性によって事実上縮小されたことになります。
セルの値以外のプロパティでの式の使用
セルの値以外のプロパティの値を割り当てるために使用されている MDX 式では、パフォーマンスの向上は得られません。パフォーマンスは SQL Server 2005 Analysis Services (SSAS) と同じレベルにとどまります。
ドキュメントに記載されていない関数の使用
このドキュメントに記載されていない関数が MDX コードで使用されていると、新しいバージョンで期待されるパフォーマンスの向上が得られません。このドキュメントの「Functions with enhanced performance」を参照してください。
セルのセキュリティの使用
セルのセキュリティが定義されている領域に対して MDX 式を評価すると、コードのパフォーマンスの向上が妨げられます。
セルのセキュリティとパフォーマンスの関係を次の表に示します。
セルのセキュリティ |
予想されるパフォーマンス |
|---|---|
なし |
高 |
Read |
中 |
Read-Contingent |
低 |
「MDX 式を使用したセル データ権限の設定」および「セル データへのカスタム アクセス権の付与」を参照してください。
動的な次元の使用
MDX コードで動的な次元の式が使用されていると、コードのパフォーマンスの向上が妨げられます。たとえば、Sum( IIF( Sales > 10000, h1.Members, h2.Members)) のような式では、Sales 式の評価に応じてコードで合計されるメンバが変わるため、パフォーマンスの向上は得られません。同様に、Account 属性の現在のメンバに属する属性に応じて Calendar Year 階層のメンバか Fiscal Year 階層のメンバを使用して、並列期間の同等の値を比較する必要があるシナリオでも、パフォーマンスの向上は得られません。このシナリオに必要な MDX 式は、たとえば次のサンプル コードのようになります。
ParallelPeriod(Iif( Account.CurrentMember.Properties("UsesFiscalCalendar")="Y", FiscalTime, CalendarTime).CurrentMember)
この場合も、Account ディメンションの現在のメンバが変化するとディメンションが動的に変化します。
動的なパラメータの使用
MDX コードで動的なパラメータが使用されていると、コードのパフォーマンスの向上が妨げられます。たとえば、KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) のような式は、計算されるセルによって変化しますが、式 KpiGoal("Sales_" & Cstr(Year(Now))) は変化しません。
.gif "重要な注意事項") 重要 |
|---|
式 KpiGoal("Sales_" & [Fiscal Year].currentmember.UniqueName) が、計算される領域全体で同じ値に評価される場合もありますが、それでも、期待されるパフォーマンスの向上は得られません。 |
動的なメンバ参照
MDX コードで動的なメンバ参照が使用されていると、コードのパフォーマンスの向上が妨げられます。たとえば、次のような式があったとします。
(IIF( e, mbr1, mbr2), Sales)
この式では、実行時に IIF() 式が評価されるまで結果の組はわかりません。ただし、次の等価の式では事情が変わります。
IIF( e, (mbr1, Sales), (mbr1, Sales))
この式の結果の組はいずれも、式 e が評価される前にわかります。
プラン ヒント
プラン ヒントは、式の評価方法をエンジンに対して指定するための MDX 言語の拡張機能です。このバージョンの Analysis Services では、IIF(,,) 関数に対するプラン ヒントのみが導入されています。
プラン ヒントは、MDX 式で指定することも、サーバー構成プロパティでグローバルに構成することもできます。
IIF() 関数のプラン ヒント
IIF(,,) では、次の構文を使用して式のヒントを指定します。
IIF(<cond>, <expr>, <expr>) [HINT <hints>]
<expr> ::= <expr> [HINT <hints>]
<hints> ::= <hint> [<hints>]
<hint> ::= EAGER | STRICT | LAZY
EAGER を指定すると、式が IIF サブスペース全体で評価されます。
STRICT を指定すると、条件式の結果に基づくサブスペースのみで式が評価されます。
LAZY を指定すると、式がセル単位で評価されます。
ヒントで EAGER と STRICT を同時に使用することはできません。同じ IIF(,,) の別の式で使用することはできます。
構文例 :
IIF([Measures].[Internet Sales Amount]=0
, {([Date].[Calendar Year].CURRENTMEMBER, [Customer].[Country].[All Customers])} HINT EAGER
, {{[Date].[Calendar Year].CURRENTMEMBER} * [Customer].[Country].[Country].MEMBERS} STRICT LAZY
)
構成プロパティのプラン ヒント
プラン ヒントをサポートするために、OLAP\Query に次の構成プロパティが導入されています。
プロパティ名 |
有効値 |
説明 |
IIFThenMode |
0 | 1 | 2 |
0 : ヒントなし (既定値) 1 : EAGER 2 : STRICT |
IIFElseMode |
0 | 1 | 2 |
0 : ヒントなし (既定値) 1 : EAGER 2 : STRICT |
LazyEnabled |
0 | 1 |
0 : 無効 (既定値) 1 : 有効 |
ユーザー定義ストアド プロシージャ (COM または .NET)
MDX コードでユーザー定義ストアド プロシージャが使用されていると、コードのパフォーマンスの向上が妨げられます。
.gif "注意") 注意 |
|---|
SQL Server 2008 Analysis Services (SSAS) には、パフォーマンス向上のために最適化されたストアド プロシージャが用意されています。 |
パラメータでの名前付きセットやセットの別名の使用
MDX コードで、Sum、Min、Max、Avg、Aggregate などの関数の最初のパラメータとして名前付きセットやセットの別名が使用されていると、コードでパフォーマンスの向上が得られません。
たとえば次の MDX 式は、複数の子を持つメンバをカウントします。
Sum(h.members as S, Iif(S.Current.Children.Count > 1, 1, 0))
このコードでは、h.members の別名として S が指定され、そのセットの別名を使用して Current 関数の値が取得されているため、パフォーマンスの向上が妨げられます。
次のコードは、この状況のもう 1 つの一般的な例を示しています。
WITH
SET [Core Products] AS '{[Product].[Category].[Bikes]}'
MEMBER [Measures].[Core Products Sales] AS SUM([Core Products], [Measures].[Internet Average Unit Price] * [Measures].[Internet Order Quantity])
Select [Measures].[Core Products Sales] on 0
From [Adventure Works]
メンバ定義の SUM 関数は名前付きセットに基づいているため、期待されるパフォーマンスの向上は得られません。
カスタム ロールアップ式での遅延バインドの使用
カスタム ロールアップ式が、計算されるメンバや、実行時に評価されるその他の MDX 式を参照していると、パフォーマンスの向上が妨げられます。
スクリプトでの前方参照の使用
MDX コードで、別のステートメントにある前方の定義の参照を作成すると、コードでパフォーマンスの向上が得られなくなります。たとえば次の MDX スクリプトでは、X の定義の中で Y に対する前方参照が作成されています。
Create Member X as Y * 2;
…
Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);
この状況を修正するには、次のように、Y の定義を X の定義の前に置きます。
Create Member Y as ( Sales, [Date].[Calendar].[Month].PreviousMember);
Create Member X as Y * 2;
…
パフォーマンスが向上した関数
スカラ関数
以下は、パフォーマンスの向上を期待できるスカラ関数の一覧です。この一覧の最初の列にはスカラ演算子が含まれています。
- |
OR |
KEY |
* |
XOR |
LEVELS.COUNT |
/ |
CALCULATIONPASSVALUE |
MEMBERTOSTR |
+ |
CASE |
MEMBERVALUE |
< |
COALESCEEMPTY |
NAME |
<= |
HIERARCHIES.COUNT |
ORDINAL |
<> |
ID |
PROPERTIES |
= |
IIF |
UNIQUENAME |
> |
IS |
USERNAME |
>= |
ISANCESTOR |
VALIDMEASURE |
unary minus |
ISEMPTY |
VALUE |
NOT |
ISLEAF |
|
AND |
ISSIBLING |
|
| 注意 |
|---|
ユーザー定義ストアド プロシージャでは、COM コードかマネージ コードかに関係なく、SQL Server 2005 Analysis Services (SSAS) に比べてパフォーマンスは向上しません。詳細については、前の「ユーザー定義ストアド プロシージャ (COM または .NET)」を参照してください。定数式では、リテラルか数値かに関係なく、パフォーマンスが向上します。 |
メンバ関数
以下は、パフォーマンスの向上を期待できるメンバ関数の一覧です。
.CurrentMember |
.FirstSibling |
.LastSibling |
.DataMember |
.Item |
.Lead |
.DefaultMember |
.Lag |
.Parent |
.FirstChild |
.LastChild |
.UnknownMember |
Ancestor |
KPIStatus |
NextMember |
Ancestors |
KPITrend |
OpeningPeriod |
Ascendants |
KPIValue |
ParallelPeriod |
ClosingPeriod |
KPIWeight |
PrevMember |
Cousin |
LastPeriods |
StrToMember(<String Expression>, CONSTRAINED) |
KPIGoal |
LinkMember |
|
| 注意 |
|---|
StrToMember(<String Expression>, CONSTRAINED) では、<String Expression> が静的な式の場合に最大限のパフォーマンスが得られます。 |
集合関数
以下は、パフォーマンスの向上を期待できる集合関数の一覧です。
Aggregate |
Max |
Sum |
Avg |
Min |
|
ただし、これらの関数を使用する際には、以下の関数の任意の組み合わせが使用されている式を最初のパラメータとして使用する必要があります。
- (差集合演算子) |
.Children |
MTD |
(<set expression>,(<set expression>, …,(<set expression>) (クロス積演算子) |
.Members |
PeriodsToDate |
* (クロス積演算子) |
.Siblings |
QTD |
: (範囲演算子) |
AddCalculatedMembers |
StrToSet(<String Expression>, CONSTRAINED) |
+ (和集合演算子) |
Crossjoin(<set expression>,(<set expression>, …,(<set expression>) |
Tail |
|
Descendants |
Union |
|
Distinct |
Unorder |
|
Except |
WTD |
|
Hierarchize |
YTD |
|
Intersect |
|
| 注意 |
|---|
静的セット (空のセットを含む) でも、期待されるパフォーマンスの向上が得られます。 |
VBA 関数
以下は、パフォーマンスの向上を期待できる VBA 関数の一覧です。
Abs |
CLng |
Len |
CDate |
CStr |
Now |
CDbl |
Int |
Right |
CInt |
Left |
Round |
以下の VBA 関数では、変化する属性に対して評価された場合にパフォーマンスの向上を期待できます。
Asc |
Format |
Sgn |
AscW |
FV |
Sin |
Atn |
Hex |
SLN |
Cbool |
Hour |
Space |
Cbyte |
Ipmt |
Sqr |
Ccur |
Lcase |
Str |
Cdec |
Log |
StrComp |
Chr |
Ltrim |
StrConv |
ChrW |
Minute |
String |
Cos |
Month |
SYD |
CSng |
Nper |
Tan |
Cvar |
Oct |
Timer |
Date |
Partition |
TimeSerial |
DateAdd |
Pmt |
TimeValue |
DateDiff |
PPmt |
Trim |
DatePart |
PV |
TypeName |
DateSerial |
QBColor |
Ucase |
DateValue |
Rate |
Val |
Day |
RBG |
Weekday |
DDB |
Rnd |
Year |
Exp |
Rtrim |
|
Fix |
Second |
|