Problem while Designing Fact table

Hello Experts,

I have a requirement where I have to show user Total of a measure for selected week, selected week-1, Selected week -2, 12 weeks average , YTD values as a row.

Example

Consider a table with column names as below (in the same order)

Product , Region, Account , Week , 12 weeks average,Selected week -2,selected week-1,selected week
A USA X 2011-W01 5 3 2 1
A USA X 2011-W02 5 3 2 1


This fact table is built on a transaction table.
Now the problem is If for a Week there is no data , in fact table that does not get recorded Due to this the values in Selected week -2,selected week-1 is not coming as zero.

In short , my problem i have to record rows in every possible combination of all dimensions, which is not a case in transactional fact table. Some one suggested me to insert dummy rows. Is that the only way?

Our users use Cognos as reporting software on which I work ,my etl team is relatively lesser experienced so i would be glad if you can let me know some ways to handle .

Regards

Cognos -

I would recommend handling this in an OLAP layer. You can build calculated measures for those trailing periods. MDX is very capable of time-intelligence calcs like this.

If you force the solution to the relational layer in a physical table, you would need to go with a periodic snapshot fact table. But including trailing period measures like that can get messy fast, especially if your environment has back-dated transactions. You're ETL would have to manage all rows that had trailing period measures that covered a date range including the back-dated date.

Hope that helps.

Or you could build a coverage factless fact table that contains events that did not occur. Ralph had a link to this article but it no longer appears to be working. Broken Link