Search This Blog

Saturday, August 24, 2013

Migrating TM1/Alea formulas in Excel to Analysis Services

SQL Server 2005-2012

In scenarios where TM1 (Applix, then Cognos, now IBM) or MIS Alea is used, you often find workbooks using specific TM1/Alea functions like DBR(), DBRW() or DBGET() to access cube values. Since those workbooks are easy to create you may find a lot of such workbook reports in existing TM1/Alea installations. Assuming you have successfully migrated the cube to Analysis Services (Multidimensional or tabular) there might still be a lot of work left in order to migrate the workbooks.

One easy method in order to reuse the old workbooks and to check, if the new cube matches the results from the old one is to open the Excel workbook without the TM1/Alea addin being installed, add an SSAS connection to your cube and provide the VBA code for the functions (DBR, DBRW, DBGET etc.) by yourself. This is relatively easy to do and makes it easy to reuse the old workbooks without much work.

To illustrate this process, let’s assume you’re connected to a TM1 cube “Sales” with only two dimensions: Date and Product Category. In order to call the cubevalue-function from VBA I’m using a function that is very similar to the code I posted back in 2008 to provide a “CubeValue”-function for Excel 2002/2003:

Function CubeValueVBA(connstr As String, ParamArray axes()) As Variant
    On Error GoTo CubeValueVBA_error
    Dim conn As WorkbookConnection
    Dim adoconn As ADODB.connection
    Dim cs As ADOMD.Cellset
    Dim tuple As String
    Dim mdx As String
    Dim result As Variant    
    Set conn = ActiveWorkbook.Connections(connstr)    
    If Not conn.OLEDBConnection.IsConnected Then
    End If
    Set adoconn = conn.OLEDBConnection.ADOConnection    
    Set cs = New ADOMD.Cellset    
    For Each s In axes()
        If tuple <> "" Then tuple = tuple + ","
        tuple = tuple + s
    mdx = "SELECT (" & tuple & ") on Columns from [" & conn.OLEDBConnection.CommandText & "]"
    cs.Open mdx, adoconn
    result = cs.Item(0)
    If IsNull(result) Then result = ""
    CubeValueVBA = result
    Exit Function   
    CubeValueVBA = Error$
    On Error GoTo 0
    Exit Function
End Function

Please remember to add references to “Microsoft Activex Data Objects Library” and “Microsoft Activex Data Objects (Multi-Dimensional)” in order to use the code above.

Now you can create for example a DBR(…) function like this:

Public Function dbr(conn As String, ParamArray axes()) As Variant
    dbr = CubeValueVBA("AdventureWorks", _
        "[Date].[Calendar].[Calendar Year]." & chkstr(axes(0)), _
        "[Product].[Product Categories].[Category]." & chkstr(axes(1)), _
        "[Measures].[Internet Sales Amount]" _
End Function

Of course you need to adjust this function to the type and order of the dimensions in the TM1/Alea cube of your installation. Within the DBR-function (as well as with DBRA, DBRW, DBGET etc.) all dimensions have to be addressed and the order is important. For the CubeValue function usually we only specify the axis which are not on their default element. Also, if you have more than one cube being queried from your workbook and if the dimension list differs, you need to switch to right connection as shown below:

Public Function dbr(conn As String, ParamArray axes()) As Variant
    Select Case conn
        Case "Sales":
            dbr = ...
        Case "Sales2":
            dbr = ...
        Case Else
            ... do some error handling
    End Select
End Function

For dealing with the square brackets, I used a helper-function:

Function chkstr(ByVal s As String) As String
    s = Trim(s)
    If Left(s, 1) = "[" And Right(s, 1) = "]" Then
        chkstr = s
        chkstr = "[" + s + "]"
    End If
End Function

So, with only a little bit of code, many calls in the workbook can be migrated to Analysis Services. However, I would consider this a temporary solution, as the connection handling and the need of having macros in your Excel workbook is not optimal. But since the solution from above is very easy to implement a lot of the workbooks can be reused immediately while you have time to properly migrate workbook by workbook. Also, having the macros from above makes migration a lot easier since you already know the connections and the order of the axes to be addresses. You can also write a macro to replace the functions in your workbook with the proper CubeValue function calls.

No comments:

Post a Comment