Power BI

This cat can indeed be skinned in so many ways that even gathering a representative list of keywords and tool names could take years. From my personal experience (especially in settings where you have registered and identifiable users), the nuances are often in the logical desire to tie tracking events into your product and CRM automation. In those contexts, I had rather positive experiences with Segment + (Braze, Mixpanel, all-things-social) + analytics warehouse + your choice of BI tool for fully hosted zero code solutions. That also works well in multi-platform settings. If analytics is all you need you could also consider much cheaper or free (if hosted on-prem) solutions like Matomo and a plethora of similar tools...

Metabase is more user friendly and totally targeted at non technical people. Setup is super easy and it supports all of the most popular datasources.

You can give meaningful names and descriptions to your tables and columns. This helps the end users when they are creating the queries (called "questions" in Metabase). Question creation is all done visually, you can even join tables and do aggregations (group by) without coding any line of SQL, although SQL can be used for more complex cases.

PowerBI works for your use case of connecting to different data sources, but it also requires programming knowledge. It's not an solution you can deploy without a programmer who has experience in SQL (querying your data sources) and data engineering (how to structure data such that the queries execute performantly).

BI tools are only as good as the data they're connected to. If you're already experiencing performance issues, you might want to consider a cloud database like Snowflake.

In my experience, there isn't a whole lot of a difference in PBI vs Tableau, for the vast majority of analysis. Personally, I favor Tableau because you can make really fancy/pretty things, but most business don't require that level. Most of the time I've seen people choose PBI due to cost or their team is already familiar with the platform.

I also recommend checking out Sigma Computing (sigmacomputing.com), they are a BI tool built like a spreadsheet so it's easy for people to learn and understand, and they're specifically built to optimize Snowflake connections.

You need to start using Datawarehouse (DWH) solution and then feed data to tableau. For that you need to setup data pipelines and then transform data in the DWH, move all the custom queries into the DWH. This process is called ELT. You need AWS Redshift or Google Bigquery, a serverless scalable solution, this is the bottle-neck on your current SQL, not meant for processing millions of rows of data. A team of Data engineers can help you to setup these solutions.