「都市伝説 (Urban Legends) を読み解く」を読み解く ~ 必要なデータをフィルタリングする方法

まずはこちらをご覧ください。SQL Server プロダクトマネージャー 北川がBLOGに投稿した記事です。 都市伝説 (Urban Legends) を読み解く – 必要なデータをフィルタリングする方法は!? この中で「PowerPivot で必要なデータをフィルタリングする方法は!?」の項で触れられているレポーティングサービスからのデータ取込みについて補足しておきたいと思います。  PowerPivot for EXCEL 2010 を使用したセルフサービスBIは、今後確実に現場ではやるテクノロジーだと思います。MS社内でも、特に社員教育があったわけでもないのに、気が付けばエンジニアではない多くの社員がマーケティングデータや売り上げデータを PowerPivot を使用して分析しており、「現場は正直だなぁ」と思った次第です。 ただ、セルフサービスBIを社内に浸透させるうえで注意しなければならないことがあります。それはテーブルへのアクセス権です。以前、このBLOGでも触れたことがありました。以下の投稿では、SQL Server のテーブルへの接続権限をエンドユーザーに与える方法について書いたものです。 【SQL】PowerPivotを使用してデータベースに接続するとアクセスが拒否される SQL Server に直接アクセス権を与える方法は確かに手軽ですが、分析に不必要なデータまで提供してしまうことになります。2007年度の分析を行いたいのに、2001年のデータまで含まれていたりとか。また、生データなのでエンドユーザーから見れば無駄なカラムあったり、テーブル構造がエンドユーザーには理解しずらかったりと、その問い合わせによって管理者の手間を増やしてしまう可能性もあります。 そこで、お勧めの方法として用意されているのが、「SQL Server Rporting Service(SSRS)」を使用した方法です。都市伝説を読み解く記事で、北川が触れている方法です。 右の図に書かれている通り、社内の CRM や ERP システムのデータをそのままPowerPivotのデータソースとするのではなく、一端 SSRS を介することで「整理された生データ」として公開することができます。 管理者にとっては、レポートビルダー 3.0 を使ってテーブルを作ってあげるだけですから、エンドユーザーが多くなればなるほどテーブルへのアクセス権を与えるよりもはるかに作業が軽減されます。 レポートビルダー3.0に用意されているデータソースとの接続性は以下の通りです。 SQL Server SQL Azrele SQL Server 並列データウェアハウス(日本未発表) OLE DB SQL Server Analysis Service Oracle…

0

【BI】PowerPivot 計算列で使用できる Excel の関数

※2010/5/26 BLOG環境の移行により消えてしまった記事を再投稿しました こんにちは。PowerPivot 生活をエンジョイしていますか? PowerPivot には「計算列」という独自の列を追加することができることはご存知の通りです。 例えば、読み込んだテーブルに「単価」と「受注数量」しかない場合には、新たな列「受注金額」に [単価]*[受注数量] を埋め込むことができます。一度定義すれば、すべての行に自動的に定義されるため、とても便利な機能です。 この計算列で使用できる数式言語は DAX(Data Analysis Expression)言語と呼ばれています。DAX言語を使用すると、複雑な計算が簡単に行え、独自の計算列を作成することができます。 DAX言語では Excelでサポートされている関数も使用可能です。ただし、すべての関数が使えるわけではなく、以下に示すものがサポートされています。 日付/時刻関連 論理演算 統計 DATE AND AVERAGE DATEVALUE IF AVERAGEA DAY IFERROR COUNT EDATE NOT COUNTA EOMONTH OR COUNTBLANK HOUR FALSE MAX MINUTE TRUE MAXA MONTH   MIN NOW 算術 MINA SECOND ABS テキスト操作 TIME CEILING, ISO.CEILING CONCATENATE TIMEVALUE EXP EXACT…

0

【BI】PowerPivot for SharePoint で「ユーザー資格情報の委任を実行できません」が表示される場合の対処(暫定版)

※ 2010/5/26 BLOG環境の移行により記事が一部削除されてしまったため、再投稿しました ↓ 注目コンテンツ ↓ PowerPivot for SharePoint はお試しになりましたか?なかなかいいやつなのですが、セットアップが少々やっかいかもしれません。 というのも、正しくセットアップしたつもりが、以下の現象に見舞われることがあります。 【現象1】 [SharePoint 2010 全体管理]-[アプリケーションの全体設定]-[PowerPivot 管理ダッシュボード]を開くと以下のエラーが表示される  データ接続に Windows 認証が使用されていますが、ユーザー資格情報の委任を実行できません。以下の接続を更新できませんでした。Data 【現象2】 現象1と同じく、[SharePoint 2010 全体管理]-[アプリケーションの全体設定]-[PowerPivot 管理ダッシュボード]を開くと以下のエラーが表示される。  外部データソースへの接続を確立しようとしたらエラーが発生しました。以下の接続を更新できませんでした。 【現象3】 PowerPivot ギャラリーでワークブックを開き、スライサーを操作しようとすると以下のエラーが表示される。  データ接続に Windows 認証が使用されていますが、ユーザー資格情報の委任を実行できません。以下の接続を更新できませんでした。(現象1と同じエラーメッセージ) 【現象4】 現象3 と同様に、PowerPivotギャラリーでワークブックを開いて、スライサーを操作しようとすると以下のエラーが表示される。   外部データソースへの接続を確立しようとしたらエラーが発生しました。以下の接続を更新できませんでした。PowerPivot Data これらの現象が発生したとき、イベントログ([アプリケーションとサービス ログ]-[Microsoft]-[SharePoint Products]-[Shared]-[使用可能])には以下のエラーが出ている可能性があります。   ログの名前: Microsoft-SharePoint Products-Shared/使用可能 イベントID:5252 ソース: Excel Services アプリケーション メッセージ:Excel Service アプリケーションが Windows ID を取得できなかったので、資格情報の委任に失敗しました…

0

【BI】PowerPivot for EXCEL 2010 のアーキテクチャと容量制限などを簡単に

いやー、ここ数日の当 BLOG へのアクセス数をみると、見事に人気のない PowerPivot に関する投稿です(笑)。でもいいんです。認知が低いものを訴求するのが私の仕事ですから…(涙)。 興味のある方は 1,048,576人にお1人程度かと思いますが、PowerPivot for EXCEL 2010 のアーキテクチャと、ハードウェア要件および容量制限等について簡単に書いておきます。参考サイトは以下の通りです。 MSDN:PowerPivot Team Blog より PowerPivot Component Architecture PowerPivot for Excel 2010 は Excel 2010 のアドインであることは何度も触れている通りです。このアドインをインストールすると Excel 2010 にデータ分析のための機能が追加されます。これによって、SQL Server Analysis Service(SSAS)を使用することなく、OLAPベースの ピボットテーブル、ピボットチャートが使えるようになるのです。よろしければ以下のサイトも参考にしてください。 【BI】PowerPivot ってなんだ? 「アドイン」であるということはとても重要な概念でして、これを踏まえて以下の図をご覧ください。PowerPivot for EXCEL 2010 のアーキテクチャを表したものです。 ざくっと書いてしまうと、PowerPivot for Excel 2010 Add-In Assembly が PowerPivot を Excel本体にロードし、VertiPaq とエンジンと呼ばれる高速処理機構が圧縮したデータをメモリ上に片っ端から読み込んで処理を行います。PowerPivot の特徴は、「メモリ上で実行する」という点にあり、一切のクエリーや計算はディスクを介さずに実行されます。それによって高速な分析を可能にしています。 それそれのコンポーネントについて、以下に簡単に解説しておきます。 ■ PowerPivot…

2

【BI】イベントログを PowerPivot に読み込んで分析ーその2 分析軸の作成

「イベントログを PowerPivot に読み込んで分析」シリーズです。 はじめに 計画 分析軸の作成 ←今回  PowerPivot for EXCEL 2010 の準備とデータの取り込み 計算列の作成とテーブル間リレーションの作成 ピボットテーブルの作成 今回は EXCEL を使用して分析軸の作成を行います。 前回の話の流れから、以下のシナリオで考えてみます。 シナリオ:時間帯およびサーバーごと、およびサービスごとのエラーの傾向を分析したい ■メジャー(尺度) :エラーの数 ■ディメンジョン(軸) :日時、イベントタイプ、サーバー名、サービス さっそく、分析軸を作成しましょう…といっても大した作業ではありません。単にEXCELのシートを作成するだけです。ここで作成した分析軸を、あとでPowerPivotに読み込みます。 ■日時ディメンジョンの作成 日時ディメンジョンは、イベントの時間的な推移をみるうえでも重要ですし、異なる種類のイベント同志、異なるサーバー間のイベント同志を関係づける際にも重要な軸となります。以下の図を見てください。時間軸が用意されているからこそ、サーバーAとサーバーBそれぞれで発生しているイベントの密度に「同じ時間帯」という関係性を見出すことができます。 パフォーマンスログがとられていれば、以下のようにパフォーマンスとイベント発生の密度の関係を把握することも可能です。 ということで時間軸を作るわけですが、事前に軸の粒度を決めておく必要があります。1秒単位の軸なのか、1分単位なのか、はたまた1時間単位なのか…。これによって分析の粒度が変わってきます。 イベントログを分析する場合、秒単位ほどの細かさは必要ないでしょう。よって、今回は分単位とします。 作成する時間軸の範囲は 2009年12月1日 から 2010年11月30日 の 1年間とします。 ※ 軸を作成するのは Excel 2010である必要はありませんが、ここでは2010を使用します Excel を起動して、適当なシートを開いてください。 はじめに、シートの名前を「時間軸」に変更しておきましょう。 次に、以下のようにシートの1行目に起点となる「2010/12/1 0:00」を入力してください。 これ以降、2010年11月30日 23:59 まで1分単位の値を手で入力するわけにはいかないので、Excel の素晴らしい機能である「フィル」を使用します。以下(左図)のように「フィル」メニューから [連続データの作成] を選択してください。 [連続データ]ダイアログが表示されたら、以下(右図)のように設定します。これは、 選択されたセルを基準にして、列方向に 1分(0:01)単位で2010/11/30 23:59 になるまで加算…

0

【BI】イベントログを PowerPivot に読み込んで分析ーその1 分析の計画

「イベントログを PowerPivot に読み込んで分析」シリーズです。 ※ SSASと併用したデータマイニング については別の機会に..ということでご了承ください はじめに 計画 ← 今回はここ 分析軸の作成 ※2010/03/30 追記  PowerPivot for EXCEL 2010 の準備とデータの取り込み 計算列の作成とテーブル間リレーションの作成 ピボットテーブルの作成 Business Intelligence という言葉から、BIに関連する機能って「なんか人口知能のようなもの」をイメージしてしまう方もいらっしゃると思います。え?いません!? じ、実は、何を隠そう、私がそうです(恥ずかしい….)。 ということで、今回は「分析の考え方」についてズブの素人なりに説明してみたいと思います。 分析を実行するには、はじめに「何を分析したいのか?」をざっくりと洗い出しておく必要があります。具体的に言えば、分析のための「メジャー(尺度)」と「ディメンジョン(軸)」が明確になっていないと分析に着手することができません。 メジャー(尺度)とは、分析したい数字です。イベントログであれば「イベントの数」が該当するでしょう。 もちろん、「イベントの数」といっても、単なる総数では意味がありません。「いつ発生した」「どのサーバーの」「どんなサービスが出した」イベントの数であるかを明確にすることで、はじめて分析が行えます。具体的には、例えば「3月1日から3月10日のあいだの、DNSに関連したエラー」の数 などです。 この「いつ」や「どの」、「どんな」に相当するのがディメンジョン(軸)です。 メジャーが「売上」であれば、ディメンジョンは「店舗」「商品名」「地域」「日時」「値引き額」などでしょう。 恥の上塗りになるので言いたかありませんが、「ディメンジョン」と聞いて「中学校で習ったXYZ軸」を思い浮かべてしまうと、かえって混乱します。だって、店舗、商品名、地域、日時、値引き額 で既に5次元ですから、立体を頭に思い浮かべようとするとワケが分からなくなります。わたしゃ馬鹿なので、その呪縛から抜け出すために相当な時間がかかりました…ほんとに恥ずかしい話です。だからいまだに Newton とか読めないんですよね…。 話を戻しまして。 イベントログの分析であれば、求められるのは以下のような結果かと思います。 ※ 本当は、ここにパフォーマンスログを重ね合わせたいところですが、ひとまず今回はイベントログだけを見ていきます。 時間帯およびサーバーごと、およびサービスごとのエラーの傾向 不正なアクセスが発生している時間帯 重大およびエラーイベントの発生履歴 サーバーの稼働状況 ・ ・などなど 例えば「時間帯およびサーバーごと、およびサービスごとのエラーの傾向」を分析したいと考えた場合には、メジャーとディメンジョンは以下のようになるでしょう。 メジャー(尺度) = エラーの数 ディメンジョン(軸) = 日時、サーバー名、エラーを出しているサービス ここに「サーバーのメンテナンススケジュール」などを組み合わせられると、SEの夜間作業の正当性を主張する際のネタにもなりますね。 メジャーとディメンジョンがざっくり決まったら、必要なデータを準備します。 メジャーとなる「エラーの数」は、イベントログが格納されているテーブルを集計することで取ってこられますから、事前に準備する必要はありません。 では、ディメンジョンはどうでしょうか?…

2

【BI】イベントログを PowerPivot に読み込んで分析ーその0 はじめに

  2010年3月26日に実施したセミナーでは、SSIS を使用してイベントログを SQL Server に取込み、これを PowerPivot for Excel 2010 に読み込んで分析する方法について解説しました。 SSIS を使用して イベントログを SQL Server に取り込む仕組みの構築手順については以下の資料に掲載されておりますのでご覧ください。 ここではSQL Serverに取り込んだデータを PowerPivot に読み込み、分析レポートを作成する方法について、その手順を解説します。 PowerPivot を使用して分析を行うには、以下の手順を踏みます。 メジャー(尺度)とディメンジョン(分析軸)の決定 分析軸の作成 PowerPivot for Excel 2010 の準備とデータ取り込み 計算列の作成とテーブル間リレーションの作成 ピボットテーブルの作成 それぞれの手順について、次回の投稿にて解説してまいりますのでお待ちください。

0

【BI】PowerPivot ってなんだ?

これまで、PowerPivot について何回か投稿してきました。 【BI】クイズに挑戦 PowerPivot for EXCEL 2010 【SQL】PowerPivotを使用してデータベースに接続するとアクセスが拒否される 【SQL】PowerPivot for EXCEL 2010 がうまくインストールできない場合の対処 しかし、そもそも PowerPivot って何?という投稿が一度もありませんでした…深く反省…そこで、今回は PowerPivot そのものをきちんと「ご紹介」したいと思います。 ※PowerPivot for Excel 2010 の使い方を詳しく知りたい方は、是非とも以下のドキュメントをご覧ください SQL Server 2008 R2 自習書「PowerPivot for Excel 2010によるセルフサービス分析」 ※PowerPivot for SharePoint Server 2010 について詳しく知りたい方は以下をご覧ください PowerPivot for SharePoint ——————- BI (Business Intelligence) と聞いて、システム管理者の方々はどのような印象を持たれますか? 「訳がわからん!」というのが正直なところではないかと思います。データベースの管理やチューニングは得意でも、BI となると一気に腰が引けてしまう…そんなエンジニアは多いはずです。実を言えば、私もそうです。 軽視されがちですが、以前から SQL Server には BI をサポートする機能が実装されています。 SQL Server…

2

3/26 SQL Server 2008 R2 セミナー & SQL Server Bloggers Meeting を開催

みなさん、こんにちは。SQL Server 2008 R2 のリリースまで残すところ 40 日あまり。いやーテンションが上がりまくりですね! さて、3月26日、マイクロソフトの新宿オフィスにて、SQL Server のセミナーと Bloggers Meeting を開催いたします。年度末で忙しいと思いますが、是非ともご参加ください。 以下の内容を予定しております。 ※それぞれ個別のお申し込みが必要となりますのでご注意ください ■BI を用いたログ管理(取込みから分析まで)~SQL Server 2008 R2 編 (1月29日実施内容と同様です) 13:30~19:00(懇親会含む)(受付開始 13:00) お申込みサイト http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032443007&Culture=ja-JP 2010年5月1日にリリースが決定した SQL Server 2008 R2 を用いたイベントログの取込みと分析手法について解説します。特に、SSIS については詳しく解説しますので、データの取り込み手法に興味のある方は是非ともご参加ください。 SQL Server Integration Service(SSIS) の基本的な使用方法 SQL Server Integration Service を使用したイベントログの取り込み方法 SQL Server Integration Service を使用した SQL Azure とのデータ同期方法 PowerPivot for EXCEL 2010…

0

【新提案】理想は現場主導のセルフサービスBI

BI (Business Intelligence) と聞いて、システム管理者の方々はどのような印象を持たれますか? 「訳がわからん!」というのが正直なところではないかと思います。データベースの管理やチューニングは得意でも、BI となると一気に腰が引けてしまう…そんなエンジニアは多いはずです。実を言えば、私もそうです。 軽視されがちですが、以前から SQL Server には BI をサポートする機能が実装されています。 SQL Server Integration Services (SSIS) 複数のデータソースを抽出/変換し、データ ウェアハウスとして統合する。俗にいう ETL ツール。 SQL Server Analysis Services (SSAS) 多次元データベース (キューブ) を作成し、オンライン分析処理 (OLAP) やデータ マイニングを行う。 SQL Server Reporting Services (SSRS) 各種 RDB や、キューブ、XML ベースのデータ ソースから、表やグラフを用いたインタラクティブなレポートを作成する。 これらのサービスは使いこなせればかなり便利ですし、とても高度で有用な機能を実装しています。しかしあまりメジャーではない理由…。それは、これらの機能が主に「データベース アプリケーションの開発者」向けだったからです。 事実、これらの機能を使用するには Business Intelligence Development Studio と呼ばれる Visual Studio に似た開発ツールを使用しなければなりません。覚えてしまえばなんてことのないツールではありますが、マイクロソフトからの情報の多くが開発者向けに提供されていることもあり、インフラ担当 SE や…

0