I have log data that records user activity, it is pretty typical stuff.
I have another table with the shift start/stop dates and times per user, again, pretty typical time-keeping data.
I am trying to figure out the best way to handle the relationship between these two data sources. I currently add the start/stop date and time to every record in the log, this allows me to do things like check if log activities were within the hours worked, when the activities occurred in relation to the start/stop times etc. In other words, I modify the log by adding the time-keeping data that relates to the user and date of each log entry.
The problem I have now is that I want to perform some calculations like Average Activities per Hour. To do this I am linking the log and the time keeping tables in Tableau. I would like to know if this is the best way to do it, or if there is a way I can use the timekeeping information that I include in the log to calculate the hours worked per day. I assume it will require an LOD calculation or something similar in order to aggregate the total hours. So that's where it sits.
I tried several approach, but not find better way.
I mean you are right. Considering not using LOD, your approach seems reasonable to me.