Skip to main content

Frequently Asked Questions

A compiled list of one-off questions with links to notes in the docs or quick answers

How do I count / sum / average?​

Omni uses a lot of right click actions, try right clicking on the field you want to aggregate and we should have some quick calculations.

I still don't see aggregates?​

Sometimes we aren't smart enough to aggregate a specific field. This is often because we don't recognize the type (for example, we think it's a string or some unknown type rather than a number). Often you can cast the field or tune the model to open up the expected calculations. Ping us if you need help working through it.

How do I add to dashboard / clear query / close the field picker / some other action?​

Try the menu at the top of the page (file, view, etc). We're working on putting every action exactly where it should be, but when you can't find it on page try the header.

Is there a way to add a row with totals to the query?​

Row totals and column totals are available in the options menu on the table (next to 'chart' in the menu bar; then will appear on the right in a gutter).

Is there a way to add a subtotals to the result set?​

Right click on any dimension and all dimensions right of the given dimension will then subtotal.

Why isn't my dimension aggregating? Why isn't my number a sum?​

Each transaction or record usually has a numeric value associated with it (say the cost of a given purchase). If you select this dimension, Omni will simply return a list of all the different transaction values, which is not usually what you want. To total purchases, for example, simply right click or click the arrow and select sum. This will transform the dimension into a metric that you can then aggregate across other dimensions (say purchases by state).

How do I control caching policies?​

Query results are cached for 6 hours by default, but this can be configured at the model and topic level using the cache parameters outlined here.

What is the square icon that is not a date or number or string or boolean in the field picker? How do I set a field type in Omni?​

That means we do not recognize the data type. You may need to transform the field via CAST (ie model) to do more productive things with the field in Omni (ie stringify a blob).

Is there a quick way to remove all fields from the table?​

Yep, check the 'Tab' menu for 'Clear Query', you can also remove just the fields or just the filters, and see the keyboard shortcuts.

Can you sort by one column, then another column?​

Yes, simply hold shift after your first column sort. The right click menu also offers options to remove an additional sort.

Can you query the data unsorted?​

Yes, simply right-click on the column header and remove sort; you can also remove sort in the SQL query itself. Omni will often sort queries by default.

What is the row limit?​

Queries have a row limit of 50,000 in the UI (note that this does not prevent you from querying full tables with billions of rows--the Omni UI just won't try to render more than 50,000).

Do views I save use the row limit?​

By default, UI queries saved as view will strip the row limit. The row limit can be re-included by manipulating the YAML model directly.

Can I drill into measures to see the specific items in a count or other measure?​

Yep, click on the measure in your data table or visualization, and you'll be offered drill down to the rows. Omni will drill into a few of the fields from the view that measure is defined in, but you can curate this at the view or measure level.

How do I get missing dates to show up in my results set (some dates are not in the data set)?​

This is an unfortunate property of SQL, but Omni offers an option to add in missing rows. Simply right click on the column or pivot and select 'fill in missing rows'. Note that you'll need to have a filter on the date so Omni knows what range to fill in!

Fill in missing rows is not showing up, what did I do wrong?​

Omni can only fill missing rows in a data set where we know the list of rows to fill. This means, for now, the dimension must be an enumerated list (case statement, boolean, or known list like day of week name) or a bounded date. To make sure filling appears on dates, make sure there is a filter that sets a start and end date for the query, which will set the range Omni fills over (ie [2023] will fill for all days in 2023, before and after today).

All my results are showing up with 0 or 1, what is happening?​

This is a frequent problem with integer math - dividing two whole numbers. We're looking to add helpers for these in the model, but this can also be avoided by multiplying by 1.0 to cast the result to a decimal.

Why can't I put totals on my tables​

Column totals and row totals only work over measures, if you'd like to include a totals row, you can often swap your dimensions in a fact table for an aggregate (ie the measure, spend_facts.sum_spend, will total but the dimension, spend_facts.spend, will not)

I'm getting an error on my quick average, what happened (could not compute aggregation where the joins fan out the data without a primary key)?​

Omni attempts to protect from bad aggregation one queries where one table fans out another table. For example if you have users with many orders, and are looking at the data set by user.state, median_order_size requires understanding both individual users and individual orders. This is the primary key that defines each user in the user table and each order in the orders table.

To add a primary key, simply right click on the corresponding field (usually something like user.id or orders.id) and set as primary key. In cases where there may be no primary key, you can make a new field by concatenating fields together - for example CONCAT($users.name, ' - ', ${users.tag}) in a table where users are duplicated with multiple tags).

My table calculations aren't working across null values in my pivoted table (why isn't dimension fill working)​

Value fill and dimension fill are slightly different.

Dimension fill will return missing rows or columns for a dimension, often a date series or an enumerated list like yes/no or entries in a case statement. Dimension fill will ensure each value shows up whether or not there are corresponding rows in the database. This toggle is available on each dimension in the data table that can be filled (dates with endpoints or enumerated lists).

Value fill is used to fill in null values in a pivoted table. This is most useful to build calculations on top of a pivoted data set that may be sparse. Null values will be returned as zero, and thus calculations like moving averages or moving sums will work over even columns or rows with missing data. This toggle is available in the measures on a table.

What is verified content​

Verified is a special dashboard tag that can only be applied by administrators. It provides a protected tag space that can be used to flag important or more robustly maintained dashboards and workbooks. Using it is not required, but it offers a simple official designation for content when needed.

Dealing with TIMESTAMP_TZ in Snowflake​

Snowflake has a data type called TIMESTAMP_TZ, which stores timestamps in UTC, alongside a timezone offset. When querying this datatype in Omni, this data type lead to confusion and seemingly inconsistent behavior when querying and filtering data on the margins of dates across the offset,

For this reason, we recommend converting out of this data type in Omni by casting to the TIMESTAMP_LTZ data type.

This invovles updating the field definition in a fashion similar to this: ${timestamp_field}::timestamp_ltz

I need a comma in my filter what do I do (escape comma)?​

By default Omni will delimit between entries using comma. To explicit include a comma in a filter (say San Francisco, CA) you can escape the break with a \, so you would type San Francisco\, CA.