470,647 Members | 1,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

Count the number of cubes

Hi all,

Do you know how can I count the number of cubes and number of databases
in SQL Analysis Services using T-SQL?


Jul 23 '05 #1
2 1791

To count number of databases use this

select count(catalog_name) from information_schema.schemata


Jul 23 '05 #2
Analysis Services is totally separate from MSSQL, so you can't use TSQL
to query it directly. Even if you create a linked server from MSSQL, it
is linked to a single AS database, not to the AS server as a whole. But
there are (at least) a couple of ways to enumerate the databases and

First, if you've migrated the metadata repository to an MSSQL database,
then you can use queries like this:

select count(*) from OlapObjects
where objectdefinition like '<database%'

select count(*) from OlapObjects
where objectdefinition like '<cube%'

If you haven't migrated the repository, you may be able to use an
equivalent query in Access to get the information, but I've never tried
that myself.

Second, you can use the DSO COM interface to enumerate the MDStore
objects. See "Decision Support Objects Architecture" in Books Online
for more details. This is probably a better solution, because the
repository database isn't documented (as far as I know), and DSO is the
standard mechanism for managing AS from code.

If this doesn't help, you could try posting in


Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Achim K?nstler | last post: by
reply views Thread by AOstarello | last post: by
reply views Thread by | last post: by
2 posts views Thread by ashu | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.