Each case has a status code (O - Open, C - closed etc).
I plan to have a dedicated status dimension to hold these translations to the user can easily see cases by status.
I also have a type 2 scd case dimension to store case related data. I wish to include the status on this table so any changes over time are tracked. I feel different questions can be answered using both dimensions.
I can look up the description in the ETL process and write this to the table so the overhead is minimal.
I want to avoid snowflaking and keep the model as straightforward as possible.
Can anyone see any issues with this from a design perspective?
I mean I could drop the status dimension completely as the fact table will have the key to the case dimension table. In this case I would also need to have the status code in the case dimension table so I can grab the surrogate key.
The users could still use this dimension to split cases by status I guess?