Visualization Editor
Charts from a query result, without leaving the data platform. IBM.
After running a query in IBM watsonx.data, users can turn the result into a chart in the same place. The editor opens with a recommended chart already drawn, and every choice can be changed by hand. We built the editor and the recommendation behind it, which runs in the browser on rules, not on a model.
React · JavaScript · MobX · Carbon Charts
Why it matters
Users write SQL on the platform to extract, clean and prepare data. To see it as a chart, they moved the result to a separate BI tool, built charts and dashboards there, and shared them from there. Any change to the query meant going back to the platform, running it again and moving the data again. Charting a result where the query runs removes that round trip.
The problem
A query result is just column names, sometimes types, and rows. Nothing says what each column means, and a chart drawn from a wrong guess looks fine and says the wrong thing:
- Names and types mislead. A numeric customer ID is not a quantity, a year stored as a number is time, and a text column can hold dates.
- Some numbers cannot be added. Summing percentages, averages or rates gives a number with no meaning.
- Results are often already aggregated. Aggregating them again is sometimes right, as with a sum of sums, and sometimes wrong, as with an average of averages.
- Order carries meaning. Months sorted alphabetically start with April and August.
The solution
Rules in the browser, not a model
We first built a recommender that used an LLM. Every chart would have added a model call to the customer’s cost, so we replaced it with rules that run in the browser. A recommendation costs nothing and appears as soon as the result does.
Working out what each column is
Every column becomes a measure, a category or time. The evidence is checked from most to least reliable, and the first rule that applies decides:
| Evidence | Example | Decision |
|---|---|---|
| Declared type | a date or timestamp column | time |
| Name marks a label | customer ID, postal code | category, even if numeric |
| Shape of the values | text that is nearly always a date | time |
| Name marks time or a quantity | order month, total revenue | time, or measure |
| Numbers | a numeric type, or values that are numbers | measure |
| Nothing else applies | category |
Measures are also marked when they look like percentages or like values that cannot be summed, such as averages and rates. The rules read a sample of the result, so they stay fast on large results.
Reading the query
The SQL says directly what the column names only hint at. Columns in the GROUP BY are categories, and aggregated expressions are measures. The function used tells the editor how the measure can be aggregated again, and it fills that in:
| In the query | Aggregated again by | Why |
|---|---|---|
| SUM or COUNT | sum | sub-totals add up to the total |
| MIN or MAX | the same function | the minimum of minimums is the minimum |
| AVG or COUNT DISTINCT | not allowed | it would change what the number means |
| a raw column | the column’s usual aggregation | sum for revenue, average for a rate |
Charts that would need an aggregation marked as not allowed are not recommended.
Choosing the chart
Each chart type that fits the columns gets a score, and the editor opens on the highest:
| Data | Chart |
|---|---|
| a measure over time | line |
| a measure by category | bar, ranked lower when there are many categories |
| shares of a whole, few categories | pie or donut |
| two measures | scatter |
A chart that uses only some of the result’s columns ranks below one that uses all of them. The other chart types are one click away, already filled in where the data fits.
Sorting by meaning
Columns whose values are months, weekdays or seasons are recognised from the values and sorted in calendar order, unless a user has set an order of their own. Pie and donut slices keep that order too, which needed the order passed into the drawing step, because the charting library sorts slices by size on its own.
Changing it by hand
Users can pick any chart type (bar, grouped or stacked bar, line, area, scatter, pie, donut or a table), choose the fields and the aggregation, sort by label or by value, and set a title and axis labels. Aggregation runs in the browser, so changing a field redraws the chart without running the query again.
Results
Users can chart a query result where they wrote the query, starting from a sensible chart, with no added model cost. The first version covers single charts; dashboards are not part of it.