For over a year, Databricks AI/BI has been democratizing business intelligence and analytics across your organization with highly visual and interactive low-code AI/BI Dashboards. Following a brief mention in our most recent AI/BI roundup blog, we’re excited to showcase the ability to define expressive, reusable custom calculations in AI/BI Dashboards. Custom calculations allow you to model your data in more expressive ways on top of source datasets to create richer, more interactive, and more performant dashboards.
Custom calculations are defined using familiar SQL syntax, so there’s no learning curve to use them. Crucially, custom calculations also allow you to define aggregations and transformations on top of your dashboard datasets without modifying the original dataset queries. They come in two forms:
- Calculated measures are aggregated calculations that can be applied dynamically across different groupings as needed by different visualizations. Example:
- Calculated dimensions are non-aggregated calculations that are applied row-wise. These are helpful for formatting or transforming individual row values. Example:
Why use custom calculations?
Reduced dataset sprawl
Suppose you have the following dataset:
You want to visualize profit margin by region. Without custom calculations, you would need to create a new dataset with a margin column:
While this approach works, the new dataset is static and might only support a single visualization. Filters applied to the original dataset do not affect widgets using the new datasets without additional manual adjustments.
With custom calculations, you can express the profit margin as an aggregation using the formula:
Though this measure is defined on the original, ungrouped dataset, it is dynamic. When it’s used in a visualization, it automatically updates to reflect groupings and filters applied to the dataset. In this example, we can even use the same custom calculation to calculate profit margin per region in one visualization, and profit margin per product in another visualization. Without custom calculations, this would require at least two additional “bespoke” datasets defined with the right groupings.
So far, usage has indicated that dashboard authors need less than half as many datasets to support the same number of visualizations when they use custom calculations.
More unified interactive filtering
Interactivity is a key component of what makes AI/BI Dashboards powerful. However, interactive filtering through filter widgets and cross-filtering operates on a per-dataset basis, which means that ease of interactivity takes a hit when visualizations are splintered across many datasets. In such cases, users may need to take extra steps to filter all desired widgets.
Consolidating datasets as detailed above enables the same filters to take effect across more visualizations. This results in a more consistent, intuitive experience – widgets based on the same underlying data are more easily configured to react to the same filters.
Of course, custom calculations are also supported in static filters defined per-widget. You can read more about interactivity in AI/BI Dashboards here.
Expressive formatting
Custom calculations include support for over 40 different functions and expression syntaxes, covering basic arithmetic, aggregations, conditional case expressions, data type casting, and a slew of functions for string/date manipulation.
These functions allow for more than what is already offered in the visualization editors. For example, you can:
- Construct arbitrary strings using the CONCAT and CONCAT_WS functions. Example:
- Format dates using the DATE_FORMAT function. Example:
- Create custom category strings using CASE expressions. Example:
Streamlined dataset authoring
The introduction of custom calculations enhances the dataset authoring experience in many ways:
- Custom transformations can be siloed into well-labeled custom calculations, keeping clutter out of dataset text.
- A new dataset schema view details what fields are available to reference in calculations, and their data types.
- Instant expression validation and data type inference computed client-side are available for custom calculations.
- A semantically consistent set of datasets for your dashboard is easier to achieve when there is less dataset sprawl. Having fewer, more coherent datasets minimizes confusion and extra “detective work” to relearn how your datasets are structured every time you need to update your datasets.
Performance built in
Widgets using custom calculations are subject to the same performance optimizations as all other widgets in AI/BI dashboards. This includes caching query results and fast client-side computation for small datasets.
In addition, having fewer datasets that perform less complex grouping operations results in faster dataset query execution, speeding up iteration time when authoring and reducing load time when viewing.
How to create and use custom calculations
To create and use a custom calculation:
- On the relevant dataset, click Custom Calculation.
- In the panel that opens, fill out the Name and Expression fields, using the dataset Schema View and built-in expression validation to guide your expression authoring. Optionally, write a Comment describing what your calculation is doing. Click Create.
- You can now view your custom calculation in the dataset schema view and select it just as you select any other field in the visualization editor.
For full instructions, see the custom calculations documentation. If you are new to AI/BI dashboards, check out this tutorial to get started.
Conclusion
Custom calculations bring a new level of expressivity and flexibility to the AI/BI Dashboard authoring experience and enable more unified interactive experiences for dashboard consumers. Check out the documentation for even more details on how to manage your custom calculations and for a full list of supported functions and expression syntaxes.
If you are ready to explore the latest in AI/BI, you can choose any of the following options:
- Free Trial: Get hands-on experience by signing up for a free trial.
- Documentation: Dive deeper into the details with our documentation.
- Webpage: Visit our webpage to learn more.
- Demos: Watch our demo videos, take product tours and get hands-on tutorials to see these AI/BI in action.
- Training: Get started with free product training through Databricks Academy
- eBook: Download the Business Intelligence meets AI eBook
We can’t wait to see what you build with custom calculations, and value your feedback as we continue to expand this feature — let us know what support for even more expressive calculations you’d like to see!
