TM1 Forum

Hi all,
I was looking for some advice on how to create a view zero out but using the elements from my source variables.
Typically a view zero out is done on the Prolog tab but in this case I want to be able to zero out the specific elements from my source variables only.

Here are some more details:
-I'm loading data from a .csv into a cube
-I would like to zero out the intersections only relating to whatever elements are on on my source file then proceed to load the data as per usual on the data tab.

Essentially I would like to use my variables tab to zero out and create my view but if done on the Prolog the data source is not open yet and the TI cannot resolve the variables.

I'm thinking this probably can't be achieved in one TI but would appreciate any feedback on how this can be done in an efficient way.
Thanks in advance!

Sure this can be done in a single TI, although it is not by any means a typical use case. Remember that if an element disappears from your data source it will not be zeroed out and that data will stay in the cube indefinitely.

The way to do what you want is to use the Metadata tab to collect all the distinct elements. You can use temporary dimensions to ensure each element is only captured once - which is harder to do with subsets (as they can contain duplicates). Then on the Data tab have a counter that you increase for every data source record (or at the very least for the first record). Only when processing the first record (use an IF clause for this based on the counter) use your temporary dimensions to create dimension subsets, attach them to a cube view and zero out the view there, at the top of the Data tab. I often do my zeroouts on the Data tab anyway - this allows me to only clear out data when there is at least one source data record, which minimises the odds of ending up with no data due to some unforeseen data source failure.

One last note: this approach will increase processing time because your data source will have to be parsed twice, but other than that it should work just fine. For a small dataset it's not even something you need to worry about.

CRP0021 wrote: ↑Wed Jul 18, 2018 3:58 pm -I would like to zero out the intersections only relating to whatever elements are on on my source file then proceed to load the data as per usual on the

If your data source contains a record for all the intersections in the cube that you want to update why bother zeroing it out? Just replace the existing value with the new one. This accomplishes the same thing as zeroing out and updating.

tomok wrote: ↑Wed Jul 18, 2018 5:29 pm If your data source contains a record for all the intersections in the cube that you want to update why bother zeroing it out? Just replace the existing value with the new one. This accomplishes the same thing as zeroing out and updating.

One case where such zeroout would be needed is if data in the source file is not consolidated (equivalent of SQL's group by) and there are multiple records for each element combination. In that case you need to accumulate (CellIncrementN) but need to reset values to 0 first. Of course the OP isn't telling us the whole story so we can only speculate.

Thank you folks I did figure this out taking into account some of your feedback.
Much appreciated!
Cheers

I have 2 articles about this subject:
http://www.wimgielis.com/tm1_datadrivenzeroout_EN.htm

and

http://www.wimgielis.com/tm1_datadriven ... ion_EN.htm