December 31, 2009 at 2:49 am
Hi,
I'm working on the prototype for a data warehouse / BI solution for a Hospital. I'm currently designing the Patient Admissions fact and related dimensions. The following is the structure of the tables:
FACTS_ADMISSIONS
ADMISSIONS_KEY INT --Surrogate key
ADMISSIONS_CODE VARCHAR(50) --Business Key in ODS
PATIENT_KEY INT --Foreign Key to DIM_PATIENTS dimension
ADMISSIONDATE DATETIME --Date dimension
ADMISSIONDATE_KEY INT --Foreign Key to DIM_DATES dimension table
DIM_PATIENTS
PATIENT_KEY INT --Surrogate Key
PATIENT_CODE VARCHAR(50) --Business Key in ODS
PATIENT_NAME VARCHAR(200)
GENDER VARCHAR(20)
AGEGROUP VARCHAR(20)
BLOODGROUP VARCHAR(20)
PATIENTTYPE VARCHAR(30)
NATIONALITY VARCHAR(30)
MARITALSTATUS VARCHAR(30)
SCDSTATUS VARCHAR(15) --Used for SCD. Will indicate if this is a current or expired record
STARTDT DATETIME --Used for SCD. Starting date for the record
ENDDT DATETIME --Used for SCD. Ending date for the record. Set to '31/12/9999' for Current Records.
I now need to incorporate the Patient Contacts into this. I was initially thinking of adding this as a snowflake, but it got VERY confusing, so I decided to put it up here and ask advice from the forum. Let's take for example that the following is the table structure of the DIM_PATIENTCONTACTS table:
DIM_PATIENTCONTACTS
PATIENTCONTACT_KEY INT
PATIENTCONTACT_CODE VARCHAR(50) --business key in ODS
PATIENT_CODE VARCHAR(50) --foreign key to the business key of the DIM_PATIENTS table
COUNTRYNAME VARCHAR(50)
STATENAME VARCHAR(50)
CITYNAME VARCHAR(50)
STREETADDRESS1 VARCHAR(200)
STREETADDRESS2 VARCHAR(200)
OFFICENUMBER VARCHAR(15)
OFFICEEXTENSIONNUMBER VARCHAR(10)
RESIDENCENUMBER VARCHAR(15)
MOBILENUMBER VARCHAR(15)
FAXNUMBER VARCHAR(15)
EMAILID VARCHAR(100)
SCDSTATUS VARCHAR(15) --Used for SCD. Will indicate if this is a current or expired record
STARTDT DATETIME --Used for SCD. Starting date for the record.
ENDDT DATETIME --Used for SCD. Ending date for the record. Set to '31/12/9999' for Current Records.
I was not sure about incorporating the contact details into the DIM_PATIENTS table because there might be quite a few nulls in the Patient Contacts data.
One of the reports I have to design is the 'Admission Date'-wise contact details of the patients. Now I'm really confused about what to do, because of the following reasons:
1.The contact details can change at any time, and history has to be maintained. I would therefore have to implement SCD here.
2.The data to be displayed would be Contact Details (OFFICENUMBER, FFICEEXTENSIONNUMBER, RESIDENCENUMBER, MOBILENUMBER, FAXNUMBER AND EMAILID. I guess I would therefore have to add these as measures.
3.WRT (2), I vaguely remember seeing some SSAS video where such details can be displayed by doing a Mouse-Over on the Measure. Can I achieve this if I capture the contact details as measures?
My main confusion is that while (1) requires SCD to be implemented, (2) requires these fields to be displayed as measures. I'm therefore confused about how to design this.
Kindly help!
December 31, 2009 at 2:15 pm
You would definitely want to use SCD Type 2 for the contact info. I would personally never attempt to use these as measures, they are completely non-additive, and not even in the 'normal' sense of non-additive. 🙂 You should be able to use these as properties, basically attributes that are related to other attribute dimensions but not displayed/available to the end user, other than when hovering over the current attribute value (in say Excel or even PerfPoint).
Ignoring the fact that you 'can' do the hover for the info, as your primary requirement is a report showing the contact info/details 'as-at' the Admissions date, I think what you have is more of a TSQL problem than a cube or even data warehousing one. As long as you're storing the contact info in a SCD II, then you should be able to hook it to your admissions data and produce the required report.
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply