I'm using following code to get list and properties of all objects in my
Analysis Server.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList>
<ObjectExpansion>ExpandFull</ObjectExpansion>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>


The XML fresult is too long and I don't need eveything (e.g. dimension
properties) in my XML result file. My question is how to exclude unwanted
(levels, attributes or properties) in my XMLA query and get only the list of
only Databases and Cubes available on my server. In that case XML file will
be smaller and contain only the data that I need.

Thanks

and I hope this post will help many others.

I can't think of any way of getting just dimensions and cubes in a
single XMLA query. So apart from using XSLT to strip out just what you
need, the other option would be to do multiple queries.

A DBSCHEMA_CATALOGS query will get you a list of all the databases
eg.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions />
<Properties />
</Discover>

Then for each database you could run an MDSCHEMA_CUBES query to get a
list of cubes.
eg.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions />

<Properties>
<PropertyList>
<Catalog>Adventure Works DW</Catalog>
</PropertyList>
</Properties>
</Discover>

If you are using these queries to populate a UI, the multiple query
approach could work quite nicely as normally you would "drill down" from
the database list to the list of cubes for a selected database. You will
also find the more specific queries MUCH faster than a
DISCOVER_XML_METADATA query.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <61BD8102-B77F-430A-AC99-5DF87CEA6EFA (AT) microsoft (DOT) com>,
alextt (AT) discussions (DOT) microsoft.com says...

Thanks Darren for your help.

There is a small problem with DBSCHEMA_CATALOGS - it doesn't list
unprocessed cubes and it doesn't show processing status information about
cubes. Unfortunately, DISCOVER_XML_METADATA is the only solution for me.
Thanks

"Darren Gosbell" wrote:

You should be looking at the ObjectExpansion restriction. Have you looked at
the options ReferenceOnly and ExpandObject?

You may need to send more requests but that is a much more scalable
solution.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"alextt" <alextt (AT) discussions (DOT) microsoft.com> wrote

Thanks all for you help. I need to find another way. DISCOVER_XML_METADATA is
very powerfull and I need to find the way to import XML result somehow into
SQL server table.

Small XML is not problem. I can use:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions>
<RestrictionList/>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>

and then run following for each database:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
</Restrictions>
<Properties>
<PropertyList>
<Catalog>$(DATABASENAME)</Catalog>
<Format>Tabular</Format>
</PropertyList>
</Properties>
</Discover>

and import XML result into SQL using OPENXML but that is not enough
information for me. I need to use DISCOVER_XML_METADATA with EXPANDFull and
import everything into SQL table. Please help if you have time and if you
know the answer.


"Akshai Mirchandani [MS]" wrote:

Why do you *need* to use ExpandFull? And if you actually need all the
metadata then what is the problem?

DISCOVER_XML_METADATA allows you get at all the metadata of objects -- the
ObjectExpansion options allow you to limit how much detail you get back. You
can also fetch metadata on a per-major-object basis.

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"alextt" <alextt (AT) discussions (DOT) microsoft.com> wrote

Thanks Akshai for your time for tyring to solve my problem.

How can I use <ObjectExpansion> property to get the list of all database and
all cubes (for each database) and nothing else. No dimensions, no Measures -
nothing else. Or at least minimum that I can get? ExpandObject is still
masive list...
Thanks


"Akshai Mirchandani [MS]" wrote:

Check out this section from this page:
http://msdn2.microsoft.com/zh-cn/library/ms127747.aspx

--------------------
The ObjectExpansion restriction can be used to control the degree of
expansion of ASSL XML returned by the server. This restriction has the
options listed in the following table.
Enumeration value Allowed for <Alter> Description
ReferenceOnly
no
Returns only the name, ID, and timestamp for the requested object and
for all contained major objects recursively.

ObjectProperties
yes
Expands the requested object and minor contained objects, but does not
return major contained objects.

ExpandObject
no
Same as ObjectProperties, but also returns the name, ID, and timestamp
for contained major objects.

ExpandFull
yes
Fully expands the requested object and all contained objects
recursively.

--------------------

You could do ExpandObject for the server in step 1. Then for each database
returned, you could request ExpandObject for that database -- this is step
2. This would return the Cube IDs/Names under the database -- but
unfortunately you can't avoid getting all the other major objects under the
database also.

Each step does return you a lot more information than you are apparently
interested in. But it is much better than doing ExpandFull...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"alextt" <alextt (AT) discussions (DOT) microsoft.com> wrote

Finally I got this working. I should use xml datatype with sql 2005:

CREATE TABLE xmldata (data xml)


INSERT INTO xmldata SELECT * FROM OPENROWSET(BULK
'C:\.....\OLAP_DEVELOPMENT\discover_xml_metadata_q uery.xml',SINGLE_BLOB) AS A


DECLARE @doc xml
SELECT @doc = data from xmldata

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

SELECT * FROM
OPENXML(@docHandle, '', 2)


EXEC sp_xml_removedocument @docHandle


Thanks everyone for helping me to resolve this problem. I'm sure that XMLA
will be used much more with AS2005.


Thanks

"alextt" wrote: