Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Non Aggregateable Targets

3 posters

Go down

Non Aggregateable Targets Empty Non Aggregateable Targets

Post  siptec 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.


Posts : 1
Join date : 2009-03-17

Back to top Go down

Non Aggregateable Targets Empty Re: Non Aggregateable Targets

Post  Tim R 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 R

Posts : 2
Join date : 2009-03-20

Back to top Go down

Non Aggregateable Targets Empty Re: Non Aggregateable Targets

Post  Joy 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.

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

Back to top Go down

Non Aggregateable Targets Empty Re: Non Aggregateable Targets

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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