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));

  }

Comments

• Anonymous
  July 05, 2007
  Okay, can I get an explanation of how to fix this while in BIDS without coding and what is going on?

• Anonymous
  July 10, 2007
  Hey Doug - Generally, the error means that you modified something that will cause a cascading effect to other objects it is related to and you are not also modifying those objects in sympathy. You'll need to post a little more information before anyone can be more helpful, however

• Anonymous
  August 16, 2007
  I am trying to perform a similar task using Vb.net.  When I try to update an attribute in a dimension, and the dimension is already referenced in a measuregroup, I receive the same error.  I am not sure how, in vb.net to mimic the c# commanddim.Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);

• Anonymous
  June 17, 2009
  In your code attr.KeyColumns.Add(CreateDataItem(dsv2, "dbo.DimAccount", "CustomMembers")); I tried the same way to add an attribute, but I did not find such method in the Microsoft.AnalysisServices.dll assembly. My version is 9.0.242.0 Runtime Version: v2.0.50727, is that too old? thanks!

• Anonymous
  December 06, 2010
  Your solution solved my problem perfectly! Thanks for your post!

• Anonymous
  September 16, 2011
  @Russell, I think what Doug meant was how to perform the equivalent operations in BIDS, in terms of how to do the ExpandFull and AlterDependants.  I am wondering myself.  I am experiencing the same issue except I added an attribute through BIDS, and particularly got the issue after I added it to a hierarchy and update the attribute relationships.

• Anonymous
  May 22, 2013
  How can this be achieved using the XMLA?

• Anonymous
  January 06, 2016
  Hey Russell, just wanted to let you know that's it's 2016 and this post is still helping people! Thanks!