Problem while Designing Fact table

View previous topic View next topic Go down

Problem while Designing Fact table

Post  cognos on Mon Dec 26, 2011 8:37 am

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.


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 .



Posts : 1
Join date : 2011-12-26

View user profile

Back to top Go down

Re: Problem while Designing Fact table

Post  BrentGreenwood on Wed Dec 28, 2011 9:52 am

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.


Posts : 6
Join date : 2011-12-25
Location : San Diego, CA

View user profile

Back to top Go down

Re: Problem while Designing Fact table

Post  BoxesAndLines on Wed Dec 28, 2011 11:15 am

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

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

View user profile

Back to top Go down

Re: Problem while Designing Fact table

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