Multi-Level Dimension

Hello

I am constructing an Working Status Dimension that will contain the following

Working Status Group Working Status
EMPLOYED FULLTIME
CASUAL
PARTTIME

UNEMPLOYED CEASED
RETIRED
OTHER

Is it best to have a parent key in the dimension record so that the record representing the status (e.g "Employed - Full Time" will have a parent key pointing to the parent record "Employed" or keep everthing in one record (see below)

Key Description Group
1 Unknown Unknown
2 Employed - Full Time Employed
3 Employed - Casual Employed
4 Employed - Part Time Employed
5 Unemployed - Ceased Unemployed
6 Unemployed - Retired Unemployed
7 Unemployed - Other Unemployed

Regards

Tim

Keep it all in one record (row). I think you're asking whether snowflaking is a good idea and the answer to that is no.

Thanks for that, I was thinking more of recursion, a key in the table pointing to the primary key of the same dimension rather than having a separate dimension for the group attributes

I don't follow why you would want a recursion here. The situation is far too simple for that.

You basically have a handful of statuses. There is no hierarchy. The fact that some represent employed or not employed is usually represented as attributes of the status.

Thanks