Data model for Reporting needs - Event based or fact based

View previous topic View next topic Go down

Data model for Reporting needs - Event based or fact based

Post  dwcurious on Mon Jan 21, 2013 4:03 pm

I have a set of reports that display # of Patients in a clinic for a date. There many other scenarios as well, all with some metrics or the other. The table that has been given to us is more at an event level, which is a record for a Patient rather than dimensional i.e. number of patients. When we asked for a Dimensional model which has a fact with all the metrics and corresponding dimensions such as date & clinic, we were told to aggregate the events at the reporting layer. They stressed it by saying the data is only few thousands per year. Is it acceptable or should we stress for a Star Schema with a fact table with metrics

dwcurious

Posts : 20
Join date : 2011-04-14

View user profile

Back to top Go down

Re: Data model for Reporting needs - Event based or fact based

Post  umutiscan on Thu Jan 24, 2013 7:34 am

I always prefer to store this kind of data at transaction level and aggregate the events in reporting level, if I do not have any performance issues.It makes your model more adaptable for additional dimensions.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

The grain of the fact table can be 1 row for each patients visit to a clinic

Post  rafi asraf on Thu Jan 24, 2013 8:58 am

Fact table can be on the event level and still be part of a star schema in a dimensional model. It is also the recommanded thing to do, when all you want is to count.

Sometimes event count are non interesting for reporting, but rather to what happens between events (duration, etc) e.g. if we have data for when the doctor started the home visit, and when the doctor left the patient's home. In those cases, I'll advocate to create a fact table that translates events to fact table that can aggregate duration, hense translating events to visits.

Regards
Rafi Asraf

rafi asraf

Posts : 5
Join date : 2012-04-17

View user profile

Back to top Go down

Re: Data model for Reporting needs - Event based or fact based

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