Mdx Queries on Stored Procedure

hello,
   I wonder if i could call a queries to OLAP cube
from stored procedure ?
Can someone show me the example ?
g_tempAsked:
Who is Participating?
 
SYASSINConnect With a Mentor Commented:
try the following:

select {[Measures].members} on columns, non empty {[CardID].members} on rows from Card where ([ProdID].[0162930169])

or

select {[Measures].members} on columns, {non empty [CardID].members} on rows from Card where ([ProdID].[0162930169])
0
 
SYASSINCommented:
As far as I know this is a mixed answer... Yes if you want to run a store procedures with a parameter, no if you need to add a parameter.  The reason being that you would have to embed the mdx into an openrowset query.  Here is an example:


CREATE PROCEDURE procedurename
as
select * from
openrowset(
'msolap', 'Datasource=SERVERNAME ; USER ID=; PASSWORD=; Initial Catalog=LIBRARY NAME;',
'PLACE MDX Statement here')

If anyone knows a way to make parametrized mdx stored procedure, I would love to know!!!!

Sultan
0
 
SYASSINCommented:
oops... the answer above should read:


Yes if you want to run a store procedures without a parameter, no if you need to add a parameter.  
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
g_tempAuthor Commented:
Hi,
I try with the answer but the system complains with the following message. Why ?


Error 7399: OLE DB provider 'msolap' reported an error. The provider did not give any information about the error.
0
 
SYASSINCommented:
Check your username and password as well as your library name.  Remember the library name is  not the cube name, its the catalog name.

Also, you may want to try adding the olap server as a linked server.  From the query analyzer type the following:

EXEC sp_addlinkedserver
@server='olap_server',
@srvproduct='',
@provider='msolap',
@datasrc='servername',
@catalog='catalogname (eg Foodmart)'

Once you have added the linked server, you can simplify the above stored procedure to the following:

CREATE PROCEDURE procedurename
as
select * from
openrowset(olap_server, 'PLACE MDX Statement here')

hope that helps.
0
 
g_tempAuthor Commented:
Hi,
I encounter error putting the following statement
but the system complian error about the openrowset,
this statement work perfectly on VC using ADO.
Why ?

select [Measures].members on columns, non empty [CardID].members on rows from Card where [ProdID].[0162930169]
0
 
SYASSINCommented:
try the following:

select {[Measures].members} on columns, non empty {[CardID].members} on rows from Card where ([ProdID].[0162930169])

or

select {[Measures].members} on columns, {non empty [CardID].members} on rows from Card where ([ProdID].[0162930169])
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.