ZeroOut code in Prolog using a variable

[robincollettabc123]

Hi All,

I use CognosExpress version 10.1. I have a process which loads data in on a weekly basis (or will do in the future).
The data is loaded in by the date on which the a report is run. I.e. external report is run on the 10th, the TM1 process connects via ODBC and is run linking to this report and will load data against the 10th. This all works fine. I want to be able to zeroout anything on this report date prior to loading the data but don't want to have to manually change a view before running it every time as this will lead to mistakes. I set up a zero out process however, as the variable will not be read prolog it cannot be used and hence I cannot use a variable to create my zero out subset.

If anyone has any solutions please feel free to let me know. I am relatively new to TM1 so go easy on me if this is a pretty simple or foolish question. If you require any additional information feel free to ask.

Cheers

Robin

[jim wood]

Have you tried using parameters at all? You could execute the clear process from within the meta data tab of the load process. Set a counter to 1 in the prolog. Then wrap the clear execute within an IF statement so it only executes when the counter =1. Then straight after the counter set the counter to 2. You could even add this to the data tab so the process never hits the data tab.

[tomok]

What Jim said will work. What I like to do in scenarios where the periods to be zeroed out have to be determined by what's actually in the data itself, is to split into two processes; 1) create the view and subsets to clear and 2) clear and load. The first process actually reads the source to find out which elements in all the various dimensions need to be cleared. You can choose to load one month, two months, one cost center, four cost centers, etc and the clear and load routine never changes, it's all dependent on what is in the data. Obviously, this is a little wasteful of processing resources because you may have to read the entire source (in the case of flat files) but it makes the whole routine extremely flexible. If the source is ODBC you may be able to reduce the resource usage of the view and subset building by doing a SELECT DISTINCT query against the source to just get what needs changing without processing every data record.

[robincollettabc123]

Thank you for the two posts, I have done as Jim suggested and will let you know how that goes by the end of the day but it is all set up to go, I just need that report running.

Fingers are already crossed. I might try the second option at some point to see if I prefer doing it that way, but thank you both in advance.

Cheers

Robin

[robincollettabc123]

Happy to say it has worked like a charm Jim.

Thank you again - very helpful!

[Wim Gielis]

What Jim said will work. What I like to do in scenarios where the periods to be zeroed out have to be determined by what's actually in the data itself, is to split into two processes; 1) create the view and subsets to clear and 2) clear and load. The first process actually reads the source to find out which elements in all the various dimensions need to be cleared. You can choose to load one month, two months, one cost center, four cost centers, etc and the clear and load routine never changes, it's all dependent on what is in the data. Obviously, this is a little wasteful of processing resources because you may have to read the entire source (in the case of flat files) but it makes the whole routine extremely flexible. If the source is ODBC you may be able to reduce the resource usage of the view and subset building by doing a SELECT DISTINCT query against the source to just get what needs changing without processing every data record.

Or... filling 1 or more subsets in the Metadata tab, and on line of the Data tab, do your zero out procedure using the subsets of the Metadata.
If we use a small counter in the Data tab (set to 0 or 1 in the Prolog tab), that is all relatively easy. Saves you a second process, variables definition, double maintenance, and so on.