Comment puis-je «extraire» des données d'un cube OLAP MDX dans une table SQL Server standard?

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

  • il a une contrainte de 8000 caractères sur la requête MDX
  • vous devez créer manuellement un server lié pour chaque database
  • un code spécial est requirejs pour gérer lorsque le résultat est vide
  • types de données excessifs dans les colonnes de l'set de résultats (ntext pour les colonnes et nvarchar (4000) pour les lignes)

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:

  • gère le cas lorsque le résultat reçu n'a pas les colonnes attendues (rien n'est fait)
  • effectue des arrondis pour de très petits nombres (cela peut arriver puisque le cube sait fonctionner avec des nombres à floating point seulement)
  • toutes les instructions mdx passent par une seule procédure

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.