Power automate to Fabric?

Stephanie Osborne

Has anyone used power automate with Fabric or know if it’s possible to export data from a flow into fabric? What I’m ultimately trying to do is export data using query api and schedule it to update into our data warehouse. We currently use a data pipeline in fabric (using the queue in RE) to upload data into our data warehouse which I then query from to create powerBI reports. However, I haven’t been able to use fabric with query api directly and was looking into adding power automate as a first step in pulling the data from RE as a flow. The queue process works but isn’t consistent and I’m looking to pull additional data and on a more frequent schedule.

Rebecca Sundquist

@Stephanie Osborne, the challenge in Fabric is securing your authentication. The certified Power Automate connectors do this for you, so it makes sense that you want to execute a flow that is already performing the Query API actions. @Alex Wong has shown how to call a PA flow from Power BI: Sharing my Power Automate and Power BI template: Power BI use of Query API via Power Automate. The same concept works as a Copy Data action in a Fabric data pipeline using a Web v2 connection. In the Copy Data step, you can choose a Fabric Lakehouse or Warehouse table as the destination. However, Alex noted the lack of authentication from the Power BI user to the Power Automate HTTP trigger. Since the PA HTTP trigger here is “Anonymous,” Alex's flow adds a check against an authorization code stored in SharePoint. In Fabric you can use a Lookup activity to access the auth code from a SharePoint list before the Copy Data step. This adds some security by verifying that the user running the Fabric pipeline has authorization to access the code referenced in the HTTP trigger. However, the auth code is still passed in plain text as a URL parameter, so be sure to secure inputs and outputs in each step of the data pipeline.

At bbdevdays, Blackbaud's partners Esgenio and IBW Solutions presented a Fabric data pipeline to execute Query API, monitor status, and then save results to Fabric. However, the example does not demonstrate how to secure your client_secret or subscription_key, aside from storing them in a Fabric Warehouse table. More investigation is needed. In the mean-time, it is possible to use the Esgenio example with client_secret and subscription_key stored as SecureString parameters. This means copy/pasting these secure values every time you run the data pipeline, which is inconvenient. The Esgenio presentation at bbdevdays was Modernizing Blackbaud Financial Edge NXT® Data Workflows: Automating Analytics with Fabric and SKY API®.

Alex Wong

@Rebecca Sundquist
Secure Input/Output is good, but isn't exactly needed.

Only Power Automate environment admin or those that has been given access to the specific power automate flow will have access to the run history that display the “key” for access. For better security, this “key” can be automated via Power Automate to change to random alphanumeric every X time (every 1 day, every 1 hour, etc) to improve security.

Stephanie Osborne

@Rebecca Sundquist Thank you for the info!!