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 (8)

  1. Doug says:

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

  2. 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

  3. KMeiser says:

    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# command

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

  4. Mary says:

    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!

  5. Mike says:

    Your solution solved my problem perfectly! Thanks for your post!

  6. @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.

  7. Raunak says:

    How can this be achieved using the XMLA?

  8. Geoff Freeman says:

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