Goal of this demo: Update a Power BI model schema by adding a column to the data model without opening a PBIX file and ensure the scheduled refresh still works.
Why would this be useful? Updating the schema in the desktop tool requires an entire refresh of the data model which can take a while if your model is large. Also, app developers could systematically add new elements to existing data models using a formulaic XMLA script through SSMS, saving your report designers time when new fields need to be added.
Limitations: You cannot export the PBIX file from the service once the XMLA updates have been made. For adding columns to the model, that’s not a big problem since those would be added in once you opened the desktop tool again. The problem comes if you create or edit visuals in the online service that you don’t want to overwrite in future iterations.
Tools needed:
- A data source you can edit (Excel will work, this demo uses a SQL view)
- Power BI Desktop
- Power BI Premium Workspace (Premium Per User should also work)
- Power BI Pro License
- SSMS (SQL Server Management Studio)
Notes:
- Ensure you have a data source you can add columns to if you are following the example below
- Save a copy of your PBIX report so you can make visual edits in the future. Once you edit a data model using the XLMA endpoint, you can no longer export it as a PBIX file from the online PBI service
Process:
- First, create and publish a Power BI Report to the online service. No need to add any visuals, but make sure you have at least one table you have access to edit the columns in to follow along with this demo. You will need a Power BI Pro license and access to publish to a Premium workspace.
- Next, add a column to your data source that does not currently exist in your Power BI report. For example, make a column in Excel or SQL called “Testing” with the number 1 filled in for every row.
- Unfortunately, Power BI does not refresh the schema in the service so it will not pull in the new column unless you open up the report in Power BI Desktop and refresh there then republish. One way around this is using the XMLA endpoint from the premium workspace and add the column into the JSON code using the TMSL scripting in SSMS. Before we walk through each of those steps, keep in mind that doing this will prevent that Power BI dataset from being downloaded as a PBIX file ever again. So, it’s best to keep a local copy of that PBIX file for any visual updates that need to be made, or simply use this dataset as a certified dataset to be used in multiple reports.
- Open the premium workspace, select settings, and go to the “Premium” tab to copy the workspace connection.
5. Open SSMS and select “Analysis Services” for your server type. In the server name, paste in the workspace connection string that you copied in step 4. Authentication will be Azure Active Directory with MFA. The user name and password will be the same email and password you use to access Power BI.
6. Under databases, you’ll see all the datasets present in that workspace. Expand the database with the name of your dataset and expand the tables. Note, there will be tables present that you don’t recognize. For every date table in your data model, Power BI builds a table behind the scenes that will now be exposed. Navigate to the table you want to add the column to and right click it.
7. Navigate through by hovering over “Script Table as” then “CREATE OR REPLACE To” then select “New Query Editor Window”. This will open a script to adjust the data model of that table in TMSL (tabular model scripting language).
8. Now here’s the tricky part. It’s best if you already have a column in your data model that is the same data type as the one you want to add so you can just copy/paste the JSON object from the existing script. My example is for an integer column, but you can do this for any data type. Scroll down in the code until you start to see your column names. In my dataset, I have a column named “Custom1” that is the same type as my “Testing” column. All you have to do is copy and paste the code of your sample column then swap out any place where it says “Custom1” (aka whatever your sample column name is) with the name of your new column.
9. Delete the line of code that says “lineageTag” from your new section of code. The lineage tag only matters if you are editing an existing column, Power BI will generate a new lineage tag once this column is officially added to the schema.
10. Hit “Execute” or F5 to push the schema change to the data model in the service. The message at the bottom will run through a few items, but the final response should look like the one in the image below.
11. The final step is to refresh your data model in the Power BI service on demand or by using the script below. To run this script, you’ll need to select your main table then select “New Query”. You should see your measures and metadata populate as the analysis service cube is exposed. Once you see that, you can copy/paste the code below to refresh the table (this is also how you can refresh one table at a time if needed, hint hint). Execute and your new column will now have data in it, yay!
{
“refresh”: {
“type”: “automatic”,"objects": [
{
"database": "YOUR DATASET NAME HERE",
"table": "YOUR TABLE NAME HERE"
}
]
}
}
12. Test it out! Go into your report in the service, hit the edit button and update your report with the your new column! But remember, you no longer have the option to download the PBIX file. So any changes that need to be made to the data model (i.e. new measures) need to be done through the XMLA end point, and any visual changes must be done in the online service.
Additional Resources:
- https://justb.dk/blog/2020/04/use-cases-for-the-xmla-endpoint/
- https://justb.dk/blog/2020/04/using-the-xmla-endpoint-without-premium/
- https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools
- https://docs.microsoft.com/en-us/analysis-services/tmsl/tabular-model-scripting-language-tmsl-reference?view=asallproducts-allversions
- https://powerbi.microsoft.com/en-us/blog/using-xmla-endpoints-to-change-data-sources-in-a-power-bi-dataset/
- https://docs.microsoft.com/en-us/analysis-services/tmsl/createorreplace-command-tmsl?view=asallproducts-allversions
