Historisation of Registration fact
5 posters
Page 1 of 1
Historisation of Registration fact
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 ?
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
Re: Historisation of Registration fact
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.
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.
Re: Historisation of Registration fact
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
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
Re: Historisation of Registration fact
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.
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.
Re: Historisation of Registration fact
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)
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
Re: Historisation of Registration fact
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.
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.
Re: Historisation of Registration fact
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
Re: Historisation of Registration fact
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).
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
Re: Historisation of Registration fact
+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
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
Re: Historisation of Registration fact
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.
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.
Re: Historisation of Registration fact
Another option would be to take daily snapshots of the accumulating snapshot. This can give you day over day history of the measure changes.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Historisation of Registration fact
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.
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
Similar topics
» Registration Fact, many instructors per fact row.
» How to design student quarterly registration fact table
» Student Course Registration Fact and Dimension Tables : How to model
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» How to design student quarterly registration fact table
» Student Course Registration Fact and Dimension Tables : How to model
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|