SSASのドリルスルーアクションの問題点 - nagasakit の日記： SQL Server BI メモ

SSASのドリルスルーアクションを使う機会があり、いろいろ問題に気がつきましたのでメモします。対象バージョンは SQL Sever 2008 R2 です。

・翻訳機能に対応していない
・BIDSの設定機能では項目順序の設定が不自由
・計算メジャーに対応していない

それぞれについて説明します。

●翻訳機能に対応していない
ドリルスルーアクション機能によって発行される MDX のDRILLTHROUGH ステートメントでは AS 句が付きません。
Excel 2007 や 2010 （や、その他のドリルスルーアクションに対応したクライアントツール）では、クエリ結果の項目タイトル部分がオブジェクト名そのまま（翻訳されていない）になります。
AS句を付けるだけなんですが...

●BIDSの設定画面では項目順序の設定が不自由
BIDSでの設定画面（アクションタブ）では、ドリルスルー項目の指定をディメンション単位で行わなければならないので、項目の順序にこだわりがある場合は意図どおりに設定ができません。

例えば、以下のテーブルがあるとします。
売上テーブル：「伝票番号(PK)」「顧客ID」「売上高」「売上区分」
顧客テーブル：「顧客ID(PK)」「顧客コード」「顧客名」

SSASオブジェクトとしては以下のように構成したとします。
売上ファクト：メジャー「売上高」
売上ディメンション：Key属性「伝票番号(PK)」、一般属性「売上区分」
顧客ディメンション：Key属性「顧客ID(PK)」、一般属性「顧客」（KeyColumn=顧客コード、NameColumn=顧客名）

このとき、BIDSの設定画面でのドリルスルーアクション設定では
「ディメンション」や「メジャー」のかたまりごとにまとめて項目順序を設定する必要があります。
不可能な設定例：「売上.伝票番号(PK)」「顧客.顧客」「Measure.売上高」「売上.売上区分」
　可能な設定例：「売上.伝票番号(PK)」「売上.売上区分」「顧客.顧客」「Measure.売上高」

対応策はいくつか存在します。
対応策1【設計変更】
・ファクトベースのディメンションではなく個別のディメンションに変更する。上の例では「売上区分」ディメンションを独立させる。
ただし、ファクト上にあるドリルスルー対象の項目を全てディメンションにできるかどうかは状況によります。
対応策2【管理リスク、次善？】
・BIDS上で「コードで表示」を行い、XMLで記述されている定義の順序を変更する。
この対策を行った直後にデプロイすれば正しく動作します。しかし、BIDSで該当タブを選択した状態で「保存」したりすると元に戻ってしまうことも...
対応策3【主に性能リスク】
・ファクトべースディメンションを複数設定する（マルチロールさせる）。
複数設定することで、「ディメンションのかたまり」を複数使うことができるようになります。
ただし、2つめ以降を非表示にすること忘れずに。SSASオブジェクト処理への影響がどの程度でるのかも分かりません...
対応策4【主に性能リスク】
・「項目順序のかたまり」ごとに異なるファクトべースディメンションを用意する。
対応策3の亜種ですが、対応策3の方がましです。
さらに亜種で、「ファクトベースディメンションの属性1つづつを異なるディメンションにばらす」という荒業もあります。SSASオブジェクト処理の必要時間は最長になるでしょう。
対応策5【実装の手間、だが最善】
・行セットアクションを使用して、DRILLTHROUGH ステートメントを自分で書く。
次項を参照してください。
この対応策であれば、「翻訳」「項目順序」「計算メジャー」の問題全てを解決できます。
ただし、（通常は）CodePlexの「Analysis Services Stored Procedures Project」の利用が必要になります。
「ドリルスルーアクションを使わない」ことが「ドリルスルーアクションの問題点の最善の解決策」だなんて、少し悲しいですね...

●計算メジャーに対応していない
説明が面倒なのでMosha先生のブログ記事をご確認ください。
http://sqlblog.com/blogs/mosha/archive/2008/09/01/drillthrough-on-calculated-measures.aspx

このブログ記事にある通り、この問題は2006年10月に指摘され、開発サイドも対応したい意向を示していますが...
https://connect.microsoft.com/SQLServer/feedback/details/225212/enable-drillthrough-on-calculated-measures
SQL Server 2008 R2 に間に合わなかったようですね...（いやきっと忘れてますな...）

（追記）CodePlexの「Analysis Services Stored Procedures Project」の Drillthrough 関連機能はかなり進化してますね。
http://asstoredprocedures.codeplex.com/wikipagetitle=Drillthrough&ProjectName=asstoredprocedures

（追記）行セットアクションでは、ドリルスルーアクションでの Default=true にあたる機能（Excel ピボットテーブルでは、右クリック＞追加のアクションではなくてダブルクリックで呼び出せるようになる）が無い様子なのが残念です。それと、私の環境（Excel 2010 英語版）では行セットアクションが追加のアクションに表示されないんですよね...これも心配。

（追記）書籍を読んで、リアルメジャーをMDXスクリプトで上書きすれば実装可能、との情報を得ました。なんとかなるもんだ。

⇒ちょっと話題がずれますが、
結論としては
・ディメンショナルモデリングされたキューブ＋サロゲートキー適用⇒ドリルスルーアクション
・ディメンショナルモデリングされたキューブ＋サロゲートキー不適用⇒通常のドリルスルー
・ディメンショナルモデリングされていないキューブ⇒レポートアクションに逃げる、または行セットアクションでがんばる

というところなんでしょうね。
ディメンショナルモデリングではなく、ドリルスルー中心アプローチ（笑）で設計したのに
「ドリルスルーアクション」に頼るのは間違いだと、そう理解したわけです。