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

Is accumulating fact table the right design?

3 posters

Go down

Is accumulating fact table the right design? Empty Is accumulating fact table the right design?

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

Back to top Go down

Is accumulating fact table the right design? Empty Re: Is accumulating fact table the right design?

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

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

http://aginity.com

Back to top Go down

Is accumulating fact table the right design? Empty Re: Is accumulating fact table the right design?

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

Back to top Go down

Is accumulating fact table the right design? Empty Re: Is accumulating fact table the right design?

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

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

http://aginity.com

Back to top Go down

Is accumulating fact table the right design? Empty Re: Is accumulating fact table the right design?

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

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

Back to top Go down

Is accumulating fact table the right design? Empty Re: Is accumulating fact table the right design?

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