This project is read-only.

[Installation]

Installation instructions:

  1. Enable CLR:
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'clr enabled', 1;
    GO
    RECONFIGURE;
    GO
    
  2. Change the db owner for the db you're adding the CLR to to sa:
    EXEC sp_changedbowner 'sa' 
  3.  Make the db you're adding the CLR to TRUSTWORTHY:
    ALTER DATABASE [MyCLRDb] SET TRUSTWORTHY ON;
  4. Verify you have the ADOMDB Client installed. It's typically in C:\Program Files\Microsoft.NET\ADOMD.NET. If not, it's freely available via the Feature Pack page for the product version.  2008 R2 SP2: http://www.microsoft.com/en-us/download/details.aspx?id=30440 2012 SP1: http://www.microsoft.com/en-us/download/details.aspx?id=35580 Download and install.
  5. Execute the script from the Downloads page for the SQL version you are installing on.

[Usage]

Usage:

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;

ExecuteOLAP_results

Create Sets for Temp Table like functionality:

DECLARE @Server NVARCHAR(30) = 'localhost';
DECLARE @Database NVARCHAR(50) = 'AdventureWorksDW2012Multidimensional-EE';
DECLARE @MDX NVARCHAR(MAX) = '', @ProductSet NVARCHAR(MAX) = '{';

SELECT @ProductSet += '[Product].[Product].&[' +  EnglishProductName + '],'
FROM ( SELECT	DISTINCT EnglishProductName
  FROM [AdventureWorksDW2012].[dbo].[DimProduct]
  WHERE ProductAlternateKey LIKE '[ABCD]%'
  ) dp;

SET @ProductSet = LEFT(@ProductSet, LEN(@ProductSet) - 1) + '}';

SET @MDX = 'SELECT 
[Measures].[Internet Sales Amount] ON 0
,NonEmpty((
	' + @ProductSet + '
	)) ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING '

DECLARE @Output TABLE (Product nvarchar(50), Sales money null);

INSERT @Output(Product,Sales)
EXEC ExecuteOLAP @Server, @Database, @MDX;

SELECT * FROM @Output;

ExecuteOlap_results2

Last edited Sep 28, 2013 at 4:48 PM by Brian_Welch, version 12

Comments

No comments yet.