TI process skipps empty cells

[dan.stroebel]

Hi,

I try to set all cells of a cube to the value of 1 using CellPutN in a TI process.
However, only cells already having values <> 0 get the value 1 set,
Zero value cells stay untouched.

How can I positively fill every detail cell of a cube with a value,
whether the cell is already filled or empty?

Thanks,
Dan

[wissew]

The view that you are cycling through as a data source should have zero supression DESELECTED.

[dan.stroebel]

Zero suppression is disabled.

But with a Matrix like this:

Code: Select all

              Jan    Feb    Mar
Article 1     100     0      0
Article 2      5     -1      0

Doing CellPutN(1, 'myCube', 'Month', 'Article); results in

Code: Select all

              Jan   Feb    Mar
Article 1      1     0      0
Article 2      1     1      0

but I want it to be

Code: Select all

              Jan   Feb    Mar
Article 1      1     1      1
Article 2      1     1      1

[rkaif]

Can you tell us the reason why you would like to fill the whole cube with 1?

In TI Process, by default it skips the data source lines which have zero (no value).

Have you considered using Rules to achieve this. You can write a simple rule

Code: Select all

[] = N: 1;

This rule will put 1 in the whole cube, disregarding what the the actual values are.

[rkaif]

Alright, I have dig this out. We can do this using a TI Process. Do the following steps:

Create a TI Process
On Data source tab, slect TM1 data source type.
Click on Browse (to select a View name)
Now here is the trick -> Click on Create View button
On the View Extract dialog, uncheck the Skip Zero/Blank] Values. See the attachment.
Click OK. Provide a new name for the view you have just created
On variable tab, create a new Variable (of Data type) and its Formula should be vMyValue = 1;
Map your variable (Elements and data)
Run the process.

This should work for you.

Hope it helps!

[lotsaram]

As Rizwan has already asked why would you want to want to do this? ... as this will create a totally dense cube which is a strange thing to do. If it is a small cube then fine no significant memory of performance impact (but still why?)

If this is a (relatively) small cube and you don't mind waiting a few seconds to a few a minutes you can also do the same very easily manually by spreading. Just select the highest consolidation that you want 1s in all leaf cells filled under and right-click then select data spread > repeat leaves > value =1, all leaf cells, replace > Apply

My assumption is that the reason your process is not working is that you created the source view in the cube viewer. It is important to note that although TM1 makes no distinction in the UI between user views and data queries the treatment is actually quite different in the back end. Zero suppression on a user cube view is not the same as zero suppression (=skipping blanks) in a VIEW data source. These are actually 2 distinct properties of a view and have separate functions to set each (ViewSuppressZeroesSet and ViewExtractSkipZeroesSet)

[dan.stroebel]

I need to spread amounts equally over all detail items, that's why I populate all cells with the same value before running CellPutProportionalSpread on it.
Coming from Cognos Planning, that's how one would do that, is there a better way in TM1 I didn't think of?

[lotsaram]

Hi Dan

Why don't you start a new thread and go back a step and explain your problem and why you are doing this? What's the problem you are trying to solve? If people understand the task that you are trying to achieve someone might be able to suggest a better approach to do it in TM1. I'll go out on a limb and say that the best way to model something in EP is rarely going to be the appropriate way to do it in TM1.

[kielmc]

I need to spread amounts equally over all detail items

Why not use the "Equal" or "Repeat" spread method?

The Equal Spread method distributes a specified value equally across the cells in a view.

The Repeat method repeats a specified value across cells in a view.

[dan.stroebel]

So, how can I use the Repeat or equal spread from within A TI process then?

Background is I am supposed to spread data equally in an empty cube.
Just using CellPutProportionalSpread on an emty cube produces 191 errors,
as Alan explained in his post.

So my idea is to set all cells to a certain value <> 0 to use the proportional spreading method.
Manual spreading using repeat is not an option in this case.

As stated before, this is a Planning procedure, but if there's a better way to do this, I'd be thrilled to learn it.

\dan

[rkaif]

Dan,

I have explained you the step by step method of how to create a TI process using View Extract. By this you can place any number e.g 1 in each cell of the cube.

Then you can use CellPutProportionalSpread within a TI Process to do your spreading. In this way you dont have to do the spread manually.

[dan.stroebel]

Hi rkaif,

I did it your way and I am very happy with the result.
I just wanted to elaborate further as kielmc asked for it and to explain lotsaram why his approach would not suit the process in this case.

Many thanks for the help to all of you.

\dan