【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…


【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 を取得できなかったので、資格情報の委任に失敗しました…


【iPod/iTunes 対応 セミナー動画公開(1)】SQL Server 2008 R2 BI機能によるイベントログの取り込みと分析 全10本

@福岡の安納です。あぁ、腰が痛い…イスって重要ですね… さて、TechNet Edge ってご存知ですか?マイクロソフトが運営するインフラSE向けの動画サイトです。 TechNet Edge 日本語 動画コーナー 実は、TechNet Edge に登録された動画は iTunes からも参照可能です。つまり、iPod にダウンロードできるんですねぇ。iTunes ストアから、「TechNet Edge」で検索してみてください。日本語動画以外も含まれていますが、そんなに数があるわけではないので識別は容易です。   http://itunes.apple.com/us/podcast/home/id321319203 今回公開したのは、以下の10本です。3時間もののセミナーでしたので、結構な本数になってしまいました。  TFセミナー「(01/10) SQL Server 2008 R2 BI によるイベントログの取り込みと分析」 TFセミナー「(02/10) SQL Server 2008 R2 BI によるイベントログの取り込みと分析」~SSISの基礎(1) TFセミナー「(03/10) SQL Server 2008 R2 BI によるイベントログの取り込みと分析」~SSISの基礎(2) TFセミナー「(04/10) SQL Server 2008 R2 BI によるイベントログの取り込みと分析」~SSISの基礎(3) TFセミナー「(05/10) SQL Server 2008…によるイベントログの取り込みと分析」~SSISジョブの自動化 TFセミナー「(06/10) SQL Server 2008…るイベントログの取り込みと分析」~SQL…


【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 になるまで加算…


【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 の準備とデータ取り込み 計算列の作成とテーブル間リレーションの作成 ピボットテーブルの作成 それぞれの手順について、次回の投稿にて解説してまいりますのでお待ちください。


【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

【新提案】理想は現場主導のセルフサービス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 や…


【BI】クイズに挑戦 PowerPivot for EXCEL 2010

Office 2010 のリリースが近づいてまいりました。Office といえば Excel。Excel といえば、PowerPivot。 これからの BI 生活に欠かせない PowerPivotをいち早く理解するためのクイズに挑戦しましょう。 【Q1】1984年、最初に開発された EXCEL はどのプラットフォーム向け? Windows Linux Macx ※2010/3/15 修正 umotoさんご指摘感謝です! IBM OS2 【Q2】Excel 2010 の一番最後のカラム名は? XKD XYZ ZZZ XFD 【Q3】Excel 2010(2007)で新しい行を挿入するためのショートカットは? Alt – H – I – R Alt – I – R – I Shift + Alt + X Shift + Ctrl – R 【Q4】次の中で…