Russell Christopher's Semi-Useful BI Musings

AMO Error when adding an attribute to an existing dimension: The transaction cannot be committed because one or more objects were invalidated during the transaction

Today’s tale from the front: I needed to write some POC code and kept on running into the error below when attempting to Update() the dimension I had added an attribute to:

The cube with the ID of '<cube>', Name of '<cube>' was invalidated by operations in the transaction.

Errors in the metadata manager. The Dimension with the ID of ‘<dimension>', Name of ‘<dimension>' was invalidated by operations in the transaction. Errors in the metadata manager. The transaction cannot be committed because one or more objects were invalidated during the transaction.

(Very) long story short, I forgot that I needed to use not only the option ExpandFull, but the option AlterDependents as well since the dimension was already related to a measure group in the cube.

Here’s some code that:

·         Modifies an existing DSV, changing a table in the DSV to a named query and adding an additional field to the named query

·         Adds the new column to an existing dimension in the cube

·         Processes the cube

  

    Microsoft.AnalysisServices.Server server = new Server();

    server.Connect("localhost");

 

    Microsoft.AnalysisServices.Database database = new Database();

    database = server.Databases.GetByName("aDataBase");

    Microsoft.AnalysisServices.DataSourceView dsv2 = database.DataSourceViews.GetByName("aDSV");

 

 

    // The sample DSV contains one table - Grab it.

    DataTable dt2 = dsv2.Schema.Tables[0];

          

    // Remove certain existing properties from the DataTable and replace them with

    // new ones which are appropriate when using a Named Query

    dt2.ExtendedProperties.Remove("TableType");

    dt2.ExtendedProperties.Add("TableType", "View");

    dt2.ExtendedProperties.Remove("IsLogical");

    dt2.ExtendedProperties.Add("IsLogical", "True");

 

           

 

    // Define more information about how this new table should behave inside the DSV:

    // What is it's name, what is the table's SQL statement, etc.

    dt2.ExtendedProperties.Remove("DbTableName");

    dt2.ExtendedProperties.Remove("FriendlyName");

    dt2.ExtendedProperties.Add("DbTableName", "DimAccount");

    dt2.ExtendedProperties.Add("FriendlyName", "DimAccount");

    dt2.ExtendedProperties.Remove("QueryDefinition");

   

    dt2.ExtendedProperties.Add("QueryDefinition", newSQLStringForQuery);

 

 

    DataColumn dc = new DataColumn();

    dc.Caption = "New Attribute Thing";

    dc.ColumnName = "NewAttribute";

    dc.MaxLength = 300;

    dc.ExtendedProperties.Add("FriendlyName", "New Attribute Thing");

    dc.ExtendedProperties.Add("DbColumnName", "NewAttribute");   

    dt2.Columns.Add(dc);

 

    dsv2.Update();

 

    dsv2 = database.DataSourceViews.GetByName("Adventure Works DW  Original");

            

    Microsoft.AnalysisServices.Dimension dim = database.Dimensions.GetByName("Dim Account");

 

    DimensionAttribute attr;

    attr = dim.Attributes.Add("CustomMembers");

    attr.KeyColumns.Add(CreateDataItem(dsv2, "dbo.DimAccount", "CustomMembers"));

    attr.NameColumn = CreateDataItem(dsv2, "dbo.DimAccount", "CustomMembers");

 

 

    dim.Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);

 

 

    Console.WriteLine("Processing...");

    database.Process(ProcessType.ProcessFull);

    Console.WriteLine("Complete");

          

    Console.ReadLine();

    

  }

 

  static DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName)

  {

           

    // Helper function

    DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName];

    DataColumn dataColumn = dataTable.Columns[columnName];

    return new DataItem(tableName, columnName,

                OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType));

  }