Problem while Designing Fact table
3 posters
Page 1 of 1
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
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- Posts : 1
Join date : 2011-12-26
Re: Problem while Designing Fact table
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.
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.
Re: Problem while Designing Fact table
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
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Need help in Fact table designing
» Designing a Fact for Sales DW
» Bridge Table Problem in T-SQL
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» Designing a Fact for Sales DW
» Bridge Table Problem in T-SQL
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum