Self-Serve Dashboards
briefer.cloud
briefer.cloud
It turned out that it was doing a left join when the intent was an inner join, and the data being shown was an order of magnitude higher than it should have been. This is when I lost all faith in these kinds of abstraction layers on top of SQL targeting people who don't actually know SQL.
Decentralized/embedded Engineers/Scientists; self-service dashboards; low-code BI/data tooling; and, now, LLM-driven text to SQL/viz lipstick on a pig have been floated as some of the solves to the problems seen in the analytics space over the 25 years I've worked in the space. Unfortunately, to date, nothing has actually solved the root issue: lack of data understanding and, its end result, trust in the deliverables.
But, to your specific point, SQL isn't the solve here, either. Too many folks know enough SQL to pull data and use it as they see fit, but too few folks understand the data, its structure/schemas, and valid use of those data. THAT requires time, energy, knowledge, and experience in the space. NO TOOLING, other than experience, solves for this--today (note: will LLMs get to a place where they can? Maybe; but, let's be honest--probably not).
Dashboards are great at giving quick hit information of KPIs and the ability to drill down into them; but, the most important thing to solve are always:
1. Data Management practices
2. Understanding of data, its relationships, and proper use of those data/metrics in deriving insight to drive the business forward.
I am excited to see what the future holds, but my grey beard doesn't allow me to ever, Ever, EVER trust any next-gen tooling being it hasn't held true to date.
I always ask: tell me the question you are answering with this.
99% of people can't answer that question.
I honestly will prefer people make decisions with their guts than with ill-understood data. Instead what I get is people who don't understand the data, its context, its meanings (and what it doesn't mean) trying to lead people down the wrong road while using the data as crutch. It is so frustrating to me.
Intuition of folks with good understanding of the business, especially when their salaries depend on it is often a much much better compass than some rubbish someone is claiming data is saying.
Another term I hate with a passion "data driven". No! data drives nothing. "Data informed" is where its at. You take the data, our best understanding, mix it our understanding of things the data doesn't cover, and use to that inform the best decisions we can make.
I'm aware of a product that uses time-based attendance for education, because not every day, every school, or every campus uses the same timetable quilt and often you have to be flexible (school sports carnivals, relief swaps, joint class activities, or 14-day rolling timetables for example). Doesn't mean there isn't a view that synthesizes the quilt into class-based attendance, or even just AM/PM for those users that think that way.
I've been at big companies where you realize a subtle bug/design causes higher revenue and nobody wants to touch it and be responsible for loss of revenue. Things like the free plan button being just below the fold on average resolutions, calculating state incorrectly that causes discounts to not be applied, someone forgetting to add a "false" that causes people to be prompted to signup when signup isn't technically needed, etc.
I wonder if you felt the same way; like people would blame you for there being lower numbers?
These solutions are targeted at non-technical users with the promise of not having to write code, but those users often get lost or produce erroneous results because they still don't understand the more complicated parts of engineering their solution.
Every time I see an attempt to scale up complex business processes with "no code" tools, it inevitably winds up hitting a wall after a lot of churn, then ultimately having to be handed off to actual engineers anyway, who are in turn hamstrung by the lack of any of the affordances applicable to actually writing code in a real programming language. It's almost impossible to work off a shared repo, have proper version control, do code review, automate testing, or do any kind of CI/CD when you are stuck working with visual flow builder tools.
I could flip this problem around ..
In my experience they are always willing to learn but often times the data modelers don't understand their domain well enough to capture all the nuances of the questions they ask.
Instead they just hide nuance (and increase the time it takes to answer a question) or eliminate it (and therefore produce inaccurate and misleading answers) all in the name of dumbing down self-serve.
I hate the euphemism "non-technical" you can absolutely find a middle ground between LLMs and BI query generation tools and SQL, instead of just declaring by fiat some impenetrable wall of competence.
The solution to the problem of "the domain expert doesn't understand the data and the data expert doesn't understand the domain" is to have them be the same person.
There is a dilbert comic that said similar criticism about spreadsheets but it applies to BI tools and AI tools. It was something like.
"The spreadsheets in this presentation are of course riddled with errors and incorrect information. It doesn't matter because unless they reinforce a decision that upper management has already made no one will ever look at them again." - Dilbert Comic Paraphrase
I've seen this in display in the real world. Various reports and dashboards in states of broken.
1. Totally broken. Are not updating at all for months and sometimes years and no one seems to have noticed. People are actively using them for processes/decisions/workflows.
2. Broken in a large but not obvious way. The data is not updating but is pivoting on a date/time so it changes every cycle it runs but just rearranges the same data.
4. Endless other things I'm sure others here have many stories too. 3. Various formulas and "math" that is completely incorrect and outputting made up fantasy numbers.
Bad choice of words. They are not incompetent.
It's just that the data challenge has become exponentially harder as the world moved away from centralised EDW and ERP systems.
And the level of investment hasn't caught up.
The web of SaaS many companies run on will never be as coherent as a purpose built ERP, as every service is generalized and abstracted concepts don't map perfectly to your business.
You see it often, where a business will inherit the models of the SaaS they use instead of what makes sense to their business, as an attempt to map the two entities better.
Value extractors?
Back in the early computing days, most of the work was done on the large, shared, mainframe computers. The computers were so costly, that the "computer division" was it's own, separate division of the corporation that had premises with the other divisions. For example the Western Division made products, but if it wanted computer resources, it contracted with the Computer Division, who handily had mainframes installed onsite.
Our group was a feisty, small internal analysis group using the new, "cheap" mini computers. One of our points of service was simply being much more reactive to the users needs, we could simply respond more easily because of how the funding worked.
To you point about "data scraped", we were walking through the plant and saw one of our users with one of our reports. They were cutting the lines out of the green bar report, taping them to another piece of paper, and photocopying it. They were sorting the report by a different criteria. We told them "You know, we can do that for you!" "Oh really!?"
People that need to Get Stuff Done, get it done. Our goals as service providers (which is what we in the computer systems groups are, service providers to our internal customers), is to make that as efficient as possible.
Another person was using a PC, our tablet digitizer, and Autocad to record the points on aircraft to generate radar profiles. It was an inventive use of the digitizer, not for fundamental CAD work, but simple data capture from the drawings in Jane's Combat Aircraft.
Of course the guy backpacking the contract making $20 an hour is gonna have something adverse to think of his "CSR" title relative to a few salaried watering hole loiters/"Software Engineers" who's biggest achievements are having named an Excel sheet 4 years ago and googling a Salesforce configuration option and misplaining it.
The post is fundamentally correct though, and I say this as a professional data person, a BI tool rarely gives more people correct understanding of the data or the technical skills needed to use it correctly. If your data is well managed, the tools are easy and people can figure things out, but the world is complicated, so your data will become complicated, and the cost of data management is very visible while the benefits are invisible.
An example of this might be a dashboard with 20 filters and 20 parameters controlling breakdown dimensions and “assumptions used.” So asking “how did Google ads perform in the last month broken down by age group” is about changing 3-4 preset dropdowns. Parameters are also key here - this way you only expose the knobs that you’ve vetted - not arbitrary SQL.
Obviously this is a hard dashboard to build and requires quite a bit of viz expertise (eg experience with looker or tableau or excel) but the result is 70% of questions do become self service. The other 30% - abandon hope. You will need someone to translate business questions into data questions and that’s a human problem.
And tinker with basic parameters
I have seen this over and over. Once low tech users have access to the data, they start building pyramids of bogus analysis, somehow convinced that after all it's not that hard.
The real blocker is all the context that is - let's be honest - always required to perform a correct analysis:
- "Oh no, you cannot use the delivery date to compute monthly sales, since the finance team refill it for recurrent sales"
- "Oh no the prices are stored in USD in the catalog but we actually adjust the rate monthly based on the `monthly_discount` table"
- "Yeah you have to remove items that have a null purchase date from the sales report we have that convention to mark last year's unsold stock"
- "No you cannot sum the sales without joining with the FX rate table since prices are in local currency"
- Etc, etc, etc
As a former sales guy/ manager I also have no sympathy for those people...
Similarly a competent CxO knows that the real time is spent on getting the details absolutely correct - null handling quirks, date handling, mismatched coalescing — even though sql is “high level” it still takes time and dedication to get a trustworthy answer - if there’s people who available who specialise in that, let them do that.
It's ultimately a trade-off: you can make your tool more accessible to non-technical users than SQL, but it will necessarily be less powerful than SQL. And I still think there are plenty of use cases within that space. IME so much "BI" is just "I have two columns of data and I want to plot one against the other".
The author describes SQL as the only "self-serve" BI tool, but honestly, I think that is Excel. So many of these BI tools are just reinventing Excel with new (and therefore less familiar) interfaces. It is a meme to hate on Excel and I think that is because people have in the past tried to use it for complex stuff that really should be done in SQL. If we used SQL for complex data manipulation and Excel for "give me that as a pie chart", there would truly be no need for BI tools.
I've also found that giving non-technical users the opportunity to self-serve when more than one data source is involved always leads to offline mashups of data and the question - "Hey data team, why doesn't 'your' data match 'mine'", always with the assumption that 'theirs' is the correct one!
In org-mode I can create "good looking slides" in a snap, I can quickly craft some chuck of code, run it and get some results, it's damn limited in "dashboard" terms, let's say I can quickly plot some data but the plot is just a crude static image, make it glow with PGF/TikZ it's very time consuming so it's not an option either and it would be still static, because Emacs itself it's the right tool but from an older era. Modern tools offer more eye candy and quick manipulation but only for very limited actions in a very inflexible UI obviously not integrated with anything else. R probably is the quickest with R Studio/quarto to produce contents quick, dirty and still nice to see, but it's still far from the flexibility of Emacs. I think there is no solution without re-writing the entire modern software stack with the classic paradigm and modern stuff doable thanks of much more horsepower under the wood.
What we learned in building AWS Glue was that it wasn’t just about the context — it was also about escape valves. Escape valves are tools necessary to get out of a situation that wasn’t anticipated. When the answer doesn’t make sense, the technical users are the only ones that have the know how to debug it.
When I worked for an ad-tech company, we built an ETL system to convert our messy, complex, and technical debt-ridden application database into a well-organized analytical database. The main purpose was to make the jobs of data scientists and machine learning engineers easier.
However, it also helped business people with some technical knowledge create the dashboards they wanted. Although some queries were a bit messy, it made it easier for them to organize their requirements and communicate effectively with the tech team. Unfortunately, it also resulted in a lot of half-baked and unused dashboards, but overall, it brought positive change to the company.
That said, I don't think it's worth developing an ETL system just for business people. It requires multiple dedicated devs, whereas writing SQL for dashboards occasionally only takes a few days per month for a single dev. I agree that the most important part is fostering a good relationship between tech and business people. If business people have a mental barrier, it becomes challenging to create new dashboards and update or fix existing ones.
I disagree. Just because understanding the data is a difficult problem doesn’t mean that SQL isn’t also a problem.
I understand the data and the set up of the database just fine. Darned if I can remember SQL syntax
Most times I see this type of article, it's with folks that have never worked in a modeled BI tool. Salesforce data, for example, is very complex. But an ability to make a table of live opportunities with metadata and order them freely, next to usage data in an app is self service BI. It's not hard; it takes some setup; but it's self service.
The idea that folks can jump from business understanding to fully mapping the data as it lives in the data warehouse, on the other hand, is not trivial and won't be. The nuance of the real world is hard.
Different types of users need different interfaces - SQL all the way down to point and click. And there's no free lunch on modeling raw data to bring it to a consumable place for the company.
What we've found to actually work at Definite (I'm the founder) is text-to-semantic-query. This is an older video, but here's an example: https://www.youtube.com/watch?v=44mhLgUYOp8
1. You also have complete control over what the LLM can do / access thru the semantic layer (e.g. you can remove tables that the LLM shouldn't consider for analytical questions).
2. One of the biggest choke points for text-to-sql is constructing joins. All the joins are already built into the semantic layer.
3. Calculating metrics / measures is handled in the semantic layer instead of on the fly with SQL (e.g. if you ask something like "how much revenue did we generate from product X", you wouldn't want the LLM to come up with a calculation for revenue on the fly. Instead, revenue is clearly defined in the semantic layer).
4. The query format for our semantic layer (we use cube.dev) is JSON, which is much easier to control then free form SQL.
The semantic layer gives the LLM a well defined and constrained space to operate within whereas there are hundreds of ways for it to fail writing raw SQL.
The post has an entire section discussing this.
The problem with text-to-sql is that, as the post elaborates, writing SQL is not the problem. It's understanding the context and the data:
> On the other hand, a technical person would notice that the question doesn't make sense, and they would ask for more context. They would ask for details about the business person's hypothesis and the problem at hand. Then, they would explain what type of data is available, and work with the business person to formulate a precise and useful question.
Text-to-sql in practice is a solution that nobody was asking for, despite the insane number of SV startups shipping GPT-text-to-sql wrappers as products.
There certainly is like places where LLMs can help (post touches on this briefly), and that is in semantically exploring databases/tables/etc and contexts around data, but this is a very different project and would require a lot of curation from data teams to make it happen.
I agree, a user with no grasp of the schema, or structure of the database, or with zero knowledge on how to wire a query (with or without a graphic interface) will not make the effort because even when they do, they feel like a caveman in front of an iPhone, and everybody hates that feeling.
Most of the time, even people that have worked with PowerBI, or other tools wont make the effort because they also know, push comes to shove, I will end up querying for them, so why would they?
This is a problem with no easy solution I'm afraid... me, I love writing SQL, so it's a bit of a break each time I need to produce stats of some sort, or get a report out.
Unless the data model is either extremely clean or simple, users lacking deep context will struggle.
There are always tables for abstract concepts, code paths for legacy behavior, etc. Should we expect users to embed this internal business logic into their SQL queries? How do these users know when they need to change their embedded assumptions?
I also strongly believe in SQL to be the "glue", or the least common denominator for accessing data, that's why I built https://sql-workbench.com which is a free SQL environment in your browser for querying and visualizing local and remote Parquet, CSV, JSON and other data via DuckDB WASM.
It also supports bringing your local LLM for Text-to-SQL generation via Ollama...
But that said, I think we can do better than just static dashboards. What I'm trying to do with https://sql.ophir.dev is to let the same data teams write apps instead of dashboards. Apps are more flexible, and allow deep dives and navigation to a level that is not possible with just a dashboard.
Even at this moment, with 70+ comments here, not a single person has mentioned what this stands for.
The small amount of effort helps not only for those who are new to the concept, but also makes it easier to discover the article via search.
> Some abbreviations become so common they transcend the expectation of definition.
I agree with this, however it just seems silly to use an acronym 10+ times (as "BI" was in this article) and never once expand on its definition.
And it isn't as though the web site in question is specific to this concept, where users discovering the article are conveniently and unavoidably exposed to information regarding its meaning. In fact, according to a quick Google search, the string "business intelligence" doesn't appear at all across all of the site's indexed pages.
I'm sure I'm in the minority, but it just throws me off to have to stop reading and go on a quick side quest to learn what a key acronym used in the article might stand for. :)
The solution is usually to paste/implement that query in a low-tech automated email report, web page, or Grafana/shared spreadsheet.
It's a (simple) automation problem, which should not be conflated with cross-training an MBA to be a data scientist.
This https://docs.uxwizz.com/guides/ask-ai-new works sort of ok half of the time, but LLMs have to get better before they can really understand the database structure and make correct queries. At the moment, sometimes they get wrong even basic things like using a wrong table name.
Here is an example of an agent that is communicating with an SQL database using simple language.
It's a big canvas that multiple people can use at once, with SQL and Python, text annotations, comment threads, shapes and drawing etc.
Connects into the regular cloud database / warehousing systems.
It's great because it allows you to get the flow of data quite literally visualised on a big board and it exposes the underlying SQL so that, over time, non technical users can learn by exposure and osmosis, as things aren't super hidden away "behind the scenes".
That being said, I like being able to create dashboards for myself, but the interface for every tool to make these queries tries to be too clever and it ends up being painful to use -- coughJIRAcough
I've not felt as impressed by a tool or had it so quickly change how I work ever before.
General-purpose self-serve is hostile to non-technical end users. Most do not have the mental model of SQL to guide their usage of the tool, so giving them an “open-ended” option to run their own vizes ends up really being a fence-toss of some technobabble garbage that isn’t useful to them at all.
“Slice n’ Dice”-style filtering added to an existing set of reports, however, is a completely viable middle ground. Practically speaking, this means writing the basic dashboard query, then hooking a bunch of query parameters up to some front-end UI widgets to let end users pick the date range, level-of-detail, filter which trendlines or categories of data are shown, etc. Just use good taste to avoid going overboard - don’t try to make every last detail configurable.
This has been the basic truth of any self-serve BI system I've used.
Even in smallish orgs there are often three steps - the engineer who instruments code/implements a metric, the engineer who builds the ETL pipeline into the underlying BI warehouse, and the person querying that data. So there are minimally three people in potentially three different roles who need a shared specification and understanding.
Also, self-serve BI tools can be surprisingly opaque and their output can be hard to validate/test. So even if you know accurately what data you are querying, testing that your query is what you intend is hard.
I wholeheartedly agree that just giving a business user access to the straight database is not ideal for all the issues mentioned - they don't know the context and the gotchas in the data combined with probably not understanding how to write SQL. I think an effective data warehouse strategy with straightforward data marts of materialized views can simplify the interaction and maybe even make it really simple for someone to generate basic visualizations. A lot of business people can make basic dashboards in Excel which, worst case, could be connected to a data mart. It's not going to handle BIG data but may cover a large number of use cases for most businesses.
I'm in favor of creating some basic dashboards We've also been experimenting with embedding dashboards in internal tools that provide some slice and dice capabilities but with a high level filter. A user can manipulate and tweak a dashboard for a specific customer but to look at a different customer they need to navigate to it in the internal tools, not via the dashboard.
Lyft uses mode [0] for most dashboards, it has a very well documented data catalog thanks to the system they created, Amundsen [1], and the tables are almost all free to peruse by anyone at the company.
This lets anyone with curiosity and the willingness to write some SQL build some very useful dashboards. And every dashboard is very easy to understand because the SQL can be inspected and the sources can be inspected. If you wanted to go further, you can even pin down where and from which repository the data got emitted. Mode has a very poor charting library, but it satisfies 80% of the needs and the other 20% can easily be complemented by the fact that they have an integration with Jupyter notebooks that gives you even more power.
The entire system felt very ergonomic. It also felt like it gave the opportunity for non-technical people to step up and get their hands dirty rather than wait for a data analyst.
An LLM agent can shine here because a) you can give all the relevant context that is needed to make sense of the data and b) it can be personalized and proactive, so the user just gets appropriate reports when they need it, instead of wading through a "Customer360 Cockpit" with hundreds of visuals and options.
I largely agree with the sentiment but it's just not what we've seen in practice.
No one uses it for jack shit.
All the colorful graphs, charts and cool visualizations have very little actionable information for 99% of people. The other 1% is management and executives who need some random 3 KPI points charted over 3 months to look at so they can feel like they're doing their job or have something to complain about to their underlings.
I have never seen any substantial discussion about any BI metric. Its always a passing thought of curiosity, get the data, chart it, go "Oh Wow, would you look at that." and then immediately move on.
And to take a step back, the only time people actually use any sort of metric consistently, is when they have an obsessive curiosity about a particular area.
Or is this not true?
Dashboards are lossy, compressed selective subset of hyper processed data. They don't show the why, or how, or when.
yet they survive in the form of a report or a chart because they shift the control of the narrative on the designer of the dashboard. You pick and choose what you want to emphasize and what to hide. Convenient, if thats what you want.
OTOH, i've never found or resolved or identified a single issue from a dashboard. I've spent hours on why something wasn't highlighted or something was and found the culprit to be the dashboard itself.
Yep, jackshit