Process SSAS Dimensions and Cubes Individually and Automatically using AMO .NET objects

I posted a blog entry before to Process the SSAS dimensions individually and automatically. If you use the same method to process the cube, you will find that it may not work all the times since the cube could be in an invalid state and unable to retrieve the Cube ID/Cube Name.

To overcome the issue, I will need to use the AMO .Net in stand of using MDV to accomplish this.

1. Add a Script Task first in the the ETL control flow to retrieve the Dimension and Cube information.

   Here is the steps in the Script Task:

   • Create the AMO.Server object and connect to the SSAS database; Make sue you add Microsoft.AnalysisServices reference to the project.
   • Retrieve the Database ID once it is connected.
   • For each AMD Dimension in the database, retrieve the Dimension ID into an Array List
   • For each AMO Cube in the database, retrieve the Cube ID into an Array List
   • Save the ArrayLists as the SSIS Object Variables to be used later.

   // Create instance of Analysis Services object

   AMO.Server aServer = new AMO.Server();

   ArrayList aDimensionList = new ArrayList();

   ArrayList aCubeList = new ArrayList();    

   try

   {

       //Get SSAS_DataMart connection string

       string strConnection = "";

       foreach (ConnectionManager aManager in this.Dts.Connections)

       {

           if (aManager.Name == "DataMart")

           {

               strConnection = aManager.ConnectionString;

           }

       }

       //Throw error if not such connection manager

       if (strConnection == "")

       {

           Exception ex = new Exception("No OLAP Connection manager: OLAP_DataMart found");

       }

       // Connect to DataMart instance of Analysis Services

       aServer.Connect(strConnection);

       string strCatalog = aServer.ConnectionInfo.Catalog;

       //Get the Database name

       AMO.Database myDataBase = aServer.Databases[strCatalog];

       System.Diagnostics.Debug.WriteLine(" Database Id = " + myDataBase.ID);

       Dts.Variables["_DatabaseName"].Value = myDataBase.ID;

       //Get list of dimension and set to the DTS Variable

       foreach (AMO.Dimension aDimension in myDataBase.Dimensions)

       {

           System.Diagnostics.Debug.WriteLine(" Dimension Id = " + aDimension.ID);

           aDimensionList.Add(aDimension.ID);

       }

       Dts.Variables["_DimensionList"].Value = aDimensionList;

       //Get list of Cube and set to the DTS Variable

       foreach (AMO.Cube aCube in myDataBase.Cubes)

       {

           System.Diagnostics.Debug.WriteLine("Cube = " + aCube.ID);

           aCubeList.Add(aCube.ID);

       }

       Dts.Variables["_CubeList"].Value = aCubeList;

   }

   catch (Exception ex)

   {

       System.Diagnostics.Debug.WriteLine(ex.ToString());

       Dts.TaskResult = (int)ScriptResults.Failure;

       Dts.Variables["Pamlico_PackageExecutionResult"].Value = Dts.TaskResult;

       throw (ex);

   }

   finally

   {

       // DisConnect to the local instance of Analysis Services

       if (aServer != null)

       {

           if (aServer.Connected)

           {

               aServer.Disconnect();

           }

       }

   }

   Dts.TaskResult = (int)ScriptResults.Success;

   Dts.Variables["PackageExecutionResult"].Value = Dts.TaskResult;

   
   
2. Add Foreach Loop to process the Dimensions individually. In this step, you may add logging information to indicate the dimension being processed.
   
3. Add another Foreach Loop to process the Cubes individually after processing the dimensions.

This way even thought the dimension process failed for some reason, we will still be able to retrieve the Cube ID back and reprocess them later without problem.