Query Analysis Services via a T-SQL CLR.
The ExecuteOLAP CLR Stored Procedure enables querying any analysis services server and database, which you can connect to via your Windows Identity, with nvarchar(max) MDX commands. It also returns a strongly typed result set, regardless
of whether any rows are returned.
DECLARE @Server NVARCHAR(30) = 'localhost'
DECLARE @Database NVARCHAR(50) = 'AdventureWorksDW2012Multidimensional-EE';
DECLARE @MDX NVARCHAR(MAX) = '
select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].Members on 1
from [Adventure Works]
EXEC ExecuteOLAP @Server, @Database, @MDX;
Many are aware of the
OPENQUERY method of querying analysis services. The issues with this method include
- 8000 character constraint on the MDX query
- must manually create linked server for each database
- special code required to handle when result is empty
- excessive data types in result set columns
Requires first creating the Microsoft.AnalysisServices.AdomdClient assembly in the database with Unrestricted.
Why might one need nvarchar(max) MDX queries? Simple. Analysis Services has no temp table support. You can, however, build huge sets for temp table like features.
Let's say you have a big cube which takes 40 hours to process. You need to find the Sales Amount for all products produced in the past 2 years with TaxCode = 5. The TaxCode member is not in the analysis services database and you won't be able to add it until
the next maintenance window. You do have the ProductId member in the olap database though. There are 10000 Products with TaxCode = 5. There are over 15 billion records in the associated fact table and the relational query will take many hours.
With ExecuteOLAP, one can build an MDX set of all ProductIds with TaxCode = 5, query the cube or tabular model and have the results in seconds.