I need to get a list of available cubes from a DB.

is there an mdx query for that?

<DataSourceInfo>Provider=MSOLAP;Data
Source=local</DataSourceInfo><Catalog>CRM
Oracle</Catalog><Format>Multidimensional</Format><AxisFormat>TupleFormat</Ax
isFormat><Content>Data</Content>

this is part of my xml. Then i need a query.
like 'show tables' but what is it in olap?

You could query the Analysis Services repository for this information. To
find a list of all cubes in an AS database, use something similar to the
following:

SELECT * FROM OlapObjects WHERE ParentID = '<GUID_OF_DATABASE>' AND
ClassType = 9

Cubes have a ClassType of 9 in the repository database. You'll can find the
database GUID by running a query similar to the following:

SELECT * FROM OlapObjects WHERE ObjectName = '<OLAP_DB_NAME>' AND ClassType
= 2

Databases have a ClassType of 2.

Your repository is either an mdb file that will be found in the Data
directory of your Analysis Services install, or a SQL Server database (if
the repository has been migrated).

--
Jay Nathan
http://www.jaynathan.com/blog


"Cindy Lee" <cindylee (AT) hotmail (DOT) com> wrote

Accessing the repository directly ?? Ugh. Definitely NOT supported. If
someone moves the repositry, you don't know about it (with SP3 the
repository connectstring is encrypted in the registry).

The supported way to do this is via schema rowsets. Read in BOL. PTS returns
schema rowsets for cubes, dimensions, etc.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jay Nathan" <jay (AT) jaynathan (DOT) com> wrote

Good points, Dave, thanks for the tip.

--
Jay Nathan
http://www.jaynathan.com/blog


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

I gotta get it with a soap query. I'll that out.


"Jay Nathan" <jay (AT) jaynathan (DOT) com> wrote

I need to find this out with a soap mdx query. I can't go to the machine.
Is there anyway?



"Jay Nathan" <jay (AT) jaynathan (DOT) com> wrote

If you need to use a SOAP query are you using XMLA already? if not, this will definitely help you out lot, plus you could actually start using ADOMD.net 9sits atop the xmla/soap calls).

however, if you need to use plain ol'xmla, the you are looking for a 'discover' call. If you check out the BOL that comes with XMLA, it gives reasonable examples of discover calls for catalogs, cubes etc. basiclly it's just a prm you supply to tell the web service what objects your looking for, and oviously you need to supply other param vals if required (eg need to supply ctalog name if looking for cubes).

--
Message posted via http://www.sqlmonster.com