August 29, 2013 at 4:40 am
Hi All,
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?
October 8, 2013 at 4:58 am
Why would you not have just a single Status dimension which would include (as a minimum) StatusKey, StatusCode (the natural key, e.g. 'O' and 'C' in your example) and StatusDescription? To handle a Type 2 SCD you would then just need to add a row with a new StatusKey.
The Case data would be stored in a Fact table surely rather than a Dimension? The Fact table would include a StatusKey column which would be a FK to the Status dimension.
If the Status is used in more than one context in the same or different Fact tables then it becomes a role-playing dimension.
Regards
Lempster
October 9, 2013 at 3:30 pm
Status tables are normally very straightforward, although you don't give enough information to know if yours is an exception. An extract from mine would look like
Five columns
| | | | The Operational key goes in this column and is used to determine the surrogate key value.
1 Closed Completed Completed Per Installer
4 Closed Completed Some other comment
8 Closed Cancelled Cancelled by customer
15 Open Scheduled By Phone
19 Open On Hold Missing equipment
Hope that helps. My operational key is actually the second column. I was trying to do this from memory.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply