Hi @SachaG it seems you’re dealing with two related challenges: PostgreSQL session limits and query errors in Power BI. Let’s break this down into actionable steps to address both issues:
1. Fixing PostgreSQL Session Limits
The error "53300: Exceeded session limit of 10" means PostgreSQL is running out of available sessions. Power BI may be opening too many concurrent connections during the data refresh process. Here’s how you can manage this:
Adjust Parallel Loading in Power BI:
- Go to File > Options and Settings > Options.
- Under Data Load, disable Enable parallel loading of tables and set the number of simultaneous evaluations to 1 or 2.
Optimize Queries:
If your queries involve large datasets or complex transformations, they might be overloading the system. To optimize:
- Reduce the number of queries sent to the database.
- Ensure transformations in Power Query are “foldable,” so computations happen in PostgreSQL instead of Power BI.
Increase PostgreSQL Session Limits (If Possible):
Work with your database administrator to review and adjust the session limit configuration on PostgreSQL. Temporarily increasing this limit can help identify if the problem stems from Power BI’s connections or the database itself.
2. Resolving "Union" Casting Errors
The com.facebook.presto.sql.tree.Union error indicates issues with how Power BI processes query logic when appending tables.
Using Native SQL Queries (UNION ALL):
You mentioned trying UNION ALL directly in PostgreSQL. Double-check the query format to ensure compatibility with PostgreSQL. Test it in a tool like pgAdmin.
Example:
SELECT * FROM table1 UNION ALL SELECT * FROM table2;
If this query works in PostgreSQL, replace Power Query's append operation with a native SQL query using this structure.
Simplify Power Query Logic:
If appending tables within Power Query is necessary:
- Break the append operation into smaller, incremental steps.
- Load and validate intermediate results to pinpoint where the issue arises.
- Verify that column names and data types are consistent across all tables.
3. Monitoring and Debugging the Refresh Process
Power BI has built-in tools to analyze refresh issues:
- Go to View > Performance Analyzer in Power BI Desktop.
- Start a refresh and monitor the queries sent to PostgreSQL.
This can help you identify whether the bottleneck lies with the append operation, session limits, or another source.
4. Exploring Alternative Solutions
If these steps don’t resolve your issue, consider using a third-party tool like Windsor.ai. It provides seamless PostgreSQL integration and can simplify how data flows into Power BI.
Benefits of Windsor.ai:
- Efficiently manages sessions and queries to reduce database load.
- Allows direct integration with Power BI, bypassing some Power Query limitations.
- Handles large datasets and complex transformations more effectively.
Hope these suggestions help!
