TM1 Forum

Hello All

I have a strange business requirement and it has faced following issue.


We have approval hierarchy with 4200 elements. Each metro (metro is a consolidation level) holds average 230 leaf elements. According to business requirement, user should do forecast at consolidation level (Metro level). When user are inputting in a consolidate cell (in contributor), it takes average 4 seconds to update that sales. I know this time is being taken due to the breakdown of consolidation number in to more than 200 leaf elements.When user do input at a leaf cell than it takes less than a second.

In contributor, when we enter the value at consolidation cell than it spreads the data as per proportion spread method. Can we stop the proportion spreading till the time user input the data and it should get break back once we recalculate it.??????

There is a work through way " Snapshot of forecast , update forecast and copy forecast and paste it in to contributor." Copy-Paste whole forecast is taking less than 50 second from consolidation. I am eager to know whether there is a way to fix it or i have to go through with work through?

Thanks

Dharav

The answer is to add another leaf level node under each metro and enter the forecast amounts there. It will roll up to the metro and not be mixed in with the actuals in the other leafs under the metro.

Or if metro is your lowest level of input build a separate dim that has metro at the lowest level, create new cubes based on that, have the users input data there and when the submission is complete have it TI'd to the more detailed cubes and spread at that time.

Rodger

TI option is not feasible as

Forecast data is updating in to Respective month forecast. All forecast month data are going in to forecast variance cube.


Still i am curious to know that

Suppose if i move data through TI as you said than how could it be data tab statement should get change in order to upload data at consolidation?

Appreciate Input of all

Thanks

Dharav

Sorry for the delay in replying.

I'm not sure what you mean as to it not being feasible, extra work for the developer yes but not infeasible.

As to the data tab of the TI process you can write whatever you want in TI. Either use the proportional spread function or use loops to create some more customised spreading functionality.

In my opinion if every single input is done on a spreading basis then the cube is at too detailed a level and should be broke into an input cube and a more detailed reporting cube.

declanr wrote:Sorry for the delay in replying.


In my opinion if every single input is done on a spreading basis then the cube is at too detailed a level and should be broke into an input cube and a more detailed reporting cube.

I wanted to do same as you mentioned. I did following to attain objectives of users.

1) I created the cube having only metro level (N level) to do forecast ( it will reduce time to break back number to n level elements.). It is also approval hierarchy called Metro hierarchy for now.

2) User Just want all actual at PC level (Region level2, Metro Level 1, and PC Level 0). So actual output cube would contain detailed hierarchy dimension including pc level.

3) I Assign respective element security to detailed hierarchy so when they click on approval hierarchy, they would see those elements in actual output cube where element security set to READ or WRITE.

Till now everything works fine.. The problem arises where user has access of more than one metro.

Metro Hierarchy [Approval Hierarchy]

USA (LEVEL 1)
New York (Level 0)
New Jersey (Level 0)
Georgia (Level 0)
Las Vegas (Level 0)

Detailed Hierarchy
USA (LEVEL 2)
New York (Level 1)
A (Level 0)
B (Level 0)
C (Level 0)

New Jersey (Level 1)
D (Level 0)
E (Level 0)
F (Level 0)

Georgia (Level 1)
G (Level 0)
H (Level 0)
I (Level 0)

Las Vegas (Level 1)
J (Level 0)
K (Level 0)
L (Level 0)

Now, suppose user has access of both New York and Georgia. He would see both region when he opens contributor. When he clicks on New York, the screen opens and he would see New York in Input cube (which is based on approval hierarchy) and actual output cube (which is based on detailed hierarchy) shows both New York as well as Georgia..

So how could i manage the functionality in the actual output cube to see only New York.? Can we utilize MDX statement to filter detailed hierarchy dimension based on approval hierarchy dimension?

Any other solution/Input would also Helpful.

Thanks

Dharav

I wouldn't necessarily have added it to the contributor app but no problem in doing so. Are you using websheets or cube views?

cube views. Could you please explain it further, please?

Thanks for your time!

Dharav