ayesamson

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