Repeat attribute in two dimensions?

  • 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?

  • 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.



  • 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