In this writing, I love to share a seamless approach to executing an all-tables-all-partitions-full refresh with just one click in the semantic model, with incremental refresh configured.
I have a single report/semantic model published to the premium capacity workspace, housing 10 fact tables. Each of these 10 fact tables is set up with incremental refresh, refreshing only the most recent 7 days of data with every refresh cycle.
Everything operates flawlessly until certain circumstances arise necessitating a full refresh of the semantic model. This might be triggered by alterations to the source of the data warehouse or the warehouse structure itself. Instances such as the addition of extra columns across all 10 fact tables within the data warehouse or a sudden shift in business requirements demanding numerical values to display four digits rather than two, mandate a comprehensive refresh. Various other factors may also compel a full refresh of the semantic model, extending beyond the confines of the standard seven-day incremental refresh.
In such instances, my process involves utilizing tools like Tabular Editor 3 or SQL Server Management Studio (SSMS) to identify the semantic model requiring a full refresh. Within the semantic model, I initiate the process by selecting the first fact table, then proceed to choose all partitions and opt for the full-refresh option. This sequence is repeated for each of the remaining nine fact tables, resulting in a laborious and time-consuming task.
However, I explored a more efficient approach to streamline this repetitive process and alleviate the burden. Through exploration and experimentation, I discovered a solution using the Rest API - Enhanced API, which enables a single-click full refresh for all fact tables within the semantic model. Below, I outline the step-by-step process for achieving this efficiency.
Before proceeding, it's essential to review the provided documentation to grasp the intricacies of the process and understand any limitations associated with it.
The constraints of this approach are clearly outlined in the documentation provided via the link below.
Microsoft documentation link: Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
The following images depict the configuration of incremental refresh for two out of ten fact tables. They illustrate that only data from March 26th, 2024, up to the most recent data is affected by the refresh action. The remaining eight fact tables exhibit similar behavior.
Step by step process
Navigate to the workspace and select the semantic model.
Locate a group ID and a semantic model ID.
Please access the provided link down below, and click on the "▶️Try it" button. Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Input all the required information as illustrated below, then proceed to click the "Run▶️" button.
After initiating the refresh, you can review the refresh history of the semantic model, which will appear as follows: (Type = Via Enhanced API).
Upon completion of the refresh process, thoroughly verify that all partitions and fact tables have been fully refreshed. Double-check to ensure accuracy.
(all ten fact tables are checked.)
To summarize, I explored the intricacies of executing a full refresh on a semantic model using Rest API - Enhanced API in Power BI. Initially, I discussed the scenarios that necessitate a full refresh, such as changes to the data warehouse structure or sudden shifts in business requirements. Facing the challenge of manually refreshing each fact table individually, I sought efficiency and discovered a solution through the Rest API - Enhanced API. With step-by-step process, I demonstrated how to initiate the refresh, monitor its progress, and confirm its completion.
By leveraging the Rest API - Enhanced API, I was able to perform a single-click full refresh for all fact tables within the semantic model, saving time and reducing the risk of errors. With a final check to ensure all partitions and fact tables were successfully refreshed, I concluded my journey towards optimizing the refresh process in Power BI.
I hope this blog post enhances your enjoyment and exploration of the Rest API - Enhanced API, making your experience with it both informative and enjoyable.
Comments