Power bi DB server setup as alternative for Azure SQL server

[MAAbdullah_47]

Dear Experts

I need to setup a power bi configuration for generating dashboard report on power bi services in environment similar to (azure sql server) , please review the attached Image with the thread , I need to do this setup on the cloud using (open source) repositories (Not Azure). The Infrastructure and processes as the following:

1-Extract the data from API as (JSON) format.

2-Create a script e.g.(Python) that convert the JSON format into Tabular (Row & column) format the script will do the cleansing part of the data.

3-The script on (2) will Insert/Update the data into (Mysql or Postgressql) DB.

4-We need to put the Power Bi (PBi) desktop application in a server (cloud).

5-This server on (4) have credentials on (PBi) services to refresh the data daily and setup this through Enterprize Gateway.

6-The User will be able to read the updated data from (PBi) cloud services.

I need the Ideal setup to do the above Infrastructure as an alternative of (Azure Sql server) , taking into consideration the following:

• The host(s) should be in the cloud.
• For The power bi desktop application (4) is it better to be on the same host of the DB (3) or in a separated host? which is more better? explain please.
• The alternative solution should be less cost than Azure by at least 40-50%.
  Waiting your advises.

[lbendlin]

You are missing the gateway component.  Since your data is in a private cloud you need to have gateways that can broker between your data sources and the Power BI Azure Service.  These gateway need to be able to see your private cloud data sources, so they will most likely also have to reside in that cloud.

[MAAbdullah_47]

Hi @lbendlin  you are right I explained this on (5) but not included on the diagram , what I'm asking here: 

1-Shall I put the power bi Application for the refreshing purpose on the same host of the DB e.g.: MySql ? Or separated in another host? 
2-For cost wise , Where can I find providers other than (MS Azure SQL server) who can satisfy the requirement on the diagram? 

[lbendlin · Accepted solution]

1. The recommendation is to keep the gateway VMs separate from the database servers, but as close as possible. That way you get best CPU and low latency.

[MAAbdullah_47]

Hi @lbendlin for Q2 ?

[lbendlin]

I have no experience on that part.