Creating a data model for pregnancy
4 posters
Page 1 of 1
Creating a data model for pregnancy
Hi All,
I've been tasked to create a data model that can report on all aspects of a Women's pregnancy from Past History, through booking, to delivery and discharge from care. Most it it is OK, the area I'm having a problem with is modelling the Fact table.
I am happy with my level of granularity, I will be recording data at the Pregnancy Event level, i.e. a new record every time a patient is seen by staff and data is recorded against her profile in the transactional system. Each stage of pregnancy records a huge set of data against the pregnancy and each stage records different data.
For Example, at booking the data collected could be BMI, Smoker ?, Alcohol Consumption and several other measurements regarding the patients current condition. The reason for collecting this is we'd like to be able to analyse : At booking how many women smoked, year on year.
At Delivery, the recorded data is based around : Equipment Used, Drugs administered, results of numerous tests performed. Again the stats to be produced go along the lines of Most Popular Drugs used, Equipment used, # of deliveries using where this test result = "" .
My questions really is whether I should have one BIG fact table (there are over 300 different pieces of information gathered over the course of a pregnancy that don't fit well in a Dimension)
or
Should I create a fact for each stage of the pregancny and include a summary fact for cross pregnancy aggregated data
or
Can I build a Dimension with the majority of the data from the Fact, which are not measures but their combination will be unique per person/pregnancy.
Can anyone suggest what I should try ?
Many Thanks,
Andy
I've been tasked to create a data model that can report on all aspects of a Women's pregnancy from Past History, through booking, to delivery and discharge from care. Most it it is OK, the area I'm having a problem with is modelling the Fact table.
I am happy with my level of granularity, I will be recording data at the Pregnancy Event level, i.e. a new record every time a patient is seen by staff and data is recorded against her profile in the transactional system. Each stage of pregnancy records a huge set of data against the pregnancy and each stage records different data.
For Example, at booking the data collected could be BMI, Smoker ?, Alcohol Consumption and several other measurements regarding the patients current condition. The reason for collecting this is we'd like to be able to analyse : At booking how many women smoked, year on year.
At Delivery, the recorded data is based around : Equipment Used, Drugs administered, results of numerous tests performed. Again the stats to be produced go along the lines of Most Popular Drugs used, Equipment used, # of deliveries using where this test result = "
My questions really is whether I should have one BIG fact table (there are over 300 different pieces of information gathered over the course of a pregnancy that don't fit well in a Dimension)
or
Should I create a fact for each stage of the pregancny and include a summary fact for cross pregnancy aggregated data
or
Can I build a Dimension with the majority of the data from the Fact, which are not measures but their combination will be unique per person/pregnancy.
Can anyone suggest what I should try ?
Many Thanks,
Andy
AndyBarber- Posts : 2
Join date : 2011-03-18
Re: Creating a data model for pregnancy
Isn't the information recorded during the booking really characteristics of the patient and not characteristics of the treatment?
This sounds like 2 facts - one for treatment and one for patient. At some point, you may want to link the patient info to the treatment. You may even be able to put the characteristics in a dimension table that is flattened out - 1 column for each important trait, and attach this dimension onto the treatment fact table.
I think the test results are a different fact as well. Again, you may be able to use the information in the test fact to classify patients.
This sounds like 2 facts - one for treatment and one for patient. At some point, you may want to link the patient info to the treatment. You may even be able to put the characteristics in a dimension table that is flattened out - 1 column for each important trait, and attach this dimension onto the treatment fact table.
I think the test results are a different fact as well. Again, you may be able to use the information in the test fact to classify patients.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Creating a data model for pregnancy
I'm not quite sold on your design. This seems like there are a lot of fact tables involved here, not just one. There could be a multitude of drugs administered during the final visit. That fact alone causes you grain issues.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Creating a data model for pregnancy
I'm with B&L on this.
It appears there is a lot going on and I would create facts based on the type of activity being tracked.
The visits and delivery seem to be pretty much claims and encounters data. All that should be covered the way claims are modeled anywhere else. As far as the state of the patient, that would be its own fact, possibly one row per condition being tracked with date ranges. You may also want a fact to track outcomes...
It appears there is a lot going on and I would create facts based on the type of activity being tracked.
The visits and delivery seem to be pretty much claims and encounters data. All that should be covered the way claims are modeled anywhere else. As far as the state of the patient, that would be its own fact, possibly one row per condition being tracked with date ranges. You may also want a fact to track outcomes...
Similar topics
» creating dimensional model of log data
» Creating YTD, PTD provision in Data Model
» Question - creating a dimensional model for incident management
» Question - creating a dimensional model for facility management
» Newbie Creating a Data Mart
» Creating YTD, PTD provision in Data Model
» Question - creating a dimensional model for incident management
» Question - creating a dimensional model for facility management
» Newbie Creating a Data Mart
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|