We all know how easy it is to refresh a single table in Power BI Desktop by right-clicking the table and selecting Refresh Data. However, in Power BI Service, we can only refresh the entire semantic model, as there is no out-of-the-box solution for refreshing a single table. Fortunately, this can be achieved using XMLA Endpoints.Â
In this blog, we will explain how to refresh a single table in Power BI Service using XMLA Endpoints.
This is particularly helpful in scenarios where:
You are working with large semantic models, and refreshing the entire model takes too long. By refreshing only the relevant table (e.g., a daily updated table), you can save time, speed up the process, and reduce resource consumption.
You made a change to one specific table in a large semantic model. Refreshing the entire model would take a long time, so in this situation, it would be very helpful to refresh just that one table instead.
Prerequisites
The following are prerequisites to refresh a table using XMLA endpoints in Power BI Service.
XMLA endpoints must be enabled asÂ
READ WRITEin the Power BI Tenant Admin settings page.To enable read-write for a Premium capacity
In Power BI Service, select Settings > Admin portal
In the Admin portal, select Capacity settings > Power BI Premium > Capacity name
Expand Power BI Workloads. In the XMLA Endpoint setting, select Read Write and click on Apply.
-
To enable read-write for Premium Per User
-
In Power BI Service, select Settings > Admin portal
-
In the Admin portal, select Premium Per User
-
Expand Semantic model workload settings. In the XMLA Endpoint setting, select Read Write and click on Apply.
-
You should have at least Contributor access to the workspace
Download SQL Server Management Studio
Step-by-Step Guide for Refreshing a Single Table in Power BI Semantic Model
Using a demo data model, let’s walk through how to refresh a single table in a Power BI semantic model.
In this example, I’m using a demo semantic model with three tables: Customer, Sales, and Date.
I have published this model to a Power BI Premium Capacity workspace.
Normally, when you refresh the model either manually or through a scheduled refresh, the entire model gets refreshed. You can check the status of these refreshes in the Refresh History.
Go to the Premium Capacity workspace settings to refresh a single table in the Power BI semantic model. Under License Info, copy the XMLA Endpoint connection link.
Now, open SQL Server Management Studio (SSMS). In the Object Explorer, choose Analysis Services from the Connect drop-down.
In the Server name, paste the XMLA endpoint link you copied from the workspace settings. Select the appropriate authentication type, enter your username and password, and click Connect.
In the Object Explorer, expand the Databases folder, the Demo Database, and then the Tables folder.
Right-click on the table you want to refresh and select Process Table.
A window will open. In the Mode dropdown, choose Process Data and click OK.Â
Note: If needed, you can select multiple tables for refresh in this window.
The refresh process will begin. Once completed, you will see a success message.
You can verify the refresh by checking the Refresh History, where the refresh type will be listed as “Via XMLA Endpoint”.
Limitations
XMLA Endpoints are not available for Power BI Pro workspaces.
Tables in a Power BI semantic model cannot be refreshed via XMLA Endpoint if they are located in personal workspaces.
Tables in a Power BI semantic model connected to Azure Analysis Services or SQL Server Analysis Services via a live connection cannot be refreshed using the XMLA Endpoint.
Closing
Using XMLA Endpoints to refresh a single Power BI semantic model table through Power BI Service is a highly efficient method. It allows you to refresh only the required tables, reducing resource consumption and saving time compared to refreshing the entire model. While it is limited to Power BI Premium Per user or Premium Capacity workspaces, this approach is ideal for refreshing modified or frequently updated tables. You can easily manage table-specific refreshes in Power BI Service by following the steps outlined.
If you have additional questions or need further assistance, please contact phData. Our team of experts is here to help you optimize your Power BI processes and ensure efficient data management tailored to your needs.
FAQs
What is an XMLA Enpoint?
The XMLA (XML for Analysis) Endpoint in Power BI is an interface that enables external tools and applications to interact with the Power BI semantic models at the backend. It supports read and write operations, allowing users to manage and refresh models, run queries using SQL Server Management Studio (SSMS) and Tabular Editor tools, and automate tasks through APIs. This endpoint grants access to the underlying Analysis Services engine in Power BI.
What is the Difference between XMLA Read Only and XMLA Read Write?
XMLA Read Only permits users to query and retrieve data without making changes to the semantic model, making it ideal for reporting and analysis purposes. On the other hand, XMLA Read Write provides full access for reading and writing, enabling users to modify the model, refresh data, and perform administrative functions. Read Only is focused on data consumption, whereas Read Write is designed for model management and development.
