SQL Dialog: Customize how to present data

Oracle Digital Assistant release 22.12 brings us a ground-breaking new capability – SQL Dialog – which can translate a user’s natural language input into SQL queries, send the queries to a backend data source, and display the response. All of these without the need to create intents, utterances, or custom components!

If you read the first article in this series, you should have (or know how to do it) a SQL Dialog Skill. This article will focus on how can we can configure the presentation of data to the user and the available capabilities.

Presentation

What can we do?

Configure Whether to Display Form or Table
Show One or Two Horizontal Sections in Form
Set the Title for the Results
Define an Entity’s Default Sort Order
Define Which Attributes to Include When Not Specified by the Utterance
Define Which Attributes to Always Include in the Results
Configure the Results Page Size
Add Buttons and Links to the Results
Add a Custom Attribute
Use Event Handlers for more customization

All of the configurations are done at the query entity level, and you can find a dedicated tab called Presentation.

This is the first thing you should do

Create meaningful names for the attributes. As part of enhancing the model (check this post to read more about it), we should change the attribute’s primary names and provide also synonyms. The entity and attributes primary names are the display names, so we want something that makes sense as opposed to typical database tables and attributes names.

Which data do you want to show?

The second thing you should think of is: which attributes do you want to see?

If an utterance doesn’t mention any specific attributes, we use the Default Attributes. For example, when an utterance says “show me all the employees” or “show all the departments”, the Default Attributes list determines which attributes are displayed.

If an utterance mentions attributes, consider displaying additional attributes for context. For example, simply showing the “salary” data lacks context. To remedy this, we establish Minimum Attributes configuration to provide context.”

How to present the data?

You can choose a table or form layout, and define the number of columns and horizontal sections. You can also choose a threshold after which the data is presented in one format or the other.

Format individual attributes

Attributes can be formatted on an individual basis with a series of predefined parameters

For example, Date/Time/Number have specific format masks.

Global and Row Actions

Query entity results can include buttons and links at both the global and row level. Row actions are displayed in each individual row, while global actions appear below the overall results

You can choose an action of type URL or Query.

For Query, we need to define the OMRQL query where we can dynamically reference data from the results by using ${row.<attribute_name>}

There is also the option to define a Visibility Expression.

Event Handler

An SQL Query Event Handler (SQEH) enables you to customize the SQL Dialogs query results.
https://github.com/oracle/bots-node-sdk/blob/master/DATA_QUERY_EVENT_HANDLER.md

The SQEH is deployed as part of a component service. You use the below command to add an event handler to an existing package.

bots-node-sdk init component myEventHandler sqlQueryEventHandler

This example creates a component of type sqlQueryEventHandler that is named myEventHandler.

You can have Entity level and Attribute level events

Entity

changeUISettings
changeResponseData
changeBotMessages

Attribute

changeUISettings
format

Below is an example taken from the GitHub samples where we implement an entity-level event, which will show the query execution time.

Unlike Composite Bags where we can create an EEH directly in the UI. For SQEH we need to package the custom component externally. Once we add that custom component to the Skill, then it becomes available.