SQL ServerのT-SQLからSSASを利用

#はじめに
SSASは高速（取得するデータ数によるが、数千万件のデータから数秒で結果が得られる）に動作するが、複雑な条件の場合一度プログラムを経由するか、Excelで加工して使用していた。
Stored ProcedureからSSASを使えないかと調べていたところ、[MDX + T-SQL: Combining relational and multi-dimensional data into one query result set ](http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx)を見つけた。
この記事では、MDXを使い簡単にSSASからデータを取得していたが、実際簡単にデータ取得できなかったので記載する。

#訂正
providerを「MSOLAP.5」でなければいけないと書いたが「MSOLAP.5」では認証がうまくいかないことがわかった。
OpenRowSetで実行するとうまくいくと書いたが、これは別環境のSQL Serverで実行していたためたまたまうまくいった。
サーバーのリンクを解決するため調査したところ[Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server](https://blogs.technet.microsoft.com/mdegre/2012/08/18/invoke-sqlcmd-the-32-bit-ole-db-provider-msolap-cannot-be-loaded-in-process-on-a-64-bit-sql-server/)を見つけた。
この通り、msolap110.dllを登録しなおすことで「MSOLAP」で動作し認証もうまくいくことがわかったので記事を訂正する。
登録しなおしたコマンドは下記の通り。

```bat
regsvr32 /u “C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB110\msolap110.dll”
regsvr32 /u “C:\Program Files\Microsoft Analysis Services\AS OLEDB110\msolap110.dll”
regsvr32 “C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB110\msolap110.dll”
regsvr32 “C:\Program Files\Microsoft Analysis Services\AS OLEDB110\msolap110.dll”
```


#環境
SQL Server 2012（64bit）で試した。

#SSASサーバーのリンク
[MDX + T-SQL: Combining relational and multi-dimensional data into one query result set ](http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx)でのリンクサーバーの登録は、「master.dbo.sp_addlinkedserver」を利用しているが、SQL Server 2012ではsp_addlinkedserverはsysスキーマにあるので、「master.sys.sp_addlinkedserver」で登録する。
実際登録したサーバは下記のように登録した。

```sql
exec master.sys.sp_addlinkedserver 
    @server = N'AdventureWorksServer', 
    @srvproduct='', 
    @provider=N'MSOLAP', 
    @datasrc=N'localhost',
    @catalog=N'Analysis Services Tutorial'
```

#リンクサーバーでのMDX問い合わせ
provider「MSOLAP」の64bitが正常に登録されていると下記mdxは正常に動作する。

```sql
select * from OPENQUERY(AdventureWorksServer,
'
select {[Measures].[Internet Sales-Sales Amount], [Measures].[Internet Sales-Order Quantity]} on 0,
        NON EMPTY {[Customer].[State-Province].Children} on 1
    from [Analysis Services Tutorial]
');
```

pvovider「MSOLAP.5」を利用したとき、発生したエラーは下記の通り。

```text
メッセージ 7399、レベル 16、状態 1、行 1
リンク サーバー "AdventureWorksServer" の OLE DB プロバイダー "MSOLAP.5" により、エラーがレポートされました。アクセスが拒否されました。
メッセージ 7350、レベル 16、状態 2、行 1
リンク サーバー "AdventureWorksServer" の OLE DB プロバイダー "MSOLAP.5" から列情報を取得できません。
```

#OpenRowSetでのアクセス
リンクサーバを利用してのアクセスが失敗したため調査していたところ[How to perform a SQL Server distributed query with OLAP Server](https://support.microsoft.com/en-us/kb/218592)を見つけた。
このサイトで「OpenRowSet」を使ってアクセスしておりリンクサーバーを利用しない方法のようで試した結果動作することに成功した。

```sql
select * from openrowset('MSOLAP', 'DATASOURCE=LOCALHOST; Initial Catalog=Analysis Services Tutorial;',
'
select {[Measures].[Internet Sales-Sales Amount], [Measures].[Internet Sales-Order Quantity]} on 0,
        NON EMPTY {[Customer].[State-Province].Children} on 1
    from [Analysis Services Tutorial]
');
```
はじめ上記MDXを実行したときエラーが発生したがわかりやすいエラーメッセージが表示され「ad hoc distributed queries' を有効にする手順」をキーワードに検索すると[ad hoc distributed queries サーバー構成オプション](https://msdn.microsoft.com/ja-jp/library/ms187569.aspx)を見つけ下記SQLを実行し、上記MDXを実行すると結果が得られた。

これも、pvovider「MSOLAP.5」を利用すると上記エラーメッセージが表示される。

```sql
sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO 
```

#最後に
~~とりあえずSSASを利用できるので要件は事足りているがリンクサーバーでの利用したいので引き続き調査を行う。~~
試行錯誤したがリンクサーバーを使っての問い合わせができるようになった。
返された結果をintoでテーブルを作成しデータタイプを見るとMeasureが文字列になっており数値変換の必要があるが、その手間を考慮してもレスポンスがよくSSASを使う価値がある。