メニュー

関連ページリンク

トップ > business > business - 人気ブログ(Blog)検索結果詳細 (2008年12月2日 6時)

MDX クエリ のパラメータ名が日本語の場合の手直し方法

SQL Server 2008 オンライン ブック「変数とパラメータの使用 (MDX)」 http://technet.microsoft.com/ja-jp/library/ms145518.aspx のサンプル XMLA コードは以下のとおりです。

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
          select [Measures].members on 0,
          Filter(Customer.[Customer Geography].Country.members,
          Customer.[Customer Geography].CurrentMember.Name =
          @CountryName) on 1
          from [Adventure Works]
        </Statement>
      </Command>
      <Properties />
      <Parameters>
        <Parameter>
          <Name>CountryName</Name>
          <Value>'United Kingdom'</Value>
        </Parameter>
      </Parameters>
    </Execute>
  </Body>
</Envelope>

このサンプルのままでは SQL Server Managment Studio からの XMLA 発行では正しく動作しません。
Properties を記述すると動作します。

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
          select [Measures].members on 0,
          Filter(Customer.[Customer Geography].Country.members,
          Customer.[Customer Geography].CurrentMember.Name =
          @CountryName) on 1
          from [Adventure Works]
        </Statement>
      </Command>
      <Properties>
        <PropertyList>
          <DataSourceInfo>Provider=MSOLAP;Data Source=**********;</DataSourceInfo>
          <Catalog>Adventure Works DW 2008</Catalog>
        </PropertyList>
      </Properties>
      <Parameters>
        <Parameter>
          <Name>CountryName</Name>
          <Value>'United Kingdom'</Value>
        </Parameter>
      </Parameters>
    </Execute>
  </Body>
</Envelope>

さて、パラメータ名に日本語（ダブルバイト文字）を使用したい場合はどのように記述すればよいでしょうか。
正解は @ValiableName → @[変数名] です。

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
          select [Measures].members on 0,
          Filter(Customer.[Customer Geography].Country.members,
          Customer.[Customer Geography].CurrentMember.Name =
          @[カントリーネーム]) on 1
          from [Adventure Works]
        </Statement>
      </Command>
      <Properties>
        <PropertyList>
          <DataSourceInfo>Provider=MSOLAP;Data Source=**********;</DataSourceInfo>
          <Catalog>Adventure Works DW 2008</Catalog>
        </PropertyList>
      </Properties>
      <Parameters>
        <Parameter>
          <Name>カントリーネーム</Name>
          <Value>'United Kingdom'</Value>
        </Parameter>
      </Parameters>
    </Execute>
  </Body>
</Envelope>

ディメンション名や属性名を日本語にしている場合に SSRS の MDX クエリビルダで MDX を作成すると パラメータ名も日本語になります。このクエリを手直しする手段として使えます（@変数名 → @[変数名]）。

参考情報として、日本語をそのまま使ってしまった場合（@ValiableName → @変数名 ）の（XMLAでの）エラーメッセージ（構文解析エラー）を記載しておきます。

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <soap:Fault xmlns="http://schemas.xmlsoap.org/soap/envelope/">
      <faultcode>XMLAnalysisError.0xc10e0005</faultcode>
      <faultstring>パーサー: 解析中に次の構文エラーが発生しました: Invalid token, Line 5, Offset 11, @。</faultstring>
      <detail>
        <Error ErrorCode="3238920197" Description="パーサー: 解析中に次の構文エラーが発生しました: Invalid token, Line 5, Offset 11, @。" Source="Microsoft SQL Server 2008 Analysis Services" HelpFile="" />
      </detail>
    </soap:Fault>
  </soap:Body>
</soap:Envelope>

作者:長崎友嘉

更新日:2008年9月12日 16時56分

このブログのホーム

Dynamic Named Set (AS2008 新機能) を試してみました。

Dynamic Named Set (AS2008 新機能) を試してみました。

まず、 AS2005 での Named Set（名前付きセット）の振る舞いを確認します。

(1-0) AS2005 のサンプルデータベース [Adventure Works DW] に接続

(1-1) CREATE SET コマンドで、[Adventure Works] キューブに 名前付きセット [Top 5 Resellers] を作成
CREATE SET [Adventure Works].[Top 5 Resellers] AS
    TopCount
    (
      [Reseller].[Reseller].[Reseller].MEMBERS
     ,5
     ,[Measures].[Reseller Sales Amount]
    );

(1-2) 名前付きセット [Top 5 Resellers] を使ったクエリを発行
SELECT
  [Date].[(All)] ON COLUMNS
 ,[Top 5 Resellers] ON ROWS
FROM [Adventure Works]
WHERE
  [Measures].[Reseller Sales Amount];

Brakes and Gears  \877,107
Excellent Riding Supplies \853,849
Vigorous Exercise Company \841,909
Totes & Baskets Company  \816,756
Retail Mall   \799,278

[Reseller Sales Amount] のトップ 5（売上成績の良い再販業者上位 5 社とその売上金額）が表示されます。

(1-3) 名前付きセット [Top 5 Resellers] を使ったクエリを発行（[Product].[Category] を [Accessories] に限定）
SELECT
  [Date].[(All)] ON COLUMNS
 ,[Top 5 Resellers] ON ROWS
FROM [Adventure Works]
WHERE
  [Measures].[Reseller Sales Amount] * {[Product].[Category].[Accessories]};

Brakes and Gears  \486
Excellent Riding Supplies \227
Vigorous Exercise Company \8,420
Totes & Baskets Company  \3,736
Retail Mall   \2,346

期待としては、[Product].[Category] を [Accessories] に限定した状態での [Reseller Sales Amount] のトップ 5 （"付属品"に関する売上成績の良い再販業者上位 5 社とその売上金額）を表示したいのですが、実際にはそうはなりません。事前に [Top 5 Resellers] のセットが決まってしまうため、(1-2) でのセットと同一（Brakes and Gears、Excellent Riding Supplies、Vigorous Exercise Company...）になってしまいます。

(1-4)  名前付きセット [Top 5 Resellers] を削除
DROP SET [Adventure Works].[Top 5 Resellers];

(1-5) WITH 句で（クエリスコープで）セットを指定してクエリを発行（[Product].[Category] を [Accessories] に限定）
WITH
  SET [Top 5 Resellers] AS
    TopCount
    (
      [Reseller].[Reseller].[Reseller].MEMBERS
     ,5
     ,[Measures].[Reseller Sales Amount]
    )
SELECT
  [Date].[(All)] ON COLUMNS
 ,[Top 5 Resellers] ON ROWS
FROM [Adventure Works]
WHERE
  [Measures].[Reseller Sales Amount] * {[Product].[Category].[Accessories]};

Eastside Department Store \9,870
Registered Cycle Store  \9,341
Bike Dealers Association \9,060
Larger Cycle Shop  \8,950
Advanced Bike Components \8,765

WITH 句で（クエリスコープで）セットを指定した場合は、[Product].[Category] を [Accessories] に限定した状態での [Reseller Sales Amount] のトップ 5 （"付属品"に関する売上成績の良い再販業者上位 5 社とその売上金額） が表示されます。

このように、（セッションスコープ以上での）Named Set（名前付きセット）は事前に静的にセットに含まれる組が決まってしまい、動的ではありません。WITH 句で書けば回避できますが、事前に定義しておくことのできる名前付きセットの方が便利です。そこで、AS2008 では Dynamic Named Set がサポートされました。

 

(2-0) AS2008 のサンプルデータベース [Adventure Works DW 2008] に接続

ここで、(1-1) ～(1-4) と同一のクエリを発行すると、AS2005 と同じ結果になることが確認できます（通貨記号や小数点以下の表示桁数など、FORMAT_STRING 関連の部分が変わっていますが今回の主題と関係無いため無視します）。CREATE [Dynamic|Static] SET の 既定値は Dynamic ではなく Static のようです。

(2-1) CREATE SET コマンドで、[Adventure Works] キューブに動的な（Dynamic）名前付きセット [Top 5 Resellers] を作成
CREATE DYNAMIC SET [Adventure Works].[Top 5 Resellers] AS
    TopCount
    (
      [Reseller].[Reseller].[Reseller].MEMBERS
     ,5
     ,[Measures].[Reseller Sales Amount]
    )

ここで、(1-3) と同一のクエリを発行すると、(1-5) と同じ結果になることが確認できます（通貨記号や小数点以下の表示桁数など、FORMAT_STRING 関連の部分が変わっていますが今回の主題と関係無いため無視します）。動的な名前付きセット（Dynamic Named Set）に含まれる組は、実行時に動的に決定するわけです。

Eastside Department Store $9,869.92
Registered Cycle Store  $9,340.86
Bike Dealers Association $9,060.41
Larger Cycle Shop  $8,949.71
Advanced Bike Components $8,764.77


以上、Dynamic Named Set (AS2008 新機能) を試してみました。

作者:長崎友嘉

更新日:2008年9月10日 7時52分

このブログのホーム

IIF の評価（→振る舞い）の仕組み と クエリヒント（SQL Server CAT ブログから）

SQL Server CAT ブログで、MDX の IIF 関数の評価（→振る舞い）の仕組みと、クエリヒントについて投稿がありました。IIF 関数のクエリヒントは AS2008 新機能のようですね。

IIF Function Query Hints in SQL Server Analysis Services 2008（SQL Server CAT チームブログ）
http://blogs.msdn.com/sqlcat/archive/2008/09/04/iif-function-query-hints-in-sql-server-analysis-services-2008.aspx

そこで、ここ http://blogs.sqlpassj.org/nagasaki/archive/2008/08/31/25540.aspx で使ったクエリで試してみました（AS2008 です）。

(1)ヒントなし
WITH
MEMBER [Measures].[High Volume Products Count] AS
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time             : 2 sec 109 ms
Calc covers      : 0
Cells calculated : 78588
Sonar subcubes   : 1
SE queries       : 3
Cache hits       : 7
Cache misses     : 2
Cache inserts    : 0
Cache lookups    : 9
Memory Usage KB  : 0

2 秒くらいです。


(2) 固定値 0「hint Eager」、計算式「hint Strict」
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Eager,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Strict)

Time             : 2 sec 46 ms
Calc covers      : 0
Cells calculated : 78588
Sonar subcubes   : 1
SE queries       : 2
Cache hits       : 6
Cache misses     : 2
Cache inserts    : 0
Cache lookups    : 8
Memory Usage KB  : 0

同じく 2 秒くらいです。固定値 0 は(おそらく Inexpensive な default branch として) Eager、計算式の方は Strict で評価されているようです。


(3) 固定値 0「hint Eager」、計算式「hint Eager」
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Eager,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Eager)

Time             : 46 sec 987 ms
Calc covers      : 0
Cells calculated : 2908404
Sonar subcubes   : 1
SE queries       : 2
Cache hits       : 4
Cache misses     : 0
Cache inserts    : 0
Cache lookups    : 4
Memory Usage KB  : 0

46 秒もかかりました。


(4) 固定値 0「hint Strict」、計算式「hint Strict」
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Strict,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Strict)

Time             : 1 min 39 sec 65 ms
Calc covers      : 0
Cells calculated : 5738220
Sonar subcubes   : 1
SE queries       : 2
Cache hits       : 6
Cache misses     : 0
Cache inserts    : 0
Cache lookups    : 6
Memory Usage KB  : -4

99 秒もかかりました。
(3)、(4)はずいぶん遅くなりました。逆に言えば、クエリオプティマイザが「Strict」と「Eager」を間違えるとかなり遅くなる可能性がある、ということですね。あまり積極的に使うものではないのでしょうけど、最終手段として覚えておいても良さそうです。

作者:長崎友嘉

更新日:2008年9月8日 20時23分

このブログのホーム

正：SYSTEMRESTRICTSCHEMA、誤：SYSTEMRISTRICTSCHEMA

RTM 以前に connect にフィードバックがあった内容ですが、エラーメッセージでスペルミスがあり、それが 2008 RTM でも修正されていません。
気がつかないとエラーメッセージをコピーして使用して動かない...なんてことも、あるかもしれませんのでブログに載せておきます。

まず前提知識です。2008 の Analysis Services の新機能で、MDX の SELECT でメタデータにアクセスできるようになっています。
↓こんな感じです。

SELECT * FROM [$SYSTEM].[DBSCHEMA_CATALOGS] ※注：SQL ではありません。MDX です。

このメタデータにアクセスするエンティティを「スキーマ行セット」と呼びます。「スキーマ行セット」の中には「制限」（WHERE 句みたいなもの）を指定することのできるものがあります。
中には「制限」の指定が必須となっているものもあります。この場合に、SYSTEMRESTRICTSCHEMA という関数? を使います。
↓こんな感じです。

SELECT * FROM SYSTEMRESTRICTSCHEMA([$SYSTEM].[DISCOVER_INSTANCES], [INSTANCE_NAME] = 'InstanseName') ※注：しつこいですが SQL ではありません。MDX です。

ちなみに、「制限」の指定が必須となっている「スキーマ行セット」は
・DISCOVER_DIMENSION_STAT
・DISCOVER_INSTANCES
・DISCOVER_PARTITION_DIMENSION_STAT
・DISCOVER_PARTITION_STAT
です。これら以外でも、Books On Line で「制限の列」に記載がある「スキーマ行セット」で、「制限」を指定できます。

で、本題です。この「制限」を指定しなかった場合のエラーメッセージにスペルミスがあります。

SELECT * FROM [$SYSTEM].[DISCOVER_INSTANCES]

----------
クエリを実行しています...
SQL クエリ モジュールからのエラー: 必要な 'INSTANCE_NAME' 制限が、要求にありません。
SYSTEMRISTRICTSCHEMA を使用して制限を指定することを検討してください。
実行完了
----------

よく見ると、微妙にスペルミスしています。

正：SYSTEMRESTRICTSCHEMA
誤：SYSTEMRISTRICTSCHEMA

お気をつけください。

作者:長崎友嘉

更新日:2008年9月2日 19時51分

このブログのホーム

RS2005 の 「レポートに移動」 機能で複数値パラメータを渡すときの書き方

Reporting Serivces (2005) にて、レポートにジャンプする機能
※BIDS で、テキストボックスのプロパティ ＞ 「ナビゲーション」タブ ＞ ハイパーリンクのアクション ＞ レポートに移動
で複数値パラメータをジャンプ先のレポートに渡すときの書き方

=Split(Join(Parameters!%ParamaterName%.Value, ","), ",")

作者:長崎友嘉

更新日:2008年9月1日 16時58分

このブログのホーム

Count(Filter(...)) の最適化 - その2：AS2008 での実験結果

前回の投稿「Count(Filter(...)) の最適化（Mosha Pasumansky さんのブログから）」http://blogs.sqlpassj.org/nagasaki/archive/2008/08/31/25540.aspx の続きです。

AS2008 で、サンプルデータベース「Adventure Works DW 2008」のサンプルキューブ「Adventure Works.cube」を使用して試してみました。パフォーマンス測定のため「MDX Studio」を使用させてもらっています。

(1)
WITH
MEMBER [Measures].[High Volume Products Count] AS
Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time : 40 sec 203 ms
Calc covers : 0
Cells calculated : 2836944
Sonar subcubes : 2
SE queries : 1
Cache hits : 3
Cache misses : 0
Cache inserts : 0
Cache lookups : 3
Memory Usage KB : 52

AS2005（前回投稿で、同じクエリで約30秒） より遅くなったということではありません（実行環境が違います）。これが基本速度です。


(2)
WITH
MEMBER [Measures].[High Volume Products Count] AS
Count(Filter(Exists([Product].[Product].[Product], , "Internet Sales"), [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time : 12 sec 312 ms
Calc covers : 0
Cells calculated : 50217
Sonar subcubes : 7277
SE queries : 7276
Cache hits : 7278
Cache misses : 0
Cache inserts : 0
Cache lookups : 7278
Memory Usage KB : 1212

前回投稿の AS2005 と同じような振る舞いです。


(3)
WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, 0))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time : 8 sec 156 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 3
Cache misses : 2
Cache inserts : 1
Cache lookups : 5
Memory Usage KB : 0

前回投稿の AS2005 と同じような振る舞いです。


(4)
WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time : 578 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 4
Cache misses : 0
Cache inserts : 0
Cache lookups : 4
Memory Usage KB : 0

前回投稿の AS2005 より高速です。Mosha さんが述べているように、2008 では NON_EMPTY_BEHAVIOR の明示が無くても高速化されます。


(5)
--MDX Script
CREATE HIDDEN Summator;
[Measures].[Summator] = Iif([Measures].[Internet Order Quantity] > 5, 1, NULL);
NON_EMPTY_BEHAVIOR([Measures].[Summator]) = [Measures].[Internet Order Quantity];

--Query
WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], [Measures].[Summator])
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

非推奨になっていますが一応 NON_EMPTY_BEHAVIOR を試してみました。1 分以内に結果が戻らず、計測不能という結果です。 AS2008 での NON_EMPTY_BEHAVIOR の使用は避けたほうがよさそうです。


(6)
ここまでの最速は(4)ですが、(4)の結果セットには 0 ではなく NULL が表示されており、厳密に言えば(1)～(3)までの結果セットとは異なっています。そこで、結果セットに NULL ではなく 0 を表示するように以下のような記述に変更してみました。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL, 0, Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Time : 1 sec 968 ms
Calc covers : 0
Cells calculated : 78588
Sonar subcubes : 1
SE queries : 3
Cache hits : 8
Cache misses : 0
Cache inserts : 0
Cache lookups : 8
Memory Usage KB : 0

この書き方ですと、実行時間は 2 秒程度で済んでいますが計算量（"Cells calculated"）がかなり増えてしまっています。


(7)
そこで、別の計算メンバを使用して、結果セットに NULL ではなく 0 を表示するような別の方法に変更してみました。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)), SOLVE_ORDER = 1
MEMBER [Measures].[High Volume Products Count 2] AS
Iif([Measures].[High Volume Products Count] = NULL, 0, [Measures].[High Volume Products Count]), SOLVE_ORDER = 2
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count 2]

Time : 812 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 6
Cache misses : 0
Cache inserts : 0
Cache lookups : 6
Memory Usage KB : 0

この書き方であれば、約 0.6 秒の(4) と比較しても遜色ありません。"Cells calculated" の値も (4) と同じです。


まとめ
・AS2008 でも Count(Filter(...)) の書き方は自動的には（インスタンスのクエリオプティマイザでは）最適化されない。 Sum + Iif( , , NULL) に変換する必要がある。
・AS2008 では NON_EMPTY_BEHAVIOR を明示的に指定する必要はない。（逆に、指定したら非常に遅くなる振る舞いを見せた。）
・表示上、NULL から 0 に変更しておきたい場合は Sum + Iif 部分 が 1 回の計算で済むような工夫が望ましい。具体例は (7) を参照のこと。


次は、「Optimizing Count(Filter(...)) expressions」で触れられていた「Performance of IIF function in MDX」http://www.sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx を翻訳してみようと思います。

作者:長崎友嘉

更新日:2008年9月1日 8時24分

このブログのホーム

Count(Filter(...)) の最適化 （Mosha Pasumansky さんのブログから）

Mosha Pasumansky さんのブログ投稿のご紹介です。（翻訳の許可を頂いています。翻訳の正確さは保証できません。）

原文：http://www.sqljunkies.com/WebLog/mosha/archive/2007/11/23/92198.aspx
****************************************************************************************************
「Optimizing Count(Filter(...)) expressions」

私のブログの読者のみなさんはご存じのように、Katai（※訳者注：SQL Server 2008 の開発コードネーム）で「ブロック計算モード（block computation mode）」と呼ばれている「バルク評価モード（bulk evaluation mode）」は、「セルバイセル評価モード（cell-by-cell evaluation mode）」と比べて、かなり良いパフォーマンスを発揮します。ですから、Analysis Services の MDX における最も重要な最適化テクニックは、「ブロック計算」で実行されるように MDX を書き直すことです。これは言うのが簡単ですが、実施するのは常に簡単という訳ではありません。

Katmai CTP5 の リリースに際して、マイクロソフトは「ブロック計算」が行われる場合とそうでない場合の状況を概説する BOL 記事（※訳者注：Books On Line 記事）を公開しました。Chris Webb はこの記事を取り上げて、彼のブログの投稿「the list of set functions is a bit limited (where is Filter ?)」で言及しています。私は、このブログ投稿のリプライで「MDX 計算の中で Filter 関数が一般的に使われるとは思っていなかった」と述べましたが、Greg Galloway はすぐに Filter 関数が使用される例の一つとして Count(Filter(...)) を挙げました。"特定の条件を満たす対象の数"を調べるこのような計算は、実に一般的な計算です。(Greg の例は、特定の期間内に 10 以上の手術を担当した医師の人数を調べる、という例でした。)

Adventure Works サンプルキューブを使った例で、この種類のクエリをどのように最適化できるかを見ていきましょう。私たちのタスクは"インターネット経由で 5 つ以上の注文を受けた製品の数"を調べることです。 この計算は次のように記述されます。

Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))

実は、AS2005 と AS2008 のいずれにおいても Filter 関数 は「ブロック計算モード」で動作するように最適化されていません。ですから、この計算に関わるクエリは「セルバイセルモード」で実行されるでしょう。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

MDX Studio で（※訳者注：Mosha Pasumansky さんによる MDX 開発/実行環境。http://www.mosha.com/msolap/mdxstudio.htm からダウンロード可能。）このクエリを実行すると、以下のような統計情報を得るでしょう。

Time : 30 sec 781 ms
Calc covers : 4
Cells calculated : 4217436
Sonar subcubes : 2
SE queries : 1
Cache hits : 1
Cache misses : 1
Cache inserts : 1
Cache lookups : 2
Memory Usage KB : 4160

このクエリが「セルバイセルモード」で実行されたことに関するベストヒントは "Cells calculated" パフォーマンスカウンタの値です。このような MDX の最適化において通常採用されるアプローチは、繰り返し使用されるセルの数を減らすことです。これを行う方法として、マニュアルで Filter 関数の中からのすべての空のセルを排除する方法があります。言い換えて説明すると、仮に特定の製品の [Internet Order Quantity] の値が NULL であるなら、それは当然、5 よりは少ないはずです。これを念頭に、クエリを次のように書き換えることができます。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Count(Filter(Exists([Product].[Product].[Product], , "Internet Sales"), [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

Filter 関数の中の Exists 関数は空白を排除します。これを再び MDX Studio で実行して、新しい統計情報を見てみましょう。

Time : 12 sec 46 ms
Calc covers : 954
Cells calculated : 50036
Sonar subcubes : 7900
SE queries : 7899
Cache hits : 7899
Cache misses : 1
Cache inserts : 1
Cache lookups : 7900
Memory Usage KB : 43936

実行時間は 30 秒から 12 秒まで減少しました。これは良い結果ですが、つじつまが合いません。"Cells calculated" の値は 400 万から 5 万まで減りました。およそ 85 倍です。しかし、実行時間の減少はたったの 2 倍でしかありません。

これに対する説明としては、MDX 計算の中に Exists (または NonEmpty) を記述するのは、通常は悪いアイデアであるということです。これを記述した以降、全てのセルに対して "SE query" が発生しています。（統計情報の "Query Subcube" イベントでも見ることができます。）今回のケースでは、そのようなクエリが 7900 回も発生しています。（※訳者注：統計情報の "SE queries" を参照）。Exists 関数の中のセットは常に同じでありかつコンテキストが固定されているため、1 回のクエリだけは実際にディスクに行かなければなりませんが、他の 7899 回はキャッシュにヒットします。しかし、 "SE query" の発行に起因するオーバーヘッドは、たとえキャッシュから回答が得られる場合であっても無視できないものです。

結論としては、このアプローチによりいくらかのパフォーマンス向上は得られますが、残りの部分では良い振る舞いをしません。そしてこれは、優れた「ブロック計算モード」に切り替える目標から、より遠くに向かわせているだけです。私たちは、Filter 関数を排除する方向でクエリを書き直す必要があります。幸いなことに、それは可能です。Count 関数はセット内のタプルの数を返し、Filter 関数は特定の条件を満たすタプルのセットを返すことを思い出しましょう。要するに、条件を満たしたタプルの数を数えています。Count 関数を Sum 関数に変更して、条件と一致したら 1 をカウントアップし、条件と一致しなかったたら 0 をカウントアップするようにすれば同じ結果を得るでしょう。

Count(Filter(set, condition)) = Sum(set, Iif(condition, 1, 0))

この式を使って、クエリを次のように書き直すことができます。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, 0))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

このクエリを実行すると、次の統計情報を得ます。

Time : 6 sec 375 ms
Calc covers : 4
Cells calculated : 6948
Sonar subcubes : 1
SE queries : 1
Cache hits : 1
Cache misses : 1
Cache inserts : 1
Cache lookups : 2
Memory Usage KB : 0

これはもちろん、かなり良いです。実行時間は 6 秒まで減りました。6948セル（これは結果となるセルセットの正確なセル数です。）が計算されるだけですので、全てセルの中で Sum 関数がとても効率的に動作していると言えます。

しかし、これはまだ本当の「ブロック計算モード」ではありません。このクエリは、1 回のオペレーションで全体のクエリを計算する代わりに、いまだに全てのセルのために Sum を実行しています。何が原因で、それができないのでしょうか。問題は、Sum 関数の中にある Iif 関数にあります。私は過去に、Iif 関数とその「ブロック計算モード」との相互作用について書きました。その記事を再読すると、Iif 関数が属性座標ではなくセル値を参照する状態のシナリオにいることがわかるでしょう。ここでできる唯一の対応は NULL を返す分岐を持つことです。そして、それは恐らく、条件を満たす製品が全くないときに 0 の代わりに NULL を返す計算メンバを定義することを意味します。この書き直しの後で次のクエリを得ます。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL))
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

このクエリは実行時間を 4 秒まで減りますが、まだ改良の余地があり、まだまだ最良の実行プランに達していません。いまこそ MDX のクエリオプティマイザヒントを使用するべきタイミングです。式 Iif([Measures].[Internet Order Quantity] > 5, 1, NULL) を見てみると、[Measures].[Internet Order Quantity] が NULL である場合に NULL になることが保証されていることが分かります。そこで、NON_EMPTY_BEHAVIOR についての MDX スクリプトを定義します。副次式 (subexpression) で NON_EMPTY_BEHAVIOR を定義できないため、特別な計算メジャーにそれを分離します。MDX スクリプトの中に次の内容を記述しましょう。

CREATE HIDDEN Summator;
[Measures].[Summator] = Iif([Measures].[Internet Order Quantity] > 5, 1, NULL);
NON_EMPTY_BEHAVIOR([Measures].[Summator]) = [Measures].[Internet Order Quantity];

そして、クエリの中でこれを使用します。

WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], [Measures].[Summator])
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count]

最終的にこのクエリの実行時間はおよそ 0.2 秒（218ミリ秒）になります。これは「ブロック計算モード」によって得られると予想している性能です。（32 秒から 0.2 秒となり、160 倍の向上です。）

さらに良いお知らせがあります。それは Katmai の 11 月の CTP5 から開始されますが、（数日前にリリースされたので、私はやっと公式にそれについて話すことができます。）明示的に NON_EMPTY_BEHAVIOR を定義する処置は必要ではありません。エンジンがそれ自体を認識します。ですから、前のクエリ(AS2005 で 4 秒かかったもの)でさえ、AS2008 では 0.2 秒で戻ります。

(この Katmai のクエリオプティマイザの改良は、ブロック計算クエリプランの改良の周りにある小さな氷山の一角に過ぎません。もしそのための十分な時間があれば、私は今度のブログ投稿でより深くこの対象をカバーするつもりです。）
****************************************************************************************************




「Count(Filter(...))の最適化」をまとめますと、以下のようになります。
(1) Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
　 → 30秒。全く工夫のない計算処理であり、すごく遅いです。
(2) Count(Filter(Exists([Product].[Product].[Product], , "Internet Sales"), [Measures].[Internet Order Quantity] > 5))
　 → 12秒。Exists 関数を使用して限定することで "Cells calculated" を減らしましたが、Exists 関数により"SE queries" が増加するため十分な高速化にはなりません。 この方向では改善幅も限定されます。
(3) Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, 0))
　 → 6秒。Count(Filter()) を Sum + Iif にリライトする方法により、計算量（"Cells calculated"）がさらに減り、パフォーマンスが改善されます。ただしこれでも（無駄をかなり省いているとは言え）本当の「ブロック計算モード」とは言えません。
(4) Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL))
　 → 4秒。Iif 関数の最適化として NULL を返すように変更し、さらに改善しています。まだ最良の実行プランに達していません。
(5) NON_EMPTY_BEHAVIOR クエリオプティマイザヒント
　 →0.2秒。NON_EMPTY_BEHAVIOR クエリオプティマイザヒントを指定することで、「ブロック計算モード」相当の速度で実行させることができます。かなり速いです。
(6) AS2008 では NON_EMPTY_BEHAVIOR クエリオプティマイザヒント の指定は必要ありません。自動的に「ブロック計算モード」になります。
　　※ただし、(1)～(4)のどの書き方で「ブロック計算モード」になるのかは定かではありません。たぶん(4)だけだと思います...

なお、2008 では NON_EMPTY_BEHAVIOR が非推奨機能になっていますのでご注意ください。
http://msdn.microsoft.com/ja-jp/library/ms143346.aspx

次回の投稿では実際に自分で試してみます。特に AS2008 で (1)～(4) の速度に注目してみます。

作者:長崎友嘉

更新日:2008年8月31日 8時53分

このブログのホーム

SQL Server 2008 インストール完了画面での「補足情報」をメモ

SQL Server 2008 のインストールも無事終わりました。
インストーラの「完了」画面で「補足情報」が表示されますが、インストーラを終わらせると読めなくなってしまうのでコピーして貼っておきます。

----------------------------------------------------------------------------------------------------
以下の注意事項は、SQL Server の今回のリリースにのみ適用されます。

Microsoft Update
SQL Server 2008 での更新を Microsoft Update で調べる方法については、Microsoft Update Web サイト <http://go.microsoft.com/fwlink/?LinkId=108409> (http://go.microsoft.com/fwlink/?LinkId=108409) を参照してください。

Reporting Services
セットアップ時に指定した Reporting Services のインストール オプションに応じて、レポート サーバーにアクセスする前に追加の構成が必要かどうかが決まります。
既定の構成をインストールした場合は、レポート サーバーをすぐに使用できます。
プログラム ファイルだけをインストールした場合は、Reporting Services 構成ツールを実行してレポート サーバーを展開する必要があります。
SharePoint テクノロジ用 Reporting Services アドインをインストールするには、SQL Server セットアップの終了後、
SQL Server 2008 インストール メディアの Setup フォルダにある rsSharePoint.msi を実行してください。

ドキュメントとサンプル
.NET Framework SDK をインストールするには、SQL Server 2008 オンライン ブック <http://go.microsoft.com/fwlink/?LinkId=114089>の
「.NET Framework SDK のインストール」(http://go.microsoft.com/fwlink/?LinkId=114089) を参照してください。
既定では、SQL Server のセットアップ時にサンプル データベースとサンプル コードはインストールされません。
Express 以外のエディションの SQL Server 2008 のサンプル データベースとサンプル コードをインストールするには、
http://go.microsoft.com/fwlink/?LinkId=87843 を参照してください。
SQL Server Express のサンプル データベースとサンプル コードのサポートについては、
CodePlex Web サイト (http://go.microsoft.com/fwlink/?LinkId=110391) を参照してください。

SQL Server の今回のリリースにおける最新の変更内容については、最新の Readme ファイル
 <http://go.microsoft.com/fwlink/?LinkId=100093> (http://go.microsoft.com/fwlink/?LinkId=100093) を参照してください。

SQL Server 2008 セキュリティ構成の詳細については、SQL Server 2008 ドキュメントの次のトピックを参照してください。

SQL Server 2008 オンライン ブック : 「セキュリティ構成について」

SQL Server 2008 セットアップ ヘルプ : 「Minimize SQL Server 2008 Surface Area」(SQL Server 2008 の外部からのアクセスの最小化)

SQL Server 2008 オンライン ブック (MSDN) : 「セキュリティ構成について <http://go.microsoft.com/fwlink/?LinkId=106282>」(http://go.microsoft.com/fwlink/?linkid=106282)
----------------------------------------------------------------------------------------------------

それと、「概要ログファイル」の保存先をメモしておきます。

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\%YYYYMMDD%_%HHMISS%\Summary_%ComputerName%_%YYYYMMDD%_%HHMISS%.txt

作者:長崎友嘉

更新日:2008年8月30日 7時16分

このブログのホーム

PCDimNaturalizer （Mosha Pasumansky さんのブログから）

Mosha Pasumansky さんのブログ投稿のご紹介です。（翻訳の許可を頂いています。翻訳の正確さは保証できません。）

原文：http://sqlblog.com/blogs/mosha/archive/2008/08/25/parent-child-dimension-table-naturalizer.aspx
****************************************************************************************************
「Parent-Child Dimension Table Naturalizer」

親子ディメンションは Analysis Services の重要な機能の 1 つです。親子ディメンションは BOM（部品表）、勘定科目一覧表、組織図などのようなシナリオに関するディメンションモデリングを行う際に柔軟性を与えます。しかし、この柔軟性はコストを伴います。親子ディメンションの使いすぎはパフォーマンスの問題を引き起こしますし、さらに、そこには計算における問題を引き起こす意味的な*ねじれ*があります。例えば親子ディメンションは、（ノーマルなディメンションがそうするように）関係属性を解析しません。ですから、場合によっては親子階層をノーマルなマルチレベル階層に変換することが望ましいこともあります。この変換のプロセスは些細なものではなく、いくつかのステップを含んでいます。

マイクロソフト社の? Senior Support Escalation Engineer である Jon Burchel 氏 はこの問題を深く調べて、"Analysis Services Dimension Table Naturalizer" または "PCDimNaturalizer" と呼ばれる、親子ディメンションを通常のディメンションに自動的に変換するツールを考え出しました。彼は www.codeplex.com にそのプロジェクトを置きました。これは、すべてのソースコードを入手可能であることを意味します。このツールは、ほとんどの（少なくとも codeplex の）オープンソースプロジェクトとは異なり、しっかりとドキュメント化されています。(私は、Support Engineer である Jon が、良く出来たドキュメントの価値を本当に理解していると考えています。)PCDimNaturalizer はコマンドラインまたは UI から使用できます。また、.NET アプリケーション や SSIS パッケージに組み込むためのオブジェクトモデルを公開しています。

私はこのツールが、多くの Analysis Services を実践している方々にとって役に立つことを確信しています。
codeplex の PCDimNaturalizer プロジェクトはこちら： http://www.codeplex.com/PCDimNaturalize
****************************************************************************************************




というわけで、PCDimNaturalizer を使ってみました。

1. 親子ディメンションを設定
　AdventureWorksDW データベース の DimEmployee テーブルを対象にして、
　・Employee Key を Usage : Key
　・ParentEmployeeKey を Usage : Parent
　おまけとして
　・Last Name を Usage : Regular
　としてディメンションを設定しました。

2. 配置 + ビルド
　Analysis Services インスタンスに配置してビルドします。

3. PCDimNaturalizer で、対象ディメンションとして指定
　PCDimNaturalizer を起動し、対象のインスタンス、データベース、ディメンションを指定します。

4. PCDimNaturalizer のオプションを指定して、実行します。
　主なオプション
　○"Action Level for naturalization"
　　　Level 1 : Create SQL View
　　　Level 2 : Add to data source view
　　　Level 3 : Create naturalized dimension
　　　Level 4 : Add to cube(s)
　　　Level 5 : Process
　　※「データソースにビューを作り」→「"データソースビュー"に加え」→「ディメンションを設定し」→「キューブに加え」→「処理する」という
　　　ステップのうちのどこまでやるかの指定です。
　○"Minimam levels"
　　自動的に階層数を調べて、「Level 1 : Create SQL View」で作るビューでの階層数を決めるようですが、これの指定により
　　実データに存在しない数の階層を作るようです。

5. 結果
　「Level 1 : Create SQL View」でのみ、動作しました。
　「Level 2 : Add to data source view」より上位の設定では正しく動作しませんでした。データソースビューの作成がエラーになってしまいました。
　作成されたビューを載せてておきます。このビューがキモですので、どんな機能なのかはこのビューだけで理解できます。
　ちなみに、"Minimam levels"に 7 を指定してみたところ、[Level 08_KeyColumn] まで含まれるビューが生成されました。
----------------------------------------------------------------------------------------------------
CREATE VIEW [dbo].[DimNaturalized_DimEmployee] AS
WITH PCStructure(Level, [ParentEmployeeKey], [CurrentMember_KeyColumn], [Level 02_KeyColumn], [Level 03_KeyColumn], [Level 04_KeyColumn], [Level 05_KeyColumn], [Level 06_KeyColumn])
AS (SELECT 3 Level, [ParentEmployeeKey], [EmployeeKey], [EmployeeKey] as [Level 02_KeyColumn],
[EmployeeKey] as [Level 03_KeyColumn],
[EmployeeKey] as [Level 04_KeyColumn],
[EmployeeKey] as [Level 05_KeyColumn],
[EmployeeKey] as [Level 06_KeyColumn]
FROM [dbo].[DimEmployee] WHERE [ParentEmployeeKey] IS NULL OR [ParentEmployeeKey] = [EmployeeKey]
UNION ALL SELECT Level + 1, e.[ParentEmployeeKey], e.[EmployeeKey], CASE Level
WHEN 2 THEN e.[EmployeeKey] ELSE [Level 02_KeyColumn]
END
AS [Level 02_KeyColumn],
CASE Level
WHEN 2 THEN e.[EmployeeKey]
WHEN 3 THEN e.[EmployeeKey] ELSE [Level 03_KeyColumn]
END
AS [Level 03_KeyColumn],
CASE Level
WHEN 2 THEN e.[EmployeeKey]
WHEN 3 THEN e.[EmployeeKey]
WHEN 4 THEN e.[EmployeeKey] ELSE [Level 04_KeyColumn]
END
AS [Level 04_KeyColumn],
CASE Level
WHEN 2 THEN e.[EmployeeKey]
WHEN 3 THEN e.[EmployeeKey]
WHEN 4 THEN e.[EmployeeKey]
WHEN 5 THEN e.[EmployeeKey] ELSE [Level 05_KeyColumn]
END
AS [Level 05_KeyColumn],
CASE Level
WHEN 2 THEN e.[EmployeeKey]
WHEN 3 THEN e.[EmployeeKey]
WHEN 4 THEN e.[EmployeeKey]
WHEN 5 THEN e.[EmployeeKey]
WHEN 6 THEN e.[EmployeeKey] ELSE [Level 06_KeyColumn]
END
AS [Level 06_KeyColumn] FROM [dbo].[DimEmployee] e INNER JOIN PCStructure d ON e.[ParentEmployeeKey] = d.[CurrentMember_KeyColumn] AND e.[ParentEmployeeKey] != e.[EmployeeKey])
select CurrentMemberSubselect.*,
Level2Subselect.*, Level3Subselect.*, Level4Subselect.*, Level5Subselect.*, Level6Subselect.*
from PCStructure a
?left outer join (select [EmployeeKey] [CurrentMember_KeyColumn], [EmployeeKey] [CurrentMember_EmployeeKey],
[ParentEmployeeKey] [CurrentMember_ParentEmployeeKey],
[LastName] [CurrentMember_LastName]
from [dbo].[DimEmployee]) CurrentMemberSubselect on CurrentMemberSubselect.[CurrentMember_KeyColumn] = a.[CurrentMember_KeyColumn] left outer join (select [EmployeeKey] [Level 02_KeyColumn], [LastName] [Level 02_Last Name_KeyColumn]
from [dbo].[DimEmployee]) Level2Subselect on Level2Subselect.[Level 02_KeyColumn] = a.[Level 02_KeyColumn]
left outer join (select [EmployeeKey] [Level 03_KeyColumn], [LastName] [Level 03_Last Name_KeyColumn]
from [dbo].[DimEmployee]) Level3Subselect on Level3Subselect.[Level 03_KeyColumn] = a.[Level 03_KeyColumn]
left outer join (select [EmployeeKey] [Level 04_KeyColumn], [LastName] [Level 04_Last Name_KeyColumn]
from [dbo].[DimEmployee]) Level4Subselect on Level4Subselect.[Level 04_KeyColumn] = a.[Level 04_KeyColumn]
left outer join (select [EmployeeKey] [Level 05_KeyColumn], [LastName] [Level 05_Last Name_KeyColumn]
from [dbo].[DimEmployee]) Level5Subselect on Level5Subselect.[Level 05_KeyColumn] = a.[Level 05_KeyColumn]
left outer join (select [EmployeeKey] [Level 06_KeyColumn], [LastName] [Level 06_Last Name_KeyColumn]
from [dbo].[DimEmployee]) Level6Subselect on Level6Subselect.[Level 06_KeyColumn] = a.[Level 06_KeyColumn]
----------------------------------------------------------------------------------------------------

作者:長崎友嘉

更新日:2008年8月30日 6時26分

このブログのホーム

SQL Server 2008 の セットアップでの構成チェックにて

SQL Server 2008 をインストールしていた Windows 2008 の PC を返却することになってしまったので
別の PC（Windows XP） に SQL Server 2008 （日本語版RTM、評価版） をインストールしています。

SQL Server 2008 の セットアップでの構成チェックの詳細レポート 「System Configuration Check Report」の中に
「Bids2005InstalledCheck」という項目があります。
この項目の評価が「SQL Server 2008 より前のリリースの Business Intelligence Development Studio はインストールされていません」
となっています。

しかし...SQL Server 2005 の Business Intelligence Development Studio はインストール済みなんですよね...
SQL Server 2008 をインストールしている PC には既に SQL Server 2000 の DB Engine のみ + SQL Server 2005 の全機能が入っています。
BI の開発もしますのでもちろん (2005) の BI Development Studio を入れてます。
いったい何をチェックしているのでしょうか。そもそもチェックする必要はあるのでしょうか。

ちなみに、構成チェックの詳細レポートはログとして残っています。
私の環境のインストールパスではここにありました。

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\%YYYYMMDD%_%HHMISS%\SystemConfigurationCheck_Report.htm

作者:長崎友嘉

更新日:2008年8月29日 22時31分

このブログのホーム

BIDS Helper （BI Development Sutudio のアドインツール） - その2 : SSIS 側のお勧め機能

今回も引き続き「BIDS Helper」のご紹介です。
「BIDS Helper」は CodePlex で公開されている BI Development Sutudio のアドインツールです。

http://www.codeplex.com/bidshelper/

機能リストはここ↑を見てください。
私がお勧めの機能だけ、簡単にご紹介します。今回は SSIS 側の機能です。

・Create Fixed Width Columns
　形式が"固定幅"のフラットファイル接続マネージャの、列の定義を Excel スプレッドシート（の一部分）のコピー&ペーストで行えます。便利です。

・Non-Default Properties Report
　SSAS と同じです。デフォルト指定以外のプロパティをリストアップしてレポートしてくれます。
　設定のチェックにどうぞ。

・Sort Project Files（Sort By Name）
　SSIS パッケージを名前順にソートしてくれます。
　既定では、SSIS パッケージは作成順に並んでしまいます。
　これが気に入らない場合は、プロジェクトファイル（*.dtproj）のこの部分↓の順番を入れ替えれば整理できます。

  <DTSPackages>
    <DtsPackage>
      <Name>Package1.dtsx</Name>
      <FullPath>Package1.dtsx</FullPath>
      <References />
    </DtsPackage>
    <DtsPackage>
      <Name>Package2.dtsx</Name>
      <FullPath>Package2.dtsx</FullPath>
      <References />
    </DtsPackage>
  </DTSPackages>

　これがちょっと面倒な作業でしたがこの「Sort Project Files（Sort By Name）」があれば 1 クリックで終了です。便利です。

・SSIS Performance Visualization（Execute and Visualize Performance）
　SSIS パッケージを実行して、パフォーマンスをビジュアル化してくれます。
　※多くの情報が取得できます。詳細は上記 URL からご確認いただけます。

こんな感じです。
特に「SSIS Performance Visualization」は強力です。

※Pipeline Component Performance Breakdown
　SSIS パッケージを実行して、「データ変換タスク」の内部のコンポーネント単位で時間を計って表示してくれるようなのですが...
　日本語環境だからなのか、正しく動作してくれません。

 

作者:長崎友嘉

更新日:2008年8月29日 18時47分

このブログのホーム

BIDS Helper （BI Development Sutudio のアドインツール）

最近はじっくり SQL Server 2005 / 2008 の BI 機能を触れることができています。
長らく投稿していませんでしたが、なるべく積極的に投稿していこうと考えています。

今回は「BIDS Helper」のご紹介です。
「BIDS Helper」は CodePlex で公開されている BI Development Sutudio のアドインツールです。

http://www.codeplex.com/bidshelper/

機能リストはここ↑を見てください。
私がお勧めの機能だけ、簡単にご紹介します。今回は SSAS 側の機能です。

・Aggregation Manager
　集計デザインの変更ツールです。
　画面の右側に、ディメンション属性が（属性リレーションシップの定義に従い）ツリー形式で表示される点が分かりやすいです。
　
・Dimension Health Check
　ディメンションの設定をチェックしてレポートしてくれます。
　チェックの内容は
　　(1) 属性リレーションシップの妥当性（反しているデータの存在確認）
　　(2) 属性キーの一意性
　　(3) 反転している属性リレーションシップの指摘
　です。
　特に (1) は有難いです。

・Measure Group Health Check
　メジャーグループの設定をチェックしてレポートしてくれます。
　いまのところ、チェックしているのは「データ型の妥当性」だけです。
　例えばウィザードでキューブを作成して、メジャーのデータ型は（データソースでの定義に従い） Integer に設定しているとします。
　集計関数が SUM ですと、合計値が Integer の許容範囲を超えることがあり、（1 ビット目が 1 になったのか）合計値がマイナス値で表示されることがあります。
　このツールは実際の値をチェックして Bigint への変更を勧めてくれます。

・Non-Default Properties Report
　（そのままですが）デフォルト指定以外のプロパティをリストアップしてレポートしてくれます。
　設定のチェックにどうぞ。

・Printer Friendly Dimension Usage
　メジャーグループとディメンションの関連付けをまとめてレポートしてくれます。
　設定書がわりに。

・Visualize Attribute Lattice
　属性リレーションシップの設定を"視覚的に"表現してくれます。
　属性リレーションシップの設定確認にどうぞ。

結構便利な機能がありますので、SQL Server BI の Developer の方は是非使ってみてください。
※先週くらいに 2008 対応版もリリースされました。
※CodePlex ですので、ソースコードも参照可能です。
※SSIS のお勧め機能は次回にご紹介します。

作者:長崎友嘉

更新日:2008年8月29日 9時28分

このブログのホーム

Excel 2003 の ピボットテーブル から SQL Server Analysis Services へ接続して使用されている方へ

Office 2003 SP3 の自動更新（日本語版）が2008年2月27日から、と近づいていますが、その前に既知の障害があるのでご連絡です。
MSDNフォーラム、PASSJメーリングリスト、答えてねっとなどで話題になっています。

Office 2003 SP3 の Excel ピボットテーブル から SQL Server Analysis Services へ接続して
ある特定の操作をした際に
「データベースドライバは必要な機能をサポートしていないためExcelで使用できません」
「CommandオブジェクトのCommandTextが設定されていません。」
などのエラーメッセージを表示して、正常な動作を行わない場合があります。

現状、判明している情報はこちらをご確認ください。
http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=2770167&SiteID=7&mode=1

http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=6&disp_mode=3

http://www.kotaete-net.net/Default.aspx?pgid=14&qid=16068806489

・KB 947648 のパッチを待てばいいようです（実際にはまだ出ていないので信用できないかも）。
・暫く「Office 2003 SP3 の自動更新をしない」措置もご検討ください。
・発生後（Office 2003 SP3 適用をしてしまった後）の、パッチ（KB 947648 ）が出るまでの暫定措置としては SP2 の Excel.exe に置き換えるという手段もあります。

#2/27以前にKB 947648が出ることを祈ってます。

 

作者:長崎友嘉

更新日:2008年2月23日 3時47分

このブログのホーム

SQL Server 2005 SP3 を出して欲しい件

Hugo Kornelis さんのブログにて、
SQL Server 2005 SP2 の現状をまとめ、SQL Server 2005 SP3 のリリースを要望しています。

■Hugo Kornelis さんのブログ "Want a Service Pack? Ask for it!"
http://sqlblog.com/blogs/hugo_kornelis/archive/2008/02/01/want-a-service-pack-ask-for-it.aspx

賛同してもらえる方は Microsoft Connect の suggestion に投票してください、とのことです。

■Microsoft Connect "Release Service Pack 3 for SQL Server 2005"
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575

私も★★★★★（とても重要）で投票しました。

※投票方法が分かりにくいと思います。
「レート」の★マークをクリックすることで、投票できます。
★～★★★★★までの5段階ですので、クリックする位置をお間違えなく。

 

作者:長崎友嘉

更新日:2008年2月3日 9時55分

このブログのホーム

（ニュースサイトから）米Microsoft，業務PMアプリ「Office PerformancePoint Server 2007」でBI製品を拡充

IT Pro から

■米Microsoft，業務PMアプリ「Office PerformancePoint Server 2007」でBI製品を拡充
http://itpro.nikkeibp.co.jp/article/USNEWS/20060607/240177/

プロクラリティの行く末はいかに...
Microsoft Dynamics ERP との絡みも楽しみです。

Microsoft Dynamics と言えば、
Microsoftアーキテクチャ ベースの中規模EPR（というか業務パッケージ）製品は競合しますね。
（小規模は日本での販売は避けたようですね。）

かつて開発にかかわった業務パッケージ製品がどうなるか、心配ではあります。

作者:長崎友嘉

更新日:2006年6月7日 12時37分

このブログのホーム

(ニュースサイトから)プロクラリティ社を買収

IT Pro から

■米マイクロソフト、データ分析／可視化ソフトベンダーを買収、成果は次期Officeにも
http://itpro.nikkeibp.co.jp/article/NEWS/20060404/234518/

プロクラリティ社が買収されてしまいました。
Microsoft も UI の強化に本腰入れてますね。
Office に統合となると、ライセンスはどうなるのかなあ...

日本の代理店さんはどうなるのでしょうか。
http://www.understanding.co.jp/

作者:長崎友嘉

更新日:2006年4月5日 13時42分

このブログのホーム

Tech Ed 2005 Yokohama : スナップショット分離レベルでもスキャンにおける、無関係な行のロック開放待ちを回避できない件について

長崎です。

Tech Ed に参加中です。
松本（み）さんのデットロックとスナップショット分離レベルのセッション中に考えたことをまとめました。
この内容は、1年前くらいに松本（た）さんと話していた内容です...（フィードバックしてない...）
非常に重要な問題なのではないか、と私は考えています。

-------------------------------------------------------------------------------------------------------
■サンプルテーブル、データ
--[col_A]がクラスタ化インデックスな主キーとなっている[tab_A]
CREATE TABLE [dbo].[tab_A](
   [col_A] [nchar](1) COLLATE Japanese_CI_AS NOT NULL,
   [col_B] [nchar](1) COLLATE Japanese_CI_AS NULL
   CONSTRAINT [PK_tab_A] PRIMARY KEY CLUSTERED
(
   [col_A] ASC
) ON [PRIMARY]
) ON [PRIMARY]

--サンプルデータを2行
INSERT INTO [dbo].[tab_A] ([col_A], [col_B]) VALUES ('1', '1') --セッションAで共有/更新/排他ロック
INSERT INTO [dbo].[tab_A] ([col_A], [col_B]) VALUES ('2', '2') --セッションBで共有/更新/排他ロック

-------------------------------------------------------------------------------------------------------
■前提知識(1)
SQL Server 2000 では、「排他ロック」がかかっている列を含んだクラスタ化インデックスを「クラスタ化インデックスのスキャン」すると
ロック開放待ちが発生する。

--セッションA：排他ロックのまま10秒WAIT
BEGIN TRAN

UPDATE [dbo].[tab_A]
   SET [col_B] = [col_B]
WHERE [col_B] = '1'

WAITFOR DELAY '00:00:10'

COMMIT TRAN

--セッションB：デフォルトの分離レベルで、共有ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM [dbo].[tab_A]
WHERE [col_B] = '2'

※「テーブルのスキャン」でも発生する。
※「クラスタ化インデックスのシーク」では発生しない。

-------------------------------------------------------------------------------------------------------
■前提知識(2)
SQL Server 2005 でも、上の状況は変わらない。
しかし、新しい"スナップショット"分離レベルを使用すると、ロック開放待ちを回避することができる。

--下準備
ALTER DATABASE <データベース名> SET ALLOW_SNAPSHOT_ISOLATION ON

--セッションA：排他ロックのまま10秒WAIT
上記と同じ。

--セッションB："スナップショット"分離レベルで、共有ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT * FROM [dbo].[tab_A]
WHERE [col_B] = '2'

※ロック開放待ちを回避しているはず。

-------------------------------------------------------------------------------------------------------
■問題点
"スナップショット"分離レベルでロック開放待ちを回避できるのは以下の組み合わせだけである。
・セッションAが「排他ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」

以下の組み合わせではロック開放待ちを回避できていない。
・セッションAが「更新ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「更新ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「排他ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「排他ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」

ちなみに、以下はもともと問題ない。
・セッションAが「共有ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「共有ロック」で、セッションBが「更新ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「共有ロック」で、セッションBが「排他ロックのテーブル／クラスタ化インデックスのスキャン」
・セッションAが「更新ロック」で、セッションBが「共有ロックのテーブル／クラスタ化インデックスのスキャン」

（例）更新ロック×更新ロック
--セッションA：更新ロックのまま10秒WAIT
BEGIN TRAN

SELECT [col_B]
   FROM [test].[dbo].[tab_B] WITH(UPDLOCK)
WHERE [col_B] = '1'

WAITFOR DELAY '00:00:10'

COMMIT TRAN

--セッションB："スナップショット"分離レベルで、更新ロックのクラスタ化インデックスのスキャン
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT [col_A]
               ,[col_B]
   FROM [test].[dbo].[tab_B] WITH(UPDLOCK)
WHERE [col_B] = '2'

-------------------------------------------------------------------------------------------------------
■理由
"スナップショット"側ではロックを管理しない。ロックは"本物"側で一元管理しなければならないからである。

よって、
"スナップショット"分離レベルのセッションが共有ロックの場合は期待通りに動作する（"本物"側のロック状況を無視する）が、
"スナップショット"分離レベルのセッションが更新ロックや排他ロックの場合、"本物"側をロックする必要があるらしい。

結局、"スナップショット"としての実装では上記の問題は解決しない。

-------------------------------------------------------------------------------------------------------
■提言（または妄想）
スキャンの際に、いきなり更新ロックや排他ロックをかけようとしないで、まずスキャン内部でデータの状況を参照してから
スキップするかウェイトするか決めればいいのでは？

ちなみに最新バージョンのDB2では、ロック開放待ちの回避策として
「スキャンにおいて、未コミットのINSERT行をスキップする」
「スキャンにおいて、未コミットのDELETE行をスキップする」
「スキャンにおいて、未コミットのINSERT行／UPDATE行／DELETE行がロック対象になる可能性を吟味して、スキップするかウェイトするか決める」
というようなオプションがあるそうです。（DB2 & RedBrick Day 2005 にて）

こんなオプションが考えられますね。
「更新ロックでのスキャンにおいて、排他ロック状態のINSERT行をスキップする」Yes or No
「更新ロックでのスキャンにおいて、排他ロック状態のDELETE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「更新ロックでのスキャンにおいて、更新ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「更新ロックでのスキャンにおいて、排他ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のINSERT行をスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のDELETE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、更新ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
「排他ロックでのスキャンにおいて、排他ロック状態のUPDATE行はロック取得前の状態で判断し、該当しなければスキップする」Yes or No
-------------------------------------------------------------------------------------------------------
■実装時の注意点
で、現実的には...以下の点を注意しましょう。
・"スナップショット"分離レベルのセッションは共有ロックに限定するように実装しましょう。
　　INSERT／UPDATE／DELETE処理を行わないようにしましょう。更新ロックも避ましょう。
・更新ロックでのテーブル／クラスタ化インデックスのスキャンが発生しないように設計しましょう。
　　→更新ロックかけてあとで更新するような長いトランザクションの対象テーブルは、非クラスタなインデックスを作りましょう。（って物理依存...）
・排他ロックでのテーブル／クラスタ化インデックスのスキャンが発生しないように設計しましょう。
　　→同一テーブルの多くの行を更新するクエリがある場合は、非クラスタなインデックスを作りましょう。
　　→同一テーブルの多くの行を更新するクエリは分割しましょう。

っていう感じですかね。
-------------------------------------------------------------------------------------------------------
以上です。

作者:長崎友嘉

更新日:2005年8月5日 15時56分

このブログのホーム

書籍「Microsoft Office Business Scorecards Accelerator ビジネススコアカードで経営情報を分析しよう！」

BSA の書籍の宣伝です。

「Microsoft Office Business Scorecards Accelerator  ビジネススコアカードで経営情報を分析しよう！」

を会社のチームで書きました。
エクスメディアの実践ライブラリ シリーズなので、
画像たっぷり（というか、ばっかりというか）で分かり易いことは保証します。
40ページも費やした付録で使用するサーバコンポーネントのインストールを全て解説（笑）。
ツールの具体的な使用方法もステップバイステップで細かく書いています。

http://www.cbook24.com/bm_detail.asp?sku=4872835123

え、その前に BSA って何かって...
BSA = Microsoft Office Business Scorecards Accelerator は、
SQL Server Analysis Services をデータソースとして使用する、Microsoft が無償ダウンロード提供している KPI モニタリングツールです。
最近（でもないか...）はやりのBIツール、BPMとかCPMとか呼ばれている「KPIをモニタリングするツール」 を検討してみてはいかがでしょうか。

宣伝でした。

作者:長崎友嘉

更新日:2005年6月29日 23時37分

このブログのホーム

最新CTPでサービス名が変更

最新CTP ではサービス名の最初に「SQL Server 」が付いて、認識しやすくなりました。
April CTP では、「Integration Services」や「Analysis Services」でした。
※"YUKONCTP"はインスタンス名です。Integration Services はマルチインスタンスにならないので。

でも、↓フルテキストエンジンサービスが変わってない...

サービスの名前が統一されていない→ソートしたときに1箇所に集まらないのは気になっていたので、変更されてよかった！

作者:長崎友嘉

更新日:2005年6月2日 6時7分

このブログのホーム

OpenReports / Mondrian / JPivot

オープンソースの世界は素人ですが...

Time-Trend.COM 社さんのページから
http://www.time-trend.com/index.files/Page499.htm

オープンソースのBI系ツールが紹介されています。（日本語化しているらしい）
レポーティングツール：OpenReports
OLAPエンジン：Mondrian
OLAPクライアント：JPivot

この中で、JPivot。
「Microsoft MDX に準拠しており、キューブへのクエリ記述作業が効率的におこなえます。」
引用元：http://www.time-trend.com/index.files/Page663.htm
自由度高そう。「効率よく」とは思えませんが。

→よし、軽く評価しよ。

これだけだと怒られそうなので
OpenOLAP というオープンソースのOLAPツールのURLを書いておきます。
http://sourceforge.jp/projects/openolap/

作者:長崎友嘉

更新日:2005年5月30日 18時13分

このブログのホーム

書籍「BIシステム構築実践入門」

書籍「BIシステム構築実践入門」がもうすぐ発売です。
DBマガジンでの連載をまとめて再構成したもので、BI を基礎から理解するのに最適です。

BIシステム構築実践入門
著： 平井明夫
監修： 株式会社アイエイエフコンサルティング
SEShop → http://www.seshop.com/detail.asp?pid=6016

実は宣伝です。すいません。著者は上司です。

作者:長崎友嘉

更新日:2005年5月18日 16時24分

このブログのホーム

[KB897127] Microsoft SQL Server 2000 レポート サービス データ収集 と診断ツール(SRSDiag) に関するFAQ

Microsoft SQL Server 2000 レポート サービス データ収集 と診断ツール(SRSDiag)
というものがあるらしい・・・と、そのFAQだそうです。

http://support.microsoft.com/kb/897127/

作者:長崎友嘉

更新日:2005年5月18日 11時41分

このブログのホーム

（ニュースサイトから）米Microsoft，ビジネス・インテリジェンス・ソフト「Maestro」の非公開ベータ版を発表

ニュースサイトから。

(2005/05/11)
■米Microsoft，ビジネス・インテリジェンス・ソフト「Maestro」の非公開ベータ版を発表
http://itpro.nikkeibp.co.jp/free/ITPro/USNEWS/20050511/160624/


なるほど・・・
Microsoft Office Business Scorecards Accelerator
http://www.microsoft.com/japan/sql/bi/downloads/biaccelerator/bizscorecards.mspx
は前置きだったのか・・・

作者:長崎友嘉

更新日:2005年5月11日 14時1分

このブログのホーム

Reprting Services Web Parts も試しました

そういえば 2000 の Reporting Serivces SP2 に含まれている 「Reprting Services Web Parts」も試しました。
こちらも画像なしですが（時間に追われているのでスイマセン）

ベータのときに書かれている...

■Jan Tielens さんの ブログ
SQL Server Reporting Services Web Parts Review
http://weblogs.asp.net/jan/archive/2005/01/21/358073.aspx

から、特に変わりありません。

Web パーツは「レポートエクスプローラ」と「レポートビューア」の2つ。
Shartpoint への、パーツのセットアップはコマンドラインで。
（sp2Readme_JA.htm に記載されています。）

「レポートエクスプローラ」はプロパティで指定したレポートマネージャ（サイト）の、
特定のフォルダ内のレポートをリストアップする機能。
「レポートビューア」は、パーツ内でレポートを実行する機能。
（ひとことで言うと、iframe要素）

「レポートエクスプローラ」を、「レポートビューア」に関連付けしない場合、
rdl ファイルへのリンクをクリックすると別ウィンドウでレポートを実行。
関連付けした場合は「レポートビューア」で実行。

まあ、こんなとこでしょうか。

作者:長崎友嘉

更新日:2005年5月5日 15時27分

このブログのホーム

Report Builder と レポートモデルプロジェクト

SQL Server 2005 April CTP で、Report Builder を試してみました。
画像は取ってませんがメモだけ。

■最初にレポートビルダを開く
レポートマネージャ（デフォルトは http://(サーバ名)/Reports/）に接続すると、
「ファイルのアップロード」ボタンの並びに「レポートビルダ」ボタンがあります。
これをクリックすると、Webブラウザ（以下、IE）上でレポートビルダが開きます。
このとき、使用可能な「データソース」がリストアップされるはずですが、
まだ用意していないのでリストアップされません。

■レポートビルダの「データソース」となるメタモデルを作成し、配置する
BI Development Studio に、「レポートモデルプロジェクト」が追加されています。
このプロジェクトで、Analysis Services と同様にデータソース、データソースビューを作成し、
データソースビューに依存するメタモデルである「レポートモデル」を作成します。

レポートモデルは、テーブルの列と計算列を属性として持たせることができるようです。
例えば数値列ならSUM、MAXなど。日付列なら年、月など。

この「レポートモデル」が、レポートビルダにおける「データソース」になります。
（同じ言葉（データソース）が複数の意味で使用されているので注意）
レポートモデルをBI Development Studio で「配置」することで、レポートマネージャ上に「レポートモデル」が配置されます。
（フォルダ「モデル」が自動的に作成されます。予約フォルダになるのかな？）
ついでにフォルダ「データソース」が作成され、レポートモデルが使用しているデータソースが配置されました。
なお、「ファイルのアップロード」ボタンでレポートモデルをレポートマネージャ上に配置するのは上手くいきませんでした。
（先に、「データソース」フォルダと、使用しているデータソースをアップロードしておく手順かな？）

■改めてレポートビルダを開く
改めてレポートビルダを開くと、「データソース」（すなわちレポートモデル）が選択できるようになっています。
1つ選んで、レポートビルダ上で（すなわちIE上で）レポートを定義します。
最後に「作成」ボタン（だったかな？）を押すと、配置するフォルダ、ファイル名を指定して、レポートマネージャ上に配置できます。
実行はいままでどおりです。

■Report Builder って
一般ユーザが、IE上で、事前に用意されたレポート用メタモデルを使用して、rdlファイルを作成して、レポートマネージャ上に配置する
機能みたいですね。

作者:長崎友嘉

更新日:2005年5月5日 15時11分

このブログのホーム