Skip to content
Tags

, ,

Query a cube in T-Sql

2011/09/28

To query a cube in T-Sql you will have to use OPENROWSET, or OPENQUERY. The query returns a “flattened rowset” and not a multi dimensional result set.

With OPENROWSET it looks like this.

SELECT a.*
FROM OpenRowset(‘MSOLAP.4′,’DATASOURCE=TESTDEV; Initial Catalog=Adventure Works DW 2008;’,
‘SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Product].[Category].members ON ROWS
FROM [Adventure Works]’) as a

If you want to use OPENQUERY you must first add a linked server and then use it in the query.

EXEC sp_addlinkedserver
     @server=’OLAP_LINKED_SERVER’,
     @srvproduct=”,
     @provider=’MSOLAP’,

     @datasrc=’TESTDEV’,
     @catalog=’Adventure Works DW 2008′

go

— MDX in OPENQUERY —

SELECT * FROM OPENQUERY(OLAP_LINKED_SERVER,
‘SELECT Measures.[Internet Sales Amount] ON COLUMNS,
[Product].[Category].members ON ROWS
FROM [Adventure Works]’ )

If you want to use dynamic MDX you must execute the whole query (not just the OPENROWSET) as a string as described in this blog post

Advertisements

From → Patrik, SQL, SSAS

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: