Connecting Power BI with Amazon Redshift via the ODBC driver can sometimes lead to unwelcomed connection failures or performance issues. Although credentials and configuration may look fine, usual suspects include driver compatibility, network restrictions, and query performance bottlenecks.
Troubleshooting Steps:
Check ODBC Driver Version-Make sure you are using the current Amazon Redshift ODBC driver, applicable for your system architecture, whether 32-bit or 64-bit.
Verify Connection String & Authentication—Use a DSN-less connection directly from Power BI with the right endpoint, port (default: 5439), and database name. If using IAM authentication, check that the necessary permission(s) are in place.
Query Performance Enhancements—Redshift is inherently columnar in design and mainly optimized for bulk query functions. Avoid SELECT * in your queries and DirectQuery for large datasets, as the import will choke with them.
ODBC Driver Parameter Tuning: For optimal performance with larger datasets, set UseDeclareFetch=True and increase MaxResultRows in case of truncation.
Check Network and Firewall Rules—Make sure the Power BI endpoint can reach the Redshift endpoint and that your VPC security group allows an incoming connection.
If anyone faced similar issues, I would appreciate insight into the working solutions.
