DB to view: finalized material

Example files with this article:
  • DB to view: finalized code
  • Introduction

    All good things come in three, they say. So once more I updated my code to create a one-cell cube view based off functions like DBRW, DBSS and the like. The cell in the cube view is that particular cell in the TM1 function.

    Comparing to the code in the earlier articles (part 1 and part 2) my latest code has these characteristics / differences:

    1. Only valid functions (no errors like *KEY_ERR) can trigger the creation of the view.
    2. The code uses the TM1 API rather than executing a Turbo Integrator process.
    3. Aliases are allowed.
    4. The view is a private view for the user asking for it.
    5. Subsets in the view show all dimension elements.
    6. The code immediately jumps to the Server Explorer.


    Let me expand on the above. I thought about setting up the code, so that any cube-like TM1 formula could create a view: incorrect DBRW formulas leading to *KEY_ERR could be handled too. We treat the offending argument(s) by showing all dimension elements in either rows or columns, in a zero-suppressed view. Since the TM1 Tools show us what arguments are wrong, I do not see a lot of value of this approach. It could be interesting for the VIEW function with argument "!". In that case, all elements in that dimension could go in rows or columns. But I did not do this, all dimensions go to the titles section of the cubeview and the view contains only 1 cell. However, given the nature of the dimension subsets I show, it is very easy to see much more detail in the view: just drag the dimension on-grid. See below.

    The TM1 API was used in this work. I was not very happy with my previous approach since it relies on executing a TI process. I do not want to have such processes in the customer’s TM1 model and above all this code should save time instead of losing time on copy/pasting code to TI. The API takes away the need for a TI process (which could be deleted or renamed).

    If your DB-like formula uses aliases (as is very usual), the code will notice this. The view is able to show aliases for the respective dimension(s).

    The end result of the code is a cubeview, private to the user who launched the code. Besides that, the view is zero-suppressed and recalculates automatically. For quick analyses this seems the better option to me.

    Any TM1 cubeview is comprised of subsets, just as many as there are dimensions in the cube. In the subsets of the view, I show at position 1 (selected) the chosen element in the original formula. Underneath, all dimension elements in the hierarchized structure will follow. This makes it very easy for analysis purposes.

    After creating the view, the code immediately jumps to the Server Explorer. There you can browse to the view and open it up. The view name is comprised of the word "DETAIL" followed by a date time stamp. Views on control cubes are possible too. Browse to the cube you need; the Server Explorer objects are refreshed programmatically (Application.SendKeys to send an F5). Although I am not a big fan of this method it seems to work reliably in my tests. Maybe later on I whip up some code using the Windows API to send a message to the active window. That is for later, for sure. The Refresh part of the code could lead to a slowdown in the code, normally the view creation is lightning fast. But you will need to wait for that Refresh anyway so why not do it programmatically instead of manually?

    The TM1 API in VBA code

    Here is my code that should go in the userform module called frm_Trace (just copy/paste all code below the add-in’s source code):

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Wim Gielis ' http://www.wimgielis.com
    ''''' ' VBA code to create a view based off a DBRW, … function ' 01/29/12 '''''
    Dim cnt As Control Dim pElements As String, pAliases As String Dim i As Long, j As Long, sAttrName As String Dim dimName As String, dimNameAttr As String, sAlias As String Dim vAttrName As String, vAttrType As String If Len(tm1Function) Then If isCubeRef Then For Each cnt In Me.Controls If Left$(cnt.name, 3) = "val" Then If cnt.BackColor = vbRed Then Exit Sub pElements = pElements & "" & cnt.Text i = i + 1 dimName = Run("TabDim", object, i) dimNameAttr = server & ":}ElementAttributes_" & dimName pAliases = pAliases & "" For j = 1 To Run("DIMSIZ", dimNameAttr) sAttrName = Run("DIMNM", dimNameAttr, j) If Run("DTYPE", dimNameAttr, sAttrName) = "AA" Then If Trim(cnt.Text) = Trim(Run("DBRA", server & ":" & dimName, cnt.Text, sAttrName)) Then pAliases = pAliases & sAttrName Exit For End If End If Next End If Next If MsgBox("Do you want to create a view with these particular cells?", vbQuestion + vbYesNo, _ "Create detail view") = vbYes Then CreateDetailView server, objectOnly, pElements, pAliases End If End If End If
    End Sub
    Sub CreateDetailView(ServerName As String, sCube As String, pElements As String, pAliases As String)
    Dim hUser As Long Dim hServer As Long Dim hPool As Long Dim hCube As Long Dim hView As Long, sView As String Dim iRegister As Long 'general stuff GetUserServerAndPoolHandle hUser, ServerName, hServer, hPool hPool = TM1ValPoolCreate(hUser) hCube = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerCubes(), TM1ValString(hPool, sCube, 0)) 'create view hView = CreateView(hUser, hPool, hCube, pElements, pAliases) If hView = 0 Then MsgBox "Creating the view failed" Else Call TM1ObjectPropertySet(hPool, hView, TM1ViewShowAutomatically(), TM1ValBool(hPool, 1)) Call TM1ObjectPropertySet(hPool, hView, TM1ViewSuppressZeroes(), TM1ValBool(hPool, 1)) sView = "Detail_" & Format(Now, "yyyymmdd_hhmmss") iRegister = TM1ObjectPrivateRegister(hPool, hCube, hView, TM1ValString(hPool, sView, 0)) If TM1ValType(hUser, iRegister) <> TM1ValTypeObject() Then MsgBox "Registering the view failed" End If 'open TM1 Server Explorer and refresh Run "CUBES_BROWSE" SendKeys "{F5}", True TM1ValPoolDestroy hPool
    End Sub
    Function CreateView(hUser As Long, hPool As Long, hCube As Long, pElements As String, pAliases As String)
    Dim hDim() As Long Dim hSub() As Long Dim hElm As Long Dim hView As Long Dim vSubTitles As Long Dim vSubColumns As Long Dim vSubRows As Long Dim vElements() As String Dim vAliases() As String Dim iCtr As Integer Dim NDims As Integer 'cube dimension count NDims = TM1ValIndexGet(hUser, TM1ObjectListCountGet(hPool, hCube, TM1CubeDimensions())) 'Redim arrays for actual dim count ReDim hDim(NDims - 1) ReDim hSub(NDims - 1) vSubTitles = TM1ValArray(hPool, hSub, NDims) vElements = Split(pElements, "") vAliases = Split(pAliases, "") For iCtr = 1 To NDims 'Create an empty Subset hDim(iCtr - 1) = TM1ObjectListHandleByIndexGet(hPool, hCube, TM1CubeDimensions(), TM1ValIndex(hPool, iCtr)) hSub(iCtr - 1) = TM1SubsetCreateEmpty(hPool, hDim(iCtr - 1)) 'get all the dimension elements in the subset, following the hierarchy Call TM1SubsetAll(hPool, hSub(iCtr - 1)) Call TM1SubsetSortByHierarchy(hPool, hSub(iCtr - 1)) 'insert the chosen element at position number 1 hElm = TM1ObjectListHandleByNameGet(hPool, hDim(iCtr - 1), TM1DimensionElements(), TM1ValString(hPool, vElements(iCtr), 0)) Call TM1SubsetInsertElement(hPool, hSub(iCtr - 1), hElm, TM1ValIndex(hPool, 1)) 'set the alias, if needed If Len(vAliases(iCtr)) Then Call TM1ObjectPropertySet(hPool, hSub(iCtr - 1), TM1SubsetAlias(), TM1ValString(hPool, vAliases(iCtr), 0)) 'update the array for the title dimensions and subsets Call TM1ValArraySet(vSubTitles, hSub(iCtr - 1), iCtr) Next hView = TM1ViewCreate(hPool, hCube, vSubTitles, TM1ArrayNull, TM1ArrayNull) If TM1ValType(hUser, hView) = TM1ValTypeObject Then CreateView = hView Else CreateView = 0
    End Function

    Fine, to reiterate, you need to copy/paste these 3 procedures below the existing code in the userform frm_Trace. (The code is also saved in the file on top the page in the downloads section.) That is all. Then you get something like this:


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links