Advice Needed: How to Pull Data from Multiple Cloud Platforms Without ODBC Connectors into PowerBI

[sajohnson05]

I recently started as the only data analyst at an HVAC distribution company, and I’m facing a challenge with pulling data from multiple third-party cloud platforms.

Here’s the setup:

• We use an ERP system that has an ODBC connector (I’ll get access to that soon).
• The other two systems are third-party cloud platforms:
  1. A field service software.
  2. A project management tool that tracks bids, quotes, and jobs (specific to HVAC distributors & suppliers).

The problem is that these two platforms don’t have ODBC connectors, and I need to pull data from them for analysis in Power BI.

Has anyone dealt with a similar setup before? What’s the best way to extract and integrate data from these systems into Power BI without direct database access? I’m open to any suggestions or insights on how you’ve tackled this problem.

[audreygerred · Accepted solution]

Hi! One option would be to have the data landed in a lakehouse or datawarehouse then connect to that. Another option would be to use APIs to get the data: How to Pull Data From an Api and Put It in Power BI - Zebra BI

[Anonymous · Accepted solution]

Hi @sajohnson05 

There are third-party tools and services that provide Power BI connectors for various platforms. You might find a suitable connector for your third-party systems.

Power BI allows you to create custom connectors for sources that are not natively supported. This is a more advanced technique and might require some development skills, here are some links may help you:

Custom Data Connector: How to Deploy and Test - Microsoft Fabric Community

Creating your first connector - Hello World - Power Query | Microsoft Learn

Power BI Custom Connector: Connect to Any Data Sources. Hello World! - RADACAD

Connector extensibility in Power BI - Power BI | Microsoft Learn

There are many data sources that provide REST APIs for integration with other data processing software, you can look for the REST APIs provided by your data source, and then write mcode in PowerQuery to connect to the data source.

I have the Power(query): using Power Query to call REST APIs | Burningsuit

Read API Data with Power BI using Power Query (mssqltips.com)

Solved: Re: Using a REST API as a data source - Page 6 - Microsoft Fabric Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande]

• Check if the field service software and project management tool provide RESTful APIs. You can use Power BI's built-in connectors for Web APIs to pull data directly.
• If the platforms allow, export data to CSV or Excel files regularly, then import these files into Power BI. You can automate this process with Power Automate.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[audreygerred · Accepted solution]

Hi! One option would be to have the data landed in a lakehouse or datawarehouse then connect to that. Another option would be to use APIs to get the data: How to Pull Data From an Api and Put It in Power BI - Zebra BI