December 15, 2018 at 11:15 am
im tracking attendance of courses. There is a user dimension, course dimension and a fact table.
I need to track if a user attended a course and if they did not attend a course.
I was thinking of storing a record for each user and course in the fact table, one if they attended and one if they did not.
Should i store non attendance records or should absence of a record indicate non attendance?
Note: source data is limited so i dont have the date of course completion.
I am building this in ssas tabular.
December 15, 2018 at 1:09 pm
Can't you do something like FILTER() on the DimCourse table to get just the courses you want, then use CROSSJOIN, which creates a Cartesian Product?
https://docs.microsoft.com/en-us/dax/crossjoin-function-dax
December 15, 2018 at 1:42 pm
yes, i probably can. I guess im asking is there any benefit to storing a non-event, in this case, the non attendance at a course?
is there any benefit of storing this instead of inferring lack of a row in the fact table as being absence?
Note I have to get the percentage of a managers employees who have completed the course. manager can be another dimension or an attribute of dimEmployee
December 15, 2018 at 1:49 pm
Others may disagree, but I don't know why you would store an instance of a non-event. It's going to make your fact table much larger than it could be. And since you can generate something like that using CROSSJOIN, I don't see any advantage in doing that. At least if you use CROSSJOIN, the set only exists for the duration of the query.
December 15, 2018 at 3:03 pm
perfect, exactly what i needed, i wanted to see what pros/cons there were of storing the non events, as they are in the source data. they are some good reasons not to store the non event record.
Thank you for your expertise!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply