Data spreading automation.

[charan]

Hi,
I have two cubes in which one cube gets data from database and this cube contains percentage value. Always data for this cube is from database(external source).
In second cube the the user has rights enter the data at the consolidated level by using the spread option(Relative propotional spread).When he enters the data at consolidated level , the value which he enters is allocated to leaf level elements based on the percentage of first cube.
Give me suggestion for making automation instead of user selecting the data spread option.
The process of data spread for the end user is complex is any technique available for this.

Note: The user enter value is not fixed.

Thanks

TM1 9.5.2
excel 2007

[asutcliffe]

Hi,
I have two cubes in which one cube gets data from database and this cube contains percentage value. Always data for this cube is from database(external source).
In second cube the the user has rights enter the data at the consolidated level by using the spread option(Relative propotional spread).When he enters the data at consolidated level , the value which he enters is allocated to leaf level elements based on the percentage of first cube.

You sound like you want to create a rule in the second cube to achieve this. Any changes to your percentages or the value the user enters, will then be reflected in real time. You will still need a place for the user to enter data at leaf level though. Try creating a cube without the elements you want to spread to the leaves of and creating a simple interface for the user to enter just the total they want to spread.

If you're stuck or I've over simplified your problem, post some more details of your current cube design.

Also, reading between the lines, there might be a simpler end to end solution for what you're trying to do.

edit: typo

[Harvey]

If I understand the question correctly, asutcliffe is correct in his answer, but allow me elaborate a little.

The technique here is to have the consolidated data which your users input either in a separate cube, or, as previously suggested, in the same cube, with dummy elements at the consolidated level to hold the raw, unspread values.

In my opinion, it is much cleaner to have separate cubes, with a version of the final dimension/s without the target leaf elements included, but I have done it both ways in the past for various reasons.

If the technique for spreading is complex, as you indicate, and there can be more than one algorithm, I would suggest creating a "spread" dimension containing the names of the spreading techniques. Add this dimension to your target cube and implement all the algorithms. That way the user can view the result of a particular algorithm by selecting the correct element in the "spread" dimension.

If you only want one algorithm in play at a time for a particular data set, you may not need this dimension in the target cube, it just depends how it will be used.

Another technique I've used in the past it so create a third separate cube with % numbers in it, which are the result of the spreading algorithm. The advantage here is that you can see the result of the spreading more easily, and that you have some flexibility later to correct feeding or performance issues if you come across any, and you often do with large spreading algorithms.

If you do have performance issues, it's easy to switch the generation of the "spreading %" cube to Turbo Integrator without having to include further logic, or alternatively switch the generation of the target cube and get rid of some feeders.

Hope that helps somewhat.