Clean up TM1 clients and groups

Example files with this article:
  • TM1 Clients and groups
  • Introduction

    TM1 security works using the principle of clients (with associated password) and groups. Security is set up at the level of groups. After that, a client can be assigned to one or more groups. However, a client could be assigned to no group, and similarly a group could be assigned no clients. You could clean up with a small TI process.

    An automated clean up

    I wrote a TI process to clean up the }Clients and }Groups dimension. The process allows a parameter to define what you want to clean up:

    • Typ C to remove clients that are in not a single group
    • Typ G to remove groups that contain no clients
    • Typ CG to do both

    This is done in 1 process. If you choose CG, the process executes itself with a C and then with a G.

    Set it up yourself

    What should you do to make use of this nifty process? Either download it on top of the page. Either do it yourself, here is how. Create a new TI process called CleanUpClientsGroups, with data source equal to TM1 Dimension Subset. In the Data Source Name, navigate towards the subset All on the dimension }Clients. To be able to see this control dimension, in the menu bar you should take View > Display Control Objects. In the Variables tab of the process, call the (only) variable V1 and set its Contents to Other. Create a parameter in the Advanced > Parameters tab:

    ParameterTypeDefault ValuePrompt Question
    pCleanUpWhatStringCGWhat do you want to clean up? (C: Clients, G: Groups, CG: Both)

    TI code

    Here is the code in the Prolog tab:

    # Wim Gielis # http://www.wimgielis.com
    ##### # TI code to delete TM1 clients without groups, # TM1 groups without clients, or both # 11/07/09 #####
    IF(pCleanUpWhat@='CG'); EXECUTEPROCESS('CleanUpClientsGroups','pCleanUpWhat','C'); EXECUTEPROCESS('CleanUpClientsGroups','pCleanUpWhat','G'); ELSEIF(pCleanUpWhat@='C'); DATASOURCETYPE='SUBSET'; DATASOURCENAMEFORSERVER='}Clients'; DATASOURCEDIMENSIONSUBSET='ALL'; ELSEIF(pCleanUpWhat@='G'); DATASOURCETYPE='SUBSET'; DATASOURCENAMEFORSERVER='}Groups'; DATASOURCEDIMENSIONSUBSET='ALL'; ENDIF;

    Here is the code in the Metadata tab:

    IF(pCleanUpWhat@='C');
    
         vClient=V1;
         IF(SCAN('ADMIN',UPPER(vClient))=0 & SCAN('}',vClient)=0);
              iGroup=1;
              vInAGroup=0;
              WHILE(iGroup<=DIMSIZ('}Groups'));
                   vGroup=DIMNM('}Groups',iGroup);
                   IF(SCAN('ADMIN',UPPER(vGroup))=0 & SCAN('}',vGroup)=0);
                        IF(CELLGETS('}ClientGroups',vClient,vGroup)@=vGroup);
                             vInAGroup=1;
                             iGroup=DIMSIZ('}Groups');
                        ENDIF;
                   ENDIF;
                   iGroup=iGroup+1;
              END;
    
              If(vInAGroup=0);DELETECLIENT(vClient);ENDIF;
    
         ENDIF;
    
    
    ELSEIF(pCleanUpWhat@='G');
    
         vGroup=V1;
         IF(SCAN('ADMIN',UPPER(vGroup))=0 & SCAN('}',vGroup)=0);
              iClient=1;
              vHasClients=0;
              WHILE(iClient<=DIMSIZ('}Clients'));
                   vClient=DIMNM('}Clients',iClient);
                   IF(SCAN('ADMIN',UPPER(vClient))=0 & SCAN('}',vClient)=0);
                        IF(CELLGETS('}ClientGroups',vClient,vGroup)@=vGroup);
                             vHasClients=1;
                             iClient=DIMSIZ('}Clients');
                        ENDIF;
                   ENDIF;
                   iClient=iClient+1;
              END;
    
              If(vHasClients=0);DELETEGROUP(vGroup);ENDIF;
    
         ENDIF;
    
    ENDIF;
    

    Here is the code in the Epilog tab:

    SECURITYREFRESH;
    

    The process avoids the double loop over clients and groups (since the combination matters). The data source is a subset on the relevant dimension, and then in the code a loop is made over the elements in the other dimension. I use the statements DATASOURCETYPE, DATASOURCENAMEFORSERVER and DATASOURCEDIMENSIONSUBSET.

    Of course, you need the relevant rights to execute the process. Client names and group names containing the word admin are skipped. Feel free to use attributes and the like to further customize the code.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links