Write my ZeroOut code!

Example files with this article:
  • Write my ZeroOut code!
  • Introduction

    TM1 consultants, TM1 Admin users and (occasionally) TM1 users: all of us write zero out procedures from time to time. If you do this regularly, you keep a generic piece of code with you and dig it up when needed. For instance, part of the code in the Advanced > Prolog tab can be recycled from other TM1 applications/cubes.

    Still, a zero out process usually only differs in the cube name and dimension names. Even more so if you stick to the same names for temporary objects like subsets and views. Hence, automating this exercise is worthwile.

    Have TI automate this job!

    Below you can find a Turbo Integrator process which ‘creates’ the code for you… Fill in 3 parameters, execute the process and off you go! The process creates a text file in the TM1 database directory for this TM1 server (you can change the path in the code itself).

    Right-click the file above and Save it. Here is information on the parameters:

    1. Cube name: no doubt about that one
    2. Pattern for the elements: A 0 means that leaf-level elements will be taken in the view for that dimension, whereas a 1 means that you will want one or more SubsetElementInsert statements. Provide as many 0’s and 1’s as there are dimensions in the chosen cube, and respect the dimension order.
    3. Long version or short version of the ZeroOut code: As you may know, you do not need to specify MDX statements for the leaf level elements in a dimension that will be part of the zero out. By default, TM1 will take all elements in the dimension if you do not restrict them by MDX or a SubsetElementInsert statements. Consequently, you do not have to provide the ViewSubsetAssign statements. Advantage: less code to maintain.

    After that you run the procedure, explore the database directory (if you have access to it - if not output the code in another folder to which you have access). Go through the code to create the subsets (both MDX and SubsetElementInsert’s). Amend to suit your needs.

    The last step is to copy the code in your Turbo Integrator process. I marked the code for the Advanced > Prolog tab and the Advanced > Epilog tab.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links