frappe/insights v2.0.0

on GitHub

New Query Builder

The classic & visual query builders were not new-user friendly. The user had to do a lot of guesswork to get the query right. Query Builder needs to be intelligent to help reduce the manual steps required to build a query for eg. inferring joins, groupings, aggregations, etc.

The new query builder is designed to improve the query-building experience. The idea was to reduce the number of steps by automatically guessing the joins and setting proper defaults wherever possible while still allowing the user to customize the query.

Major Improvements:

• Guess joins based on the relationships between tables
• Guess joins based on the columns selected from related tables
• Guess group bys, aggregations, date formats, etc based on the columns selected
• Guess chart type & chart options based on the result

The combination of these changes has reduced the number of steps required to build a query significantly.

Other Improvements:

• The old queries will continue to work as before, in the classic query builder. The new query builder will be used for new queries. Although, the user can switch to the new query builder for old queries as well.

  

• Transform section has been added which was previously hidden under the menu button.

  • Apply pivot transform to convert values from a row into columns. The results must have two grouping columns and one value column. Any other columns will be ignored. Only one pivot transform can be applied.

    PivotTransform.mp4

• All queries are saved such that they can be used as tables in other queries. Reduces the step to save the query as a table.

• Added a new Auto chart type which will try to guess the best chart type based on the result. This is useful when the user is not sure which chart type to use.

• There's a way to unstore a query from the query builder menu.

• The visual query block has been removed from the notebook interface. All the existing visual queries will be converted to native SQL queries.

Managing Table Relationships

Relationships are important for the query builder to infer joins between tables. There wasn't a way to add/edit/remove relationships between tables. The new RelationshipEditor allows the user to manage relationships between tables.

Note. Relationships are automatically imported if the data source is a Frappe app's database.

Filtering, Sorting & Pagination in Table Chart

The table chart now supports filtering, sorting & pagination. This was made possible by using one of the best datatable libraries - tanstack-table. The table chart is now more powerful and can be used to show large datasets.

Pivot Table Chart

A new chart type PivotTable is added which allows the user to select rows, columns, values and to create a pivot table. The table is rendered using tanstack-table making filtering, sorting & pagination possible.

Other Minor Improvements

• In an attempt to reduce the clutter in the Query list, a new list filter is added. By default, the user will see only the queries that they have created. However, the user can change the filter to see all the queries.

• Adding a dashboard filter is now a little easier. The link column per chart is auto selected after selecting the column to filter on.

• The table cell will show a Badge for each string if the column has a list of strings

  

• Now you can get the query result of any query in a script by using the get_query_results method.

• Performance improvements

  • Improved data source table & column import when syncing a table
  • Restore the sqlite file for demo data setup instead of importing the data from csv files