Zero out cube view from source elements

Post Reply
CRP0021
Posts: 8
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Zero out cube view from source elements

Post by CRP0021 » Wed Jul 18, 2018 3:58 pm

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!

User avatar
qml
MVP
Posts: 1059
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.4 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Zero out cube view from source elements

Post by qml » Wed Jul 18, 2018 4:45 pm

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.
Kamil Arendt

User avatar
tomok
MVP
Posts: 2483
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Zero out cube view from source elements

Post by tomok » Wed Jul 18, 2018 5:29 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
qml
MVP
Posts: 1059
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.4 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Zero out cube view from source elements

Post by qml » Wed Jul 18, 2018 9:32 pm

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.
Kamil Arendt

CRP0021
Posts: 8
Joined: Mon Aug 21, 2017 2:14 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: Zero out cube view from source elements

Post by CRP0021 » Thu Jul 19, 2018 1:10 am

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

Wim Gielis
MVP
Posts: 1809
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Zero out cube view from source elements

Post by Wim Gielis » Thu Jul 19, 2018 4:48 am

Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply