Administration via Programming


In Analysis Services 2005 you have the option of automating administrative processes using XML/A commands or using AMO, which in turn communicates with the server using XML/A. AMO provides a well-defined object model that extracts execute and discover XML/A commands that need to be sent to the server and is extremely helpful. Almost all the user interface you saw in SQL Server Management Studio uses the AMO object model while communicating to the server. You learn to manage Analysis Services using AMO in this section. You learn about using XML/A commands and Integration Services to automate some of the management tasks in Chapter 16. You don't have to type in the long code snippets that follow; they are available for download on the book's web site.

Analysis Management Objects (AMO)

In the introduction to this chapter, we mentioned that AMO is an object model that can be used for programmatic administration of an Analysis Services instance. AMO is the replacement for DSO (Decision Support Objects), which shipped in Analysis Services 2000. It is installed and registered into the GAC (Global Assembly Cache) when Analysis Services is installed. The GAC itself is part of the .NET infrastructure where all commonly used .NET assemblies are registered. Now then, the best way to actually learn AMO is to jump in and use it! In this section you learn a few sample AMO applications that will perform some of the administrative operations you saw earlier in this chapter using SSMS. With the help of AMO you can automate almost all your administrative tasks.

Processing Analysis Services Databases

As you learned earlier in this chapter, processing is one of the most important operations for an administrator. Usually administrators want to automate this process using scripts or programs. You will learn to build an AMO-based console application that takes four command-line parameters: server name, target type (cube or dimension), processing type (full, incremental, update, or unprocess), and finally, the object's name (to be processed.) Before building this console app, please read the source code shown below in advance and don't worry if you don't get it right away; you will soon learn the purpose of all but the most self-explanatory lines of code. The following code is a sample to kick start you to learn AMO with some of the processing options for processing dimension or cube objects within a database.

   #region Using directives   using System;   using System.Collections.Generic;   using System.Text;   using AMO = Microsoft.AnalysisServices;   #endregion   namespace ProcessASObjects   {   class ProcessASObjects   {       Microsoft.AnalysisServices.Server myServer = null;       bool isConnected = false;       static void Main (string[] args)       {       if (args.Length != 6)       {             Console.WriteLine ("ProcessASObjects <serverName> <1 for Cube/2 for   Dimension> <ProcessType = 1 for Full /2 for Inc /3 for Update /4 for   <databaseName> <objectName>");             return;       }         ProcessASObjects pASObj = new ProcessASObjects (args[0]);         if (pASObj.isServerConnected ())         pASObj.ProcessObject (Int32.Parse (args [1]), Int32.Parse (args [2]),            args[3], args[4]);       }       public ProcessASObjects (string serverName)       {       myServer = new AMO.Server ();       try       {          myServer.Connect (serverName);          isConnected = true;       }       catch (Exception e)       {       isConnected = false;       Console.WriteLine ("Error while connecting to the server:" + e.Message);       }   }   bool isServerConnected ()   {       return isConnected;   }   bool ProcessObject (int objectType,int processType, string dbName,       string objectName)   {         AMO.ProcessType objectProcessType = AMO.ProcessType.ProcessDefault;         switch (processType)         {         case 1: objectProcessType = AMO.ProcessType.ProcessFull; //ProcessFull                break;         case 2: objectProcessType = AMO.ProcessType.ProcessAdd;                //ProcessIncremental of new members                break;         case 3: objectProcessType = AMO.ProcessType.ProcessUpdate;                //Process to update existing members and their properties                break;         case 4: objectProcessType = AMO.ProcessType.ProcessClear;                //Clear the object                break;         }         switch (objectType)         {         case 1: // Cube Processing                return ProcessCube (dbName, objectName, objectProcessType);         case 2: //Dimension Processing                return ProcessDimension (dbName, objectName, objectProcessType);         }         return true;   }   bool ProcessDimension (string dbName, string DimName,                        Microsoft.AnalysisServices.ProcessType pType)   {       try       {       foreach (AMO.Database db in myServer.Databases)       {         if (db.Name.Equals (dbName)) //Check if database name matches         {           foreach (AMO.Dimension dim in db.Dimensions)           {              if (dim.Name.Equals (DimName))              //Check if dimension is found within the database              {                dim.Process (pType);                // Process the dimension with the appropriate                // processing type specified by the user.                Console.WriteLine ("Object {0} Successfully processed",DimName);                Console.WriteLine ("Last processed time is {0}",dim.LastProcessed);              }           } // end foreach         } // end if       } // end foreach     } // end try         catch (Exception e)         {         Console.WriteLine ("Error while processing {0}", DimName + e);         return false;         } // end catch         Console.WriteLine ("Successfully processed the object");         return true;   } // end ProcessDimension   bool ProcessCube (string dbName, string CubeName,                   Microsoft.AnalysisServices.ProcessType pType)   {       try       {       foreach (AMO.Database db in myServer.Databases)       {          if (db.Name.Equals (dbName)) //Check if database name matches          {            foreach (AMO.Cube cube in db.Cubes)            {              if (cube.Name.Equals (CubeName))              //Check if dimension is found within the database              {                cube.Process (pType);                // Process the dimension with the appropriate processing                // type specified by the user.                Console.WriteLine ("Successfully processed the object");                Console.WriteLine (cube.LastProcessed);              }            } //end for each          } // end if        } // end foreach      } //end try         catch (Exception e)         {         Console.WriteLine ("Error while processing {0}", CubeName + e.Message);         return false;         } // end catch         return true;   } // end ProcessCube   ~ProcessASObjects ()   {         //Disconnnect the server when this process is being killed.       try       {        if (myServer.Connected)          myServer.Disconnect ();       }       catch (Exception e)       {          Console.WriteLine ("Exception occurred while disconnecting from   server"+e.Message);       }       } // end ~ProcessASObjects     } // end class ProcessASObjects   } // end namespace ProcessASObjects 

To create an AMO-based console application for Analysis Server administration, open Visual Studio 2005 and select New Project under the File menu. In the New Project dialog, select project type Visual C# and use the Console Application template. Name your project AnalysisServicesProcess and be sure the Create Directory for Solution checkbox is checked; finally, click OK to continue. The next step involves adding the AMO assembly to your project; this is accomplished by right-clicking References in the Solution Explorer and selecting Add Reference. In the Add Reference dialog, scroll down to find Analysis Management Objects (Microsoft.AnalysisServices.DLL) and double-click it. This causes the Microsoft.AnalysisServices to be added to your list References.

To accomplish the tasks required in this program, you will need the following directives; please add any you don't already have listed.

     using System;     using System.Collections.Generic;     using System.Text;     using AMO = Microsoft.AnalysisServices; 

Next, consistent with .NET coding conventions, create a namespace called ProcessASObjects with a class definition inside of that, also called ProcessASObjects. For more information on namespaces or classes, please see Microsoft's C# online documentation.

     namespace ProcessASObjects     {         class ProcessASObjects         {             // the rest of the code in this application will go here...         }     } // end namespace ProcessASObjects 

First, create and initialize variables of type Server and bool for use in the Main section:

     Microsoft.AnalysisServices.Server myServer = null;     bool isConnected = false; 

Next, you create the Main section and infrastructure for processing the command-line parameters; this requires the instantiation of the ProcessASObjects object using new. Then, call the object's method, ProcessObject, with the respective arguments supplied. Of course you can name these functions any way you like, but to keep things simple, you should use the names shown for now.

     if (args.Length != 5)     {         Console.WriteLine ("ProcessASObjects <serverName> <1 for Cube/2 for Dimension>     <ProcessType = 1 for Full /2 for Inc /3 for Update /4 for UnProcess> <databaseName>     <objectName>");         return;     }     ProcessASObjects pASObj = new ProcessASObjects (args[0]);         if (pASObj.isServerConnected ())             pASObj.ProcessObject (Int32.Parse (args [1]), Int32.Parse (args [2]),             args[3], args[4]);     } 

Next is the ProcessASObjects definition; you can see clearly that a new AMO server connection is created with appropriate error handling in the event of failure.

     public ProcessASObjects (string serverName)     {         myServer = new AMO.Server ();         try         {             myServer.Connect (serverName);             isConnected = true;         }         catch (Exception e)         {             isConnected = false;             Console.WriteLine ("Error while connecting to the server:" + e.Message);         } 

If the server connection was successful, the following returns true back to Main in the last If clause in the Main section:

     bool isServerConnected ()     {         return isConnected;     } 

What follows is the code necessary to decipher the type of processing to be carried out on the target cube or dimension based on user's command line input and to determine if it is, in fact, a cube or dimension to be processed:

   bool ProcessObject (int objectType,int processType, string dbName,       string objectName)       {        AMO.ProcessType objectProcessType = AMO.ProcessType.ProcessDefault;        switch (processType)        {           case 1: objectProcessType = AMO.ProcessType.ProcessFull; //ProcessFull                  break;           case 2: objectProcessType = AMO.ProcessType.ProcessAdd;                  //ProcessIncremental of new members                  break;           case 3: objectProcessType = AMO.ProcessType.ProcessUpdate;                  //Process to update existing members and their properties                  break;           case 4: objectProcessType = AMO.ProcessType.ProcessClear;                  //Clear the object                break;         }         switch (objectType)         {         case 1: // Cube Processing                return ProcessCube (dbName, objectName, objectProcessType);         case 2: //Dimension Processing                return ProcessDimension (dbName, objectName, objectProcessType);         }         return true;   } 

The two methods ProcessDimension and ProcessCube are the key methods that perform the processing operation. Both methods return a boolean value. These methods return true only in the event the processing operations was successful without any errors. If there are errors then the errors are typically thrown as exceptions and the method returns false. In both methods, you make sure the database name matches an existing database; likewise for dimensions and cubes in their respective methods. Then you use the Process method on the object along with processing type provided by the user which is indicated by the variable pType.

   bool ProcessDimension (string dbName, string DimName,                        Microsoft.AnalysisServices.ProcessType pType)   {       try       {       foreach (AMO.Database db in myServer.Databases)       {         if (db.Name.Equals (dbName)) //Check if database name matches         {           foreach (AMO.Dimension dim in db.Dimensions)           {              if (dim.Name.Equals (DimName))              //Check if dimension is found within the database              {                dim.Process (pType);                // Process the dimension with the appropriate                // processing type specified by the user.                Console.WriteLine ("Object {0} Successfully processed",DimName);                Console.WriteLine ("Last processed time is {0}",dim.LastProcessed );              }           } // end foreach         } // end if       } // end foreach         } // end try         catch (Exception e)         {         Console.WriteLine ("Error while processing {0}", DimName + e);         return false;         } // end catch         Console.WriteLine ("Successfully processed the object");         return true;   } // end ProcessDimension   bool ProcessCube (string dbName, string CubeName,                   Microsoft.AnalysisServices.ProcessType pType)   {       try       {       foreach (AMO.Database db in myServer.Databases)       {          if (db.Name.Equals (dbName)) //Check if database name matches          {            foreach (AMO.Cube cube in db.Cubes)            {              if (cube.Name.Equals (CubeName))              //Check if dimension is found within the database              {                cube.Process (pType);                // Process the dimension with the appropriate processing                // type specified by the user.                Console.WriteLine ("Successfully processed the object");                Console.WriteLine (cube.LastProcessed);              }            } //end for each          } // end if        } // end foreach         } //end try         catch (Exception e)         {        Console.WriteLine ("Error while processing {0}", CubeName + e.Message);        return false;         } // end catch         return true;   } // end ProcessCube 

To wrap things up, you need only disconnect from the server which is done in the code below.

   ~ProcessASObjects ()   {       //Disconnnect the server when this process is being killed.       try       {       if (myServer.Connected)           myServer.Disconnect ();       }       catch (Exception e)       {       Console.WriteLine ("Exception occurred while disconnecting from   server"+e.Message);       }   } 

Once you have mastered the AMO and basic programming concepts shown in this section, you will find it easy to extend the code to take on other administrative tasks like tracing server events, designing aggregations, and writing custom AMO programs for your management tasks.

Back-up and Restore

To convince you that it really is not difficult to extend the concepts demonstrated in the preceding processing program to complete other console apps for administrative purposes, please read through these source code samples covering back-up and restore capabilities.

   using System;   using System.Collections.Generic;   using System.Text;   using AMO = Microsoft.AnalysisServices;       /// <summary>       /// This console program is backup an Analysis Services       /// ----------------------------------------------------------------------       /// </summary>   namespace ASBackup   {       class Program       {           static void Main (string[] args)           {               try               {                  if (args.Length != 2)                  {                      Console.WriteLine ("Usage: ASBackup <servername>   <databasename>");                      return;                  }                  AMO.Server myServer = new AMO.Server ();                  myServer.Connect (args[0]);                  foreach (AMO.Database db in myServer.Databases)                  {                      if (db.Name.Equals (args[1]))                      {                          db.Backup (args[1]+".abf",true); //Backup the database   the provided file name                      }//end if                  } //end forach               } //end try               catch (Exception e)               {                  Console.WriteLine ("Exception occurred:" + e.Message);               }           } // end Main       } // end class   } // end namespace   using System;   using System.Collections.Generic;   using System.Text;   using AMO = Microsoft.AnalysisServices;       /// <summary>       /// This console program is restore an Analysis Services backup file       /// ----------------------------------------------------------------------       /// </summary>   namespace ASRestore   {       class Program       {           static void Main (string[] args)           {               try               {                  if (args.Length != 2)                  {                      Console.WriteLine ("Usage: ASRestore <backupfilename>");                      return;                  }                  AMO.Server myServer = new AMO.Server ();                  //Connect to the Server                  myServer.Connect (args[0]);                  myServer.Restore (args[1]);               } //end try               catch (Exception e)               {                  Console.WriteLine ("Exception occurred:" + e.Message);               }           } // end Main       } // end class   } // end namespace 

As you can see from the above code segments, it really is quite simple — create an AMO server object, connect to the Analysis Services instance. Depending on the operation (backup or restore) call the appropriate method to perform the operation using AMO. The more you experiment with AMO programming, the more interesting and useful solutions you will generate; so come up with some solutions yourself and code them up!

Adding Assemblies to Analysis Services

You can create your own application in AMO to register assemblies in an Analysis Services database. You just need to get the full path of the assembly, set the right permission and impersonation mode, and then register the assembly. The following is a sample code of a console application that registers the assembly to a specific database in an Analysis Services instance:

     using System;     using System.IO;     using AMO = Microsoft.AnalysisServices;     namespace RegisterAssembly     {         /// <summary>         /// This console program is used to add .NET assemblies to Analysis Services     databases with appropriate permission set and impersonation mode.         /// ----------------------------------------------------------------------         /// </summary>         class RegisterAssembly         {             [STAThread]             static void Main (string[] args)             {                 int impersonationMode = 0;                 int permissionSet = 0;                 //This console application takes five command line parameters                 // Server name                 // Assembly name with full path that is to be registered                 // Database to which the assembly needs to be added                 // Permission                 // Impersonation Mode                 if (args.Length != 5)                 {                    Console.WriteLine ("Usage ...");                    Console.WriteLine ("RegisterAssembly {server} {assemPath} {database}     {PermissionSet (1-ExternalAccess,2-Safe, 3-Unrestricted) {ImpersonationMode (1-     Anonymous,2-CurrentUser, 3-ServiceAccount, 4-Default)} ");                    Console.WriteLine ("Adds an assembly to database ");                    return;                 }                 try                 {                    //Connect to the Analysis Services instance                    AMO.Server server = new AMO.Server ();                    server.Connect (args[0]);                    //get the assembly name                    FileInfo f = new FileInfo (args[1]);                    string assName = f.Name.Replace (f.Extension, "");                    assName = assName.Replace ("AMO", "");                    AMO.ClrAssembly cAss = new AMO.ClrAssembly (assName, assName);                    cAss.LoadFiles (f.FullName, true);                    impersonationMode = Int16.Parse (args[4]);                    permissionSet = Int16.Parse (args[3]);                    if (impersonationMode == 0 || permissionSet == 0)                    {                        Console.WriteLine ("Usage ...");                        Console.WriteLine ("RegisterAssembly {server} {assemPath}     {database} {PermissionSet (1-ExternalAccess,2-Safe, 3-Unrestricted)     {ImpersonationMode (1-Anonymous, 2-CurrentUser, 3-ServiceAccount, 4-Default)} ");                        Console.WriteLine ("Adds an assembly to database ");                        return;                    }                    cAss.ImpersonationInfo = new     Microsoft.AnalysisServices.ImpersonationInfo ();                    //Set the correct impersonation Mode requested                    switch (impersonationMode)                    {                        case 1: cAss.ImpersonationInfo.ImpersonationMode =     AMO.ImpersonationMode.ImpersonateAnonymous;                               break;                        case 2: cAss.ImpersonationInfo.ImpersonationMode =     AMO.ImpersonationMode.ImpersonateCurrentUser;                               break;                        case 3: cAss.ImpersonationInfo.ImpersonationMode =     AMO.ImpersonationMode.ImpersonateServiceAccount;                               break;                        case 4: cAss.ImpersonationInfo.ImpersonationMode =     AMO.ImpersonationMode.Default;                               break;                    }                    //set the correct permissionSet requested                    switch (permissionSet)                    {                        case 1: cAss.PermissionSet =     Microsoft.AnalysisServices.PermissionSet.ExternalAccess;                            break;                        case 2: cAss.PermissionSet =     Microsoft.AnalysisServices.PermissionSet.Safe;                            break;                        case 3: cAss.PermissionSet =     Microsoft.AnalysisServices.PermissionSet.Unrestricted;                            break;                    }                    //add assembly to database                    AMO.Database db = server.Databases.GetByName (args[2]);                    db.Assemblies.Add (cAss);                    cAss.Update (); //Sends the DDL to the Server                    server.Disconnect ();                    Console.WriteLine ("Registered Assembly");                 }                 catch (Exception e)                 {                    Console.WriteLine ("Register Assembly failed, " + e.Source + " " +     e.Message);                 }             }         }     } 

The AMO code samples provided in this section are primarily to help you start using AMO for the various management tasks. The samples provided in this chapter have been tested for appropriate operations. However these are still code samples and if you need to write your own AMO programs for management operations we expect you to write robust code for your production environment with appropriate error handling. AMO contains several classes that help you perform more than just management operations. You can design an entire database programmatically and deploy the entire database to an Analysis Services instance.

Synchronization is one of the administrative task operations to move database from test environments to production environments. However, AMO does not have methods to perform the synchronize operation. AMO allows you to send XMLA scripts to the Analysis Services instances. You can take the script that can be generated from the Synchronization Wizard and send the script using AMO to perform management operations for synchronization. We leave it to you to explore the AMO object model and write the code for synchronization. The AMO sample program for synchronization can be downloaded from the web site accompanying the book.



Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net