Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design Troubles - Patient Contacts Details to be displayed as Measure


Design Troubles - Patient Contacts Details to be displayed as Measure

Author
Message
naramsg
naramsg
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 201
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!
stevefromOZ
stevefromOZ
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: Moderators
Points: 2268 Visits: 3757
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search