Data Spread from Summary (CUBE A) to detail cube (CUBE B)

[dharav]

Hello ALL

Need your direction on following problem which i have been facing.

I am doing data spreading from cube A to Cube B. Cube A is a summary cube while Cube is a detail cube.
The TI process do data spreading on the bais of detiled level plan data (CellPutProportionalSpread)
Everything works fine except the following scenario:

In Cube A, There is no data for X element at Y location. In cube B, Plan data has value of X element at Y location.
############################################################
Following Cube A data we are spreading in to cube B
Cube A
XYZ (Level 1)
X 0 (Level 0)
Y 50 (Level 0)
Z 70 (Level 0)

Before Spreading
Cube B

XYZ (Level 2)

X (Level 1) 1000
1 400 (Level 0)
2 500 (Level 0)
3 100 (Level 0)

Y (Level 1)100
4 20
5 20
6 60

Z (Level 1) 10
7 4
8 4
9 2
################################################################
After Spreading
XYZ (Level 2)

X (Level 1) 1000
1 400
2 500
3 100

Y (Level 1)50
4 10
5 10
6 30

Z (Level 1) 70
7 28
8 28
9 14

##############################################################################
It should be or I want

X (Level 1) 0
1 0
2 0
3 0

(As there is X=0 in Cube A than it should breakback zero, What are the functions i could use to achieve the functionality?

[declanr]

CellPutProportionalSpread should work perfectly well via TI in your example, I presume it's just that you have suppressed zero in your source view.

[dharav]

Hello Declnr

CellPutProportionalSpread should work perfectly well via TI in your example, I presume it's just that you have suppressed zero in your source view.

I recheck my Source View process to make sure that there is not viewextractzeroesset applied. Still it happens.

There is no suppress Zero. Is there anything else specific i should look in to it?

I did not get the concept that why it is not breaking back the zero from Cube A to Cube B. There is no value (so zero appears).
It is working normally where there is a value. Zeroes are not getting break back.


Thank You

Dharav Patel

[tomok]

CellPutProportional spread will not spread a zero value, i.e., make the target cells zero. All it will do is proportionally spread a non-zero amount. If the amount you are trying to spread is zero it will be ignored.

[Wim Gielis]

CellPutProportional spread will not spread a zero value, i.e., make the target cells zero. All it will do is proportionally spread a non-zero amount. If the amount you are trying to spread is zero it will be ignored.

Sorry to correct you Tom. If I do this in a small TM1 model, it works perfectly fine: the CellPutProportionalSpread of 0 is a simple way to do a zero out...
See: http://users.skynet.be/fa436118/wim/tm1 ... ead_EN.htm

Only caveat: do not spread over 0 cells or you will get an error.
Also, watch the number of cells over which you spread

I recheck my Source View process to make sure that there is not viewextractzeroesset applied. Still it happens.

So, you did not say to NOT skip the zeroes ? What is the flag, 0 or 1 ?

[dharav]

Hello Wim

The flag value is 0 in ViewExtractZeroesSet Function to include Zeroes.

Hello Tomok
I think we can sprea 0 value to the target if it has the data.

Suppose Cube A has

XYZ element (Level 0) = 00

& Cube B has

XYZ (Level 1) = 100

X 50
Y 30
Z 20

Than zeroes would be spread out. I test it with one element.

###### For All ##########

I thought of two ways to do this function in more efficient way
First:

1) Find the element from source having value 0
2) Insert those element in target view (Data Tab)
3) View Zeroout (target) (Data Tab)

Second:

(I have implemented in my environment)

1) Assign ViewExtractZeroesOut below source view with Flag '0'

It would include all data and would spread zeroes. It is too time consuming activities as it includes all elements and datapoints to spread zerovalue


Let me know if there is another efficient way to do it

[Wim Gielis]

If you would set this up in the proper way, you:

1) use 2 measures in the target cube: measure A contains the data over which you want to spread ('before' situation), measure B is the 'after' situation
2) do a zero out on measure B
3) make a TI process to copy data at level 0 in cube B from measure A to measure B. Skip 0, rules-calculated cells and consolidations. In the Data tab, check if the value in cube A is <>0 at level 0 in cube A. If if is <>0, then copy from measure A to B. If if is =0, then do NOT copy from measyure A to B.
4) make a second TI process with the view source based on cube A, no consolidations, zeroes and rules-calculated cells. Do NO zero out whatsoever. Do a CellPutProportionalSpread in cube B over the cells for measure B.

Done. This is the quickest and best solution. It may not run very fast due to the fact that you write and read in the same cube (B) in step 3, on the parent level.
TM1 will create and discard views on the fly, I had a similar issue half a year ago.
Test this very carefully.

[dharav]

Hello Wim

Let me put in to action your concept and will get back to you with result

Thank You

Dharav

[dharav]

Hi Wim

As you suggested,

1) If i copy measure A to measure B in target cube with N level data,

2) Spreading only value cells from cube A to measure B in Cube B


Than

1) My purpose won't be solved.

How?

1) My existing TI is successful to spread back data from cube A to Cube B. I have skip Zeroes, Rule Calculated Values, and Consolidated Values.

Business Scenario before i go with point 2:

In Cube B (Detailed Cube), the plan would be forecasted on detail level. Now this forecast would be available as starting point to do
Jan (0+12)forecast.

2) The main obstacle is, If in cube B there is data for one city for XYZ metro, 2city data for ABC metro , and 5 city data for UVW metro.
In Cube A, there is data for only ABC & UVW metro. So when we do spread back using cellputproportional spread,
It would work best for these two metros. There is no data for XYZ so data spread won't work. There would be value for XYZ in cube B
which should be zero.

Solution could be possible:

>>> In Sourceview include all Zeroes by putting floag value 0 for function which skip rules. Now spread back this data. This would consume
more time as it tends to data spreading on those cells which are already zero in Cube B.

>>> We have to create a target view based

Process 1) Create a sourceview with all values including zeroes (Default View)

Process 2) Apply source view as source

In prolog: create target view (which only includes value, no zero values )

In data tab:

If (CellGetN(VSrccube, v1,v2,v3,v4)=0);

CellPutN(0,Vtgtcube,v1,v2,v3,v4);

Else;

itemskip;

Endif;

Epilog:

Destroy target view
Destroy subset create for target view


Process 3)

In Prolog

Destroy Source View

Destroy subset created for source view

(Note: Here, As our target view does not contain zero value, it should not spread back zero value retrive from cube A
to the zero values there in Cube B. It would only spread back where value is >0 in cube B. Thus Process should become faster)

I am implementing this process, let me know your suggestions to make the process more efficient