tracking the absence of an event

  • winston Smith

    SSCoach

    Points: 19481

    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.

  • pietlinden

    SSC Guru

    Points: 62365

    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

  • winston Smith

    SSCoach

    Points: 19481

    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

  • pietlinden

    SSC Guru

    Points: 62365

    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.

  • winston Smith

    SSCoach

    Points: 19481

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply