DB to view: updated material

  • Jan. 23, 2012

Introduction

This article is an UPDATED VERSION of this very recent article. While I stress that the code and material there is fully working and good study material, the material in this article is the better choice. It is more concise, better code and extends the TM1 Tools utility directly. In fact I did not notice the latest version of these tools (beginning of 2011) and I based my code and texts on the original code. As a result, some of the improvements in the other article (for instance the one on the TM1 VIEW function) are moot now.

This article will endeavor 3 things:

  1. extend the TM1 tools utility with the possibility to create a TM1 view based on the active cell
  2. simplify the TI process code we had in the earlier article
  3. extend the KEY-ERR tracer to not stop at named constants (which can be valid arguments to functions)

Extending TM1 Tools by creating an automatic view

I added a bit of VBA-code just to do this. I stress the word "added" since I did not change any existing code in there. This makes my life (and your life) easier at future updates of the source code of TM1 Tools.

Here is the code for the userform module called frm_Trace (just copy/paste the code in the addin source code): 

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


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


'''''
' VBA code to create a view based off a DBRW, … function
' 01/23/12
'''''

    Dim cnt As Control
    Dim pElements As String
    Dim tm1ServerName As String, procName As String, p1 As String, p2 As String

    If CloseMode = 0 Then

        If tm1Function Like "DB*" Then

            For Each cnt In Me.Controls
                pElements = pElements & IIf(Left$(cnt.name, 3) = "val", cnt.Text & "§", "")
                If cnt.BackColor = vbRed Then Exit Sub
            Next

            If MsgBox("Do you want to create a view with these particular cells?", _
               vbQuestion + vbYesNo, "Create custom view") = vbYes Then

                tm1ServerName = server
                p1 = objectOnly
                p2 = pElements

                '--- Update as required
                procName = "Create view from DB"    ' the name of the process being called
                '--- end updates section

                RunTIProcess tm1ServerName, procName, p1, p2

            End If

        End If

    End If


End Sub

This code will be executed whenever you hit the cross in the top-right corner of the tracer userform. If the tracer did not find any offending cell(s), and you asked the tracer to investigate a cube-like TM1 function, the code will concatenate the dimension elements. Since we know the cube name and the elements, it is possible to write a TI process that creates the view. Here I present the simpler version of the previous process. This process should be called "Create view from DB", or change the name in the above code. The process now takes 2 parameters: the cube name and the concatenated elements. To recreate the process on your TM1 server, create 2 String parameters: pCube and pElements. 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/23/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);
               VIEWTITLEDIMENSIONSET(pCube,vTemp,vDim);
               VIEWTITLEELEMENTSET(pCube,vTemp,vDim,Dimix(vDim,vElement));
               pElements=DELET(pElements,1,SCAN('§',pElements));
               vIndex=vIndex+1;
          ENDIF;
     END;
     VIEWROWSUPPRESSZEROESSET(pCube,vTemp,1);

ENDIF;

In the TI process I create a new view on the cube pCube. I call the RunTIProcess within TM1 Tools, so that even TM1RunTI is not needed anymore (yet a very good alternative). The thing is that the TI process must be present on the TM1 server, with either method. If we do not want this, we have to code the TM1 API. Someone wants to do this for me? :-) All code is then contained in the TM1 Tools addin, rather than partly in Turbo Integrator.

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 cube-like 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 mouse gesture and without entering the Subset Editor, we are able to slice and dice in a fast way.

The end result could look like these 2 illustrations:

Please read the other article too - many comments still apply here and will not be reproduced again.

Named constants

Until now I only added 1 procedure. But I was a bit surprised that the tracer would flag correct named constants as incorrect. Well, to put it more accurate: the VBA-code could not handle named constants. Examples: a server name and cube name as TM1server:BalanceSheet, or a named constant for the current month/year, and so on:

=DBRW(server&":BalanceSheet",$A13,CUR_YEAR,CUR_MONTH,$B$3)

Even though a DBRW formula returns the correct value the tracer flags the named constant argument in red. So I changed the function myEval to allow named constants. I did not test it in great detail, though, so there may be other types of false positives or other issues creeping in. For the code, insert the following IF statement above the first IF in the function myEval. The original first IF becomes ELSEIF. 

    If isName(arg) Then
        myEval = Trim(Replace(Mid(ActiveWorkbook.Names(arg).RefersTo, 2), """", ""))
    Else 'original code of myEval

Paste 1 additional function: 

Private Function isName(arg As String) As Boolean

'Find out if an argument is a name
    Dim dummy As Variant
    On Error Resume Next
    dummy = ActiveWorkbook.Names(arg).RefersTo
    If dummy <> Empty Then isName = True


End Function

A last change in the source code of TM1 Tools: with respect to evaluating the DBSA function, the TM1 Tools code is 1 argument off. You can amend it by inserting this line of code: 

If isSendRef Then expectedNumArgs = expectedNumArgs - 1
in between these 2 lines of code of the Private Sub UserForm_Activate() code: 
expectedNumArgs = countExpectedArgs
' here
dimName = objectOnly

Done !

That’s it ! You can let me know (other) types of correct functions that my code would incorrectly flag as an invalid formula. Please send me an email and I will do my best to incorporate it. See you next time for more TM1 tips and code.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links