This may be an unpopular opinion, but if you have US$70/mo to spare, it's hard to beat Tableau for this exact use case.
"Connect to an arbitrary database, create a view that joins numerous tables (including foreign tables, via blending) together, load to columnar storage on a local SSD for performance if necessary, add arbitrary derived columns (including well-defined lateral lookups for things like 'annotate this action with the date of the first action of this action's user' [0]), group by 4 of the derived columns, map two of the groupings to nested dimensions along the horizontal axis and two to the vertical axis, and show the sum or count at each cell in a resulting table, then when satisfied, drill down into a slice and turn it into a bar chart with colors that match your branding needs" - every one of those clauses can be accomplished with drag-and-drop mouse commands almost at the speed of thought.
And once you get the hang of it, there's zero impedance mismatch with hand-rolled SQL, it's just way faster to iterate on, especially with schemas where you may not remember all the columns available to you, and especially when you're doing so over screenshare with non-technical colleagues.
Tableau is my go-to too. People think of Tableau as a dashboarding tool (and it is), but it's actually a multidimensional exploratory data analysis tool. You can visualize more than 3 dimensions by way of colors, labels, sizes, etc. You can slice and dice your data and visualize it in so many different ways, and also do drill-downs and filters and aggregations in different ways. The downside is that you have to really understand SQL-like operations (GROUP BY, PARTITION BY, PIVOT) to truly take advantage of its power. Many Tableau users only scratch the surface of what Tableau can do, but there's a lot more underneath.
PowerBI on the other hand is a straight up dashboarding tool. I've used it and it isn't quite as powerful as Tableau, but it's an easier step up from Excel. It doesn't require you reorient your mindset as much as Tableau does. But it also doesn't let you probe your data as easily as Tableau does. It's essentially a supercharged Excel dashboarding tool
Excel viz is rudimentary. It gets the job done for simple plots, but it's a hassle to join data (you have to do cell VLOOKUPs or INDEX(MATCH())) and pivot tables are a poor-man's approximation of the true power of SQL operations. It doesn't scale to large datasets but the cell-based spreadsheet paradigm (vs. a relational database paradigm) is easy to understand which has an appeal of its own. But you're ultimately limited to what fits on a spreadsheet.
Tableau is a webapp version of basically SAS JMP, especially the plotting engine. So easy to create plots - literally 100x faster than matplotlib. I can drag things faster than I can type.
I'm working on something in between a BI tool and a SQL IDE. It's definitely code-heavy on the user at the moment compared to a BI tool but an improvement on switching between Python scripts, Postman, a SQL GUI and Excel.
In spirit, but with no UI, vega js, they follow the same “grammar of graphics” idea from the same research. Altair does vega charting but in Python. Kibana is using vega in more UI fashion but I haven’t tested it. I think someone should put a proper web ui on top of vega…
I have used Vega + Jupyter a few times. As well as the Vega Viewer extension for VS Code. I haven't used it enough to be proficient and fast, but I have used it enough to know that I want to be.
I second pandas, and would also highlight seaborn, plotly, and dash as complimentary data visualization libraries.
Tableau is fine for what it is, but I've found that the requests from stakeholders often grow to a point where you either can't do it in Tableau or have to move mountains to get it to work... so, in essence, sunk cost fallacy makes tableau millions.
PSA: If you haven't already learned pandas, learn dplyr/ggplot2 instead. Yes, R is a pretty clunky language (but it's closer to lisp than pretty much anything else as popular) but ggplot2 and dplyr are 100% the best currently available way to visualise tables in SQL.
I would second the decision to use ggplot2 / dplyr, and would also add data.table to the mix. That combination has been invaluable for me, allowing me to visualize all of my structured data.
I started with R, but then switched to Python because all pipelines were already written in Python (web-scrapers, some data-processing scripts, REST APIs), so I just learned pandas and it's been fine, although I do think dplyr's syntax is great and I prefer it to pandas'.
Tableau is great, but that initial fee, while being peanuts in terms of absolute IT spend, has been a blocker getting it rolled out in one large BI team I worked with.
The backstory was a large BI team having no proper tools for reporting, except maybe Looker for tabular KPI reports. So any addition of Tableau was an all our nothing buy, every single analyst or whoever on the BI team needed a seat, even though most of them wouldn't ever use it. For that matter 95 percent of the BI team would never move away from Excel. Anyway, the costs were huge for something that should have been a leap in capabilities.
My go-to has been Qlik Sense. I have enjoyed using Qlik Sense. Tableau is good also. Compared to Tableau, I’ve found Qlik to be faster and more responsive to slicing and dicing data.
Anything solved by tableau is as easily solved by Excel, which likewise supports direct SQL connections. The template graphs & incorporation of function logic, for versatility & business availability make it no contest. Though Tableau has some gorgeous templates, it doesn't also have DAX & PowerBI.
I'm curious why do you think of DAX as a virtue. My poor SQL-shaped peg brain has never really fit the DAX hole of MS software.
Also, it always struck me as something too complex for the non-technical folks, and not expressive enough for tech-literate analysts/data engineers &c.
Just options. VBA is there as well. Excel's virtue is not specializing in any specific task, but being versatile enough to express a multitude of business solutions. 'Excel is my database' wasn't always a punchline.
That's more than an equally domain specific process like Qlik, and more than a specific vendor tool like tableau. And anyway if PowerBI didn't have a pain point it wouldn't be a MS product.
"Connect to an arbitrary database, create a view that joins numerous tables (including foreign tables, via blending) together, load to columnar storage on a local SSD for performance if necessary, add arbitrary derived columns (including well-defined lateral lookups for things like 'annotate this action with the date of the first action of this action's user' [0]), group by 4 of the derived columns, map two of the groupings to nested dimensions along the horizontal axis and two to the vertical axis, and show the sum or count at each cell in a resulting table, then when satisfied, drill down into a slice and turn it into a bar chart with colors that match your branding needs" - every one of those clauses can be accomplished with drag-and-drop mouse commands almost at the speed of thought.
And once you get the hang of it, there's zero impedance mismatch with hand-rolled SQL, it's just way faster to iterate on, especially with schemas where you may not remember all the columns available to you, and especially when you're doing so over screenshare with non-technical colleagues.
[0] https://help.tableau.com/current/pro/desktop/en-us/calculati...