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

Data model for Reporting needs - Event based or fact based

3 posters

Go down

Data model for Reporting needs - Event based or fact based Empty Data model for Reporting needs - Event based or fact based

Post  dwcurious 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

Back to top Go down

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

Post  umutiscan 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 : 41
Location : Istanbul, Turkey

Back to top Go down

Data model for Reporting needs - Event based or fact based Empty The grain of the fact table can be 1 row for each patients visit to a clinic

Post  rafi asraf 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

Back to top Go down

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

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