Non Aggregateable Targets

View previous topic View next topic Go down

Non Aggregateable Targets

Post  siptec on Tue Mar 17, 2009 6:28 pm

I have a situation where I am required to set targets for two seperate divisions. These targets are static for the entire year. I have currently added a record into FactTarget for each division and then repeated the same target for every day in the year, e.g. 20%. I have then used the MAX aggregation type in SSAS. This works fine for the divisions but I then have targets for the company (the company sits above these two divisions) which have their own targets. These targets can not be derived from the divisions as they are not aggreatable. How should this situation be handled?

The situation is further complicated by the inclusion of aggregatable targets and in this case the sum of the two divisions is automtcially the target for the Company.

Any advice is much appreciated.

siptec

Posts : 1
Join date : 2009-03-17

View user profile

Back to top Go down

Re: Non Aggregateable Targets

Post  Tim R on Wed Mar 25, 2009 3:59 am

Not sure I'm fully understanding your problem.
I assume your deriving your targets, for either divisions or Company, from data in the warehouse, (rather than having the accounting department supply them)?

If the change rate is only once a year then why hold the results as a fact at all?
Would it not be more flexible to populate an appropriately structured 'targets' dimension.

Tim

Tim R

Posts : 2
Join date : 2009-03-20

View user profile

Back to top Go down

Re: Non Aggregateable Targets

Post  Joy on Wed Mar 25, 2009 12:52 pm

The best solution will require that you learn enough MDX to create a complex calculation. This is what I'd do:

- Create multiple Targets fact tables to hold the targets at the correct level of granularity, eg Division, Company. I wouldn't mix grain even for something as small and simple as a Targets fact table.

- You shouldn't have to push down to the Day level in the fact table -- you can do that bit in MDX quite easily.

- Create a calculation that first defaults to "N/A" (or "") for the entire cube
- Same calc, Scoped at the Day-Division level and returning the correct Target
- Same calculation, new Scope at the Month-Division level, ditto
- etc etc; get it working for Division before turning your attention to Company
- Same calculation, new Scope(s) for the different other pieces of your corporate hierarchy, picking up actual targets if they exist, aggregating Divisions if they don't.

You will probably hide the original Targets facts from the business users, revealing only the calculated Target in all its glory.

Good luck. This isn't very easy, but definitely do-able. Take it a tiny step at a time.
avatar
Joy

Posts : 20
Join date : 2009-02-03
Location : Kimball Group

View user profile http://www.kimballgroup.com

Back to top Go down

Re: Non Aggregateable Targets

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum