I’m currently trying to build a model with different granularities and I have several questions regarding LookML granularity.
In general: Is it possible to generate aggregated measures in one view based on one or more dimensions? (without using derived table method)
So the data is at most detailed granularity. Table contains data showing reservation records of rooms with key info. Example showed below:
What need to be achieved:
Utilization: percentage of (used_min/daily_usable_min) or (used_min/monthly_usable_min)
Because data is at reservation level, so the utilization per room per day is the sum of (used_min/daily_usable_min) group by date and room_id. Can I do this in LookML without derived table?
In the end, I want to create things like:
heat map of daily or monthly utilization.
For daily, it would be hour_of_day and day_of_week, the utilization is the average of utilization of all rooms at given hour of day and day of week.
daily or monthly utilization trends.
Also be able to filter by location or room or date.
Thanks for your inputs!