Creating a Semantic Model via TOM with ODBC Connections

[diegoervitiq]

I’m trying to create a Tabular Semantic Model programmatically using TOM (Tabular Object Model) and I’m running into issues when using ODBC connections.

I’m creating the database and adding data sources with the following C# code (inspired in Repo)

public static Database CreateDatabase(string DatabaseName, string databases_info_path)
{
    //(...)
    var database = new Database() 
    { 
        Name = newDatabaseName, 
        ID = newDatabaseName, 
        CompatibilityLevel = 1520, 
        StorageEngineUsed = Microsoft.AnalysisServices.StorageEngineUsed.TabularMetadata, 
        Model = new Model() 
        { 
            Name = DatabaseName + "-Model", 
            Description = "A demo Tabular data model with 1520 compatibility level." 
        } 
    }; 

    //(...)

    database.Model.DataSources.Add(new ProviderDataSource() 
    { 
        Name = databaseInfo.Name, 
        Description = $"Connection to database {databaseInfo.Name}", 
        ConnectionString = databaseInfo.ConnectionString, 
        ImpersonationMode = ImpersonationMode.ImpersonateAccount,
        Account = databaseInfo.Account, 
        Password = databaseInfo.Password
    }); 

    server.Databases.Add(database); 
    database.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull); 
    return database; 
}

When my connection string points to SQL Server everything works fine. For example:

Provider=SQLNCLI11;Data Source=...;Initial Catalog=...;Integrated Security=SSPI;Persist Security Info=false
 

However, when I try to use an ODBC connection I get errors when saving the model, for example:

Driver={PostgreSQL Unicode(x64)};Server=...;Port=...;Database=...;Uid=...;Pwd=...;

After creating tables, if I try to perform a request refresh in the same transaction ( model.SaveChanges() and model.RequestRefresh(RefreshType.Full) ), I receive the following error:

{
  "error": {
    "code": "Premium_ASWL_Error",
    "pbi.error": {
      "code": "Premium_ASWL_Error",
      "parameters": {},
      "details": [
        {
          "code": "Premium_ASWL_Error_Details_Label",
          "detail": {
            "type": 1,
            "value": "We are unable to retrieve the credentials for the data source '[DSR_Redacted]'. This error might occur when a request attempts to add a new data source to a semantic model and perform a data refresh in a single transaction, which is not supported. In such cases, add new data sources in a separate transaction first and then separately perform the data refresh."
          }
        }
      ],
      "exceptionCulprit": 0
    }
  }
}

If I skip the request refresh in the transaction and try to add the data source from the portal afterward, it fails as well; I cannot add the data source to the semantic model.

Does anyone know how to create a semantic model via TOM when using ODBC connections?

Any guidance or examples would be greatly appreciated.

[v-sdhruv]

Hi @diegoervitiq ,

I hope you were able to create a Support Ticket successfully. If you need any assitance,feel free to reach out.

Thank You

[v-sdhruv]

Hi @diegoervitiq ,
Maybe you want to check out this article which explains in detail abour TOM.
Programming Datasets with the Tabular Object Model (TOM) · Power BI Dev Camp

If this still doesnt help, try raising a support ticket here:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Hope this helps!

[v-sdhruv]

Hi @diegoervitiq ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.

Thank You

[diegoervitiq]

Hi @v-sdhruv, thanks for your response.
Unfortunately, separating the operations with Disconnect wasn’t enough. I tried doing it at different stages, but I still got the same error.
Regarding XMLA, I didn’t quite understand how I could incorporate it into my current logic.

[v-sdhruv]

Hi @diegoervitiq ,

Try to save and then disconnect from the session after creating the data model. Then again reconnect , add tables then request refresh.

If this still doesnt work you can try with XMLA
Programming Datasets with the Tabular Object Model (TOM) · Power BI Dev Camp

Hope this helps!

[v-sdhruv]

Hi @diegoervitiq ,
Thank you for providing detailed explaination for your query.
Mybe you can try to split the operations into two transactions.

do not add data source and refresh the model in the same transaction.
Add the data source and save the model.In a seperate operation, request for refersh.

model.RequestRefresh(RefreshType.Full);
model.SaveChanges();

This separation ensures that the credentials are properly registered before a refresh is attempted.
ODBC sources often fail when using workspace identity authentication. Instead, use explicit credentials or service principal authentication.
Consider using Power Query (M Expressions) for Import Mode.
Load data from sources like Snowflake or PostgreSQL. This method is more reliable in Premium workspaces.

Hope this helps!

[diegoervitiq]

Hi @v-sdhruv, thank you for your response.

I’ve tried to separate operations in several ways, but I still get the same error. Here’s my current workflow:

DatasetManager.ConnectToPowerBIAsUser();
Database database = DatasetManager.CreateDatabase(DatabaseName, DatabasesInfo);
string databaseNewName = database.Name;

DatasetManager.CreateModel(database, ModelFilePath, databaseNewName);
database.Model.SaveChanges();

var dbReloaded = DatasetManager.server.Databases.GetByName(databaseNewName);
dbReloaded.Model.RequestRefresh(RefreshType.Full); //here crashes
dbReloaded.Model.SaveChanges();

 I’ve even tried adding delays between operations, but it doesn’t help.

How can I properly separate transactions if it’s not done this way?