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

Creating a data model for pregnancy

4 posters

Go down

Creating a data model for pregnancy Empty Creating a data model for pregnancy

Post  AndyBarber Mon Mar 21, 2011 11:55 am

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)


Should I create a fact for each stage of the pregancny and include a summary fact for cross pregnancy aggregated data


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,



Posts : 2
Join date : 2011-03-18

Back to top Go down

Creating a data model for pregnancy Empty Re: Creating a data model for pregnancy

Post  Jeff Smith Mon Mar 21, 2011 1:12 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Creating a data model for pregnancy Empty Re: Creating a data model for pregnancy

Post  BoxesAndLines Mon Mar 21, 2011 9:26 pm

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.

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Creating a data model for pregnancy Empty Re: Creating a data model for pregnancy

Post  ngalemmo Wed Mar 23, 2011 12:15 pm

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

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

Back to top Go down

Creating a data model for pregnancy Empty Re: Creating a data model for pregnancy

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