ViewZeroOut according variables

[Charles Ang]

Hi,

For the overwrite feature in TM1, normally is check the data source, clear data then upload again.
But .... I have some bottleneck on check the data source.

I have to clear the data respect to 3 dimension :Year, Dept and Version
I have 6 dimension in the cube: Year, Month,Dept,Version,Unit and Product

For example of my data source:
Year,Month,Dept,Version,Product,Unit
2012,Jan,Sales, Actual,A,100
2012,Jan,Sales, Budget,A,100
2013,Feb,Marketing,Budget,B,20
...

I unable to use subset to put in all the variables in the data source and clear data. Take above example, the subset will have Year(2012,2013), Dept(Sales, Marketing) and Version (Actual, Budget) and will clear those area data.
but the area data include data for 2012,Marketing Dept, Actual that should not be clear.

Ideally is like SQL select distinct of Year,Dept,Version

But TM1 does not have function like select distinct.
Please shed some light on how to build the script (I have tested multiple script, unfortunately all failed)

Thanks,

[declanr]

You just need to build multiple views in the datasource and zero each of them.


I have a customer where there is a VERY big cube with Year, Week and Day dimensions and they can want to reload data for the last 20 days (for example) in that case the prolog does a while loop on the date and creates 20 views and zeroes each of them.)

[Wim Gielis]

Or...
http://users.skynet.be/fa436118/wim/tm1 ... ead_EN.htm

You can even make a temporary consolidation, add the elements below and do the CellPutProportionalSpread on the consolidated level.
This will only work if you remain within the same dimension, so clearing data for 20 days where you span 2 months, that will then be 2 different CellPutProportionalSpread actions. Each with suitable consolidation and n-elements.

[Wim Gielis]

Or, another interesting alternative that I have used in the past at customers:
use the Metadata tab to fill a dimension containing the combinations Year, Dept, Version that occur in the source data.
Then, do the zero out for each member of the dimension (While...End loop) within the Data tab of the process.
Do this only for the "first record": keep track of the record count with a simple incremening counter. If the counter equals 1, do the zero out. In the Epilog, delete that temporary dimension.

The above is an illustration of a generic approach, instead of 3 dimensions it could also be applied to 1 dimension: clear only the data for which you have records in the file / data source in general. In the case of 1 dimension, you could consider filling a subset instead of a dimension. The drawback is that subsets add the same element multiple times (for example 100,000 elements if you have 100,000 records), whereas dimensions only store "unique elements" (concatenated combinations). Much faster with big numbers of records.

[Charles Ang]

Or...
http://users.skynet.be/fa436118/wim/tm1 ... ead_EN.htm

You can even make a temporary consolidation, add the elements below and do the CellPutProportionalSpread on the consolidated level.
This will only work if you remain within the same dimension, so clearing data for 20 days where you span 2 months, that will then be 2 different CellPutProportionalSpread actions. Each with suitable consolidation and n-elements.

I have read through it, it is quite interesting as it is another solution I didn't think of it.
However, my version, year and entity(sort of flat as only 1 of the entity is roll up) are flat dimension.

Or, another interesting alternative that I have used in the past at customers:
use the Metadata tab to fill a dimension containing the combinations Year, Dept, Version that occur in the source data.
Then, do the zero out for each member of the dimension (While...End loop) within the Data tab of the process.
Do this only for the "first record": keep track of the record count with a simple incremening counter. If the counter equals 1, do the zero out. In the Epilog, delete that temporary dimension.

The above is an illustration of a generic approach, instead of 3 dimensions it could also be applied to 1 dimension: clear only the data for which you have records in the file / data source in general. In the case of 1 dimension, you could consider filling a subset instead of a dimension. The drawback is that subsets add the same element multiple times (for example 100,000 elements if you have 100,000 records), whereas dimensions only store "unique elements" (concatenated combinations). Much faster with big numbers of records.

I am not quite get the idea of it. Is it combine Year,Dept,Version and become a element? then separate it out and do the zero out?

[Wim Gielis]

I am not quite get the idea of it. Is it combine Year,Dept,Version and become a element? then separate it out and do the zero out?

Yes, that's true. Populate a temporary dimension containing elements that are concatenations of the elements to be erased.
In the Data tab, loop over the temporary dimension and break out the elements again.
It maybe be easier if you use the index of the elements in the concatenation.

[Charles Ang]

I am not quite get the idea of it. Is it combine Year,Dept,Version and become a element? then separate it out and do the zero out?

Yes, that's true. Populate a temporary dimension containing elements that are concatenations of the elements to be erased.
In the Data tab, loop over the temporary dimension and break out the elements again.
It maybe be easier if you use the index of the elements in the concatenation.

Yes, I create the dimension element in metadate and zero out at the data by breaking out the elements.
It is working. Thanks !

[Wim Gielis]

Good job !

[BariAbdul]

I am not quite get the idea of it. Is it combine Year,Dept,Version and become a element? then separate it out and do the zero out?

Yes, that's true. Populate a temporary dimension containing elements that are concatenations of the elements to be erased.
In the Data tab, loop over the temporary dimension and break out the elements again.
It maybe be easier if you use the index of the elements in the concatenation.

Yes, I create the dimension element in metadate and zero out at the data by breaking out the elements.
It is working. Thanks !

Hi Charles,for the greater benefit of the TM1 community and lots of newbies like me,Could you please post the entire solution.Thanks

[Wim Gielis]

Hi Charles,for the greater benefit of the TM1 community and lots of newbies like me,Could you please post the entire solution.Thanks

It's not enormously difficult:

Prolog:
- ViewCreate to create a temporary dimension
- set a counter to 0

Metadata tab:
- convert the dimension elements that you have in the Variables tab, to dimension indexes (Dimix function)
- concenate them and insert in the temporary dimension

Data tab:
- increase the counter by 1
- test with an if statement if the counter equals 1
- ONLY then, do a zero out as usual
- but wrap it in a WHILE... END loop to loop over the temporary dimension
- within the loop, break out the elements into the smaller parts, fill these in the respective subsets
- do the zero out (still within the loop) and clean up your other temporary objects
- outside of the IF statement, do your usual CellIncrement / CellPutN / CellPutS stuff

[Wim Gielis]

Hello

The case of zeroing out a cube slice in the Data tab of a process (over 1 dimension), has now been discussed in a new blog article on my website.

Wim

[Wim Gielis]

The case for this approach over multiple dimensions, was added as another article to my most popular TM1 website.

[BariAbdul]

Thanks Wim,Appreciate the time and effort.Merry Christmas and happy holidays to you and all the TM1 forum members.