Search code examples
sqlssasdmvdmx-ssas

How to filter SSAS catalog list?

I use DMX query like this

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

which retrieves list of OLAP DBs. I would like to add a where clause to select catalogs where name contains some sequence of chars, e.g.,

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS
WHERE [CATALOG_NAME] LIKE '%FOO%'

Any ideas on how to filter the retrieved list of Catalog names given the limitation?

Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.

Solution

  • If it is Analysis Services Multidimensional not Tabular you can install ASSP and use the ASSP.DMV function which implements the LIKE clause in the WHERE clause. Here is an example on a different DMV.