Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Starting to model…

3 posters

Go down

Starting to model… Empty Starting to model…

Post  MarkSe 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)
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.


Posts : 5
Join date : 2011-07-21

Back to top Go down

Starting to model… Empty Re: Starting to model…

Post  ngalemmo 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.

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

Back to top Go down

Starting to model… Empty Re: Starting to model…

Post  MarkSe 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.


Posts : 5
Join date : 2011-07-21

Back to top Go down

Starting to model… Empty Re: Starting to model…

Post  KKumar Sat Jul 30, 2011 11:09 am

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


Posts : 22
Join date : 2011-07-29

Back to top Go down

Starting to model… Empty Re: Starting to model…

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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