Openup data for incoming API calls

[Johan]

Hi,

I've got this requirement: Salesforce needs to retrieve customer (dimCustomer) data from the datawarehouse. 

Current architecture:

- on prem sql server for the datawarehouse

- gateway 

- semantic model in PowerBI

What are my options:

- Send data to warehouse in Fabric? Might be expensive for just 1 usecase

- Send data to azure sql? Can azure SQL be an api endpoint?

- Put data into a PowerBI dataflow Gen1 and use PowerBI api. Not sure if that's possible, but it would not need new architecture components.

- Something with Power automate?

Looking forward to your suggestions.

Thanks,

Johan

[Poojara_D12 · Accepted solution]

Hi @Johan 

1. Send data to Fabric: A modern but potentially expensive solution for just one use case.
2. Send data to Azure SQL: Azure SQL can act as an API endpoint, but you’d need to build a custom API layer.
3. Power BI Dataflow Gen1 and Power BI API: Power BI’s API is more for reporting, not for exposing data to other systems like Salesforce.
4. Power Automate: Simple to set up, ideal for moving data to Salesforce without needing new components, but may have limitations for large or complex data.

For simplicity, try Power Automate. For scalability, consider Azure SQL with an API.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS