Hi,
I have in my schema a table with a lot of dates, that I dont want Analysts and end users to use for grouping, but only for Filtering. Is it possible to apply this restriction by cube js schema design?
Here a deeper explanation:
In a situation where you have multiple dates in your schema, and you want to simplify the end-user/analyst experience, to use only the date dimension for grouping. In combination with this a user/analyst should be able to filter by other dates.
Take this example:
Tables
Orders
- created_at
Shipments
- created_at
- shipped_at
- delivered_at
Date dimension
date (mapped to Shipments/Orders created_at)
If I would want to get all created orders and shipments per day (in a star schema), i could write this query
with base as (
SELECT
count(*),
created_at ,
‘order_created’ as event_name
FROM Orders
UNION ALL
SELECT
count(*),
created_at,
‘shipment_created’ as event_name
From Shipments
)
select
base.event_name,
count(*) as count
from base
left join date_dim on date_dim.date = base.created_at
group by date_dim.date
So, what I would do here, is always use dim_date for grouping and filtering, but if someone needs to use a date such as delivered_at for filtering it is fine. I only don’t want that column to be used for grouping.