On an ongoing project, the client uses the TM1 OLAP system. One of the interesting things I noticed was this:
A user can "slice" data from the database into Excel. The result is a new worksheet where the appropriate cell contains a formula such as =DBRW(...), which essentially looks up the TM1 database for the current value that corresponds to the specified parameters.
So far so good. But, here's the twist. One can enter a new value into the cell containing the formula. TM1 will update the OLAP database with this new value and restore the formula.
As soon as I saw what was happening I guessed how it was done. Here's the implementation of a proof-of-concept. Of course, as a proof-of-concept there are a lot of safeties, performance issues, and other niceties that are missing.
An obvious requirement is that that one must have a secondary data storage since it is impossible for a value and a formula to actually co-exist in a cell. So, that requires a backend database to store the actual value and I decided to use MS Access to create one.
The database had a single table with 3 columns: Col1, Col2, DataVal. For those who want to know how this maps to an OLAP system, think of Col1 and Col2 as dimensions in a TM1 OLAP system and DataVal as the value at the intersection of specific elements in those dimensions.
That led to the infrastructure to access data in the database. In a standard module:
Option Explicit
Dim Cn As ADODB.Connection
Dim aRSTable1 As ADODB.Recordset
Function initializeADO(DataSrcName As String) As ADODB.Connection
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = DataSrcName
.Open
End With
Set initializeADO = Cn
End Function
Sub openADO()
Set Cn = initializeADO("C:\Documents and Settings\Owner\My Documents\testADO\db1.mdb")
End Sub
Public Function DBVal(Table, Col1, Col2)
If aRSTable1 Is Nothing Then Set aRSTable1 = New ADODB.Recordset
If Cn Is Nothing Then openADO
On Error Resume Next: aRSTable1.Close: On Error GoTo 0
aRSTable1.Open "SELECT DataVal FROM " & Table _
& " WHERE Col1='" & Col1 & "' AND Col2='" & Col2 & "'", Cn
DBVal = aRSTable1.Fields("DataVal").Value
End Function
OK, nothing unusual about the above. It's standard stuff to write a User Defined Function that retrieves data from an external database. Again, remember this is proof-of-concept code and leaves out a lot of safeties and performance effectiveness issues.
This is used in a worksheet cell as =DBVal(A3,B3,C3) where A3 contains the Access table being queried, and B3 and C3 the values for the 2 columns Col1 and Col2 respectively. In TM1 parlance, this would correspond to the cube name, and the two elements of the 2 dimensions in the cube.
Next, the infrastructure to allow a new value to update the database.
First, a worksheet event procedure that keeps track of the existing formula. Note that I would never deploy an event procedure in a worksheet code module, but it works well to test concepts.
Option Explicit
Dim CellFormula
As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Target.HasFormula Then CellFormula = "": Exit Sub
CellFormula = Target.Formula
End Sub
Next, an event procedure that responds to a new value entered by the user.
Private Sub Worksheet_Change
(ByVal Target
As Range
)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.HasFormula Then Exit Sub
If CellFormula = "" Then Exit Sub
Dim NewVal
NewVal = Target.Value
updateDB CellFormula, NewVal
On Error Resume Next
Application.EnableEvents = False
Target.Formula = CellFormula
Application.EnableEvents = True
On Error GoTo 0
End Sub
The SelectionChange event procedure above saves the cell formula, if it has one, at the time the user selects the cell. Then, if the user enter a value, the Change event procedure uses the new value to update the database through UpdateDB and then restores the formula saved by the SelectionChange procedure.
The corresponding code for the updateDB subroutine in the standard code module:
Sub updateDB(CellFormula As String, NewVal)
If Cn Is Nothing Then openADO
Dim Params
Params = Split(CellFormula, "(")
Params = Split(Left(Params(1), Len(Params(1)) - 1), ",")
Cn.Execute "UPDATE " & Range(Params(0)).Value & " SET DataVal=" & NewVal _
& " WHERE Col1='" & Range(Params(1)).Value & "' AND Col2='" & Range(Params(2)).Value & "'"
End Sub
And, for completeness, code, in the standard module, to close the database connection:
Sub closeADO()
On Error Resume Next
aRSTable1.Close
Set aRSTable1 = Nothing
Cn.Close
Set Cn = Nothing
End Sub
To use the above, in the Access database, a table named Table1. I had 4 records in it corresponding to values of "a" and "b" for Col1 and Col2. The associated DataVal values were 1, 2, 3, and 4, respectively.
In the Excel worksheet, Cell A3 had the value "Table1" and in B3 and C3 one could enter the values corresponding to Col1 and Col2 (i.e., either "a" or "b"). The =DBVal() formula then retrieved the corresponding value from the database.
Now, enter a new value in the cell that contains the =DBVal() formula. The code will update the database with the new value and restore the =DBVal() formula. This will show the new value in the cell.
