Tuesday, June 21, 2011

Get the list of SSAS Databases


Most of the times, I found question on MSDN forums that “how to get the list of SSAS databases?”. So I thought lets create one post to answer this question.

If you are using SSAS 2008 or higher versions then you can use following DMV (Dynamic Management Views). Open your MDX query pane in SSMS and execute below query.

SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS

You can also retrieve the list of SSAS Databases by using AMO (Analysis Management Views). but for getting a list of all SSAS databases using AMO, your active directory user should have administrator privileges on SSAS server.
For using AMO, you need the reference of “Microsoft.AnalysisServices.dll” in your project. After referencing dll, Import it for using different classes of that dll (i.e Imports Microsoft.AnalysisServices).
Following is the sample code for getting the list of SSAS databases.

Dim strDatabaseName As String
Dim objServer As Server
objServer = New Server

objServer.Connect("localhost")
If objServer.Databases.Count > 0 Then
   For Each objDatabase As Database In objServer.Databases
        strDatabaseName = objDatabase.Name
   Next
End If

objServer.Disconnect()




No comments:

Post a Comment