Dynamically Call a Analysis Services Linked Server and Cube

Question

Post reply

Dynamically Call a Analysis Services Linked Server and Cube

ItzMe

Ten Centuries

Points: 1233
More actions
January 22, 2009 at 12:33 am

#196313

I have a stored procedure which updates a table [MyDB].[dbo].[Report_Measures], using a OPEN QUERY containing a MDX query.
Gets data from a cube 'CUBE1' in analysis services linked server 'ASLS1'
CREATE PROCEDURE CUBEPROC
@LINKEDSERVERNAME NVARCHAR(50),
@CUBENAME NVARCHAR(50)
AS
UPDATE [MyDB].[dbo].[Report_Measures]
SET LinkedServerName = @LINKEDSERVERNAME, CubeName = @CUBENAME,
[Sales] = (Select * from openquery (ASLS1,'select [Measures].[Sales] on 0
from [CUBE1]'))
WHERE Row_NO = (select max(Row_No) from [MyDB].[dbo].[Report_Measures]);
Exec CUBEPROC 'ASLS1', 'CUBE1' -- will get the Sales measure from CUBE1 in ASLS1.
My task is to make this stored procedure execute dynamically, say i have AnalysisServicesLinkedServers like
ASLS1
ASLS2
ASLS3
I should be able to connect to any of the LinkedServers within the above list and should connect to a concerned cube within that linked server.
A linked server may have any no. of cubes within.
I used parameters for LinkedServerName and CubeName to execute this stored procedure dynamically but could not fix it . The code looks as below using parameters.
CREATE PROCEDURE CUBEPROC
@LINKEDSERVERNAME NVARCHAR(50),
@CUBENAME NVARCHAR(50)
AS
UPDATE [MyDB].[dbo].[Report_Measures]
SET LinkedServerName = @LINKEDSERVERNAME, CubeName = @CUBENAME,
[Sales] = (Select * from openquery (@LINKEDSERVERNAME,'select [Measures].[Sales] on 0
from [@CUBENAME]'))
WHERE Row_NO = (select max(Row_No) from [MyDB].[dbo].[Report_Measures]);
How can i make this stored procedure run to execute dyanmically?
Let me know, if anything is unclear.
Please help on this issue.....
Regards
ItzSam

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ItzMe Ten Centuries Points: 1233 More actions · Answer 1

I got a fix for it.

Here is the code.........

DECLARE @sSQL varchar(4000);

SET @sSQL = '

UPDATE [MyDB].[dbo].[Report_Measures]

SET LinkedServerName = ''' + @LINKEDSERVERNAME + ''',

CubeName = ''' + @CUBENAME + ''',

[Sales] = (

SELECT *

FROM OPENQUERY(' + @LINKEDSERVERNAME + ',''

SELECT [Measures].[Sales] on 0

FROM [' + @CUBENAME + ']

'') )

WHERE Row_NO = (select max(Row_No) from [MyDB].[dbo].[Report_Measures]) ';

EXEC (@sSQL);

Embed the above code into the stored procedure CUBEPROC.

For Execution of Stored Procedure,

Exec CUBEPROC 'ASLS1','CUBE1'

Hope this helps others in the future.....:)

Regards

ItzSam

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 2

I also have an example of setting this up dynamically on this posting here if you need a reference - Using Reporting Services (SSRS) with SSAS data. The example is towards the bottom of the posting.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com