Actual vs Plan on different grain
2 posters
Page 1 of 1
Actual vs Plan on different grain
Hello I have to prepare model for new datamart and I encountered following problem:
I have a snapshot(weekly snapshots) fact table like this one:
date_id
branch_id
prod_id
segment_id
amt
plan
and hierarchies like this one:
dimension product
prod_id
prod_nm
prod_grp_id
prod_grp_nm
main_grp_id
main_grp_nm
The problem is that the plan could be defined on any aggregation level (for example plan for branch 1 and prod_grp_id=2 is 200, Plan for branch 2 is 300, Plan for Branch3 and prod_Id=4 and segment_id=4 is 400). Not on the lowest grain!
Does anyone knows how to design fact and dimension tables to get this working?
I have a snapshot(weekly snapshots) fact table like this one:
date_id
branch_id
prod_id
segment_id
amt
plan
and hierarchies like this one:
dimension product
prod_id
prod_nm
prod_grp_id
prod_grp_nm
main_grp_id
main_grp_nm
The problem is that the plan could be defined on any aggregation level (for example plan for branch 1 and prod_grp_id=2 is 200, Plan for branch 2 is 300, Plan for Branch3 and prod_Id=4 and segment_id=4 is 400). Not on the lowest grain!
Does anyone knows how to design fact and dimension tables to get this working?
Franta- Posts : 1
Join date : 2010-11-09
Re: Actual vs Plan on different grain
This is typical... it is rare that a company plans at the lowest possible level.
Actuals need to be rolled up to the plan level. You usually have two fact tables, one for actuals (i.e. detailed sales facts) and one for budget/plan.
Actuals need to be rolled up to the plan level. You usually have two fact tables, one for actuals (i.e. detailed sales facts) and one for budget/plan.
Similar topics
» Actual and Plan Facts at different granularity - one conformed dimension?
» Incorporate a plan figure
» Sale plan, should it be fact or dimension table?
» Actual Vs Budget Amount in Sales DW
» Cube design Planned and Actual data?
» Incorporate a plan figure
» Sale plan, should it be fact or dimension table?
» Actual Vs Budget Amount in Sales DW
» Cube design Planned and Actual data?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum