Writing SQL in Omni

For those with permissions, the workbook can also be used as a SQL IDE for ad hoc analysis. Dialect SQL can be written directly, or Omni fields and operators can be used. For field references, use ${} notation, fully scoped to the view, ie $\{user_facts.lifetime_value\\}.

SQL authoring comes in two main varieties: with and without SQL Super Powers.

SQL Super Powers

By default, when entering SQL mode, you will have SQL Super Powers activated (either via edit SQL in the SQL block or from a new tab then SQL in field picker). What this means is that your SQL will be parsed and restructured to retain the flexibility to use the field picker (for new fields, filters, etc) after queries are run. This will also result in net new modeled concepts being parsed and suggested in the field picker.

Here is an example where we have classified our marketing sources via a case statement in SQL. Note how the field picker is active after we run our query, and the formatting and structure of our CASE statement changed slightly upon run.

At times Omni will not be able to translate the query structure into a data model. The SQL will still be run, including swapping Omni operators like OMNI_SUM() for their SQL counterparts, and swapping field references like ${users.state} to their underlying SQL (users.state). In these cases, the field picker will be deactivated, because we cannot append new fields to the query intelligently. To return to using the UI, you will either need to clear the query or adjust the query to SQL Omni can understand.

Deactivated SQL Super Powers (Raw SQL)

When SQL Super Powers are turned off, OmniSQL will be translated into the exact SQL run in the database, including complex concepts like pivots. When SQL Super Powers are turned off, Omni will not adjust the structure of your query, we will only swap field references (ie. ${users.state} to users.state, etc) and Omni operators (ie. OMNI_SUM(users.id) to COALESCE(SUM(users.id), 0)). Operators and Omni field references will never be inserted unless defined by the user.

In raw SQL mode, the field picker will be deactivated for selection, but still browseable across All Views and Fields, to allow for understanding the schema and finding relevant fields in the database. Topics are unavailable in raw SQL mode, as they are no longer accessible to the querier.

To exit this experience, users will need to discard SQL from the SQL menu:

Custom SQL Filters

Enabling custom SQL filters directly from a workbook is possible by creating custom SQL queries using templated filter syntax. By implementing the templated filter syntax, you can link dashboard filters to specific fields or filter fields in the query. Although the process can be a bit complex, it offers flexibility in filtering data within SQL queries and ultimately allowing dashboard tiles based on SQL queries to have dynamic filters.

Setup Instructions

To set this up write a SQL query like this example:

SELECT * FROM order_items as items
WHERE 
    {{# order_items.created_at.filter }} items.created_at {{/ order_items.created_at.filter}}
    AND {{# order_items.status.filter }} items.status {{/ order_items.status.filter}}
LIMIT 100

This query will parse out the filters so they can be altered from the UI and even be mapped to dashboard filters. The field referenced in the {{ # filter_name_here }} has to be a field or filter field that exists in a table and referenced the exactly as it is modeled. In this example, order_items.created_at.filter is written with the view referenced order_items even though the SQL query aliases the table as items because order_items is the modeled view's name.

Video Tutorial