「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。 -[[戻る>SQL Server]] * 目次 [#rba1dafb] #contents *概要 [#m05659b3] SQL Server Analysis Services(SSAS:分析サービス)は、~ SQL Server の標準機能として搭載されている、"データ分析" のための~ サーバ機能(自習書シリーズ「Analysis Services 多次元モデル入門」より引用) -AD(Active Directory)のユーザセキュリティを前提とするので、AD環境が必須 -下記ビジネス インテリジェンス(BI)アプリケーション用の機能が用意されている。 --[[オンライン分析処理 (OLAP) 機能>https://dotnetdevelopmentinfrastructure.osscons.jp/index.php?%E3%83%93%E3%82%B8%E3%83%8D%E3%82%B9%20%E3%82%A4%E3%83%B3%E3%83%86%E3%83%AA%E3%82%B8%E3%82%A7%E3%83%B3%E3%82%B9%EF%BC%88BI%EF%BC%89#fd192c94]]~ --[[データ マイニング機能>https://dotnetdevelopmentinfrastructure.osscons.jp/index.php?%E3%83%93%E3%82%B8%E3%83%8D%E3%82%B9%20%E3%82%A4%E3%83%B3%E3%83%86%E3%83%AA%E3%82%B8%E3%82%A7%E3%83%B3%E3%82%B9%EF%BC%88BI%EF%BC%89#e629d176]] -Excelのピボットテーブルのデータソースとして利用でき、 --リボンの「ピボットテーブルツール」、「フィールドリスト」で~ ドラッグ&ドロップの操作だけで自由に分析軸を入れ替えたり、 --ドリルダウン(詳細を表示)をしたり、指定した値でフィルターしたり、 >などなど、高度なデータ分析を行うことができる -Oracle や DB2 など、ほかのデータベース製品のデータをもとに OLAP キューブを作成することもできる --Oracleに直接接続するのではなくSQL Serverでリンク サーバーにするケースもある ---参考:[[SQL Server で Oracle へのリンク サーバーのセットアップおよびトラブルシューティングを行う方法>https://support.microsoft.com/ja-jp/kb/280106]](2011年頃) *詳細 [#ccb48b71] **OLAP機能 [#r13277c6] -以下の3つのモード用意している -インストール時のオプションで設定し、変更はできない。 ***多次元 OLAP (MOLAP) [#k402902c] -集計された実体キューブを構築(コンパイルのイメージ)し、クライアントが参照。 -直接キューブに対して、読出しと書込みの両方ができる。 -分析時にデータソースにアクセスしないので、応答時間が速い。 -データを変更した場合、キューブの再構築が必要。 ***リレーショナル OLAP (ROLAP) [#b8291422] -クライアントの要求に基づき、データソースにアクセスし、分析を開始する。 -データベースの性能がボトルネックになる。 -実体キューブを構築しないので、データの変更を気にしなくて良い。 ***ハイブリッド OLAP (HOLAP) [#g0a48364] -MOLAP と ROLAP の属性を組み合わせたもの -キューブは構築するが、構造だけでデータは格納しない(データのインデックスのみを保持) -※参考[[パーティションのストレージ モードおよび処理>https://msdn.microsoft.com/ja-jp/library/ms174915(v=sql.120).aspx]] **動作モードについて [#o8baa15a] -以下の2つのモード用意している -インストール時のオプションで設定し、変更はできない。 -動作モードによってモデル構造も異なる。 ***多次元モード [#zcf84825] -SQL Server 7.0 の OLAP Servicesの頃から提供されている成熟された技術(モード) -データマイニング機能を利用できるが、スキル習得まで時間がかかる ***表形式(テーブル)モード [#fe991ce8] -2012から追加されたインメモリで動作する高速な処理エンジンモード -PowerPivot の処理エンジンを改良したものがサーバ上で動作するイメージ -セルフBIを目指したコンセプトと言われている -参考 --[[テーブル ソリューションと多次元ソリューション (SSAS) の比較>https://msdn.microsoft.com/ja-jp/library/hh212940(v=sql.120).aspx]] --[[BI Semantic Model>https://msdn.microsoft.com/en-us/library/jj735264.aspx]] ※ 本文のwordドキュメント(英語) **ロール [#x90131f8] アクセスセキュリティをロールで設定可能で、 モデルによって設定箇所が異なる(サーバロールは同様?) ***多次元モデル [#m979448c] -大きく分けてサーバロール、データベースロールがあり、グループ - グループメンバ的な管理が可能 -オブジェクト毎に細かい設定ができ、例えばディメンションに対して~ 見れるユーザと見れないユーザをロールによって制御することができる -参考 --[[セキュリティ ロール (Analysis Services - 多次元データ)>https://msdn.microsoft.com/ja-jp/library/ms174840(v=sql.120).aspx]] ***テーブルモデル [#w1bb7982] -多次元モデルと同じように設定可能。※ どこが違うのかは未調査 -参考 --[[ロール (SSAS テーブル)>https://msdn.microsoft.com/ja-jp/library/hh213165(v=sql.120).aspx]] *基本的な作業の流れ [#a108a826] **前提 [#e1526c4d] -多次元モデルが前提 -SQL Server Data Toolsを使用 -分析に適した接続可能な対象データベースがある **流れ [#n3adc1f2] 参考:自習書:Analysis Services 多次元モデル入門・STEP 2. Analysis Services 多次元モデルの基本操作 ***多次元モデル プロジェクトの作成 [#eecbaf0d] -SQL Server Data Tools ([[Visual Studio]])を起動し、プロジェクトを作成 -「Analysis Services多次元およびデータマイニング プロジェクト」を選択、基本項目(プロジェクト名)などを入力 ***データ ソースの設定 [#c703d126] -キューブの元となるデータを格納しているデータベースサーバに対して接続の設定をする -プロバイダを指定することによってOracleに接続する事も可能 -Windowsサーバにプロバイダをインストールする事によって~ 他のデータベースの接続も理論的には可能 ※ 未検証 ***データ ソース ビューの設定 [#fa4d2bd7] -基本的なビューのスキーマはウィザードで自動的に作成可能~ (キーがしっかり定義されていれば、インテリジェンスが自動でリレーションも作成する) -「テーブルの置換 > 名前付きクエリ」を実行すると、SQLエディタが表示されSQLを書くことができる~ (where句やjoinも普通に使用できるので、ここで大福帳的なデータを作成することも可能) ***名前付き計算の追加 [#i1dbd0f7] -必要に応じて、テーブル内の項目同士を式を使って演算したカラムを作成 -※ 単価 * 数量 = [受注金額] など ***OLAP キューブの作成 [#xe2f743b] -キューブ・ウィザードを使用して、ディメンションやメジャーを大まかに定義 -※ 詳細な設定は後で一つ一つ行う必要がある。 ***属性と階層の設定 [#x4c29d4b] -分析軸となるディメンションの「属性」、「階層」を設定(設計) -メジャーに対して、どの関連データの切り口で分析するか、どんな階層をもたせるかデザインする ***OLAP キューブの参照(利用) [#xc564385] キューブの処理を実行すると、 -「MDX クエリ デザイナー」と呼ばれるツールでキューブを確認 -ExcelのPivotTableのデータソースとして接続することで利用 できるようになる **計算メジャー [#v8078637] ***名前付き計算 と 計算メジャーについて [#ba21702f] -名前付き計算はテーブルに設定してメジャーとして使用するが(※)、テーブルには設定せずにキューブ内に計算メジャーを作成し、同様な事ができる ※使用しないこともある --難易度高でパフォーマンスが悪いが、組込みの集計関数を使用して特殊な計算ができる ---参考: [[メジャーおよびメジャー グループ>https://technet.microsoft.com/ja-jp/library/ms174792(v=sql.110).aspx]] ---参考: [[集計関数の使用>https://technet.microsoft.com/ja-jp/library/ms365396(v=sql.110).aspx]] ***書き戻し(WriteTable) [#k5229ef6] -多次元モデルの場合、「書き戻し」と言って処理後のキューブのデータに対してメジャーの更新が可能 --キューブ > パーティションの設定で有効にする必要がある --Excel(Pivot Table)から利用するする場合、あわせてPivot Tableオプションから「What-if分析」を有効にする必要がある -この機能をうまく利用すると、現在のデータを元に将来の予測増減データを更新保存し、予測シミュレーション的な事ができる -書き戻しで保存したデータは、[メジャーの格納テーブル名]_WriteTableと言う名前のテーブルに差分として格納される --元々の値''100''を''101''に変更保存した場合、_WriteTableには''1''が格納される --元々の値''100''を''99''に変更保存した場合、_WriteTableには''-1''が格納される ---PivotTableなどで接続して確認すると、メジャーの格納テーブル(元テーブル)のデータとそのWriteTableのデータが合算されて表示される -差分のトランザクションが保存されるため、更新回数が多いとレコード数が嵩んでいきパフォーマンスに影響する%%ことがある%%(自動で集約される事は無い) --1レコードはPivotTableの1セルに相当する ---1000セル更新したら1000レコードのトランザクションが格納される ---毎日毎日更新したら、毎日毎日トランザクションデータが増えていく --書き戻しのデータを集約する方法はいくつかあるが、例えばtmpテーブルにWriteTableのデータをキーでGroupかつsumして格納しておき、さらにtmpと元テーブルとを合算したデータをあらたなtmpに作成後、元のテーブルデータを消去、合算したデータを元のテーブルに入れると言った操作が必要となる(tmpテーブルはビューで代替しても良い) -参考: [[パーティションの書き戻しの設定>https://msdn.microsoft.com/ja-jp/library/ms174823(v=sql.120).aspx]] -参考: [[Excel 2010 Writeback to Analysis Services>https://www.youtube.com/watch?v=RVfmoRGlSu0]] (youtube) //*ToDo: 分析ツールとしてExcelのPivotTableを使う例を書く //--ToDo: 他、SSRS、PowerViewのさわりだけ書く // //*ToDo: 開発の言語、アーキテクトが対象によって違う事を書く //-開発者ガイド (Analysis Services) //--https://msdn.microsoft.com/ja-JP/library/bb500153(v=sql.120).aspx //--「テーブル モデルのプログラミング」から大雑把に説明し、特に「多次元モデルのプログラミング」について書く // //*ToDo: SQL Server Data ToolsとManagementStudioでできることの違いについて //-書く // //*ToDo: ADOMD.NET での開発について記載する [#vae61a4c] //-https://msdn.microsoft.com/ja-jp/library/ms123483(v=sql.120).aspx // //*ToDo: ローカルキューブについて書く [#mec90cb7] **その他 [#y1c6fd5c] -エディション毎の機能サポートについてはMicrosoftのページ [[SQL Server の各エディションがサポートする機能>https://msdn.microsoft.com/ja-jp/library/cc645993(v=sql.120).aspx#SSAS]]を参照 -[[自習書シリーズ>https://www.microsoft.com/ja-jp/server-cloud/local/sqlserver/2012/technology/self-learning.aspx?WT.srch=1&WT.mc_ID=SEM_jHjUAPFU]] --[[Analysis Services 多次元モデル入門>http://download.microsoft.com/download/5/A/2/5A29D047-2D83-4ACD-919F-4614847E8392/SQL11_SelfLearning10_SSAS.pdf]] --[[Analysis Services 多次元モデル応用>http://download.microsoft.com/download/5/A/2/5A29D047-2D83-4ACD-919F-4614847E8392/SQL11_SelfLearning11_SSAS2.pdf]] --[[Analysis Services によるインメモリ BI 入門>http://download.microsoft.com/download/5/A/2/5A29D047-2D83-4ACD-919F-4614847E8392/SQL11_SelfLearning12_InMemory.pdf]] ---- Tags: [[:データアクセス]], [[:SQL Server]]