Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
1 year ago
Solved

Report Builder - multiple data sources

I need to create paginated report using report builder based on below data sources. how to do this using report builder?   AWS Aurora PostgreSQL External ODBC MS SQL Excel/CSV 
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi powerbiexpert22,
    Thank you for the follow-up question.

    You are correct. Amazon Aurora PostgreSQL is a cloud data source, and in most scenarios, it can connect directly without a gateway if it is publicly accessible or uses a cloud connector.

    However, since in your case there are security restrictions (for example, private network, VPC, firewall rules) and the Aurora instance is not directly accessible from the Power BI Service, you can still use the same on-premises data gateway you have already set up for MSSQL and Excel.

    The gateway just needs network connectivity to your Aurora PostgreSQL instance (e.g., via a VPN or private link). Install the correct 64-bit ODBC or Npgsql driver for PostgreSQL on the gateway machine. In the Power BI Service, create a new data source connection under that same gateway for Aurora PostgreSQL.

    So, no separate gateway installation is needed one standard mode gateway can manage all three: MSSQL, Excel/CSV, and Aurora PostgreSQL, as long as it is configured with the right drivers and network access.

    Refer these link:
    Power Query PostgreSQL connector - Power Query | Microsoft Learn
    What is an on-premises data gateway? | Microsoft Learn

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

Hi powerbiexpert22,

Thanks for reaching out. Also, thanks to bhanu_gautam, for his inputs on this thread. 

You are trying to build a paginated report in Report Builder using data from AWS Aurora PostgreSQL, an external ODBC MS SQL source, and Excel/CSV files. Here is how you can approach this.

Report Builder supports connecting to multiple data sources, but each dataset must be tied to a single source. So, while you can include multiple datasets in one report, you can't directly join them across sources within the same dataset.

Connect to AWS Aurora PostgreSQL: Use the ODBC data extension in Power BI Report Builder. Make sure you have the 64-bit ODBC driver for Aurora PostgreSQL installed. You will also need to configure the Power BI Gateway to allow cloud access to your Aurora PostgreSQL instance. Power BI gateway and Report Builder support for ODBC data sources - Power BI | Microsoft Learn

Connect to External ODBC MS SQL: Similar to Aurora, use the ODBC data extension. Ensure the System DSN is configured correctly on the gateway machine. Use supported authentication methods like Windows or Basic.

Use Excel/CSV Files: You can embed data directly into your report or use Power Query to connect to Excel/CSV files. For embedded datasets, you can paste data from Excel directly into Report Builder. Enter data directly into a paginated report in Report Builder - Power BI | Microsoft Learn
Connect paginated reports to data sources using Power Query - Power BI | Microsoft Learn

Hope this helps. If you have any doubts regarding this, please feel free to ask here. We will be happy to help.
Thank you for using the Microsoft Community Forum.