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
-
conn.OLEDBConnection.Reconnect
-
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
-
Next
-
mdx = „SELECT (“ & tuple & „) on Columns from [“ & conn.OLEDBConnection.CommandText & „]„
-
cs.Open mdx, adoconn
-
result = cs.Item(0)
-
cs.Close
-
If IsNull(result) Then result = „“
-
CubeValueVBA = result
-
Exit Function
-
CubeValueVBA_error:
-
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
-
Else
-
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.
Kommentare (0)