By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

Count the number of cubes

P: n/a
Hi all,

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

Thanks.

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

To count number of databases use this

select count(catalog_name) from information_schema.schemata

Madhivanan

Jul 23 '05 #2

P: n/a
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
cubes.

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
microsoft.public.sqlserver.olap.

Simon

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.