2011-05-01

DimensionAttribute の KeyColumns のカラムに NOT NUL 制約が付いていない場合の対応

DimensionAttribute の KeyColumns として設定したカラムに NOT NUL 制約が付いていない場合、
（かつ、データ型が文字型である場合）
（かつ、NullProcessing プロパティが既定値の Automatic である場合）
値として空文字とNULL値が存在する（可能性がある）ため、処理エラーが起きます。
"a duplicate key error has been found when processing ..."

対応方法は
(a)そもそもテーブルカラムに NOT NULL 制約を付けておく
(b)KeyColumns の NullProcessing プロパティを Presreve にする
(c)ErrorConfiguration プロパティをいじる
などが考えられます。

まあ、ここを読んでおけば問題を解消できるはずですね。
http://msdn.microsoft.com/ja-jp/library/ms345138(v=sql.90).aspx

同様に、
DimensionAttribute の KeyColumns として設定したカラムに NOT NUL 制約が付いていない場合、
（かつ、データ型が数値型である場合）
（かつ、NullProcessing プロパティが既定値の Automatic である場合）
値としてゼロとNULL値が存在する（可能性がある）ため、処理エラーが起きます。
以下同文です。

（追記）
(d)データソースビューで NULL値を回避する。ただし(a)の方がまし。

2011-04-23

SSASのドリルスルーアクションの問題点

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スクリプトで上書きすれば実装可能、との情報を得ました。なんとかなるもんだ。

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

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

2011-04-23

SQL Server のトレースフラグってたくさんある（のかも）

SQLTO というSQL Serverユーザーグループ組織（たぶん）とMicrosoft の共催のセミナーイベントを USTREAM で拝見させて頂きました。
Masayuki_Ozawa さんのセッションで、トレースフラグに興味が沸いたので、DBCC TRACEOFF が成功する番号を調べてみることにしました。以下がT-SQL スクリプトです。

- - - - -

SET NOCOUNT ON;

DECLARE @OKTable TABLE
(Value int
);
DECLARE @NGTable TABLE
(Value int
);
DECLARE @Value AS INT = -10;
DECLARE @MaxValue AS INT = 9999;
WHILE @Value < @MaxValue
BEGIN
BEGIN TRY
DBCC TRACEOFF(@Value)
--DBCC TRACEON(@Value)
INSERT INTO @OKTable ([Value]) VALUES (@Value);
END TRY
BEGIN CATCH
INSERT INTO @NGTable ([Value]) VALUES (@Value);
END CATCH
SET @Value = @Value + 1;
END

SELECT * FROM @OKTable;
SELECT COUNT(*) FROM @NGTable;

SELECT @@VERSION;

- - - - -

結果：
マイナス1から9300まで、（ゼロを含んで）9302回、成功しました。
ちなみに @@VERSION の結果は以下のとおりです。
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

■MSDN ライブラリ「トレースフラグ (Transact-SQL)」
http://msdn.microsoft.com/ja-jp/library/ms188396.aspx

公開されているトレースフラグはこの13個なのでしょうか...
公開されていないトレースフラグ（undocumented trace flag）がたくさんあるようで、上記セミナーイベントでも T1117、T1118、T3604、T3615 あたりが紹介されていましたし、"undocumented trace flag sql server"とかでググるといろいろ出てきますね。

まあ仕事上ではほとんど使う機会はないはずなのでこれ以上は調べません...

2010-04-08

有効期限を伸ばした PowerPivot for Excel 2010 November CTP

■PowerPivot Team Blog「Fix for PowerPivot Expiration Released」
http://blogs.msdn.com/powerpivot/archive/2010/04/07/fix-for-powerpivot-expiration-released.aspx

有効期限を伸ばした PowerPivot for Excel 2010 November CTP がリリースされました。入れ替えないと。

2010-04-04

PowerPivot for Excel 2010 November CTP が有効期限切れで動作しない...

■PowerPivot Team Blog「PowerPivot for Excel 2010 November CTP Expired」
http://blogs.msdn.com/powerpivot/archive/2010/04/02/powerpivot-for-excel-2010-november-ctp-expired.aspx

PowerPivot for Excel 2010 November CTP が期限切れで動作しなくなりました。PowerPivot Team Blog で謝罪投稿があり、このブログを見ておけば期限切れを起こさないインストールプログラムの配布が案内される模様です。

2010-03-31

SSRS 2008 R2 レポートモデル生成

（つぶやき程度のメモです）

SSRS 2008 R2 で、レポートマネージャ上でデータソース設定からレポートモデルを生成して、それを Reortbuilder 3.0 でデータソースとして読み込む、というのは新機能ですよね...

Reortbuilder 3.0 は Reortbuilder 2.0 に地図レポート、共有レポートパーツ&データソース、スパークラインなどの表現を対応したくらいだろ...と思ってたのですが、Reortbuilder 1.0 を継承した仕様があるのですね！と、理解しました。

2010-03-30

ホワイトペーパー「Microsoft SQL Server PowerPivot Planning and Deployment」の草稿

ずいぶん久しぶりのメモです。PowerPivot チームブログで、気になる投稿がありました。

■PowerPivot Team Blog「PowerPivot Component Architecture」
http://blogs.msdn.com/powerpivot/archive/2010/03/22/powerpivot-component-architecture.aspx

ホワイトペーパー「Microsoft SQL Server PowerPivot Planning and Deployment」の草稿のようです。仕組みの説明があり、ありがたいです。VertiPaq という用語が気になりますが、正確に理解できていません。ここで説明されているのが VertiPaq エンジンとVertiPaq データベースで、他に
http://technet.microsoft.com/ja-jp/library/ee637273(SQL.105).aspx
http://technet.microsoft.com/ja-jp/library/ee210629(SQL.105).aspx
http://msdn.microsoft.com/ja-jp/library/ee210646(SQL.105).aspx
このあたりで SSAS の VertiPaq モードについて説明されていますがなんだかよく分かりません。
ちなみに、SQL Server 2008 R2 November CTP の「Analysis Services SharePoint 統合」でのインストールは既に行なっており、環境準備は出来ています。インストールの際に、おそらく必要なTCPポートを空けていなかったせいで何度かはまりました。たぶんSharePointの管理サイトかどこかのポートを空けておく必要があるのでしょう。私はWindows ファイアウォールを無効化してインストールしましたので正確なことはわかりませんが、皆さんはお気をつけて。