How to Setup Multiple On-Prem Databases to Then Bring Data into Onelake

[Nicole93west]

Hi there,

I have just enabled the Fabric trial for my organisation and I am attempting to bring our clients data into Onelake to report off.

I am very new to all this, hence looking for some guidance.

This client has three sites (NZ,AU,US) and we support their on-prem ERP software.

This software uses a Firebird database, therefore I am using the ODBC connector on each server.

My questions are:

1. Do I need to RDP into each server, open Fabric through a browser and bring in the tables I need to Onelake via ODBC & connect the on-prem Gateway for LIVE refresh?

2. Once i've done this, can I then access all the data from my local PC within Powerbi desktop to create their reports?

3. Why does the OBDC connector bring in the data initally, then the on-prem application allow it to refresh live? What is the difference between these applications?

I made this diagram just to get my head around it, can anyone tell me is this correct?

If anyone has extra advice around this, it would be so greatly appreciated! Thank you!

[Anonymous · Accepted solution]

Hi @Nicole93west ,

1. Connecting to On-Prem Databases via ODBC & On-Premises Data Gateway:
You do not necessarily need to RDP into each server to open Fabric through a browser for connecting your databases via ODBC. Instead, you should focus on setting up the ODBC connectors on each server where the Firebird databases are hosted. After setting up the ODBC connectors, you'll use the On-Premises Data Gateway to facilitate the connection between your on-premises data and Microsoft Fabric/Power BI. The gateway acts as a bridge, securely transferring data for live refreshes and queries without the need for direct RDP into each server.

2. Accessing Data from Power BI Desktop:
Once you have established the connections through the On-Premises Data Gateway, you can indeed access all the data from your local PC within Power BI Desktop to create reports. You'll be connecting to the data sources via the gateway in Power BI Desktop, which allows you to create and publish reports based on the live or refreshed data from your on-premises databases.

3. Understanding ODBC Connector and On-Premises Data Gateway:
The ODBC connector is used to establish a connection between your on-premises databases (in this case, Firebird databases) and Power BI or Microsoft Fabric. It's the initial step to ensure that Power BI can communicate with your databases.
The On-Premises Data Gateway facilitates ongoing data refreshes and live queries. It's a bridge that securely transmits queries from Power BI to your databases and returns the results. The difference lies in their roles: the ODBC connector sets up the initial connection, while the gateway maintains a secure, efficient path for live data access and refreshes.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.