Is accumulating fact table the right design?

View previous topic View next topic Go down

Is accumulating fact table the right design?

Post  businessintelligence on Mon Aug 03, 2015 1:57 am

Guys we would like to analyze a university scheduling process as it goes through different stages. First a CRN is created, then it is scheduled, after this teachers are assigned and finally students are registered.

However it is not a linear process for example I can register students even though teachers are not assigned. Users want to know how many CRN'S are created, how many scheduled, how many don't have teacher assigned and how many don't have students registered.

We already have a section dimension. Do you think an accumlating fact table is the answer or it is better to expand the section dimension to have these attributes which is Is Section Scheduled (Y/N), Is Teacher Assigned (Y/N), Is Student Registered (Y/N)

Thanks

businessintelligence

Posts : 14
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Is accumulating fact table the right design?

Post  ngalemmo on Mon Aug 03, 2015 2:31 am

Hard to say. If they want a history of when the different actions took place, an accumulating fact is the correct choice. If all they want is the state of the section, then the dimension flags make sense. The latter is also simpler to query.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is accumulating fact table the right design?

Post  businessintelligence on Mon Aug 03, 2015 2:39 am

Thank you for your help. I assumed that an accumulating fact table is typically used in a linear process for example in orders where an order gets created, billed, shipped and finally delivered. In such a scenario and order cannot be delivered before it is billed.

If I apply the same concept to my scenario I can still have students register even though the instructors are yet to be assigned. Do you think my understanding is correct for the accumulating snapshot. If not can you let me know what kind of questions would one be able to answer if we go for the acumlating fact instead of expanding the section dimension.

Thanks

businessintelligence

Posts : 14
Join date : 2015-06-23

View user profile

Back to top Go down

Re: Is accumulating fact table the right design?

Post  ngalemmo on Mon Aug 03, 2015 3:02 am

In a typical order scenario, there are multiple fact tables covering orders, fulfillment and invoicing, as each is a separate business process. There could also be an aggregate fact that combines the three (order-to-cash).

The same may be the case for you. You can certainly have an accumulating fact covering the scheduling and assignment process, but enrollment is best handled as a separate fact. In such a case there would be one row per student (section,date enrolled, student,etc...). Instructors would most likely be represented by a bridge (section, instructor) as I assume there could be more than one instructor for a section.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is accumulating fact table the right design?

Post  BoxesAndLines on Tue Aug 04, 2015 12:29 pm

Two things, most accumulating snapshots have an accompanying transaction fact that feeds the designated events. The transaction fact table simply captures all events by date. You should consider including this in your design. Secondly, you need some semblance of sequence for an accumulating snapshot. The two primary metrics are when an event occurred and how much time elapsed between two events (lag time). If events occur haphazardly, calculating lag time becomes problematic.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Is accumulating fact table the right design?

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