Create a custom view and zero out it

  • Feb. 14, 2010
Example files with this article:
  • Create and zero out a custom view

    • Introduction

    Turbo Integrator allows you to create custom views. Views are mainly created for 3 purposes:

    1. you need a custom view that you want to zero out
    2. you want to create a specific view on a cube, for instance as the result of user input
    3. you need a view for data input (this type of view is most of the time static and created by hand)

    This article focuses on the first type of views and the code behind. To ease the interpretation, the logic behind the code is as follows:

    1. a view on a cube contains 1 or more elements of each dimension of that cube
    2. in each dimension, create a public subset holding the specific elements (their chronological order in the subset does not matter)
    3. when you want to use all elements in a dimension, you do not need to create a subset. By default, TI will take all elements.
    4. create the view by assigning a subset in each dimension (the subsets you created during the process)

    A TI approach

    Put the following code in the Advanced > Prolog tab of a TI process. It will give you an example of a Balance sheet cube (called BS), but you need to adapt the cubename and dimension names to your needs. 

    # Wim Gielis
# https://www.wimgielis.com
    

    #####
# TI code to create and zero out a custom view
# 07/11/09
#####
    
# 1. Create subsets in any dimension where
#    not all elements are desired
SUBSETCREATE('BS_Accounts','ZeroOut');
SUBSETELEMENTINSERT('BS_Accounts','ZeroOut','420000',1);
SUBSETELEMENTINSERT('BS_Accounts','ZeroOut','421000',1);

SUBSETCREATE('BS_Measures','ZeroOut');
SUBSETELEMENTINSERT('BS_Measures','ZeroOut','Val',1);


# 2. Create view & assign subsets
VIEWCREATE('BS','ZeroOut');
VIEWSUBSETASSIGN('BS','ZeroOut','BS_Accounts','ZeroOut');
VIEWSUBSETASSIGN('BS','ZeroOut','BS_Measures','ZeroOut');


# 3. Zero out
VIEWZEROOUT('BS','ZeroOut');


# 4. Tidy up
VIEWDESTROY('BS','ZeroOut');
SUBSETDESTROY('BS_Accounts','ZeroOut');
SUBSETDESTROY('BS_Measures','ZeroOut');

    To obtain this code in a process, right-click the file above and Save it.

    Practical example

    In this example, you have a BalanceSheet cube called BS, with dimensions like:

    1. Entity
    2. Scenario
    3. Years
    4. Months
    5. BS_Accounts
    6. Currency
    7. BS_Iteration
    8. BS_Measures

    Both general and specific dimensions are part of the cube. Of all the data in the cube, you would like to delete the data associated with BS_Accounts 420000 and 421000 and the measure 'Val'. Since you do not specify any other elements in the 6 remaining dimensions, by default all elements are taken in these dimensions. As a result, the code will create a view with all lowest level elements, no consolidated elements, no rules-calculated cells, on the intersection of the accounts 420000 and 421000, for measure Val and no matter what elements in the other dimensions.

    As mentioned, inserting elements with SUBSETELEMENTINSERT can be done in any order. Therefore, it is not a problem when we have twice the 1 at the end for the BS_Accounts dimension.

    You do not need to hard-code element names in the subsets. You might definitely want to consider MDX statements. An MDX statement is the counterpart of a SQL query to a relational database, but MDX statements attack multidimensional databases (like TM1). A more than excellent tutorial on MDX statements can be found here.

    Finally

    As a final note, it is useful to also execute the code in part 4 before step 1. Then, you are sure to start with non-existant views and dimensions. Also, using variables to contain the cube name, view name and subset names is handy as it makes the code more generic and more easy to maintain.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links