Historisation of Registration fact

View previous topic View next topic Go down

Historisation of Registration fact

Post  ATran on Tue Jul 27, 2010 3:36 pm

Let's take the Student registration events as fact table with a mesure : cost

So we have one row for each registered course by student and term.

What if the cost can change and we want keep those change : that will become SCD2 ... but SCD is for dimension and not fact table.

Is it possible to track change for fact tables ? do we need to change it to a dimension ?

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  ngalemmo on Tue Jul 27, 2010 3:47 pm

What do you mean by cost? What the student paid or what is charged (price) for the course?

What the student paid is a measure, the price of the course is a dimensional attribute.

But, to your question, sure... you can track history in a fact table. The is what a transactional fact table is. You can also have accumulating snapshots where different versions of a fact are bounded by effective and expiration dates. There are other variations as well.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Historisation of Registration fact

Post  ATran on Tue Jul 27, 2010 4:20 pm

Thanks for the quick reply

Cost was the mesure (What the student paid).

i don't understand well, how we can track history in a fact table when the mesure change.
The grain is : one row for each registered course by student and term.
So if the measure change, then it is still the same registration : same course/student/term
How can i add a new line with the new mesure (and i guess startdate/endate/iscurrent column)

The probleme with accumulation snapshots is that we need a number of predefined step. if we can have unlimited change for one registration, it won't work

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  ngalemmo on Tue Jul 27, 2010 4:48 pm

What is the relationship between courses a student enrolled in and what was paid? Is this a college or a trade school? Is registration and payment actually two different events? What history are you trying to track? Enrollment or payment?


This may best be modeled using two fact tables, one tracking the money (charges, payments, adjustments) and the other tracking enrollment... its hard to say based on what you have described.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Historisation of Registration fact

Post  ATran on Wed Jul 28, 2010 12:02 pm

this was a general question base on the fact in the datawarehaouse toolkit book : what if we want to track change in the fact table.

My personal (simplified) exemple is:
In company (banque or aeronotic ...), employees can register and attend courses/session.

For each registration, there are cost associated that are calculated based on different field ( field from employee, from dev plan ...) and also the status of the registration (confirm,cancel,waiting for approval ...), attendance status, attendance hour etc

what we need are for exemple :
-analyse those cost, by company, grade, diploma,course ...
-track change for the registration (status, différent cost/mesure)

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  ngalemmo on Wed Jul 28, 2010 12:30 pm

There are two basic techniques for tracking change in a fact table: accumulating snapshot or transactional (delta) facts.

The accumulating snapshot approach adds effective and expriration date columns to the fact. When a fact is updated you insert a new row with the updated information and adjust the expiration date on the previous row.

In a transactional approach you add a transaction or "as of" date column and calculate the net change and insert new rows. You wind up inserting one or two rows depending on wither or not dimensional references have changed as well.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Historisation of Registration fact

Post  D_Pons on Thu Jul 29, 2010 3:30 am

The accumulating snapshot approach adds effective and expriration date columns to the fact. When a fact is updated you insert a new row with the updated information and adjust the expiration date on the previous row.

I'm a bit confused by this idea.
I'd understood that an accumulating snapshot held fact rows that were updated as dates for specific events were captured. Thus there is only one row for each 'fact'.
If there are new rows each time there is a new bit of information, how are the measures handled to make them additive? Or is the intention here to filter on facts that were 'current' at the time you are interested (or filter on the specific event of interest)?

D_Pons

Posts : 16
Join date : 2009-02-10
Location : UK

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  hang on Thu Jul 29, 2010 8:19 am

Looks like you may need a time-stamped employee transaction dimension to track the changes for employees' course study related attributes, and in this case the cost and attendance hour are just attributes in the dimension. Similar to what ngalemmo suggested for the fact table, you need effective and expiration date pair in the dimension to indicate the valid date range for a set of attribute values related to the course registration and attendance.

While the above dimension alone can provide a whole range of analysis possibilities related to the course registration, you would however need another periodic snapshot fact table to support your measurement aggregation where you can have a period key (eg. month key), employee transaction key, organisation key, cost and attendance hour (as of the end of the period).

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  ATran on Thu Jul 29, 2010 9:33 am

+1 for ponzie. I have the same kind of understanding.

hang : what your are saying is having a dimension scd2 instead of having a fact table + one periodic snapshot fact table.
The dimension will have FK to other dimension ?

I have difficulties understanding the solutions proposes

ATran

Posts : 11
Join date : 2010-03-25

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  ngalemmo on Thu Jul 29, 2010 11:44 am

Substitute "fact" for "version of a fact". The common use for an accumulating snapshot is to record states (such as enrollment or membership) with effective dates. As a member moves in and out, there would be multiple rows with unique effective ranges.

Apply the same concept to an order line... you have an order fact table with effective dates. As an order line changes, you insert a new row and adjust the effective date period of the previous version. The fact table is recording states (versions) of the order line with unique effective date ranges. It allows you to look at an order as it existed at any point in time. Any query of the fact table needs to be bound by a specific as of date.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Historisation of Registration fact

Post  BoxesAndLines on Thu Jul 29, 2010 1:33 pm

Another option would be to take daily snapshots of the accumulating snapshot. This can give you day over day history of the measure changes.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Historisation of Registration fact

Post  hang on Fri Jul 30, 2010 8:50 am

Normally the employee dimension is relatively small dimension, so your employee transaction dimension can be the main employee dimension storing all the relevant attributes including the course related attributes. Don't get distracted by the numeric values in dimension. If their changes need to be tracked, they should be treated like normal SCD2 attributes including other course related textual attributes which will be used to provide the source of query constraints and report labels for your matrix. Don't be concerned about having other attributes repeated only because of the frequent changes in one group of attributes, unless it is a huge dimension. Denormalizing dimension is one of keys to dimensional modeling.

If you are dealing with a big employee dimension (>100,000), or if you have strong reason to separate course related attributes from the employee dimension because they may have quite different load patterns, you may have a employee-course dimension with proper SCD2 process to keep change history and have the FK in the main employee dimension pointing to the current employee-course profile. You could also connect all the historical changes together through self join on the natural key (employee id). Kimball calls this type of dimension an outrigger.

As mentioned earlier, you will need a fact table to analyse/aggregate numeric values, the cost and hours in your case, and that's the primary purpose of the fact table, just like tracking the change history is one of the primary goals for dimensions. The periodic snapshot fact is really an event/change driven snapshot. Depending on at what granular level you want analyse the measurs, you may load the snapshot at certain point of change, say when the status changes to approval so you can apply the final cost to the analysis.

The fact table should only contain a date key and other dimension keys plus course related numeric values. Your fact source is mainly from the dimensions. You could have both employee key and employee-course key in the fact table, and you may also use self join on the natural keys to refer to the current profile on the dimensions. Hopefully this is clear enough.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Historisation of Registration fact

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