Querying a cube

Question

Querying a cube

Del Piero

SSCrazy

Points: 2265
More actions
June 19, 2005 at 7:48 pm

#109550

Hi all,
I am new to cubes and Analysis Services. I know there is a product which comes with Analysis Services (seems something like MDX sample application) which allows us to build MDX queries to query an Analysis Services cube.
Is it possible to query a cube through a T-SQL stored procedure? Any simple way to allow our front-end application to query the cube and display the results to them, other than using Pivot Table Service and Excel?
Thanks a lot,
delpiero

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

MDXBuilder is a pure MDX authoring tool. A quick scan of google didn't tell me where you van get it from but you can get a trial version here: http://www.amazon.com/exec/obidos/ASIN/0471400467/ref=pd_sxp_f/002-9886975-3721645

It is not possible to query a cube from a T-SQL sproc in SQL Server 2000. I think this changes in SQL Server 2005 as you can now issue T-SQL against the UDM (which, for simplification, is what Analysis Services has become).

Excel and PTS is the simplest way to go I find. Anything more than that and you're into the realm of front-end vendors (e.g. Panorama, proClarity, MIS AG) which don't require you to have PTS installed on your client.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Del Piero SSCrazy Points: 2265 More actions · Answer 2

Thanks,

Besides using Excel, how can we utilize the Pivot Table Service through a front-end interface? Is it possible to access the cube or execute an MDX statement using ADO?

delpiero

bmiller SSC Journeyman Points: 77 More actions · Answer 3

You could try this to query a cube using TSQL

sp_addlinkedserver

@server = 'OLAP'

, @srvproduct = ''

, @provider = 'MSOLAP'

, @datasrc = 'servername'

, @catalog = 'foodmart 2000'

select * from openquery

(OLAP, 'select {[Gender].[F], [Gender].[M]} on columns

, {[measures].[Unit Sales]} on rows

from [Sales]')

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

As per BMiller's post, you can definitely query AS2K via TSQL using linked servers (ie use openquery) but you could also try opendatasource (ie don't have to have a permanent linked server).

You can also write your own querying and analysis tool, you'd be best to look at ADOMD.net (newer and much more in line with AS2K5 approach) rather than ADOMD. With AS2K, basicallyany fat client (ie not a browser based solution) will require PTS on the client side. Even using ADOMD.net (currently) will use PTS, it's just sitting on the server (ie ADOMD.net uses XMLA to send/receive queries/results, and the AS2K version uses PTS.)

HTH,

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 5

The linked server method works fine. Thanks a lot!

Tiago Rente Default port Points: 1403 More actions · Answer 6

Hi,

Tried out the example above and when executing the query I got this error message:

Server: Msg 7302, Level 16, State 1, Line 1

Could not create an instance of OLE DB provider 'MSOLAP'.

OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP returned 0x80040154].

Does anyone knows how to solve it?

Thanks.

Del Piero SSCrazy Points: 2265 More actions · Answer 7

Were you able to create the linked server using Enterprise Manager?

And did you have the latest service pack applied for your analysis server?

Alex Wilson-148544 SSC-Addicted Points: 450 More actions · Answer 8

morning.

in the 'provider option' [button] on the first tab of the 'add linked server' dialog within EM, make sure that "allow in process" (or something like that, is checked. I dont know of a way of enabling that check box thru QA though, so if anyone knows, please post a note to the group - as i would love to find a way.

Cheers,

- - Alex

Akshaya-779156 SSC Enthusiast Points: 150 More actions · Answer 9

Hi,

I want to retrieve data from a cube. What query can i use to perform search on a cube? I have been using Visual Studio 2005 to create the cube.

Thanks..

Akshaya

elegantkvc SSCommitted Points: 1910 More actions · Answer 10

hi,

You can use the given below query to acheive your task

Select Measures.columnname from Cubename

You can use the columns which are all you want to display.

Regards

vijay

pavan_crm SSC Enthusiast Points: 180 More actions · Answer 11

Hi

I'm new to analysis services in SQL server 2005. However, I've got some cubes defined, the source of which I'm unable to figure out. Can anyone out there help me on find the source of the cube and the columns int he cube.

I want to retrieve data fromt eh cube into teh reporting servies adn then pass a filter on teh rdl file trhu DOTNET program.

ss-710497 Old Hand Points: 308 More actions · Answer 12

HI,

For Retriveing the data from CUBE(using Querie), you have to conncet to Analysis Database in Management studito, then click on new querie and wirte.

Example:

SELECT Measures.[Internet Sales Amount] on COLUMNS

FROM [Adventure Works]

WHERE ( [Date].[Calendar].[Calendar Quarter].&[2004]&[1],

[Product].[Product Line].[Mountain],

[Customer].[Country].[Australia])

Dirk Wegener Ten Centuries Points: 1141 More actions · Answer 13

Akshaya,

Akshaya (6/12/2008)
I want to retrieve data from a cube. What query can i use to perform search on a cube? I have been using Visual Studio 2005 to create the cube.

You can use the browser in the BIDS, SQL Server Management Studio, Mosha's MDX Studio, Excel or a tool like Datawarehouse Explorer

Dirk

http://www.dwexplorer.com
@dtwegener

pavan_crm SSC Enthusiast Points: 180 More actions · Answer 14

pavan_crm

SSC Enthusiast

Points: 180

February 16, 2009 at 4:53 am

#944422

Thnx