Enable Cross-Schema Queries in EMX2 Reports

[Fernananas]

Issue

Currently, EMX2's reports functionality allows querying only within the same database/schema. It would be highly beneficial to extend this capability to allow querying across different schemas.

Use Case
In many cases, relevant data is spread across multiple schemas, and having the ability to reference and join data from other schemas would greatly enhance reporting flexibility. This would enable:

• More comprehensive reports combining data from multiple sources.
• Improved data analysis without needing to duplicate data across schemas.
• Better integration with complex data structures where related information is stored separately.

Solution

Proposed Solution

• Add support for cross-schema queries within the EMX2 reports feature.
• Provide a way to specify schema references in queries.
• Ensure necessary permissions and security considerations are handled.

Potential Challenges

• Schema access control and security.
• Performance considerations when querying large datasets across schemas.

Alternatives

Since EMX2 reports currently do not support cross-schema queries, I handle this by running a Python script overnight to gather and aggregate data from multiple schemas.

Challenges with This Approach

• Latency: The reports are not real-time since they rely on overnight processing.
• Maintenance: The script needs to be updated if schema structures change.
• Complexity: Additional scripting and scheduling add an extra layer of management.

Additional context

No response