New fact table or new dimension

Question

I have a fact table with 2 dimensions:

• cohort_id
• date_id

Each record contains the amount of active users from a specific cohort at a particular date - typical structure for the cohort analysis.

Currently, a cohort is defined as a group of users signed up at the same date. I would like to start additionally storing the same data, but with a bit different definition of the cohort - a group of users with the first order at the same date.

I have a conceptual dilemma: is it better to create a new fact table with the same structure or to add a new dimension (cohort_type_id) to the existing fact table?

Answer 1

You haven't given a lot of details, but on the face I'd say make it a new fact table. It sounds like your cohorts will be entirely distinct, so it wouldn't be meaningful to total or average across both sets. Your records won't have any overlap in dimensions (except Date, which is almost universal), so they're really distinct things, they just happen to have similar fields.