Preferably I’d just like to click on a MariaDB table and receive some plots and statistics on the columns.
Whats your experience on this?
Edit: to clarify, I don’t want to visualize the database itself (Schema’s, keys etc). Just the data within it.
"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...
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 really amazing actually.
PowerBI?
Apache Superset? https://superset.apache.org
On the other hand, Redash has served my startup really well for the first 3 years (and now we're finally moving to Tableau)
Pandas is better but requires programming.
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.
This is a popular opinion, in my book.
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.
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.
Paid: If you have the budget, Aqua Data Studio gives you the database management functionality AND all of the visualizations you'll find in Tableau in the 1 product.
(My company shifted from Tableau to Power BI. At first it seemed like a beta product with lower fidelity. But Microsoft has made the whole power suite into a force to be reckoned with... highly recommended)
I can see hundreds of logins to my database per user, and when I cut the logins per userid to 5, their applications collapse. Their queries cannot be tuned to available indexes according to my users.
They remain the very first ones that I throw off a database if there is a performance problem, with some degree of prejudice.
Whether that's a viable answer in any given situation is of course highly variable, but even if the analytics queries are my own hand-rolled SQL it's still my preference any time it -is- viable if nothing else so I don't have to worry as much about screwing up and taking too many locks / using too few indices while I'm iterating on the query in question.
Under the covers Power BI is running a tabular Analysis Services Cube, so import mode will be optimized for reporting regardless of the source database indexing.
The PBI dataset can be shared across users, so only 1 connection is required to update it, instead of dozens of users hitting the db directly.
Also as another person mentioned, reporting is usually done on an separate database to production applications like an operational data store, data lake, or warehouse.
The desktop version is free, no strings attached. However the value comes from publishing to the web service for sharing etc. That's not free. It can be cheap, but when you have a lot of users the "premium capacity" can be quite expensive.
the visual builder still relies on knowing fundamentally what a db is, what a join is. like mega obvious stuff that actually isn't obvious to non technical people.
we area also multi tenent and it's not the best for that. like the default permissions when you add a db are broad, the organization of dashboards, questions, etc is not great. through different versions they have re organized and stuffed things oddly.
i've also messed up the elastic beanstalk a few times. i am not even close to an expert on aws so might just be me being stupid.
probably worth just paying for their service in the end..
There are maybe 1/10-1/5 or maybe more who will be enabled by them on a technical level. The same kind of people who will build a site out of WP themes and plugins or build quite involved spreadsheets and so on.
I think it’s generally important and useful to enable and respect power users like that. They occupy a unique niche.
Also, "everyone nervous" is an interesting choice of words - I'm pretty sure most people wouldn't really care one way or the other. What is it about AGPL that causes stress among the masses?
We're trying to re-imagine popular SQL clients (phpMyAdmin, MySQL Workbench) to have the design and software quality of modern tools like Superhuman, Linear, etc. We make it very easy to query and create charts from your database then share it with your team.
We currently support MySQL, Postgres, PlanetScale, Yugabyte, and in the next couple of weeks SQLite and ClickHouse.
Use Excel, QlikSense and Tableau if Business Users need visualization. Excel pivot tables = OG data reshaping. Resist more complicated solutions : do you really need more than Excel?
QlikSense doesn't get enough love. It's actually better than Tableau at some scenarios. Or PowerBI if you're a Microsoft shop. Last time I checked, Power BI's Q&A [2] was a KILLER FEATURE. "Show me Sales per Region, Quarterly" and then you get to fine tune it. R and Shiny dashboards = last resort; too much bespoke work. 2 months using R + Shiny can be 1 week in Tableau / QlikSense / PowerBI.
1. "Efficient reshaping using data.tables" https://cran.r-project.org/web/packages/data.table/vignettes...
2. https://docs.microsoft.com/en-us/power-bi/natural-language/q...
Disclaimer: I'm one of the co-founders of Veezoo.
Also, the UI looked half baked, but I'm sure they have fixed that by now
Create a read replica, deploy metabase, connect it, start building visualizations. Or use the X-ray auto analysis feature to generate a bunch of mostly useful visualizations with a single click.
If you're using Microsoft, you can use MS Access for data entry to similar ODBC and SQL Server backends. If you want to do some VBA programming you can set up a UI in an Excel workbook too.
Disclosure: I am the Spectral Core CEO (and author of Full Convert).
Otherwise, there are many ETL solutions.
I just settled on DBeaver, but don't consider that an endorsement from my part.
I found DB Browser for SQLite to be the least bad, but it's obviously limited to SQLite.
My problem may come from the fact I have simple needs and they're all very complex apps. My SQL queries are rarely longer than 50 lines and I do DB admin tasks from the command line.
Among the unending list of apps I should code for myself there's a SQLPad project. Maybe one day.
It's simple and open source
Gephi lets me show how this kind of table bloat happens over time and helps explain performance degradation.
https://blog.miz.space/tutorial/2020/01/05/gephi-tutorial-la...
For SQLite databases, I use sqlitebrowser.
Both tools are open source.
Looking at recently merged pull requests that were less than 120 days old, apache/superset had 89 unique authors, which is very high, as the following shows:
https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...
Metabase had 37 authors, which is also quite high for an opensource project, which the following shows:
https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...
And redash has 11, but most of the contributions were more than 28 days ago, and is significantly less active than metabase and superset as the following shows:
https://oss.gitsense.com/insights/github?q=pull-age%3A%3C%3D...
I was actually quite surprised by superset, as I never heard of them before, but they are backed by serious investment (https://preset.io/about/), which clearly shows in how active their repository is.
They gave their SaaS subscribers a bunch of notice that it was being wound up, and some Redash Community members have picked up some of the load for hosting those customers.
Development of the main Redash repo though... has an uncertain future. There are people (Community members) improving things, and they seem to be getting shepherded/directly decently well by Jesse (was full time Redash employee).
So, things are ticking along. It might go really well and grow over time, or it might not. Not yet sure. Hopefully it does go well though. :)
or its commercialized offer: Preset (Cloud)
Which includes free version for five users. But it is SaaS so your database has to be visible from the internet.
I really like it though.
Can somebody please explain why they consider this to've been a terrible answer?
Just guesses.
This actually works pretty well for small (<1GB) databases, where you can run a cron periodically to build the SQLite version.
Then you can visualize with plugins such as https://datasette.io/plugins/datasette-cluster-map or https://datasette.io/plugins/datasette-vega
I also often load data into Datasette and then do custom visualizations in Observable Notebooks by fetching data back out through the Datasette JSON API - here's an example notebook that does that, using the Observable Plot charting library: https://observablehq.com/@simonw/datasette-downloads-per-day...
There used to be a lot of good candidates in this space even just a few years ago but Power BI has improved it's product and integrations very rapidly and with its affordability has displaced them at many big companies. Power BI also recently added some NLP capabilities from one of Microsoft's acquisitions which makes usage by non technical users easier.
If you're willing to put your data on BigQuery, then Google Data Studio / Looker is an even better solution for larger datasets due to the seamless integration and intelligent caching which (purely in my perception) seems to work better than Azure Analytics Services in the Microsoft side. Also BigQueryML works within SQL.
Source: i lead an Analytics and Data Science team at a Fortune 50
Moreover, if you plan to adopt and build upon one of these tools that infers and generates the models as well as provide the explore and visualization functionality, you might be painting yourself into a corner and forcing all current and future workloads to use this layer. Otherwise you'll be having to reinterpret and reimplement your models all over the place; one off SQL scripts/reports, web analytics, dashboards/visualizations/reports on other analytics tools. Then you'll also end up having to scale this tool up in both compute and storage to handle the load that grows over time. This can end up being quite costly in time, money and responsibility.
While these tools will offer a lot of value providing visibility and insight into your data, it'll probably be worth circling back and seeing if the data can and should be modeled correctly (semantic layer) before hitching your wagon to your first choice.
Once your data is all modeled, it might be worth re-evaluating all the tools that you started with and see how they manage now that your house is a little more in order.
Remember, your modeling doesn't have to be done by the same tool that does your exploration and visualization.
This is a great article related to these ideas: https://benn.substack.com/p/is-bi-dead
PopSql - https://popsql.com
Trevor - https://trevor.io
This is very much a Java application, and appears to allow several JDBC drivers for 3rd party databases.
It's free, and is designed to compete with (or drag underwater) Quest Software's Toad.
https://www.oracle.com/database/technologies/appdev/sqldevel...
3rd party drivers:
https://www.oracle.com/database/technologies/appdev/sqldev/t...
Metabase or Apache Superset, as others have mentioned, can be deployed on-prem so it's a bit more isolated/secure.
It's based on Observable (https://www.observablehq.com), which has a nice Summary table feature, sounds like what you need (https://observablehq.com/@observablehq/summary-table)
See https://www.kaggle.com/rhuebner/human-resources-data-set. I think it's a great view on top of a datatable.
It works with SQL databases and CSV file. Since it's an offline desktop app, it's free to use and you don't need to set up a server or upload data.
Give it a go at https://www.dronahq.com
The most effective, efficient data exploration tool I've ever used. I'm a data scientist, but I use this before I write so much as a line of code.
I've tried google data studio, superset, but what I need is a integrated control where user's can filter the report based on their location. Or alternatively which would make it easiest for me to develop this control myself.
Credentials to admin panel in one click.
https://github.com/thenaturalist/awesome-business-intelligen...
From this I picked Metabase and found it to be pretty good.
