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:
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 SettingsOwnerMy Documents estADOdb1.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.
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.
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:
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:
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.
That is definitely an interesting concept. Have you ever run across PALO? It is quite similar to what you describe your client as using.
http://www.jedox.com/en/enterprise-spreadsheet-server/excel-olap-server/palo-server.html
Re: “Note that I would never deploy an event procedure in a worksheet code module,…”
Why is that? Could you elaborate?
Jim Cone
San Francisco, California
Conceptually this looks very promising. I have seen a couple of commercial products which offer auditing functionality utilising SQL Server (think SoX compliance) but the spreadsheet source data is always OLAP not relational.
One thing that struck me is that the data would have to be sourced directly from a table value rather than via a query. Write-back functionality to a table will update a single value, is the data was sourced through a query – an aggregate query the cell value will be a composite of multiple underlying value in the database. Any thoughts how this might be handled?
“I would never deploy an event procedure in a worksheet code module…”
It’s difficult to control and maintain. It also limits the functionality to a single worksheet. To replicate the same functionality across multiple worksheets/workbooks would be a maintenance nightmare. This would be better served as an xla or COM add-in.
Cheers – Marcus
Tushar prefers housing his event code in class modules separate from the Excel objects. The instance of the event-responsive object must be instantiated first, which gives the programmer more control.
From a programmatic standpoint this adds reliability and probably makes more sense. I admit, though, that as a mercenary programmer, I tend to use whatever works, and sometimes keeping events and other procedures on the modules behind the objects is more practical.
Jon,
I completely agree. I’ve always found it a bit curious at how many times I’ve seen code examples here implemented via class modules. Maybe more elegant, but not alway practical. Like you, live in a world where the client wants to take hard right turns on every aspect of his “application”. This forces the “use what works” methodology. Of course the other other answer is that I’m a hack that’s in the wrong line of work. Either way, I’m gettin paid son!
Jim Cone wrote “Re: “Note that I would never deploy an event procedure in a worksheet code module,…Why is that? Could you elaborate?”
One of the fundamental tenets of developing software is to separate code from data. Contrary to the opinion of some (many?) this isn’t some “ivory tower” argument. It’s rooted in hardcore practicality.
Imagine you have code in workbooks that contain user data. Now, you want to change the code. It’s fundamentally impossible. There will be any number of copies of these files on any number of computers, desktop and laptop, at work and at home. And, that’s if your customer base is limited to one person. I’m currently working with a company with a global presence. The system I’m developing was originally intended for one small group. But, when I showed them how flexible the UI would be the reaction was “Wow! There are people around the world who might benefit from this.” If that does happen — and it is far from clear it will — the system will be deployed on hundreds, if not thousands of PCs. How anyone possibly expects to distribute updated code when it is scattered across hundreds if not thousands of workbooks is beyond me.
There’s another important issue. Distributing code in workbooks requires users to deal with the “This file contains macros” warning. And, it encourages sloppy habits. Either people will lower their security setting or will train themselves to click ‘Enable’ as a reflex. Then, the one time they open a file that has intentional or otherwise bad code they will truly regret it. Those who had administrative responsibility for mainframe systems or even DOS systems know what I’m talking about.
Finally, there’s the issue of IP and the risk of someone tweaking the code and then claiming “your code doesn’t work.” The latter happened to me a long time ago and is the reason why all my add-ins are distributed with the VB project locked.
Bottom line…the *only* practical way to distribute code is in the form of an add-in (XL add-in, COM add-in, XLL, whatever, but it has to be a “code only document.”) Yes, it’s a little extra work but it’s the only real game in town.
There are any number of examples on how to manage events in other documents through code in an add-in.
For workbook/sheet/chart events see
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL%20events.htm
though frankly that document needs an overhaul.
For buttons and ActiveX controls see my 2 posts at
http://www.mrexcel.com/board2/viewtopic.php?t=278015
and I’m sure you can find more examples through Google.
For menus, John Walkenbach has documented a method (http://www.j-walk.com/ss/excel/tips/tip53.htm) I use a similar though not identical method and Bullen et al document a similar approach in their book Professional Excel Development.
Marcus:
Yes, in the example, I identified a single ‘leaf’ element and that made it possible to update that value. If the value in the worksheet were an aggregate value — for example SUM(DataVal) for Col1=”a” — an update would be disallowed. I cannot think of any other practical alternative.
Mike –
Some days, what pays the bills = what works.
Tushar –
I agree with what you’re saying, and to the extent possible, I follow these principles. However, I am developing one very large project alongside my client. The code is open to both sides, though locked when deployed to their users. The project allows construction of elaborate financial models through addition of sheets to a basic model workbook. Most of the model building is controlled by an add-in. The basic workbook itself has some code, mostly for UDFs to enable the sheets to recalc when viewed by someone who does not have the add-in installed on their computer. The added sheets are templates, which contain any code they need in the worksheet code module. Since the data does not originate with the templates, it at least retains the separation until the sheet is inserted. A version tracking system assures version consistency between the main model workbook and the inserted worksheets. It’s all very complicated, and sometimes makes my brain hurt, but in this case the selective deviation from “best practices” makes it a more practical system. In fact, this use of templates with worksheet code make the addition of new templates an easy modular drop-in.
Hi Tushar, Excelers,
I did think to this for a long long time. My position to manage that was to intercept the input and move the data to another worksheet rather than crashed the formula. In fact, each time someone tried to input a value I use popup window in which he has to input the value. This value was then move to another hidden worksheet. The positive point is that the formula structure was never changed by the user. The negative point is that you have to develop code to manage that which become painful when the worksheet become complex.
So, your solution was very interesting but I just think to something a bit different…why not use XML rather than database. With XML you could setup 2 tags, one for the value, one for the data. This may be very useful if the user does not have access to a database. The XML could be stored and manipulated on a temp dir and, later on, the data could be easily ‘paste’ to the database if necessary.
This is my little contribution to your post which is very interesting.
Tushar –
Thank you for the excellent article. I wonder if you could expand on or perhaps consider for future discussion your comment following the first section of code in the article:
>>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.
I’ve working for some time on building a set of user-defined functions that will pull data from a read-only ODBC compliant database residing on a remote server. Using the code in the first snippet as example I was able to get the UDF to return a value, but I’m not confident that the solution is efficient or safe.
Would you consider speaking to those issues in a future post?
Thanks
jay
dayton, ohio