Starting to model…

View previous topic View next topic Go down

Starting to model…

Post  MarkSe on Thu Jul 21, 2011 7:15 am

Hi – am a modelling newbie – and am starting to look at modelling of a DW for a Health care system (nothing like jumping in at the deep-end). Am using “The Data Warehouse Toolkit” as my guide…

Have broken down the source database schema into “areas” e.g. Patient, Diagnosis, Treatment etc… to try and simplify the process.

Am starting with the Treatment area.

In the source db, there is a “parent table”
tblTreatment (that contains a unique TreatmentId)
and then several child tables (all with different schema)
tblTreatmentA (has FKey to TreatmentId)
tblTreatmentB (has FKey to TreatmentId)
tblTreatmentC (has FKey to TreatmentId)
tblTreatmentD (has FKey to TreatmentId)
etc
for details about the Treatment a patient receives.

The parent tblTreatment contains few attributes and the child ones each contain a variety of indicators, lookups and comments (as well as ForeignKey back to parent tblTreatment record).

From looking at the toolkit book and also from looking at forum entry (Bridge table for patient diagnosis), this should be modelled as a Multi Valued Dimension – or is there another way ?

If it is done as a Multi Valued Dimension, then does the Treatment Dimension table need to have in it ALL of the fields from all of the child Treatment tables as attributes (users want ALL fields in the DW)

Also, if it is done as a Multi Valued Dimension, as there are free-text comment fields in the child treatment tables, then the dimension table could grow quickly as each Treatment dimension record will probably be unique.

MarkSe

Posts : 5
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Starting to model…

Post  ngalemmo on Thu Jul 21, 2011 11:51 am

Wither it is a multivalued dimension depends on what the fact represents. Diagnosis is usually treated as a multivalued dimension in a claim fact is because claim data is driven by procedures and the same collection of diagnoses apply to all the procedures. I am not sure you have the same situation.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Starting to model…

Post  MarkSe on Fri Jul 22, 2011 7:22 am

thx for your reply.

With regards to the Fact table, I am trying to finalize what and where the Fact table(s) are.

The application is a screening application, where candidates are scheduled to have a screening at a regular intervals (every couple of years)
For each assesment, an appointment is made for the candidate (1:1).
For each appointment, a test is done (1:1).
For each test, results are read (at least once, can be more than once).
For each negative result, 'issues' are recorded (at least once, can be more than once).

With this structure, I was considering having 3 fact tables (for this area of the application)
One table to record assesment, appointment and test details.
Second table to record the results (with FK back to first Fact table - 1 to many)
Third table to record the issues (with FK back to first Results table - 1 to many)

My reasoning for this was that the lowest level of granularity appears to be the issues at the end of this section of the process. Not wanting to create a "centipede" fact table (due to the large number of details to be stored - users want ALL details), thought that splitting the fact tables as described would be the way to go.

with regard to the Treatment table question, i was thinking of either having a Fact table for the Treatment details (with a FK back to Candidate) with Treatment dimensions - or Multi-Valued Dimensions with a bridge to the Candidate dimension.


MarkSe

Posts : 5
Join date : 2011-07-21

View user profile

Back to top Go down

Re: Starting to model…

Post  KKumar on Sat Jul 30, 2011 11:09 am

You should never join fact tables directly. But they can be joined through a conformed dimension

KKumar

Posts : 22
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Starting to model…

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum