Here is the code to modify schema programmatically. I used Python to update server and database parameters.
Main.cs
using Microsoft.AnalysisServices.Tabular;
using System.Data;
namespace UdfCode
{
public class MainService
{
private readonly AadService _aadService;
private readonly Config _config;
private readonly IHelperService _helperService;
public MainService(AadService aadService, Config config, IHelperService helperService)
{
_aadService = aadService;
_config = config;
_helperService = helperService;
}
public async Task Run()
{
try
{
string? accessToken = await _aadService.GetAccessToken();
if (string.IsNullOrEmpty(accessToken))
{
throw new ArgumentNullException(nameof(accessToken), "Access token retrieval failed.");
}
string sqlConnectionString = _config.SqlConnectionString ?? throw new ArgumentNullException(nameof(_config.SqlConnectionString));
string spectrumDB = _config.SpectrumDB ?? throw new ArgumentNullException(nameof(_config.SpectrumDB));
string dimUdfQuery = _config.DimUdfQuery ?? throw new ArgumentNullException(nameof(_config.DimUdfQuery));
string factUdfQuery = _config.FactUdfQuery ?? throw new ArgumentNullException(nameof(_config.FactUdfQuery));
string PartitionDatabase = _config.PartitionDatabase ?? throw new ArgumentNullException(nameof(_config.PartitionDatabase));
string KeyColumn = _config.KeyColumn ?? throw new ArgumentNullException(nameof(_config.KeyColumn));
string TableName = _config.TableName ?? throw new ArgumentNullException(nameof(_config.TableName));
string DimUdfTable = _config.DimUdfTable ?? throw new ArgumentNullException(nameof(_config.DimUdfTable));
string FactUdfTable = _config.FactUdfTable ?? throw new ArgumentNullException(nameof(_config.FactUdfTable));
string workspaceName = _config.workspaceName ?? throw new ArgumentNullException(nameof(_config.workspaceName));
string databaseId = _config.DatabaseId ?? throw new ArgumentNullException(nameof(_config.DatabaseId));
DataTable dimUdfDataTable = _helperService.LoadDataTable(
sqlConnectionString,
dimUdfQuery.Replace("{SpectrumDB}", spectrumDB),
"dimUdfDataTable",
out int dimUdfCount
);
DataTable factUdfDataTable = _helperService.LoadDataTable(
sqlConnectionString,
factUdfQuery.Replace("{SpectrumDB}", spectrumDB),
"factUdfDataTable",
out int factUdfCount
);
Model? model = _helperService.EstablishWorkspaceConnection(_config.workspaceName, _config.DatabaseId, accessToken) ?? throw new InvalidOperationException("Failed to establish workspace connection.");
Table modelConnectingTable = model.Tables[_config.TableName];
if (dimUdfCount == 0 && factUdfCount == 0)
{
bool removedDim = _helperService.RemoveTableAndRelationships(model, _config.DimUdfTable);
bool removedFact = _helperService.RemoveTableAndRelationships(model, _config.FactUdfTable);
if (removedDim || removedFact)
{
model.RequestRefresh(RefreshType.Full);
Console.WriteLine($"\nPower BI Semantic Model \"{_helperService.DatasetName}\" refresh triggered!");
model.SaveChanges();
Console.WriteLine($"\nPower BI Semantic Model \"{_helperService.DatasetName}\" updated successfully!");
}
else
{
Console.WriteLine("\nNo UD fields found. Terminating the process.");
}
}
else
{
_helperService.RemoveTableAndRelationships(model, _config.DimUdfTable);
_helperService.RemoveTableAndRelationships(model, _config.FactUdfTable);
if (dimUdfCount > 0)
{
Table dimUserDefinedFields = _helperService.CreateAndAddTable(model, _config.DimUdfTable);
_helperService.AddColumnsToTable(dimUserDefinedFields, dimUdfDataTable);
_helperService.CreatePartition(dimUserDefinedFields, "dimUserDefinedFields Partition", _config.DimUdfQuery, _config.PartitionDatabase);
_helperService.CreateRelationship(model, dimUserDefinedFields, modelConnectingTable, _config.KeyColumn, _config.TableName, _config.IsManyToOne);
}
if (factUdfCount > 0)
{
Table factUserDefinedFields = _helperService.CreateAndAddTable(model, _config.FactUdfTable);
_helperService.AddColumnsToTable(factUserDefinedFields, factUdfDataTable);
_helperService.CreatePartition(factUserDefinedFields, "factUserDefinedFields Partition", _config.FactUdfQuery, _config.PartitionDatabase);
_helperService.CreateRelationship(model, factUserDefinedFields, modelConnectingTable, _config.KeyColumn, _config.TableName, _config.IsManyToOne);
}
model.RequestRefresh(RefreshType.Full);
Console.WriteLine($"\nPower BI Semantic Model \"{_helperService.DatasetName}\" refresh triggered!");
model.SaveChanges();
Console.WriteLine($"\nPower BI Semantic Model \"{_helperService.DatasetName}\" updated successfully!");
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Program.cs
using System.Data;
using System.Text;
using Microsoft.Data.SqlClient;
using Microsoft.AnalysisServices.Tabular;
using TabularDataColumn = Microsoft.AnalysisServices.Tabular.DataColumn;
namespace UdfCode
{
public class HelperService : IHelperService
{
public Server? server;
public string DatasetName { get; private set; } = string.Empty;
public Model? EstablishWorkspaceConnection(string workspaceName, string databaseId, string accessToken)
{
try
{
Console.WriteLine("\nConnecting to Power BI Workspace...");
string workspaceConnection = $"powerbi://api.powerbi.com/v1.0/myorg/{workspaceName}";
Console.WriteLine($"Workspace Connection: {workspaceConnection}");
Console.WriteLine($"Dataset ID: {databaseId}");
string connectString = $"Provider=MSOLAP;Data Source={workspaceConnection};User ID=;Password={accessToken};Persist Security Info=True;Impersonation Level=Impersonate;";
Server server = new();
server.Connect(connectString);
Console.WriteLine("Connection established!");
Model model = server.Databases[databaseId].Model;
DatasetName = server.Databases[databaseId].Name;
Console.WriteLine($"Connected to PBI Dataset: {model.Name}");
return model;
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
Console.WriteLine($"StackTrace: {ex.StackTrace}");
return null;
}
}
public DataTable LoadDataTable(string ConnectionString, string query, string tableName, out int ColumnCount)
{
DataTable dataTable = new();
using (SqlConnection sqlConnection = new(ConnectionString))
{
Console.WriteLine("\nEstablishing SQL Server Connection");
sqlConnection.Open();
Console.WriteLine($"\nSQL Server connection successful for {tableName}!");
using (SqlDataAdapter adapter = new(query, sqlConnection))
{
adapter.Fill(dataTable);
}
Console.WriteLine($"\n{tableName} Loaded!");
sqlConnection.Close();
}
ColumnCount = dataTable.Columns.Count - 1;
Console.WriteLine($"UD Columns fetched into {tableName}: {ColumnCount}");
return dataTable;
}
public bool RemoveTableAndRelationships(Model model, string tableName)
{
if (model.Tables.Contains(tableName))
{
Table table = model.Tables[tableName];
var relationships = model.Relationships
.Where(r => r is SingleColumnRelationship rel &&
(rel.FromTable.Name == table.Name || rel.ToTable.Name == table.Name))
.ToList();
foreach (var rel in relationships)
{
model.Relationships.Remove(rel);
}
model.Tables.Remove(table);
Console.WriteLine($"\nDeleted table: {tableName}");
return true;
}
else
{
Console.WriteLine($"\nTable '{tableName}' not found in the model.");
return false;
}
}
public Table CreateAndAddTable(Model model, string tableName)
{
Table newTable = new()
{
Name = tableName,
IsHidden = false
};
model.Tables.Add(newTable);
Console.WriteLine($"\nCreated new table: {tableName}");
return newTable;
}
public void AddColumnsToTable(Table targetTable, DataTable sourceDataTable)
{
for (int i = 0; i < sourceDataTable.Columns.Count; i++)
{
string columnName = sourceDataTable.Columns[i].ColumnName;
Type columnType = sourceDataTable.Columns[i].DataType;
DataType tabularDataType = ConvertToTabularDataType(columnType);
if (!targetTable.Columns.ContainsName(columnName))
{
TabularDataColumn newColumn = new()
{
Name = columnName,
DataType = tabularDataType,
SourceColumn = columnName,
IsHidden = (i == 0)
};
targetTable.Columns.Add(newColumn);
Console.WriteLine($"Added new column into {targetTable.Name}: {columnName}");
}
else
{
Console.WriteLine($"Column {columnName} already exists in {targetTable.Name}. Skipping...");
}
}
}
public void CreatePartition(Table table, string partitionName, string query, string database)
{
if (table.Partitions.Count == 0)
{
Console.WriteLine($"\nPartition getting created for {table.Name}");
Partition partition = new()
{
Name = partitionName,
Mode = ModeType.Import,
DataView = DataViewType.Full,
Source = new MPartitionSource
{
Expression = ConvertToMQuery(query, database)
}
};
table.Partitions.Add(partition);
Console.WriteLine($"Partition added to {table.Name}");
}
}
public void CreateRelationship(Model model, Table fromTable, Table toTable, string keyColumn, string tableName, bool isManyToOne)
{
Console.WriteLine($"\nRelationship between {tableName} and {fromTable.Name} on {keyColumn} has started");
SingleColumnRelationship relationship = new()
{
FromColumn = fromTable.Columns[keyColumn],
ToColumn = toTable.Columns[keyColumn],
FromCardinality = isManyToOne ? RelationshipEndCardinality.Many : RelationshipEndCardinality.One,
ToCardinality = RelationshipEndCardinality.One,
CrossFilteringBehavior = CrossFilteringBehavior.Automatic,
IsActive = true
};
model.Relationships.Add(relationship);
Console.WriteLine($"Established {(isManyToOne ? "Many-to-One" : "One-to-One")} relationship between {tableName} and {fromTable.Name} on {keyColumn}");
}
public DataType ConvertToTabularDataType(Type columnType)
{
if (columnType == typeof(string)) return DataType.String;
if (columnType == typeof(int) || columnType == typeof(long)) return DataType.Int64;
if (columnType == typeof(float) || columnType == typeof(double) || columnType == typeof(decimal)) return DataType.Double;
if (columnType == typeof(bool)) return DataType.Boolean;
if (columnType == typeof(DateTime)) return DataType.DateTime;
return DataType.String;
}
public string ConvertToMQuery(string sqlQuery, string database)
{
sqlQuery = sqlQuery.Replace("{SpectrumDB}", " \" & SpectrumDB & \"");
StringBuilder mQueryBuilder = new();
mQueryBuilder.AppendLine("let");
mQueryBuilder.AppendLine($" Source = Sql.Database(Server, {database}, [Query = ");
string[] sqlLines = sqlQuery.Split(new[] { "\r\n", "\n" }, StringSplitOptions.None);
for (int i = 0; i < sqlLines.Length; i++)
{
string line = sqlLines[i].Trim();
if (!string.IsNullOrWhiteSpace(line))
{
mQueryBuilder.Append(" \"" + line + "#(lf)\"");
if (i < sqlLines.Length - 2)
{
mQueryBuilder.AppendLine(" &");
}
}
}
mQueryBuilder.AppendLine(" ])");
mQueryBuilder.AppendLine("in");
mQueryBuilder.AppendLine(" Source");
return mQueryBuilder.ToString();
}
}
}
Please mark it as solution if it is helpful and like the solution too.
