Problem: I am working with a SaaS company that provides monthly services. We are trying to create a data model to track customer related metrics such as count, signups, cancellations, and reactivations. I’ve done extensive research online, but the closest I’ve found is accumulating snapshots with start/end dates, which doesn’t make sense with a SaaS company where a customer can reactivate an account.
My initial thought is to create a Factless Fact table for customer, however this factless table would also have keys to event dimension tables, I.e. DimSignupType, DimCancellationType, DimReactivationType, etc and boolean measures for isSignup, isCancellation, and isReactivation. I think this is counterintuitive because a factless fact table shouldn’t have facts, but I need track those and feel multiple fact tables is worse because I would have to join them together in the view.
Is there a better approach to this problem?
Edit based on feedback: The main goal of this is to create a dimensional model that is maintainable, but also something I create a view for with other dimensional tables that allows less technical users to discover insights with tools like Tableau. At the end of the day I need to provide a large flat view with multiple measures and dimensions that allows for easy analytical discovery. Common questions may be, "How many signups do we have MTD for this customer type vs last mtd?", "How many cancelations did we have due to Non-Payment this month compared to last", "How many reactivations from Non-Payment did we have this month compared to last?", etc. A lot of this meta data comes from Dimension tables I would join to the factless fact table based on keys, however it still requires a focus on Signups, Cancellations and Reactivations being tracked as Facts for reporting purposes. So I don't know the best modelling approach for it that abides by traditional standards. It almost seems like a Snapshot Fact Table that contains keys to dimensional tables that describe events to be aggregated. I just don't know what that would be called.
I feel the most flexible solution in terms of data management and ease of use would be a factless fact table modeled in a daily snapshot manner with "facts" for signup, cancellation and, reactivations that link to types.