Documentation - (requirements and mapping) for Fact Tables

This question is regarding how to easily document the requirements/mapping for change capture on Fact tables.

We use the Kimball Source to target excel mapping document for documenting the mapping information. While the template for dimensions allow you to easily specify SCD tpe 1, 2, 3.. etc- it seems that the Fact template does not have the SCD column. Not sure if that was intentional. What is the preferred practise for documenting on the excel template (when one of its dimensions on the fact row has changed on the Fact table) so that the ETL team knows which changes results in new fact rows vs. which changes should be simply overwrritten in the fact row... Do you extend the same SCD column for changes in dimension values on the fact worksheet? or do you describe this sepereately in some other word document? I am leaning towards inserting the SCD column in the FACT sheet. Will probably call it something else.

I haven't seen the fact template, but I would imagine that there would be a fact table type, e.g. snapshot, transaction, accumulating snapshot. Those are the only options. What happens to dimensions is irrelevant to the fact table unless a new event occurs. I don't update or insert anything on the fact table unless an event occurs.

Thanks Box&Lines, Actually that's exactly what I am trying to figure out- how to document requirements for fact table changes (when an event occurs). So that it is easy to document and easy to consume for the ETL team.

Here is a link for the template.
http://www.kimballgroup.com/html/DWLT2content/Ch08%20Physical%20Model%20Template.xls

The fact table comprises of several foreign keys (to dimensions). And I need to specify what should happen when the value of one or more dimensions on the fact row changes. So how do I do that so that it is easy to document and easy to consume for ETL developers... For example: Change in diagnosis on a medical case fact; change in open date of the case; change in the physician of the case (where diagnosis, Date and physician info are all dimensions). Thinking out loud- It seems that I might have to take a two pronged approach to document this - First specify which dimension ids on the fact row are being tracked for change- and then specify in detail what action to take on the fact row for change in each of those dimensions.

Unless somebody has a better approach. Thanks in advance.

You're making it too complex. Process all of your dimension changes then process your fact events. All FK's in the fact table are simply lookups to get the most recent dimension PK. It is a simple as that.