DB to view

  • Jan. 21, 2012
Example files with this article:
  • DB to view

    • Introduction

    As frequent readers of my TM1 articles most probably know, I try to replace as much manual tedious work with VBA code, TI code, VBS code, … whatever is feasible, elegant and not a pain in the ass to maintain. Next action to automate is creating custom views.

    DB formula to a view

    It happens a lot that I am using a DBRW formula (or, for that matter, a DBR, DBSW, DBSS, DBRA, VIEW, …) for which I want to see the way it is calculated. Or I have such a formula, but I want to extend it to similar DBRW’s along a certain dimension. Obviously, both wishes call for setting up a view in the Server Explorer or TM1 Web. I will focus on the first of these for the remainder of the article.

    I thought that it would be worthwile to write some code that could:

    1. look at the active cell
    2. if the cell contains a cube-like function, dissect the formula and get the arguments
    3. create a view in TM1 on the referenced cube with the arguments of the formula as selected dimension elements

    By doing this we will end up with a cube view created on the fly. The advantage is that navigating to the cell in the cube is not needed anymore: this could take some time in a cube with a big number of dimensions and for cubes you are not familiar with. If impersonal ID’s are referenced in the DB-like formula, it can take a while to set up the view and get to the data. Hence, automating the task!

    Here is a screenshot:

    We want to investigate the yellow cell in a TM1 cube view. Using my custom code in Excel VBA and TI, the result will be:

    The Trace Calculation… is then only a right-click away from us, and dragging subsets to rows or columns is piece of cake. The subsets contain all dimension elements and have the chosen element preselected for us.

    VBA code

    The cell C10 I selected above contains a DBRW formula. The exact cube cell it references is shown below in the cube viewer. In this article I will post up the code for making the view without manual effort. The VBA code will need to get information from the selected cell:

    1. what kind of formula do we have ( DBRW, DBR, DBSW, DBSS, VIEW, …)?
    2. what cube is referenced? (and get the TM1 server name)
    3. what are the metadata/arguments in the formula?

    Coding this can get tricky, since formula arguments can show up in different forms: hard-coded, as cell references, other nested formulas, named ranges or named constants, and so on. But luckily we do not need to start coding from scratch and reinvent the wheel. Over at TM1 forum, the TM1 tools utility contains the KEY_ERR tracer (and lots of other good stuff too). That VBA-code teaches us how we can evaluate TM1 functions and show us what function argument(s) lead to error outcomes: *KEY_ERR, #N/A , an empty cell, … You can trace misspelled or deleted elements in an easy way. The VBA-code for the KEY_ERR tracer analyzes the formula and matches each element with the respective dimension/cube. Hence, the basis of this code can be reused to list the elements of our custom view in case the user evaluates a cube-like function. I started with that VBA-code, but changed and added new code.

    I will not post the full code on this website (just download the file attachment on top of the page and you will find all code non-protected). To give a feeling of the code, below is the procedure you will execute: 

    Sub DB_to_VIEW()
    

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

    '''''
' VBA code to create a view based off a DBRW, … function
' 21/01/12
'''''
    
    If fValidDB Then
        s = "tm1runti -server #1 -user #2 -pwd #3 -process #4 pCube=#5 pElements=#6 pViewPositions=#7"
        For i = 1 To 7
            s = Replace(s, "#" & i, sQuoted(Choose(i, server, "YOUR_USERNAME", "YOUR_PASSWORD", "YOUR_TI_PROCESS", _
                        cubeOnly, pElements, pViewPositions)))
        Next
        ShellAndWait s, vbHide
    End If


    End Sub

    This code will first check whether the highlighted cell contains valid input for our exercise: is it the type of function we want to analyze? And if so, is the formula correct syntax-wise? (you could easily check this if your formula returns the good result, or you could use the KEY_ERR tracer :-)) The second step will be to execute a TI process (to be explained later on) that takes 3 parameters: the cube name, the arguments and the view position indications for the dimensions (the last 2 are separated by a § character). I use TM1RunTI as a convenient way to execute a process. For more information on this function, please read my earlier article. We need to pass certain information to this function: insert your TM1 username and password, and the name of the Turbo Integrator process (cfr. infra). TM1RunTI allows to specify these and other parameters in a text file. I decided to not do this out of simplicity. ShellAndWait will execute TM1RunTI whereby we wait a (short) while until if finishes.

    More code…

    Let us continue with the function to check the formula and its arguments: 

    Private Function fValidDB() As Boolean
    
    Dim r1 As Long, r2 As Long
    Dim vElement As String
    Dim vIndex As Integer
    Dim vEnd As Integer

    With Selection.Cells(1)
        If .HasFormula Then theWholeFormula = .Formula Else GoTo endF
    End With
    pElements = "": pViewPositions = ""

    r1 = InStr(1, theWholeFormula, "DB", 1)
    r2 = InStr(1, theWholeFormula, "VIEW", 1)
    If r1 + r2 = 0 Then MsgBox "Invalid cell", vbCritical: GoTo endF
    If r2 > r1 Then isViewFunction = True

    isAttribute = UCase$(theWholeFormula) Like "*DB[RS]A*"

    remainingFormula = Mid$(theWholeFormula, InStr(Application.Max(r1, r2), theWholeFormula, "(") + 1)
    remainingFormula = Left$(remainingFormula, findEnd(remainingFormula) - 1)
    GetCube

    If Run("DIMIX", server & ":}Cubes", cubeOnly) = 0 Then GoTo endF

    vIndex = 1: vEnd = 0
    While vEnd = 0

        getNextArgument
        vDim = Run("TABDIM", cube, vIndex)

        If vDim = "" Then
            vEnd = 1
        Else
            If currentArgument = "" Then GoTo endF
            isBang = False
            vElement = myEval(currentArgument)
            If Run("DIMIX", server & ":" & vDim, vElement) = 0 Then GoTo endF
            pElements = pElements & vElement & "§"
            pViewPositions = pViewPositions & IIf(isViewFunction And isBang, "R", "T") & "§"
            vIndex = vIndex + 1
        End If
    Wend

    fValidDB = True
    Exit Function

endF:
    fValidDB = False

    End Function

    Some of the variable names and, more importantly, the called procedures are taken from the KEY_ERR tracer addin code. That code shows us nicely how to chop up the formula, evaluating arguments, looking for left and right brackets, getting server and cube name, and so on. Pretty interesting stuff and not too advanced. I made some changes in terms of reducing code lines and skipping needless code, as well as extending some of the procedures. For example, the original code would not detect a named constant as argument to a DBRW formula. If the named constant "server" contains the TM1 server name, this formula would be rejected by the KEY_ERR tracer:

    =DBRW(server&":BalanceSheet",$A13,$B$10,$B$2,$B$3)

    I corrected this kind of false positive. Besides this, the KEY_ERR tracer could not handle the VIEW function argument of "!", which stands for a custom selection of elements in the grid of the view. You can let me know other types of correct functions that my code would incorrectly flag as an invalid DB formula. Please send me an email and I will do my best to incorporate it.

    Turbo Integrator

    The other procedures in the VBA code are mere copies of the KEY_ERR tracer code, albeit changed somewhat. I would now like to spend a word on the TI code that creates the custom view. The seasoned TM1 person will understand it without much explanations, nevertheless the code contains nice constructs and formulas.

    Create a new TI process with String parameters pCube, pElements and pViewPositions. Here is the code for the Advanced > Prolog tab: 

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

    #####
# Creating a view based off cube name and elements
# 01/22/12
#####
    
IF(CUBEEXISTS(pCube)=1);

     vTemp='Detail_' | TIMST(Now,'\y\m\d \h\i\s');

     VIEWCREATE(pCube,vTemp);

     vIndex=1;
     vEnd=0;
     WHILE(vEnd=0);
          vDim=TABDIM(pCube, vIndex);

          IF(vDim@='');
             vEnd=1;
          ELSE;
               vElement=SUBST(pElements,1,SCAN('§',pElements)-1);
               vViewPosition=SUBST(pViewPositions,1,SCAN('§',pViewPositions)-1);
               If(vViewPosition@='T');
                    VIEWTITLEDIMENSIONSET(pCube,vTemp,vDim);
                    VIEWTITLEELEMENTSET(pCube,vTemp,vDim,Dimix(vDim,vElement));
              Else;
                    VIEWROWDIMENSIONSET(pCube,vTemp,vDim,vIndex);
              EndIf;
               pElements=DELET(pElements,1,SCAN('§',pElements));
               pViewPositions=DELET(pViewPositions,1,SCAN('§',pViewPositions));
               vIndex=vIndex+1;
          ENDIF;
     END;
     VIEWROWSUPPRESSZEROESSET(pCube,vTemp,1);

ENDIF;

    In this process I create a new view on the cube pCube. The view name will be 'Detail_' followed by a date time stamp. Looping over the dimensions of the cube, I set the dimensions as title dimensions and select the elements that we had in the DB formula. They are passed to this process with the parameter pElements. The approach of shortening the variable pElements in each iteration of the loop, was explained in this article. I use the DELET function. Since we do not specify a subset in the dimensions, TM1 will default to the All subset, which is what I want: the subsets in the custom view show all elements so that we can simply drag a dimension on the grid. With this simple movement and without entering the Subset Editor, we are able to slice and dice in a fast way.

    In case of the VIEW function, the respective dimensions that show up as "!" will be put in the rows of the cube view. All elements in that dimension are part of the subset. The zero suppression is applied on rows. Beware that stacking a lot of large dimensions might result in a rather big view (layout-wise but more importantly, memory-wise).

    Done !

    That’s all ! I know that it is quite some code but it is very instructive to go through it in full detail. Both this VBA code and TI code show constructs and formulas that are not used every day. I am sure that this code will save me a lot of time. I am sure that the reader can write a TI process to clean up detail views that we do not need after a while.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links