#PowerAutomate #PowerBI #SemanticModelRefresh #Architecture This article is not about Microsoft Fabric; it's about Power BI. In a future article, I will describe a solution using more components from Fabric and explain why, from my point of view, the Fabric-based solution is more straightforward. # Use case / Problem No matter what we do, we will eventually (not to say hopefully) realize that the refresh of a semantic model in import mode will soon exceed its limits. To be more precise, a full refresh of a semantic model takes longer than 2 hours (if the model is on a shared capacity) or 5 hours if a Fabric or Premium capacity backs the model. Of course, there are some obvious steps we check/apply to avoid hitting these limits: + Only import the necessary data from a row and column perspective and use the proper data types to reduce the size of the semantic tables. + Use incremental refresh + Use aggregation tables (meaning import the hot data and keep old data in direct query mode ) Then, after we have applied one, all, or none of the above steps and realize that we are still approaching the refresh limit, we have to find a different approach. In addition to the refresh duration limit, another limit can either be solved quickly (e.g., by adding more money to the problem) or requires an entirely different approach: the size of the semantic model. Assuming the size is not an issue, we only have to address the limit on the duration of the semantic model refresh. # A solution outline - refreshing a single table The solution that might come to mind is not to refresh the entire model but only a single table or, if necessary, only a single table partition. Of course, this path might introduce new challenges, like "late arriving dimension data." These might form a future article, but I will focus on refreshing a single table instead of the entire model for now. Refreshing a single table or partition of a semantic model can be done using the "Enhanced Refresh API." This API provides the API endpoint: ``` POST https://api.powerbi.com/v1.0/myorg/groups/...id of the workspace .../datasets/... id of the semantic model .../refreshes ``` Both IDs can be collected from the URL when navigating to the semantic model inside the Fabric Service utilizing the Power BI experience: ![[Power Automate Refresh - get the the IDs.png]] **Keep in mind**: Using the Enhanced Refresh API requires the semantic model to be backed by one of the following licensing types: + Premium Per User + Premium capacity + Fabric capacity # The semantic model Mitigating the risk of exceeding the allowed duration for a refresh operation requires a close look at the semantic model, probably building an empty model or a model that starts with a fraction of the data. The image below shows my very simple model: ![[Power Automate Refresh - a simple model.png]] # The solution - a detailed view Now that we have identified the solution "calling the Enhanced Refresh API", we must find a way to call the API. ## Power Automate Thinking about a Power BI solution, the most straightforward way is to use Power Automate. The "HTTP" action allows calling a REST API. Of course, this action needs to be triggered. Inside the Power BI realm, triggering an action can be accomplished by listening to a Dataflow "completes" event. A very simple flow then will look like in the following image: ![[Power Automate Refresh - a simple flow.png]] **Keep in mind**: The HTTP connector is a **Premium** connector, this requires a Power Automate license that allows leveraging Premium connectors (https://learn.microsoft.com/en-us/connectors/webcontentsv2/). ### Parameters of the Trigger - When a dataflow refresh completes The following image shows the parameters used for the "When a dataflow refresh completes": ![[Power Automate Refresh - Parameters dataflow.png]] Please ensure that the "Recurrence" settings reflect the frequency and the duration of a single dataflow run. I use the settings above only to see if a dataflow run is "picked" up as expected 😉 ### Parameters of the HTTP-action I divide the parametrization of the action into sections + calling the API + authentication for the API #### Calling the API The following image shows the parameter used by the HTTP action (the API call): ![[Power Automate Refresh - HTTP Action - API call.png]] The settings Headers and Queries are not relevant. I copied the JSON for the "Body" parameter from here: https://learn.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh #### Authenticating for the API (this is for simplicity) It is not recommended to use the HTTP connect without reading the secret from Azure Key Vault, this is only for simplicity. See the chapter [[#The not-so-simple flow and, of course, the better option]] for the flow you should use in your environment. Providing the parameters for the API call requires authentication, and the "caller" needs to have the role "Member" assigned in the workspace that contains the semantic model. I use a Service Principal to call the API. In a dedicated chapter, I describe the steps to create and configure the Service Principal. The following image shows the configuration to authenticate using a Service Principal. The following image shows the parameter for authenticating the service principal for accessing the REST API: ![[Power Automate Refresh - HTTP Action - Authentication.png]] ### Creating and using a Service Principal A Service Principal is a "user" with permission to access resources (like an API). To be precise, a service principal is not a living person. Instead, it is used to automate processes. In the on-premises world, a service principal can be considered a technical or service account. Depending on your role in your organization, you might not be able to create a Service Principal or configure the service principal to use Power BI Rest APIs. Suppose you lack permission (most likely due to the concept of "Segregation of Duties"). In that case, you must contact an Entra ID (fka Azure Active Directory) administrator and your Power BI/Microsoft Fabric administrator. To create and configure a service principal, follow the below steps (see this document for steps 1-3: https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal?tabs=azure-portal). 1. Create an Entra ID Security Group (the Entra ID admin or a colleague with appropriate permissions) 2. Create the Service Principal, registering an app inside Entra ID (the Entra ID admin or a colleague with appropriate permissions) **Please be aware** that it's unnecessary to grant API permissions because they will be granted to the service principal (the security group) via the Fabric tenant settings. 3. Add the security group to the "Service Principals can use Fabric APIs" tenant setting in the Fabrc/Power BI Admin Portal **Please be aware** that this setting can take some time to become effective. This is important for your testing. I experienced a delay of 1 to 30 minutes. 4. Add the security group to the workspace that hosts the semantic model and assign the security group to the Member role inside the workspace. **Please be aware** that this setting can take some time to become effective. This is important for your testing. I experienced a delay of 1 to 30 minutes. ## The not-so-simple flow and, of course, the better option Power Automate provides an action that allows us to use a connector to Azure Key Vault to retrieve the secret of a service principal; the following image shows the flow I use in production (only a little simplified): ![[Power Automate Refresh - now using Azure Key Vault.png]] The next image shows the configuration of the action: ![[Power Automate Refresh - KeyVaultParameters.png]] This image shows the parameters of the Key Vault connection: ![[Power Automate Refresh - Key Vault Connection.png]] Until now, this is straightforward, but the Azure Key Vault action must enable a very important setting. This setting is the "Secure Output" setting. Otherwise, the secret of the service principal will appear in the execution logs of the flow. For this reason, do not forget to turn it on: ![[Power Automate Refresh - Security Secure Outputs.png]] ## Detailed architecture - simplified The following image shows the architecture of the solution: ![[Power Automate Refresh - architecture simplified.png]] I created a dataflow for each table to schedule a load for each dataflow independently. Of course, I also created a Power Automate flow for each dataflow listening to the "When a dataflow refresh completes" event. There are 18 tables in my world, and the smallest table contains ~2M rows. Separating the load of the tables into dedicated dataflows allows for a fine-grained load/refresh schedule. # Flow errors and their meaning While developing/testing the Power Automate flow, you might encounter two errors: one is related to the fact that the service principal is not authorized to call/execute the REST API, and the other is because the service principal has no access to the semantic model. ## Unauthorized API Call The following image shows the error when the security group is not added to the Fabric tenant setting: ![[Power Automate Refresh - Flow Errors - Unauthorized.png]] Inspecting the HTTP action, there is this error info in the Output section of the invocation of the flow: ![[Power Automate Refresh - Flow Errors - Unauthorized error info.png]] ## No access to the semantic model If the security group has not been added to the workspace with the "Member" role assigned, we see this error: ![[Pasted image 20250102005658.png]] Inspecting the HTTP action, there is this error info in the Output section of the invocation of the flow: ![[Pasted image 20250102005952.png]] This information indicates that the service principal does not find the semantic model, meaning the service principal has no access to the semantic model. I like that the service principal must be added to the workspace, meaning an outsider can not perform a refresh (basically, this is a write operation). # A final word Currently, it's not possible to use Azure Key Vault when authenticating for the Enhanced Refresh API. Instead, the secret of the service principal is stored inside the Power Automate flow when configuring the HTTP action. For this reason, the number of users who can access the Power Automate flow must be limited. I recommend using a dedicated service principal for each semantic model (at least for each workspace). # Resources + Create a Service Principal to access Microsoft Fabric and Power BI REST APIs: https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal?tabs=azure-portal + The Enhanced Refresh API: https://learn.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh + General information about the semantic model refresh and its limitations: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data Thank you for reading!