How to flatten (de-normalize, spread) fact table with time duration in data warehousing design

Check list of all posts

Context
Based on Kimball methodology, there are three different kinds of fact tables: transactional, periodic snapshot and accumulating snapshot. The case in this document is a special case of transactional fact.  For the sake of explanation, assume that there are action fact table with start date and duration. It is requested to have action days and then compare with objective days. All related metrics are on a monthly basis, or year to date basis. The requirement becomes challenge when an action is cross month end. Therefore flatten fact table is needed in staging load.

Sample

Given action fact table for month August

Dimension columns	Action date	Action Duration
xyz	August 1, 2012	18 days
xyz	August 30, 2012	5 days

Give objective table for month August

Dimension columns	Objective month	Objective Duration
xyz	August	20 days

Then the difference between action days and objective is 3 days, which actually is wrong.

Dimension columns	Month	Action Duration	Objective Duration	Difference
xyz	August	23 days	20 days	23-20 = 3 days

Solution

The solution is to spread all actions on daily basis based on business day, see below

Dimension columns	Action date	Action Duration
xyz	August 1, 2012	1 day
xyz	…………..	1 day
xyz	August 29, 2012	1 day
xyz	August 30, 2012	1 day
xyz	August 31, 2012	1 day
xyz	September 4, 2012	1 day
xyz	September 5 , 2012	1 day
xyz	September 6, 2012	1 day

Now the difference between action days and objective is 0 days, which actually is right.

Dimension columns	Month	Action Duration	Objective Duration	Difference
xyz	August	20 days	20 days	20-20 = 0 days