Forum Discussion

ChetanK004's avatar
ChetanK004
Frequent Visitor
8 months ago
Solved

Column created in a table appears in another table

Hi,   I am using Microsoft.AnalysisServices.Tabular to alter the dataset via C# code. i have a table called Table A and there is another Table Called Table A Custom, Now when i try to add a column...
Need Help
  • ChetanK004's avatar
    ChetanK004
    7 months ago

    Hi v-priyankata ,

     

    We had a procedure on dataset that would bind the columns they created dynamically to table or custom table, we had to point it to custom table in that procedure which could be one of the root cause of the issue,(So the code was trying to add to new table and the procedure pointed to old table and when we refreshed the dataset it would appear in 2 tables) anyways we are currently not facing the duplicate column issue for now after we changed the procedure to point to new table, but we are not sure if it would happen anytime again. 

ChetanK004's avatar
ChetanK004
Frequent Visitor
8 months ago

Hi Zanqueta , Thanks for the response, For the below root causes

Root Causes

  1. Shared or cloned references
    • If pbiColumns is referencing the same ColumnCollection object for both tables due to incorrect initialisation or reuse of objects, adding a column will affect both - The base Table is not in the loop, also i tried debugging the code which only looped once for the custom table but the fields appeared in both tables.
    • This can happen if the tables were cloned or created from the same object without deep copying. - Probably this might be a candidate, Although we created the table using TOM not via code, so we will re-build the dataset by creating new custom field from scratch
  2. Model refresh behaviour
    • If the original table and the custom table share the same Source or LineageTag logic, the refresh can propagate columns unexpectedly. - This can be a candidate too, but i when i add new column i dont specify the Lineage Tag, i believe Power BI creates one by default
 
Save and refresh properly
After adding columns i do perform the database.Model.SaveChanges()
 
There is One more observation i found is regarding RequestRefresh(RefreshType.Full);
 
Before i used to call an RefreshDatasetInGroupAsync REST API by passing the modified tables as a parmeters to DatasetRefreshRequest and perform a datataset refresh after called database.Model.SaveChanges()
 
But i recently found out that  RequestRefresh is a better option if we are changing the scheme of dataset (like adding / delete columns), Which should be called before database.Model.SaveChanges()
 
Now i had a scenario where i added few columns to the dataset and performed dataset refresh with below refresh mode
1) Model.RequestRefresh(RefreshType.Full) This performed action and i observerd an entry in dataset refresh history in the pbi service, every time i ran the code everytime i saw an entry of type Via XMLA Endpoint
2) Table.RequestRefresh(RefreshType.Full) This performed action sometimes, i observed it added an entry to the dataset refresh history in this 2 cases
1) If the dataset was published freshly and we ran the code, it did a refresh and added entry 
2) If dataset had changes ( adding / deleting columns via code) for the first time and we ran the code , it did a refresh and added entry , there after if we did any changes to dataset, it niether refreshed nor created an entry in the history, is this inteded?
 
I did look around some articles where it was mentioned that each RequestRefresh will add an entry in the history either its model / table, is there anything am missing?
 

 

 

  • Zanqueta's avatar
    Zanqueta
    Super User
    8 months ago

     

    That's it for me. Seems the rigth direction, just have pay attention to:
     
    Make sure each table is instantiated independently:

     
    var pbiTable = database.Model.Tables
    .FirstOrDefault(x => x.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase));
if (pbiTable == null)
    throw new Exception($"Table {tableName} not found.");

    Add columns safely

    private void AddField(string fieldName, DataType fieldType, ColumnCollection pbiColumns)
{
    if (pbiColumns == null) return;

    var newColumn = new Microsoft.AnalysisServices.Tabular.DataColumn
    {
        Name = fieldName,
        DataType = fieldType,
        SourceColumn = fieldName
    };
    pbiColumns.Add(newColumn);

    Use the recomended refresh method and order For schema changes:

    Model.RequestRefresh(RefreshType.Full);
database.Model.SaveChanges();

     

    • Prefer Model.RequestRefresh(RefreshType.Full) over Table.RequestRefresh for structural changes.
    • This ensures the dataset refresh is properly registered in the service.

     

    References:

    Model.RequestRefresh Method (Microsoft.AnalysisServices.Tabular) | Microsoft Learn

     

    Please, let me know if it worked.

     

    If this answer was helpful in any way, I’d be happy to receive a 👍 and the joy of seeing a DAX measure work for the first time without needing yet another FILTER.
    Please mark it as the accepted solution. This helps other community members find the quickest path and saves them from another endless loop 🌀.