1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
|
USE [MA_BASE_TEMP]
GO
/****** Objet*: StoredProcedure [dbo].[testMDX] Date de génération du script*: 07/19/2011 08:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- procédure stockée permettant de lire les données des cubes
/*
-- Amélioration à faire : ajouter des paramètres à cette PS, avec :
- Nom du serveur OLAP : @serveur
- Nom base OLAP : @base_olap
- Provider : @provider = MSOLAP.3 (SSAS 2000), MSOLAP.4 pour du 2005 et 2008
- RequeteMDX : @rq_mdx ici simple requête permettant de recup un montant pour l'année 2005
*/
ALTER Procedure [dbo].[testMDX] as
BEGIN
SET ANSI_WARNINGS ON: -- Must be enabled
SET ANSI_NULLS ON; -- Must be enabled
Declare
@SQL varchar(1200), -- Variable to hold SQL query
@MDX varchar (1000) -- Variable to hold MDX query
-- Establish a link to Analysis Server
exec sp_addlinkedserver
@server='linked_olap', -- Alias used to reference the link
@srvproduct='', -- Not used
@provider='MSOLAP.4', -- OLAP driver MSOLAP.4 pour 2008
@datasrc='MONSERVEUROLAP2008', -- Database server name MONSERVEUROLAP2008
@catalog='Base_Olap' -- Database name
-- Analysis Server requires a TRUSTED connection
exec sp_addlinkedsrvlogin
@rmtsrvname = 'linked_olap', -- Alias used to reference the link
@useself = 'false', -- Use own credentials
@locallogin = NULL, -- Apply to all local logins
@rmtuser = '', -- Remote user name domain\username
@rmtpassword = '' -- Remote user password
-- Create a temporary table that will be used to hold the MDX output
create table #temp_table (periode varchar(2000) null, montant varchar(2000) null)
-- Setup a string to hold the MDX so that the precompiler does not try to validate the syntax
SET @MDX = ' SELECT
{ [Measures].[Montant] } ON COLUMNS,
{ [Periode].[Année] } ON ROWS
FROM [Mon Cube] '
-- Setup a string to insert the MDX results into the temporary table
SET @SQL = 'INSERT INTO #temp_table SELECT * FROM OpenQuery(linked_olap,'''+@MDX+''')'
-- Execute the SQL and remote MDX query
EXEC (@SQL)
-- Select the results from the temporary table to return to the calling program
Select * from #temp_table
-- Drop the temporary table
drop table #temp_table
-- Release the TRUSTED connection
exec sp_droplinkedsrvlogin 'linked_olap', NULL
-- Release the link to the Analysis Server
exec sp_dropserver 'linked_olap'
END
-- STORED PROCEDURE END |
Partager