This parameter should be used on the aggregated view, which will allow Omni to match the aggregated table to the underlying views it aggregates.
materialized_query:
fields: [ <view_name>.<field_name>, ... ]
base_view: <view_name>
Properties
An array of field names from the base table that correspond to columns in the aggregated view, specified as view_name.field_name.Field order matters! Make sure the fields listed here match the column order in the view’s sql statement.
The name of the base view that this aggregated view summarizes.
Examples
In this example, you have an order_items table that contains a few metrics. Using aggregate awareness, you can create a user_facts table that rolls up these metrics. Let’s take a look at how the fields in the underlying views will map to the fields in the aggregated user_facts view:
| Underlying view fields | | Aggregated user_facts fields |
|---|
users.id | becomes | user_id |
order_items.created_at[month] | becomes | month |
order_items.count | becomes | count |
order_items.total_sale_price | becomes | total_sale_price |
user_facts.view file would look like this:
name: user_facts
sql: |
SELECT user_id, month, count, total_sale_price
FROM user_facts
dimensions:
user_id: {}
month: {}
count: {}
total_sale_price: {}
materialized_query:
fields:
[
users.id,
"order_items.created_at[month]",
order_items.count,
order_items.total_sale_price
]
base_view: order_items
user_facts view:
SELECT users.id as user_id,
order_items.created_at[month] as month,
COUNT(order_items.id) as count,
SUM(order_items.sale_price) as total_sale_price
FROM order_items
JOIN users ON users.id = order_items.user_id
GROUP BY 1,2
