List All Analysis Services Databases

Well today was my first attempt outside of a classroom setting to dig my heels into SSAS DMX. I have an idea that I want to put into action (automating a process) and I believe it will benefit my organization. So fire up SSMS and connect to instance that has Analysis Services.

1) Start a New DMX Query

2) Execute Script

SELECT * FROM $system.DBSCHEMA_CATALOGS

3) Returning Specific Columns

Similar to Transact-SQL you just specify the columns you want; however, you need to wrap up the column names with brackets [column].

SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS

Pretty simple. Now let’s try with XMLA, which is XML for Analysis Services.

Just like before connect to an instance which has Analysis Services and published cubes.

1. Start a New XMLA Query

2. Execute the Script

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

Results

Advertisements

One thought on “List All Analysis Services Databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s