Querying SSAS with TSQL

Question

Querying SSAS with TSQL

tjcavins

Old Hand

Points: 380
More actions
April 26, 2012 at 12:26 pm

#257117

I'm trying to query against cube data and can't figure it out.
My DB and Analysis Server are on the same machine so I don't need a linked server. All examples I've seen require a linked server and use OPENQUERY.
ie: SELECT * FROM OPENQUERY(LINKEDSERVER, SELECT {}...
My information
DB: MyDB (SQL Server 10.50.1600)
Analysis Server: MyDB(Microsoft Analysis Server 10.50.1600.1)
I have a database in SSAS called DBSSAS. How can I query against it with this setup?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Larry Page(Ehsan.Akbar) SSCommitted Points: 1916 More actions · Answer 1

tjcavins (4/26/2012)
I'm trying to query against cube data and can't figure it out.
My DB and Analysis Server are on the same machine so I don't need a linked server. All examples I've seen require a linked server and use OPENQUERY.
ie: SELECT * FROM OPENQUERY(LINKEDSERVER, SELECT {}...
My information
DB: MyDB (SQL Server 10.50.1600)
Analysis Server: MyDB(Microsoft Analysis Server 10.50.1600.1)
I have a database in SSAS called DBSSAS. How can I query against it with this setup?
Thanks

I had this problem before ,you should configure some thing in sql server 2008.

in sql server 2005 you should use openrowset and you should configure all information to connet to database engine ,but microsoft change this method to openquery in sql server 2008.

so it is better to use openrowset instead of openquery

---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)

Jared SSC Guru Points: 61793 More actions · Answer 2

I don't quite understand... Are you trying to query the cube through a connection to the database engine or through a connection to SSAS; i.e. when you have the connection window after opening SSMS, are you selecting Server Type = Analysis Services?

Jared
CE - Microsoft

maddencircledme SSC Enthusiast Points: 157 More actions · Answer 3

I want to be able to run a stored procedure to pass the MDX statement to SSAS but I'm not quite sure of the syntax. Do I need to use OPENROWSET or OPENQUERY or something else?

This may be the same thing tjcavins is trying to do.

Jared SSC Guru Points: 61793 More actions · Answer 4

You can use either. Maybe posting the script would help 🙂 (Not maybe... It would.)

Jared
CE - Microsoft

tjcavins Old Hand Points: 380 More actions · Answer 5

maddencircledme (4/26/2012)
I want to be able to run a stored procedure to pass the MDX statement to SSAS but I'm not quite sure of the syntax. Do I need to use OPENROWSET or OPENQUERY or something else?
This may be the same thing tjcavins is trying to do.

Yes, this would be my goal and I'm also unsure of how to accomplish this. I'm new to working with SSAS.

Jared SSC Guru Points: 61793 More actions · Answer 6

Try this to start: http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx

Jared
CE - Microsoft

Larry Page(Ehsan.Akbar) SSCommitted Points: 1916 More actions · Answer 7

maddencircledme (4/26/2012)
I want to be able to run a stored procedure to pass the MDX statement to SSAS but I'm not quite sure of the syntax. Do I need to use OPENROWSET or OPENQUERY or something else?
This may be the same thing tjcavins is trying to do.

INSERT INTO MINING STRUCTURE [People1]

([CustID], [Name], [Gender], [Age], [CarMake],[CarModel])

openrowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI',

'SELECT [Key], Name, Gender, Age, CarMake, CarModel

FROM chapter3.dbo.People')

---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)

Jared SSC Guru Points: 61793 More actions · Answer 8

Larry Page(Ehsan.Akbar) (4/26/2012)
maddencircledme (4/26/2012)
I want to be able to run a stored procedure to pass the MDX statement to SSAS but I'm not quite sure of the syntax. Do I need to use OPENROWSET or OPENQUERY or something else?
This may be the same thing tjcavins is trying to do.
INSERT INTO MINING STRUCTURE [People1]
([CustID], [Name], [Gender], [Age], [CarMake],[CarModel])
openrowset('SQLOLEDB', 'Server=EHSAN-PC;Integrated Security=SSPI',
'SELECT [Key], Name, Gender, Age, CarMake, CarModel
FROM chapter3.dbo.People')

?

Jared
CE - Microsoft

davoscollective SSCertifiable Points: 6405 More actions · Answer 9

tjcavins (4/26/2012)
My DB and Analysis Server are on the same machine so I don't need a linked server. All examples I've seen require a linked server and use OPENQUERY

I realise this is an ancient post, but just to tie up loose ends for anyone who finds this:

You still need a linked server, SQL and SSAS are different instances. You need to use the MSOLAP provider to create the linked server.

Then you can use OpenQuery() and pass through a valid MDX query as a string.

If you want to send parameters to the MDX query from SQL, then the entire statement needs to be Dynamic SQL, OpenQuery function by itself does not allow inserting parameters into the query statement.

e.g.

Declare @sql varchar(8000)

@sql = 'Select * from Openquery(LINKEDSERVERNAME,''Select [Measures].[Something] on 0 from [Cube]'')'

Exec @sql

gradin.cronos Newbie Points: 3 More actions · Answer 10

found an easier way to do it,

1. add a linked server,

http://sqlblog.com/blogs/stacia_misner/archive/2010/11/30/31193.aspx

2. enable RPC on linked server

http://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx

3. execute query

exec ('<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Type>ProcessFull</Type>

<DatabaseID>*databasename*</DatabaseID>

</Object>

</Process>') at #linkedservername#