How to Create Relationships in Power BI Semantic Model Using Tabular Editor 2.x and JSON File?

Question

I am working on a large-scale project where I need to build multiple Power BI semantic models. Each model contains dozens of tables with multiple fields, and I need to establish relationships between these tables. Manually creating relationships for each model is time-consuming, so I am trying to automate this process using Tabular Editor and a JSON file.

For context:

• I have 100+ Power BI models, and each model contains around 50+ tables.
• Each model requires relationships between the tables.
• I have a JSON file for each model, which defines the relationships, cardinality, and cross-filter direction.

Here is an example of the JSON file:

[
  {
    "FromTable": "ORDERS",
    "FromColumn": "CustomerID",
    "ToTable": "CUSTOMERS",
    "ToColumn": "CustomerID",
    "Cardinality": "ManyToOne",
    "CrossFilteringBehavior": "BothDirections"
  },
  {
    "FromTable": "ORDERS",
    "FromColumn": "ProductID",
    "ToTable": "PRODUCTS",
    "ToColumn": "ProductID",
    "Cardinality": "ManyToOne",
    "CrossFilteringBehavior": "OneDirection"
  }
]

I attempted to write a C# script in Tabular Editor to:

1. Load this JSON file.
2. Parse the relationship data.
3. Create relationships dynamically in the model based on the data.

Here is the script I tried:

using System;
using System.IO;
using Newtonsoft.Json;
using System.Collections.Generic;

string jsonFilePath = @"C:\path\to\relationship.json"; 
string jsonContent = File.ReadAllText(jsonFilePath);

var relationships = JsonConvert.DeserializeObject<List<Relationship>>(jsonContent);

public class Relationship 
{
    public string FromTable { get; set; }
    public string FromColumn { get; set; }
    public string ToTable { get; set; }
    public string ToColumn { get; set; }
    public string Cardinality { get; set; }
    public string CrossFilteringBehavior { get; set; }
}

foreach (var rel in relationships)
{
    var fromTable = Model.Tables[rel.FromTable];
    var toTable = Model.Tables[rel.ToTable];

    if (fromTable == null || toTable == null)
    {
        Output.WriteLine($"Table '{rel.FromTable}' or '{rel.ToTable}' does not exist in the model.");
        continue;
    }

    var fromColumn = fromTable.Columns[rel.FromColumn];
    var toColumn = toTable.Columns[rel.ToColumn];

    if (fromColumn == null || toColumn == null)
    {
        Output.WriteLine($"Column '{rel.FromColumn}' or '{rel.ToColumn}' does not exist.");
        continue;
    }

    var relationship = Model.Relationships.Add(fromColumn, toColumn);
    relationship.Cardinality = rel.Cardinality == "ManyToOne" ? RelationshipCardinality.ManyToOne : RelationshipCardinality.OneToOne;
    relationship.CrossFilteringBehavior = rel.CrossFilteringBehavior == "BothDirections" ? SecurityFilteringBehavior.BothDirections : SecurityFilteringBehavior.OneDirection;

    Output.WriteLine($"Created relationship: {rel.FromTable}.{rel.FromColumn} -> {rel.ToTable}.{rel.ToColumn}");
}

However, the script does not seem to work as expected. Relationships are either not created, or the script is failing with many errors.

Questions

1. How can I automate the creation of relationships in multiple Power BI semantic models using Tabular Editor and JSON files?
2. Are there any best practices for managing models multile tables each and automating relationships?
3. Are there any limitations or special considerations when using Tabular Editor scripts for relationship creation?
4. Is there a better way to debug issues when working with large semantic models in Tabular Editor?

Any help or working script examples would be greatly appreciated!