Design Question on Clinical trial data model

Hi All,
I am facing difficulty designing personnel dimension in the clinical trial data mart. A clinical trial is conducted in one or more countries. Each country can have one or more clinical sites where patient gets treatment. A trial involves different kinds of personnel who plays different kind of role in the different level. For example, some personnel plays role in the clinical site level activity, some personnel play role in the country level activities and some personnel plays role in the trial level activities. Also it is possible that same personnel can play role play in both country and site level activities.

We have three conformed dimensions dim_trial, dim_country, and dim_clinical_site. We have facts in the trial level grain, we have some facts in the country level grains and some facts in the site level grain.
I am facing problem designing facts which are site personnel, country personnel and trial personnel level grain.

How can we design the personnel dimensions which can be linked to site personnel grain facts as well country personnel grain facts.


Your answer will be appreciated.

You can design party/personnel dim as below
Party Dim Attributes (a big denormalized party related entity)
    --Role Type Code (to store different parties e.g :site personal, trial personal, patient, country personal)
    --Along with all attributes from different role types.


I would recommend the below dimensions to the fact tables (i know you already have conformed dimensions being used). You
can choose to create different fact tables for each site, country and trial level fact (if the grain are different)
One of the fact (Trail level activity grain) could be
--Clinical Site Dim
--Geo Dim (Clinical geo along with different hierarchy which might vary from one country to another)
--Trail Dim
--Party Trial Dim (trial personnel role played)
--Party Site Dim (Site personnel role played)
--Trnsactions (factual measure - additive or non-additive)

Please do let me know if this helped.

Hi,
Thanks a lot for your reply. I would like to understand how you can create a role playing dimension personnel in the trial, country, site level. For example, I have three facts trial_personnel_facts, SUBJECT_AGE_MIN, site_personnel_facts. These three facts are in different grain. As a report writer, I would also like to know which personnel are assigned to trial, country and site level. There is a possibility that the personnel who are assigned to country level may not have any record in trial_personnel_facts table. Same is true for the country and site personnel.
Should we create one DIM_PERSONNEL dimension and link that dimension with a bridge table DIM_TRIAL_PERSONNEL, DIM_COUNTRY_PERSONNEL, DIM_SITE_PERSONNEL where DIM_TRIAL_PERSONNEL is a bridge table between DIM_TRIAL and DIM_PERSONNEL, and DIM_COUNTRY_PERSONNEL is a bridge table between DIM_COUNTRY and DIM_PERSONNEL, and DIM_SITE_PERSONNEL is a bridge table between DIM_CLINICAL_SITE and DIM_PERSONNEL.
Then link DIM_TRIAL_PERSONNEL with trial_personnel_facts, DIM_COUNTRY_PERSONNEL with country_personnel_facts, and DIM_SITE_PERSONNEL with site_personnel_facts

Here are answer to your queries:
1) Since the attributes related to any personal (be it Site, Country or trail) essentially remains the same, so it should reside in a single party/personel Dim.
2) Bridge table : Whether you need a bridge table depends on whether there are any M:M relationship or not. In this case its not and we dont want any bridge.
Rather the relationship is from a fact to dimension, the fact would say whether its a site, trail or country personal.
3) ETL which populates fact would need a logic to decide which personel should be tied to the respective fact table.So each fact table at any point in time would only be referencing to its corresponding role played dimension. For eg, a site fact would point to site personel ONLY and would never reference a trail or a country personel in the party dim.

Here is a classic example of how a role played dimension works in a fact table (refere below sample fact table columns)
LN_DIM_ID
PRTY_SELLER_DIM_ID (Points to party dim who role plays as Seller)
PRTY_LENDER_DIM_ID (Points to party dim who role plays as Lender)
PRTY_BORWER_DIM_ID (Points to party dim who role plays as Borrower)
TIME_DIM_ID
LN_AMT
CREATE_DT

Hope I answered your question.

Hi,
There are many to many relationship between DIM_PERSONNEL and DIM_TRIAL, DIM_PERSONNEL and DIM_COUNTRY, and DIM_PERSONNEL and DIM_CLINICAL_SITE. Do you still think, we don't need bridge table?

These many to many would be resolved through the fact table. I would not recommend a bridge table for this scenario.

How can these many to many relationship would be resolved through fact table when person has no facts?

Ok looks like I am not getting your question completely. Can you give example of any of the site/trial/country relationship with personnel? Please be more specific.

Hi,
Thanks once again. Below is the example.
In the DIM_PERSONNEL table there are different kinds of person with different occupation. Some are doctors, some are nurse, some are investigators, project leader, study co-coordinator etc.
Now let us take a look how person plays role in different level. Suppose person John plays study coordinator role in the trial 'Omeprozol safety Study' and plays CRA role in trial 'Nexium safety study'. When John plays study coordinator role in the trial 'Omeprozol safety Study', then he has no associated facts in the 'Omeprozol safety Study'. However, when John plays CRA role in the 'Nexium safety study', then John has to go for clinical site visit for the sites where he has been assigned as a CRA role. Here point to note down is that although John has been assigned as a CRA role to a site for example 'XYZ', he might not go for site visit for this site. The same person John can play country coordinator role in 'Nexium safety study' for a specified country for example USA.

Let me know whether above explanation helps.

Regards,
Narendra

quick question : How many different role can a party play for a Trail event or for a country event. I am just thinking if we can have all the
party (role played in one fact) maybe also think of creating a factless fact just to track the various roles played by different parties if thats a specific reporting requirment.