LinkedIn
Twitter
Hi,
I am working on a project that we have some questions about.
The project works as a star schema, we have one fact table with multiple dates that we want to join with the view of the calendar. We want to know which is the best practice for Looker to define these relationships with these two tables.
For example, we have hiring dates and termination dates in the fact table as relevant date fields. We want to create a measure that counts the hiring, and the termination for the selected period of the table calendar and one measure for the staff count that could be a snapshot for the selected time period.
We want the end user to make her own reports with one calendar and the measures in one query. The output will be a table with these 3 measures and the dimensions of month/year and filters they want to see from the view of calendar.
My question is which is the best practice to define the relationship of those tables, and which would be the temporal logic for those measures?
An example of the output that we want:
So year and month comes from the view of calendar, also the filters of dates that we want to show. The 3 measure are in the fact table which does the sum of employees. The querys in SQL would be like:
------------------------------------------------------
SELECT calendar.date_year, calendar.date_month,
(SUM(CASE
WHEN ((fact_table.hiring_date >= ‘2022-01-01') AND ( fact_table.hiring_date <= '2022-12-31') THEN 1), ELSE 0) AS " fact_table. hirings"
FROM fact_table
LEFT JOIN Calendar ON (Calendar.date = fact_table.hiring_date)
WHERE filters…
-------------------------------------------------------------------
SELECT calendar.date_year, calendar.date_month,
(SUM(CASE
WHEN ((fact_table.termination_date >= ‘2022-01-01') AND ( fact_table.termination_date <= '2022-12-01') THEN 1), ELSE 0) AS " fact_table. termination"
FROM fact_table
LEFT JOIN Calendar ON (Calendar.date = fact_table.termination_date)
WHERE filters…
-------------------------------------------
And the staff count has to be a combination with these two querys
------------------------------------------
The thing is that we want to have one calendar in the explore (because if we don’t do like this, it would not be eassy for the end user to make a table), we want a relationship with the calendar (becasuse if not we would be able to make a distribution by month like in the table).
We have been trying a lot of thing and we can’t find a way that works for what we want to show. we tried: merge (messy for the explore user), derivated table (but is too long and doesn’t allow looekr to do that way),liquid syntax for a dynamic join(we haven't gotten it to work), measures/filter/parameter with logical temporally (but the sql relationship don’t change to both measures or then when we do the distribution time in month by calendar doesn’t make sence or doesn’t filter correctlly).
Someone has any idea that could work? Thank you for your help!!!
