On m'a fourni l'access à un cube et j'ai besoin de savoir si je peux configurer une procédure stockée qui peut se connecter à un cube et récupérer le contenu (via une requête MDX). J'en ai besoin pour éviter d'avoir à exporter datatables du Management Studio ou d'Excel (via PowerPivot). Je suis très nouveau dans les requêtes cubes / olap, alors pardonnez toute naïveté que je pourrais montrer.
Le plus simple est de créer un server lié à votre cube, puis INSERER … SELECTIONNER D'OPENQUERY http://sqlblog.com/blogs/stacia_misner/archive/2010/11/30/31193.aspx
Cette option a des limites en ce que
Une option avancée est la procédure stockée ExecuteOLAP CLR https://olapextensions.codeplex.com/
Si vous choisissez d'utiliser OPENQUERY (de la manière la plus simple, mais avec les limitations spécifiées par Brian), la procédure suivante peut être utile:
/* PARAMS: @mdx: mdx statement @mdx_columns: specifies the mdx columns to resortingeve from the executed mdx @linkedServer: linked server to be used @resultsTable: temporary table to hold results from mdx @resultsCols: if only some columns should be filled in @resultsTable, specify them here (eg '(col1, col2, ... )' ) @expectedColCount: expected column count for mdx result. If actual column count is different from the expected count, no data is filled in @actualColCount: actual column count. Specify NULL if not interesed in this value @Debug: outputs debug info */ ALTER PROCEDURE [dbo].[exec_mdx_over_linked_server] ( @mdx NVARCHAR(MAX), @mdx_columns NVARCHAR(1024) = '*', @linkedServer VARCHAR(64), @resultsTable VARCHAR(64), @resultsCols VARCHAR(1024) = '', @expectedColCount SMALLINT, @actualColCount SMALLINT = NULL OUTPUT, @Debug BIT = 0 ) AS BEGIN SET NOCOUNT ON if (@Debug = 1) PRINT 'Started exec_mdx_over_linked_server procedure for populating ' + @resultsTable IF LEN(@MDX)>8000 RAISERROR ('MDX too long for openquery (exec_mdx_over_linked_server)', 16, 1); declare @SQL NVARCHAR(MAX) IF (@Debug = 1) BEGIN -- getting results from mdx SET @SQL = 'SELECT ''Mdx results for ' + @resultsTable + ''' AS ''Mdx results'', ' + '*' + ' FROM OPENQUERY(' + @linkedServer + ', ''' + @mdx + ''')'; IF LEN(@SQL)>8000 RAISERROR ('MDX too long for openquery (exec_mdx_over_linked_server)', 16, 1); EXEC (@SQL) END SET @SQL = ' SELECT ' + '*' + ' INTO #resultsWithWeirdNameToAvoidTempCollisions FROM OPENQUERY(' + @linkedServer + ', ''' + @mdx + '''); SELECT @colCount = COUNT(*) FROM tempdb.sys.columns WHERE object_id = object_id(''tempdb..#resultsWithWeirdNameToAvoidTempCollisions''); if (@colCount = @expectedColCount) INSERT INTO ' + @resultsTable + @resultsCols + ' SELECT ' + @mdx_columns + ' FROM #resultsWithWeirdNameToAvoidTempCollisions' IF LEN(@SQL)>8000 RAISERROR ('MDX too long for openquery (exec_mdx_over_linked_server)', 16, 1); if (@Debug = 1) PRINT 'dbo.exec_mdx_over_linked_server SQL = ' + @SQL DECLARE @colCount INT EXECUTE sp_executesql @SQL, N'@expectedColCount SMALLINT, @ColCount SMALLINT OUTPUT', @expectedColCount = @expectedColCount, @colCount = @actualColCount OUTPUT if (@Debug = 1) BEGIN PRINT '@expectedColCount = '; PRINT @expectedColCount PRINT '@actualColCount = '; PRINT @actualColCount END -- correction for small float numbers (< 10E-10) DECLARE @UpdateSql NVARCHAR(MAX) = N'' DECLARE @SmallThreshold FLOAT = 0.00000000001 SELECT @UpdateSql += ' UPDATE ' + @resultsTable + ' SET ' + QUOTENAME(COLUMN_NAME) + ' = 0 WHERE TRY_CONVERT (FLOAT, ' + QUOTENAME(COLUMN_NAME) + ') IS NOT NULL AND ABS(' + QUOTENAME(COLUMN_NAME) + ') < ' + CAST(@SmallThreshold AS NVARCHAR(30)) FROM tempdb.INFORMATION_SCHEMA.COLUMNS with(NOLOCK) -- WHERE table_name like @resultsTable + '[_][_][_]%' -- changed, in order not to take into consideration objects from other spids WHERE table_name = object_name(object_id('tempdb..' + @resultsTable), (select database_id from sys.databases where name = 'tempdb')) IF (@Debug = 1) BEGIN PRINT '@UpdateSql = '; PRINT @UpdateSql; END EXEC (@UpdateSql); END
Il offre les avantages suivants:
Lors du profilage, j'ai remarqué environ. Surcharge de 100 ms (exécution par la procédure par rapport à l'exécution directe par rapport au server d'parsing).
Les développeurs .NET peuvent utiliser l' infrastructure ADOMD.NET , ce qui permet d'exécuter des requêtes paramétrées et d'avoir un time système réduit.