Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Repeat attribute in two dimensions? Expand / Collapse
Posted Thursday, August 29, 2013 4:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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?
Post #1489636
Posted Tuesday, October 8, 2013 4:58 AM


Group: General Forum Members
Last Login: Wednesday, January 27, 2016 7:06 AM
Points: 2,657, Visits: 1,657
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.

Post #1502545
Posted Wednesday, October 9, 2013 3:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 1, 2016 7:28 AM
Points: 1,193, Visits: 3,278
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.

Post #1503315
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse