Wim‘s Excel, TM1 & soccer site

DB to view: finalized material

Jan. 29, 2012

	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:

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

Explanations

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
' https://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: