Count the number of cubes

[Hippi]

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.

[Madhivanan]

To count number of databases use this

select count(catalog_n ame) from information_sch ema.schemata

Madhivanan

[Simon Hayes]

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 objectdefinitio n like '<database%'

select count(*) from OlapObjects
where objectdefinitio n 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.publi c.sqlserver.ola p.

Simon