Documentation - (requirements and mapping) for Fact Tables

View previous topic View next topic Go down

Documentation - (requirements and mapping) for Fact Tables

Post  dwuser30 on Thu Apr 05, 2012 4:55 pm

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.

dwuser30

Posts : 7
Join date : 2010-08-29

View user profile

Back to top Go down

Re: Documentation - (requirements and mapping) for Fact Tables

Post  BoxesAndLines on Thu Apr 05, 2012 5:31 pm

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

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

View user profile

Back to top Go down

Re: Documentation - (requirements and mapping) for Fact Tables

Post  dwuser30 on Fri Apr 06, 2012 1:52 pm

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.

dwuser30

Posts : 7
Join date : 2010-08-29

View user profile

Back to top Go down

Re: Documentation - (requirements and mapping) for Fact Tables

Post  BoxesAndLines on Fri Apr 06, 2012 2:07 pm

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

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

View user profile

Back to top Go down

Re: Documentation - (requirements and mapping) for Fact Tables

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