Export Analysis services cubes, roles and members from a server to CSV using C#

At a customer we wanted to get an overview of all the roles and members of all his cubes at specific server. I decided to create a console application in C# that query’s the server using the SSAS management objects. Here you can find the code and download the program.

What it does is create a connection to the SSAS server and loop through all the databases, inside the database loop through the roles and all the members belonging to this role.

Make sure you reference the Analysis management objects to your project when you write your own code.

You can find the code here:

static void Main(string[] args)
{
    // Test if input arguments were supplied:
    if (args.Length < 1)
    {
        Console.WriteLine("Please enter a connectionstring and filename in the argument.");
        Console.WriteLine("Press any key to continue");
        Console.ReadLine();
        return;
    }
    string filename = args[1];

    try
    {
        using (System.IO.StreamWriter file = new System.IO.StreamWriter(filename, false))
        {
            string filerowheader = string.Format(""{0}","{1}","{2}"", "Database", "Role", "Member");
            file.WriteLine(filerowheader);
            using (Microsoft.AnalysisServices.Server Server = new Microsoft.AnalysisServices.Server())
            {
                string servername = args[0];
                Server.Connect(servername);
                Console.WriteLine(string.Format("{0} {1}", "Connected to :", servername));
                foreach (Microsoft.AnalysisServices.Database ssasdb in Server.Databases)
                {
                    string Databasename = ssasdb.Name;
                    Console.WriteLine(string.Format("Reading roles from {0}",Databasename));
                    foreach (Microsoft.AnalysisServices.Role CubeDbRole in ssasdb.Roles)
                    {
                        string Rolename = CubeDbRole.Name;
                        foreach (Microsoft.AnalysisServices.RoleMember CubeRoleMember in CubeDbRole.Members)
                        {
                            string RoleMember = CubeRoleMember.Name;
                            string filerow = string.Format(""{0}","{1}","{2}"", Databasename, Rolename, RoleMember);
                            file.WriteLine(filerow);
                        }
                    }
                }
            }
        }
        Console.WriteLine("Finished reading the SSAS database");
    }
    catch (Exception ex)
    {

        if (ex is Microsoft.AnalysisServices.AmoException)
        {
            throw new ApplicationException("Error on reading the SSAS server", ex);
        }

        if (ex is System.IO.IOException)
        {
            throw new ApplicationException("Error opening or writing the file", ex);
        }
        throw new Exception("Unknown exception", ex);

    }
    Console.WriteLine("Press any key to continue");
    Console.ReadLine();
}
}

Or download the application from my skydrive.

  • clash77

    Very useful, thank you.

    I’m trying to access the list of databases on a particular analysis server. However, at the line Server.Connect(servername);
    I always get an error that “Input string was not in a correct format.”
    Some of the formats I have tried are like:

    “Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=mydatabase;Data Source=ABCD:7001;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Timeout=600;Application Name=ExcelAddin”

    “Provider=’MSOLAP.3′;Integrated Security=’SSPI’;Persist Security Info=’True’;Initial Catalog=’mydatabase’;Data Source=’ABCD:7001′;MDX Compatibility=’1′;Safety Options=’2′;MDX Missing Member Mode=’Error’;Timeout=’600′;Application Name=’ExcelAddin’;”

    – Also with “OLEDB;Provider=… ” at the beginning.

    None of these are working, even though the details have come directly from an existing Pivot Table connection. I am using Excel 2007.

    Could you provide an example of the input to your program?

    Thanks.

  • Very useful…Thanks for not complicating with hundreds of try catch nd stuff 🙂