Patient Dimension

Hi.

I have a Patient dimension table with the following columns:

patient_number (integer, primary key)
patient_code (varchar, unique)
patient_title (varchar)
patient_surname (varchar)
patient_firstname (varchar)
patient_middlename (varchar)
patient_sex (varchar)
patient_date_of_birth (date)
patient_address 1 (varchar)
patient_address 2 (varchar)
patient_address 3 (varchar)
etc

I'd like some advice on how to be structure this. I'm thinking:

Patient (Dimension)
--Patient Code (Hierarchy)
----Patient Code (Level)
--Patient Name (Hierarchy)
----Patient Surname (Level)
----Patient Firstname (Level)
----Patient Middlename (Level)
----Patient Title (Level)
--Patient Sex (Hierarchy)
----Patient Sex (Level)
--Patient Date of Birth (Hierarchy)
----Patient Year of Birth (Level)
----Patient Month of Birth (Level)
----Patient Day of Birth (Level)
--Patient Address (Hierarchy)
----Patient Address 3 (Level)
----Patient Address 2 (Level)
----Patient Address 1 (Level)

Any suggestions/criticisms/improvements?

One problem I have with this design is around the implementation of Patient Date Of Birth. I'm creating Mondrian.xml for Pentaho and dimensions can have a standard or time type however the time type for me would need to be available for the Patient Date Of Birth hierarchy, which is why I need to first ask the question about whether my design is correct.

Regards,
Stephen.

Hi Stephen,

I havent seen much of Pentaho but from a generic analysis perspective I would assume that all of the attributes you listed would best be presented without any hierarchy.  Most appear to have a single level, and I imagine none of the multi-level hierarchies would in reality contain rigidly hierarchical data. If that is the case then attempting to build multi-level hierarchies will be awkward for both design and navigation/analysis.

Also Date attributes are usually presented via a Date Dimension with many other Date-derived attributes.  This usually offers more flexibility for analysis and if your Date Dimension is conformed than any enhancements you make have a broad impact.

Good luck!
Mike