You can try Helical Insight — it's a powerful open source BI tool that checks all your boxes:

great visualizations, report generation, API access, cross-database connectivity, and it can be hosted on any server. Worth exploring!