Working with SQL Server AMO (Analysis Management Objects) – Part 2

In the first part of this article, we saw how to connect to the analysis server and list all the databases. We then chose a database and displayed the cubes, measures, partitions and dimensions in a ListBox.

In this part of the article, we will see how to take backups of MultiDimensional Anaysis Service Databases, explore the security part of how to add a role, a member to role and how to provide the necessary permissions. We will also see how to create new databases, data sources, data source views and required tables . Lastly I will also demonstrate how AMO offers to wok without connecting to the analysis services instance.

AMO can be very useful for taking backups of multidimensional databases, we can show all the available databases, ask the user to select the one he needs to backup and ask the user to specify device(s) for backup.

With following code we can take backup of any analysis services database and again restore it:

//declare objects
Server server;
Database db=new Database();
//connect to analysis server
server = new Server();
server.Connect(@"data source=<server name>");
db = server.Databases["<database name>"];
//take backup of analysis services database
//the extension is abf
db.Backup(@"<path>");

//restore database
//connect to analysis server
Server server;
server = new Server();
server.Connect(@"data source=<server name>");
 //restore database with abf file name and new name for the
 //analysis services database
server.Restore(@"<path and abf file name>", "<database name>");

We can use AMO for adding security related objects, like creating roles and later adding users to the roles. This can be a very useful feature where in we can provide the security for analysis services database access on the fly. When we want to revoke permission for a particular we can even remove a particular member from a role or even remove the complete role itself.

The following code explains how to add a role, a member to role and how to provide the necessary permissions

Server server;
try
{
   //connect to analysis server
   server = new Server();
   server.Connect(@"data source=<server name>");
   //pick up a particular database
   db = server.Databases[0];
   Cube cube=db.Cubes[0];
   //create a new role
   Role role;
   role = new Role("<role name>");
   //add member to the role
   RoleMember member = new RoleMember();
   member.Name = @"<domain name>\<user name>";
   role.Members.Add(member);
  db.Roles.Add(role);
   role.Update();
   //provide permissions as administrator
   DatabasePermission perm;
   perm = db.DatabasePermissions.Add(role.ID);
   perm.Administer = true;
   perm.Update();
   //add permission for cube
  CubePermission cubeperm;
  cubeperm = cube.CubePermissions.Add(role.ID);
  cubeperm.Read = ReadAccess.Allowed;
  cubeperm.Update();
}
catch (AmoException amoex)
{
        MessageBox.Show(amoex.Message);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
 }
}

We can use AMO to create new database, data sources, data source views and required tables in them.

With following code we will use adventure works as database and choose Person.Contact table into DSV

try
    {
        //connect to analysis server
        server = new Server();
        server.Connect(@"data source=<server name>");
        //create new database
        db = new Database("<database name>");
        db = server.Databases.Add("<database name>");
        db.Update();
        DataSource datasrc = db.DataSources.FindByName("<database name>");
        // Create the data source
        datasrc = db.DataSources.Add("<data source name>", "<data source id");
        datasrc.ConnectionString = @"Provider=SQLNCLI10.1;Data Source=<server name>;
        Integrated Security=SSPI;Initial Catalog=adventureworks";
        // Send the data source definition to the server.
        datasrc.Update();
        //add Data source view to the newly created data source
        DataSourceView dsv = null;
        dsv = db.DataSourceViews.Add();
        dsv.DataSourceID = "<data source id>";
        dsv.Name = "<data source view name>";
        dsv.Schema = new DataSet();
        System.Data.OleDb.OleDbConnection cn =
            new System.Data.OleDb.OleDbConnection(dsv.DataSource.ConnectionString);
        cn.Open();
        //add table
        System.Data.OleDb.OleDbDataAdapter daContact =
           new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Person.Contact", cn);
        daContact.FillSchema(dsv.Schema, SchemaType.Mapped, "Contact");
        DataTable[] dts =
            daContact.FillSchema(dsv.Schema,SchemaType.Mapped, "Contact");
        DataTable dt = dts[0];
        dt.ExtendedProperties.Add("TableType", "Table");
        //this step will specify the schema name for table
        dt.ExtendedProperties.Add("DbSchemaName", "Person");
        dt.ExtendedProperties.Add("DbTableName", "Contact");
        //following step can be ommitted if we want to keep same friendly name
        dt.ExtendedProperties.Add("FriendlyName", "Contact");
        dsv.Update();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Another facility which AMO offers is to wok without connecting to the analysis services instance. This metadata can be kept for later usage for deployment.

With following code we process partition, dimensions and cube without actually executing. Later we execute all in one batch in parallel.

try
    {
        //connect to analysis server
        server = new Server();
        server.Connect(@"data source=<server name>");
        server.CaptureXml = true;
        //list all the databases from the server in listbox
        db = server.Databases["<database name>"];
        foreach (Cube cube in db.Cubes)
        {
            cube.Process(ProcessType.ProcessFull);
            foreach (MeasureGroup measuregrp in cube.MeasureGroups)
            {
                foreach (Partition partition in measuregrp.Partitions)
                {
                    partition.Process();
                }
            }
        }

        foreach (Dimension dim in db.Dimensions)
        {
            dim.Process();
        }
        //stop capturing xml
        server.CaptureXml = false;
        //execute actually on server
        XmlaResultCollection results = server.ExecuteCaptureLog(true, true);
        foreach (XmlaResult result in results)
        {
            foreach (XmlaMessage message in result.Messages)
            {
                MessageBox.Show((message.Description));
                if (message is XmlaError)
                {

                    // the processing failed, there is at
                    //least one error
                }
            }
        }

    }
    catch (AmoException amoEx)
    {
        MessageBox.Show(amoEx.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);

    }

}