Sunday, November 14, 2010

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.

image





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.

No comments:

Post a Comment

Post a Comment