Export Analysis services cubes, roles and members from a server to CSV using C#
May 6th, 2010
No comments
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.
