Skip to main content

Writing SQL in Omni

For those with permissions, the workbook can also be used as a SQL IDE for ad hoc analyses. 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, filter, 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: