One of the great advantages of the incremental refresh feature in Power BI is the ability to speed up the refresh process of large models by reducing the amount of data that is being refreshed on each incremental refresh cycle. However, you sometimes have to refresh data in a partition that is before the dates that are being refreshed. Without SQL Server Management Studio (SSMS), the only way to do this is to re-publish your model to trigger a full refresh. In this blog post, I’ll show you how you can use SSMS to either refresh a specific partition of a specific table inside a model or trigger a full refresh of a table.
Prerequisites
- Power BI Premium license
- XMLA Endpoint set up to ‘Read Write’ in the Power BI Admin portal
- Admin access to the workspace where your model is located
- SSMS installed on your machine
Once you’ve made sure that you have all the prerequisites, follow these steps:
- Head to the Power BI service to obtain your workspace connection link. Open the workspace in which your model is located, and on top of your screen, click on Workspace settings. If you don’t have Admin access to the workspace, you won’t see this button.
- On the left, click on Premium and on the bottom of your screen, copy the connection string under Workspace connection.
- After you’ve copied the connection string to your workspace, open SSMS. To connect to your Power BI workspace, select Analysis Services as the Server type. Then enter your workspace connection string to the Server name. The Authentication method is typically Microsft Entra MFA and the User name is typically your password.
- A pop-up will appear asking you to add your credentials and possibly to confirm your identity with MFA.
- You now have access to all the tables of your Power BI model from SSMS. Right-click on the table for which you want to do a full refresh, and click on Partitions. If the table is set up with incremental refresh, you’ll see multiple partitions that are typically based off of your incremental refresh set up.
- You can select a specific partition you want to refresh, or if you hold the CTRL button on your keyboard, you can select multiple partitions, or even all of them. Once you’ve selected which partitions you want to refresh, click on the Process button on top, which is the green spinning arrows.
- On the left of the table, based on the partitions you’ve selected in the previous window, you’ll see which partitions are going to be refreshed. Then, on top of the screen, click on Process Full, and then click Ok on the bottom of your screen to start the refresh process.
- Once the refresh is complete, you’ll get a message telling you your partition(s) are refreshed.
- In the refresh history of your model in Power BI service, you’ll also see a refresh cycle that was completed via XMLA Endpoint.
By following these steps, you can use SSMS to either refresh a specific partition of a specific table inside a model or trigger a full refresh of a table. This is very useful when you have a model with incremental refresh and you want to refresh a specific partition without having to re-publish your model and trigger a full refresh of your model. In a future blog post, I’ll show you what else you can do to your Power BI model via XMLA Endpoint and SSMS.
