In my previous post called Working with dynamic schemas, we discussed how to create dynamic schemas in Omni to work with your database when you have a single-tenant database architecture.
There might be some views in the database that you want to create further derived views on top of using the sql block to make these logical extensions. However, when you write this SQL, you might be unsure what view to reference when you want this object to work with your dynamic schemas so that the view changes based on the user attribute.
The right way to define the sql: is by using a view reference, the Omni templating used to refer to tables or fields. For example, using the same example from the Working with dynamic schemas post, we have a dynamic schema called store_ecom and a table called orders. The view reference will be ${store_ecom__orders}.
Then if we define a new view and want to write the logic as a sql block, we could write the following:
sql: |-
WITH returned_item_count AS (
SELECT
orders.id as order_id,
order_items.order_item_id,
SUM(order_items.quantity) OVER (PARTITION BY orders.id) as number_items_in_order
FROM ${store_ecom__orders} as orders
LEFT JOIN ${store_ecom__order_items} as order_items
ON orders.id = order_items.order_id
WHERE orders.status = 'returned'
GROUP BY 1, 2
)
SELECT *
FROM returned_item_count
This approach ensures your views are dynamically aligned with user-specific schemas, providing seamless and context-aware data access.