24.08.2013

Migrating TM1/Alea formulas in Excel to Analysis Services

Technical Value

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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden