Migrating TM1/Alea formulas in Excel to Analysis Services

24.08.2013 Hilmar Buchta

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 :

  1. Function CubeValueVBA(connstr As String, ParamArray axes()) As Variant
  2.     On Error GoTo CubeValueVBA_error
  3.     Dim conn As WorkbookConnection
  4.     Dim adoconn As ADODB.connection
  5.     Dim cs As ADOMD.Cellset
  6.     Dim tuple As String
  7.     Dim mdx As String
  8.     Dim result As Variant
  9.     Set conn = ActiveWorkbook.Connections(connstr)
  10.     If Not conn.OLEDBConnection.IsConnected Then
  11.         conn.OLEDBConnection.Reconnect
  12.     End If
  13.     Set adoconn = conn.OLEDBConnection.ADOConnection
  14.     Set cs = New ADOMD.Cellset
  15.     For Each s In axes()
  16.         If tuple <> „“ Then tuple = tuple +,
  17.         tuple = tuple + s
  18.     Next
  19.     mdx =SELECT (& tuple &) on Columns from [& conn.OLEDBConnection.CommandText &]
  20.     cs.Open mdx, adoconn
  21.     result = cs.Item(0)
  22.     cs.Close
  23.     If IsNull(result) Then result = „“
  24.     CubeValueVBA = result
  25.     Exit Function
  26. CubeValueVBA_error:
  27.     CubeValueVBA = Error$
  28.     On Error GoTo 0
  29.     Exit Function
  30. 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:

  1. Public Function dbr(conn As String, ParamArray axes()) As Variant
  2.     dbr = CubeValueVBA(„AdventureWorks“, _
  3.         „[Date].[Calendar].[Calendar Year].& chkstr(axes(0)), _
  4.         „[Product].[Product Categories].[Category].& chkstr(axes(1)), _
  5.         „[Measures].[Internet Sales Amount]“ _
  6.         )
  7. 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:

  1. Public Function dbr(conn As String, ParamArray axes()) As Variant
  2.     Select Case conn
  3.         Case „Sales“:
  4.             dbr =
  5.         Case „Sales2“:
  6.             dbr =
  7.         Case Else
  8.             do some error handling
  9.     End Select
  10. End Function

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

  1. Function chkstr(ByVal s As String) As String
  2.     s = Trim(s)
  3.     If Left(s, 1) =[And Right(s, 1) =]Then
  4.         chkstr = s
  5.     Else
  6.         chkstr =[+ s +]
  7.     End If
  8. 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.

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten