Is accumulating fact table the right design?

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

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.

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

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.

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.