CODEWITHSUNDEEP
excelvbamdxcubeolap-cube
Jeffrey Fischer
Jeffrey Fischer

Reputation: 31

VBA List all Cubes in a SQL Server Analysis Server

I have 2 servers that are SQL Server Analysis Servers. On these two servers are databases that users choose depending on which cube they want in their workbook.

I need to create a userform that (1) lets the user select 1 of the 2 servers, and (2) shows a drop down box of each of the available databases on the server chosen in (1).

I have created the functionality for (1) and designed the formatting for (2), but I can't figure out how to get the list of available databases.

I think the steps I need to follow are, in vba, connect to the specified server, then perform some "Select ... From ..." query to get each database name.

The connection is OLEDB and an example of the connection string is: 

.OLEDBConnection.Connection = Array( _
    "OLEDB;Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;
      Initial Catalog=" & **database** & "; Data Source=" & **server** & _
    ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2")

Server is specified by the user, and database will be chosen by the user via the drop down box we will create.

I looked at this post Using VBA in Excel, how do I get the names of all OLAP Cubes on a server? but i can't seem to manipulate it for my purposes.

Thank you for any and all help!

* UPDATE * I was able to get it to work with the following code:

Dim conn As ADODB.Connection
Dim sConnString As String
sConnString = "Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;Data Source=" & Cube_Server_Options.Value & ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
Set conn = New ADODB.Connection
conn.Open sConnString


Set rst = New ADODB.Recordset
Set rst.ActiveConnection = conn
rst.Source = "SELECT [catalog_name] FROM $system.DBSCHEMA_CATALOGS"
rst.Open
Do While Not rst.EOF

  rst.MoveNext
Loop
rst.Close

Set conn = Nothing
Set rst = Nothing

Upvotes: 1

Views: 1419

Answers (2)

Earl
Earl

Reputation: 480

In case you want to do it in C#, it is quite straightforward and gets you additional attributes as well. Additionally you can list out and even process the dimensions and the measures of the cube with this.

        using Microsoft.AnalysisServices;


        Server server = new Server();
        server.Connect(@"Data source=" + servername + "; Timeout=7200000;Integrated Security=SSPI");

        IEnumerable<Database> result = from Database item in server.Databases select item;

        foreach (Database cube in result)
        {
            Console.WriteLine(cube.Name);
        }

Upvotes: 1

Jeffrey Fischer
Jeffrey Fischer

Reputation: 31

Dim conn As ADODB.Connection
Dim sConnString As String
sConnString = "Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;Data Source=" & Cube_Server_Options.Value & ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
Set conn = New ADODB.Connection
conn.Open sConnString


Set rst = New ADODB.Recordset
Set rst.ActiveConnection = conn
rst.Source = "SELECT [catalog_name] FROM $system.DBSCHEMA_CATALOGS"
rst.Open
Do While Not rst.EOF

  rst.MoveNext
Loop
rst.Close

Set conn = Nothing
Set rst = Nothing

Note this connection string was specific to my example but the logic to connect to the server and loop through the databases worked

Upvotes: 1

Related Questions