I have a workflow that comes in every week or so, which would be someone high-ranking asking for such and such data, by this and that range of months, with this and that kind of attributes.
We have 3 SQL database (2 PostgreSQL, 1 old Oracle), and usually this involves me opening up my SQL program, writing out the query by hand, and then exporting it.
Sometimes the demand would involve a lot of tables that are scattered.
I have DataGrip which helps me, but I want something better.
I know in my head how it all works, but I would like to know if there is something on the market that lets me:
- Assign meaning to all the relationships of tables;
- Query visually by telling it (e.g.) "all drugs dispensed" after that it knows what "all drugs" means and "dispensed" means; and
- Quickly export to Google Drive or similar share-able avenues.
I can of course write SQLs, but after a while it gets very tedious. I have so many views/functions that I built up to be ready for all the demands but sometimes it gets counter-intuitive.
Do you know of a tool that helps me with this? I've tried these following tools but they seem to pitch for different business use-cases (most seem to pitch for business people who want charts, I don't want charts, I just want my Excel files).
- Google Data Studio.
- Microsoft PowerBI.
- Mode.
- Klipfolio.
I’m not sure what kind of tools you could bolt onto SQL databases to make them more useful in this kind of context.
You might be better off exporting all your data on a regular basis to real DW databases and then using their tooling.
I’m sorry that I don’t have any better answers for you.
so we built structure to solve this - it's a sql editor like datagrip but it allows you to build datasets that you can reuse for new analysis. so, in your case you define your relationships once with SQL, in this case 'all drugs', and then you can build a model 'all drugs dispensed' that reuses the 'all drugs' dataset... and the views / tables are linked and organized so you dont have to recreate them every time.
https://www.youtube.com/watch?v=V1tmeFgWMLs
it doesn't solve the problem of your data being scattered across multiple databases (you can send your data to a data warehouse with tools like fivetran), or an export to google drive - but it does solve the hard problem of keeping your analytics organized in a sane way so you dont have to rebuild the same sql queries over and over again, or have a bunch of various SQL definitions of the same thing.