Openup datawarehouse for API endpoint - what's the best way?

[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

[nalinash · Accepted solution]

Hi @Johan

Based on your current architecture and open to use your options. Then best option is - "Power BI Dataflow Gen1". Since you are already having a Power BI Sematic model and gateway then creating a Dataflow is the least intrusive and most cost-effective solution as well as you can expose the data via Power BI REST API which allows Salesforce to retrieve the required dimCustomer data.

Alternatively, you can also go with Azure SQL and API endpoint. But this is having some constraints like if Salesforce needs API-based access for high performance queries or if you plant to scale to more use cases in the future, moving data to Azure SQL and exposing it via an API endpoint then it is a more robust and scalable solution.

For short term and just for 1 Use Case if you want to go then Power Automate is more suitable and quick option. Use Power Automate to push dimCustomer data directly to Salesforce. But it is having limitation that it works well for low-frequence and low-volume updates.

Fabric Data Warehouse is too expensive so never suggest for just 1 Use Case.

Totally, best option has depend on your internal requirement and your current architecture environment.

Best regards,

Ash

If this post helps you then please Accept it as Solution to help other members find it. Appreciate your Kudos!

[v-jingzhan-msft · Accepted solution]

Hi @Johan 

1. Here is another option: Using Data API builder for Azure SQL Databases. 

Data API builder is a product that can help developers turn Azure SQL Databases into a modern REST and GraphQL API instantly. It can work with local SQL Servers or Azure SQL Emulators so that anything can be done offline. It is Open Source and free of charge. 

DAB supports:

• SQL Server
• Azure SQL
• Azure Cosmos DB for NoSQL
• PostgreSQL
• Azure Database for PostgreSQL
• Azure Cosmos DB for PostgreSQL
• MySQL
• Azure Database for MySQL
• Azure SQL Data Warehouse

If you have interest in this tool, you can learn more from the documents , Github repository and samples. 

2. Alternatively, there is another option in Fabric:

If you want to use Fabric items, a similar option in Fabric is to use the Fabric API for GraphQL. This requires that you bring data into a Fabric item first (e.g. Warehouse, SQL database, Lakehouse, .etc). Then expose data through the API for GraphQL. After the API endpoint is ready, you can query data from the API endpoint with GraphQL statements. 

As this requires a Fabric or Fabric Trial capacity, if you only have 1 usecase, it might be not cost-effective.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[nalinash · Accepted solution]

Hi @Johan

Based on your current architecture and open to use your options. Then best option is - "Power BI Dataflow Gen1". Since you are already having a Power BI Sematic model and gateway then creating a Dataflow is the least intrusive and most cost-effective solution as well as you can expose the data via Power BI REST API which allows Salesforce to retrieve the required dimCustomer data.

Alternatively, you can also go with Azure SQL and API endpoint. But this is having some constraints like if Salesforce needs API-based access for high performance queries or if you plant to scale to more use cases in the future, moving data to Azure SQL and exposing it via an API endpoint then it is a more robust and scalable solution.

For short term and just for 1 Use Case if you want to go then Power Automate is more suitable and quick option. Use Power Automate to push dimCustomer data directly to Salesforce. But it is having limitation that it works well for low-frequence and low-volume updates.

Fabric Data Warehouse is too expensive so never suggest for just 1 Use Case.

Totally, best option has depend on your internal requirement and your current architecture environment.

Best regards,

Ash

If this post helps you then please Accept it as Solution to help other members find it. Appreciate your Kudos!