Actual vs Plan on different grain

View previous topic View next topic Go down

Actual vs Plan on different grain

Post  Franta on Tue Nov 09, 2010 10:16 am

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?

Franta

Posts : 1
Join date : 2010-11-09

View user profile

Back to top Go down

Re: Actual vs Plan on different grain

Post  ngalemmo on Tue Nov 09, 2010 11:46 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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