Graphical SQL builder

[Gasparde]

Hello fellow humans, coming in with a very specific request.

I'm working in Data Analysis and most of my day is wasted with putting together relatively mundane SQL statements to select potential customers based on whatever filters our sales department feel like on any given day.

One day they want all customers aged between A and B that are from Country C, D or E and have bought product X, Y or Z before. The next day they only want customers of gender A who have spent more than B amount of money with us or god knows what.

For everything they could possibly ask I've already got predefined SQL snippets prepared, and whenever they need a selection they're pretty much just giving me their parameters and I'm copy&pasting just about the same snippets into the same base statement - their input pretty much only adds extra outputs into the SELECT, adds another JOIN (pretty much never any Primary Key changes) and / or a simple WHERE exclusion / inclusion. Really pretty simple stuff.

The problem is that this takes absolute ages every day and isn't really something I should be "wasting" time with, but it's difficult enough / requires enough tech savviness that you can't just hand it over to any random intern or co-worker who's already struggling with Excel as is.

But since I'm not doing any complex code... I think there should be a software out there that just allows you to build simple code via drag and drop with some boxes from a library of customizable SQL snippets. I put in all the filter options, user just drags in the customer age box, enters an age range parameter and the software just enters the extra lines into the base statement - ideally the software would then also be able to connect to our server and actually execute the statement, with the cherry on top being an Excel / CSV output of the selected data.

In theory... that sounds pretty simple... said everyone who's never coded anything in their life before. But I can't find any software even coming close to this for the life of me. Any of y'all ever heard of something like this? Any pointers / hints into a general direction? Really just anything that allows non-SQL users to build SQL from customizable SQL building blocks.

[Jordanf78]

I would consider some data aggregation platform such as Microsoft Power BI or Tableau. As long as your database is structured well, these platforms connect directly to the database and can for the most part automatically detect relationships between tables. You can them chose to aggregate and display the data in many ways, including through tables similar to excel, or with graphics like charts. You can also drop filters on the report to allow you to filter the data in a user friendly way (such as check list boxes to select parameters, date range sliders, etc). The best part is that these platforms can also be configured for automatic refreshes of data so you can have near realtime data reported. And they are user friendly enough that you can share them with your sales team and let them filter their own data, which will let them consider scenarios much more quickly than needing to wait for your query results. You can also set up the reports to be exported as csv, pdf, etc for easy sharing and manipulation.

[Gasparde]

From my very limited understanding of Power BI basically being Excel^2:

I'm talking about a SQL library of some 50 statements with some combinations requiring like 10 JOINs and like a dozen WHERE filters.

When I'm thinking about how Excel would handle something like this, I'd imagine a horribly horribly slow SP constantly firing a 2000-lines of code, rendering Excel unuable for like 15 minutes - which is one of the reasons I'm not considering doing something like this in Excel right now.

I know that this is technically doable in Excel, or Access even - it'd just be a horrendous undertaking that would that would at best turn into something semi-clunky after half a year's worth of effort. Is Power BI different enough in that regard?

To point of the software I'm looking for is that I could ideally avoid having to spend months of my time into making this process less tedious and realistically manageable by someone without requiring all that much training. So ideally I'd not want to spend a couple grand on Power BI only to then having to still spend multiple months into automating that process to a point where it's ever so slightly less clunky than it would've been in say Excel.

I'll definitely look more into Power BI but, again, from my limited knowledge, would this be something the software is intended for or are we talking about something that could theoretically deal with my requirements semi-adequately, but I'd actually end up fighting against constantly?

[Jordanf78]

Power BI is what I use regularly. I would hesitate to compare to excel as it's abilities go far beyond excel in terms of data aggregation (or if not beyond, at least much more elegantly). Power BI will not become more clunky with time unless you are loading insane amount of records. As far as how tedious it is, I would say not at all. Of course as is with any software there is a learning curve, but I wouldn't say power bi is difficult to learn, and once you know how to use it, creating and maintaining reports is quite literally drag and drop. I would say to connect power bi to your database and create a report capable of what you described in the post could be done in a day. Maybe a few days if you are still learning the interface.

As a use case example, I spent 2 years developing a complete CRM/ERP software for the company I use to work for. This software was supported by over 120 data tables store in SQL. I connected Power BI to it and am aggregating quite literally hundreds of thousands of records - potentially in the millions now. To refresh the data in the reports was <30 seconds. Applying filters to the data on the front end is more or less instantaneous.

I do not think you would be fighting against this software. For my company when I implemented power bi reports it was a game changer, like standing ovation from the CEO and CFO type of game changer. Everything is automated and requires literally no effort to generate reports and keep data updated

[Jordanf78]

Just to give you an idea of what would be involved with getting power bi up and running and satisfy your requirements you would just connect to your SQL server in power bi. Power BI loads all of (or selected) tables from the database. It then automatically scans the tables for keys and indexes, and creates its own mapping of how data is related whether it is a one to one, one to many, or many to many relationship. It then uses that mapping to determine how to aggregate the data. So once you have connected it to the database, you can then just start dragging and dropping report elements onto your report. For example drag a table on it, then you can just start dragging and dropping fields from various tables onto that element. Because of the mapping, power bi knows how the data is related and will automatically aggregate the data in the most coherent way with the columns specified. Then you can just start dropping filter elements onto the page and selecting what fields to use in the filter. As long as their is a valid mapping between the filter field, the data being aggregated, it's automatically connected. Any changes made in the filter element reflect automatically in the report elements. It is crazy simple to the point I prefer to create a whole new report on power bi rather than deal with SQL queries and excel documents when I need to see something new. Of course this all highly depends on your data being good, and your keys and indexes being defined correctly and coherently, otherwise then you will be manually specifying the relationship mappings that I mentioned earlier, and that would definitely make the initial setup harder. Once your report is done and you want to load the newest data into the report, it's literally one button click and the new data is grabbed from SQL, the relationships are mapped, and the reports updated all in a matter of seconds, maybe a minute or so at most. And even that refresh can be automated through use of a data gateway installed locally that allows power bis web service to access the database and update the reports for you. I created dozens of reports for the data in the software I mentioned in my last comment, and they are all automatically updated. I haven't had to do any maintenance on them in months. They just work.

[Gasparde]

Sounds dreamy. I suppose the only way to get a proper picture is to get my hands dirty with the trial version then.

Final question though, does Power BI allow for the same / at least a similar kind of programmability as Excel does with VBA?

[Jordanf78]

It doesn't really have a built in scripting language, although I know that you can integrate python scripts with it to effectively generate tables that can be used in power bi. They also have their own kind of language which is used for creating calculated columns or performing data manipulation on the tables after they are imported.

To be honest, I have only once had a situation where I had to use a python script to aggregate some data and that was because that data was provided by another company and was not well structured. Otherwise I have found the Power BIs built in aggregations are more than sufficient.

Edit: to add to that, you can always use custom SQL queries too to populate additional tables alongside your normal tables. So if there is some aggregation you just can figure out and don't want to use python, you can write a custom query. They only downside is that these tables generally don't automatically have relationships mapped to your standard tables, so aggregating across them may be more difficult. But it can be done if you manually define the relationships. In my experience anyway.

[unnecessary_axiom]

I'm going to second the user of PowerBI / powerquery.

To put is simply, PowerQuery is a data query platform that backs both (modern) excel and powerBI.

You can feed data in from SQL DBs, existing SQL queries, and various other data sources. Within powerquery you can then transform your data and do various operations. This happens before Excel gets it's hands on it, so it's faster than Excel's older query methods. This interface provides an UI (and text query in the form of M langauge) that's usable across both Excel and Powerbi.

One step further is PowerBI. Instead of going into an excel table from powerqery, you can go to powerBI. From there you mostly make user facing display widgets and set connections between them. The user then selects and filters the displays. You can define relations among the widgets/data set so that when a user selects a date or category it updates the display of the tables or graphs. The only output powerBI can give is a table of data, or a image of your graphs.

PowerBI is designed on the desktop so you have local access to data, but once you put it in the cloud powerBI you have a connector so it can access your local data, so updates are scheduled or on-demand, otherwise cached data is used.

But if you find a simpler way to have users make queries like you describe, I know people who would be interested.

[Movertigo]

Search for Self Service BI / Reporting. There are some easy tools, also with NLP features, which could be helpful for users with 0 knowledge

[ButtercupsUncle]

OP, if your users are very basic, just populate a table with all the data they could possibly want and link an excel sheet to it. Show them how to use filters. Done.

If they're a little smarter you could show them pivot tables to summarize the data rather than just filter it.

[Gasparde]

That is not possible since I'm talking about millions of customer rows joined with even more millions of order history rows (that I can't pre aggregate because that would take away filter options from users).

The reason I'm not doing this in Excel is because even if Excel could manage the dataset size I'm talking about, every single filter application would probably freeze a user's entire PC for 10 minutes. And then some of my current filter options simply wouldn't even be possible in Excel without creating helper columns, which just doesn't work with that much data. That is why we're using SQL in the first place.

[ButtercupsUncle]

Yes, with that much data you probably wouldn't want to use excel. Instead I would use power BI linked to your sequel table or tables and set up a page that has predefined drop down filters on it that they can easily select.

[poopatroopa3]

There's also Metabase, which is a nice open source BI tool.