Forum Discussion
Continued ContributorUpdate schema of large semantic models
Hi everyone,
I have a large semantic model (40M+ rows) with incremental refresh enabled. Currently, any schema change (e.g., renaming a column or adding something new) requires opening the full model in Power BI Desktop, which takes a long time to load/process due to the size. I want a faster way to apply these updates, ideally directly in the cloud (Power BI Service) without reloading the entire dataset every time, focusing on schema/metadata changes only. How can I do it using only Microsoft tools?
Thanks
Hi julsr ,
Thank you for reaching out to the Microsoft Community Forum.
By using SSMS + TMSL you can update a Power BI semantic model without opening Power BI Desktop. It works ONLY if your workspace is on a Premium / Fabric F/SK capacity because it requires XMLA read/write.
Please try below steps.
1. In Power BI Service, Open your workspace --> Go to Settings --> Premium / Fabric capacity. Copy the XMLA endpoint URL:
powerbi://api.powerbi.com/v1.0/myorg/SalesWorkspace
2. In SSMS, Open SQL Server Management Studio --> Click Connect --> Analysis Services. Enter the XMLA endpoint URL.
3. Authentication, Azure Active Directory - Universal (with MFA). Sign in with your Power BI account. After connecting, you will see all datasets as “databases”.
4. Right-click the Dataset --> New Query --> XMLA. This opens a query window where you can run TMSL(Tabular Model Scripting Language) scripts. Execute through an XMLA connection.
To Rename a Column refer below JSON code.
{
"alter": {
"object": {
"database": "ModelName",
"table": "FactSales",
"column": "OldColumnName"
},
"renameTo": "NewColumnName"
}
}To Add a New Column refer below JSON code.
{
"alter": {
"object": { "database": "ModelName" },
"script": "{
\"tables\": [{
\"name\": \"FactSales\",
\"columns\": [{
\"name\": \"NewCol\",
\"dataType\": \"string\"
}]
}]
}"
}
}Please refer below link.
Tabular Model Scripting Language (TMSL) Reference | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
5 Replies
- cengizhanarslan
Super User
1) Tabular Editor
If your dataset is on Premium or Fabric capacity:
-
Connect to the dataset via XMLA (read/write)
-
Use Tabular Editor
-
Perform metadata-only changes: Rename columns, Add measures, Change formatting, Hide fields, Update descriptions
These changes do not trigger a full refresh, do not reload incremental partitions and apply almost instantly. This is the fastest and most common approach for large models.
2) TMDL editing in Fabric (if using Fabric semantic models)
If this is a Fabric semantic model:
-
Use TMDL in the Service
-
Edit schema definitions directly (columns, measures, formatting)
-
Save changes without touching data
-
- GeraldGEmerick
Memorable Member
julsr If you can remove the requirement for Microsoft only tools, then ALMToolkit can do it. Can you edit the model in the Service?
v-dineshyaCommunity Support
Hi julsr ,
Thank you for reaching out to the Microsoft Community Forum.
By using SSMS + TMSL you can update a Power BI semantic model without opening Power BI Desktop. It works ONLY if your workspace is on a Premium / Fabric F/SK capacity because it requires XMLA read/write.
Please try below steps.
1. In Power BI Service, Open your workspace --> Go to Settings --> Premium / Fabric capacity. Copy the XMLA endpoint URL:
powerbi://api.powerbi.com/v1.0/myorg/SalesWorkspace
2. In SSMS, Open SQL Server Management Studio --> Click Connect --> Analysis Services. Enter the XMLA endpoint URL.
3. Authentication, Azure Active Directory - Universal (with MFA). Sign in with your Power BI account. After connecting, you will see all datasets as “databases”.
4. Right-click the Dataset --> New Query --> XMLA. This opens a query window where you can run TMSL(Tabular Model Scripting Language) scripts. Execute through an XMLA connection.
To Rename a Column refer below JSON code.
{
"alter": {
"object": {
"database": "ModelName",
"table": "FactSales",
"column": "OldColumnName"
},
"renameTo": "NewColumnName"
}
}To Add a New Column refer below JSON code.
{
"alter": {
"object": { "database": "ModelName" },
"script": "{
\"tables\": [{
\"name\": \"FactSales\",
\"columns\": [{
\"name\": \"NewCol\",
\"dataType\": \"string\"
}]
}]
}"
}
}Please refer below link.
Tabular Model Scripting Language (TMSL) Reference | Microsoft Learn
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya
Hi julsr ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
- v-dineshya
Hi @julsr ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
