Skip to main content
Omni accomplishes this by leveraging the models, relationships, and topics defined in the semantic layer to dynamically build SQL statements. Through this process, Omni is able to optimize queries for application performance and readability while also providing graceful handling for data fanouts using symmetric aggregates. Omni provides the flexibility to query data in two main ways:
  1. Through the virtualized schema model
  2. Directly using raw SQL. When using raw SQL, Omni will not generate or override any SQL logic.

​
Key concepts

Think of a semantic model as instructions to Omni on how to write SQL on your behalf. Omni breaks down common SQL statements you might write into small, reusable pieces that can be pieced together on demand.You don’t have to run everything through a semantic model in Omni - and likely shouldn’t! - but it can be a powerful tool for enabling self-service reporting, AI querying, and improved change management through your BI tool.
View files in Omni are virtualized representations of tables (or views) within your data warehouse that tell Omni how fields in the workbook map to fields in your data warehouse.These map to the FROM and JOIN statements in SQL.
Relationships tell Omni the join logic that should be used when the multiple views are used in the same analysis.This maps to a JOIN statement in SQL.
Dimensions are field definitions, which can be either a column reference or a calculated field (e.g. datediffs or case whens). Dimensions are attributes that can be queried and grouped by.These go into SELECT and GROUP BY statements in SQL.
Measures are field definitions specifically for aggregations - sums, counts, averages, etc.These go into the SELECT statement in SQL, but won’t be grouped by.
Topics are not required for using Omni’s semantic model but they are a helpful tool for adding an extra layer of curation on top of your semantic model.They let you explicitly control how the UI looks and feels to users and have more control over what’s allowed to be queried for specific contexts. This is especially helpful if they are less familiar with the data warehouse (e.g. tables, schemas, column names, etc.). For example, you may want to curate which fields, tables, and joins are allowed to be used together or perhaps there is a field calculation that has different logic based on the context that it’s getting queried with. Topics will also enable you to manage row-level security, force default behaviors (filters, joins, etc.), and overwrite model and relationship logic if necessary.In the context of SQL, topics can be curated to influence all elements of generated SQL.

​
Inspecting query SQL

A great way to learn what Omni is doing is to see the SQL generated when you build a query. You can view the generated SQL by opening a workbook. For every query, Omni generates two layers of SQL:

​
SQL wrapped in Omni helper functions

This layer contains Omni’s accelerator functions and is optimized for cache performance and readability. You can inspect this SQL by clicking the SQL button in a workbook query tab:
In this example, the SQL looks like this:
SQL with Omni helper functions
SELECT
  OMNI_SUM(${order_items.sale_price}) AS "order_items.total_sale_price",
  OMNI_DATE("CREATED_AT") AS "order_items.created_at[date]"
FROM "ORDER_ITEMS" AS "order_items"
WHERE ${order_items.status} NOT IN ('Returned', 'Cancelled') OR ${order_items.status} IS NULL
GROUP BY 2
ORDER BY 2 NULLS FIRST

​
Dialect SQL executed against the database

This layer of SQL is written in the same dialect used by the database backing the connection in Omni. All field references and Omni functions will be translated to the flavor of SQL used by your database. In fact, you could copy this SQL query and run it in another IDE pointed at the database. You can view the dialect SQL by toggling the Inspector on in a workbook query tab:
In this example, the SQL looks like this:
Dialect SQL
SELECT
  COALESCE(SUM("SALE_PRICE"), 0) AS "order_items.total_sale_price",
  DATE_TRUNC('DAY', "CREATED_AT") AS "order_items.created_at[date]__raw",
  TO_CHAR(DATE_TRUNC('DAY', "CREATED_AT"), 'YYYY-MM-DD') AS "order_items.created_at[date]"
FROM "ORDER_ITEMS" AS "order_items"
WHERE "STATUS" NOT IN ('Returned', 'Cancelled') OR "STATUS" IS NULL
GROUP BY 2
ORDER BY 2 NULLS FIRST

​
Examples

Let’s look at a few examples to bring everything together. Click the following dropdowns to view the examples, each of which contains a clip of a workbook interaction that builds the Omni-wrapped SQL for a query and its corresponding dialect SQL.

​
Dialect SQL

Dialect SQL
SELECT
  COUNT(DISTINCT "ID") AS "ecomm__order_items.total_orders"
FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"

​
SQL with Omni helper functions

​
Dialect SQL

Dialect SQL
SELECT
  TO_CHAR(DATE_TRUNC('DAY', "CREATED_AT"), 'YYYY-MM-DD') AS "ecomm__order_items.created_at[date]",
  COUNT(DISTINCT "ID") AS "ecomm__order_items.total_orders"
FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"
GROUP BY 1
ORDER BY 1 NULLS FIRST

​
SQL with Omni helper functions

​
Dialect SQL

Dialect SQL
SELECT
  DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT") AS "ecomm__order_items.created_at[date]__raw",
  "ecomm__users"."STATE" AS "ecomm__users.state",
  COUNT(DISTINCT "ecomm__order_items"."ID") AS "ecomm__order_items.total_orders",
  TO_CHAR(DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT"), 'YYYY-MM-DD') AS "ecomm__order_items.created_at[date]"
FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"
LEFT JOIN "ECOMM"."USERS" AS "ecomm__users" ON "ecomm__order_items"."USER_ID" = "ecomm__users"."ID"
GROUP BY 1, 2
ORDER BY 1 NULLS FIRST

​
SQL with Omni helper functions

​
Dialect SQL

Dialect SQL
SELECT
  DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT") AS "ecomm__order_items.created_at[date]__raw",
  "ecomm__users"."STATE" AS "ecomm__users.state",
  COUNT(DISTINCT "ecomm__order_items"."ID") AS "ecomm__order_items.total_orders",
  TO_CHAR(DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT"), 'YYYY-MM-DD') AS "ecomm__order_items.created_at[date]"
FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"
LEFT JOIN "ECOMM"."USERS" AS "ecomm__users" ON "ecomm__order_items"."USER_ID" = "ecomm__users"."ID"
WHERE "ecomm__users"."STATE" = 'Michigan'
GROUP BY 1, 2
ORDER BY 1 NULLS FIRST

​
SQL with Omni helper functions

​
Dialect SQL

Dialect SQL
SELECT
  DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT") AS "ecomm__order_items.created_at[date]__raw",
  "ecomm__users"."STATE" AS "ecomm__users.state",
  COUNT(DISTINCT "ecomm__order_items"."ID") AS "ecomm__order_items.total_orders",
  TO_CHAR(DATE_TRUNC('DAY', "ecomm__order_items"."CREATED_AT"), 'YYYY-MM-DD') AS "ecomm__order_items.created_at[date]"
FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"
LEFT JOIN "ECOMM"."USERS" AS "ecomm__users" ON "ecomm__order_items"."USER_ID" = "ecomm__users"."ID"
WHERE "ecomm__users"."STATE" = 'Michigan'
GROUP BY 1, 2
HAVING COUNT(DISTINCT "ecomm__order_items"."ID") > 6
ORDER BY 1 NULLS FIRST

​
SQL with Omni helper functions