> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.omni.co/feedback

```json
{
  "path": "/analyze-explore/sql/generation",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

# Understanding SQL generation in Omni

> When building an analysis - whether that's through the workbook or using AI - Omni generates SQL to execute against your data warehouse and returns the results.

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

<AccordionGroup>
  <Accordion title="Semantic model">
    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.
  </Accordion>

  <Accordion title="Views">
    [View files](/modeling/views) 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.
  </Accordion>

  <Accordion title="Relationships">
    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.
  </Accordion>

  <Accordion title="Dimensions">
    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.
  </Accordion>

  <Accordion title="Measures">
    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.
  </Accordion>

  <Accordion title="Topics">
    [Topics](/modeling/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.
  </Accordion>
</AccordionGroup>

## 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](#sql-wrapped-in-omni-helper-functions)
* [Dialect SQL executed against the database](#dialect-sql-executed-against-the-database)

### SQL wrapped in Omni helper functions

This layer contains [Omni's accelerator functions](/analyze-explore/sql#omni-sql-operators) 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 SQL with Omni helper functions theme={null}
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.

Users with the Querier or higher connection role can view the dialect SQL by toggling the [**Inspector** on in a workbook query tab](/analyze-explore/workbook-inspector).

In this example, the SQL looks like this:

```sql Dialect SQL theme={null}
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](#sql-wrapped-in-omni-helper-functions) for a query and its corresponding [dialect SQL](#dialect-sql-executed-against-the-database).

<AccordionGroup>
  <Accordion title="Adding a measure (SELECT)">
    #### Dialect SQL

    ```sql Dialect SQL theme={null}
    SELECT
      COUNT(DISTINCT "ID") AS "ecomm__order_items.total_orders"
    FROM "ECOMM"."ORDER_ITEMS" AS "ecomm__order_items"
    ```

    #### SQL with Omni helper functions
  </Accordion>

  <Accordion title="Adding a dimension (SELECT, GROUP BY)">
    #### Dialect SQL

    ```sql Dialect SQL theme={null}
    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
  </Accordion>

  <Accordion title="Adding a field from another view (JOIN)">
    #### Dialect SQL

    ```sql Dialect SQL theme={null}
    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
  </Accordion>

  <Accordion title="Filtering by a dimension (WHERE)">
    #### Dialect SQL

    ```sql Dialect SQL theme={null}
    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
  </Accordion>

  <Accordion title="Filtering on a measure (HAVING)">
    #### Dialect SQL

    ```sql Dialect SQL theme={null}
    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
  </Accordion>
</AccordionGroup>