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

はじめに

SSASは高速（取得するデータ数によるが、数千万件のデータから数秒で結果が得られる）に動作するが、複雑な条件の場合一度プログラムを経由するか、Excelで加工して使用していた。
Stored ProcedureからSSASを使えないかと調べていたところ、MDX + T-SQL: Combining relational and multi-dimensional data into one query result set を見つけた。
この記事では、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を見つけた。
この通り、msolap110.dllを登録しなおすことで「MSOLAP」で動作し認証もうまくいくことがわかったので記事を訂正する。
登録しなおしたコマンドは下記の通り。

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 でのリンクサーバーの登録は、「master.dbo.sp_addlinkedserver」を利用しているが、SQL Server 2012ではsp_addlinkedserverはsysスキーマにあるので、「master.sys.sp_addlinkedserver」で登録する。
実際登録したサーバは下記のように登録した。

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は正常に動作する。

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」を利用したとき、発生したエラーは下記の通り。

メッセージ 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を見つけた。
このサイトで「OpenRowSet」を使ってアクセスしておりリンクサーバーを利用しない方法のようで試した結果動作することに成功した。

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 サーバー構成オプションを見つけ下記SQLを実行し、上記MDXを実行すると結果が得られた。

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

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

最後に

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