An Excel-based DQL Query Tool
January 12, 2015 15 Comments
One of the items on my personal to do list was to create a simple DQL query tool using Excel. To me, it seemed like a natural fit: Excel was adept at displaying tabular data, it had a robust scripting language to implement the logic (VBA), and VBA was dot-Net-compliant so it could load the DFC PIA directly. The only problem is that I waited too long to create this tool and the DFC PIA is no longer distributed or supported by EMC.
Instead of using the DFC PIA, I discovered Obba — a framework for integrating Java with spreadsheets, both Excel and Open Office. It is a little “heavier” solution than I had hoped to create but useful and interesting nonetheless. I had envisioned the DQL Query Tool as more of an Excel Add-in or something as portable. Obba launches a small, local, Java-based server to handle the integration instead of handling the DFC calls internally as the PIA would have.
You can download the Excel DQL Query Tool here if you are interested. I found that VBA/Obba programming takes a little getting used to in the way it handles variables, objects, and methods, but after a few examples it makes sense.
For details regarding installing and running Obba, see the Obba website (www.obba.info). For details regarding configuring and running the Excel query tool, see the README file included in the download.
Query Screen
Results Screen
Next, I think I will pursue creating a tool using the DFS RESTful services. For this interface I will likely use Excel-REST. Hopefully that integration will prove to be a little “lighter weight” than this one.
dm_misc: Miscellaneous Documentum Information 
Your link to Obba has a single “b”. Please correct.
LikeLike
Got it. Thanks for the catch!
LikeLike
Good idea! Could you help me with your tool? When I pushing any button on Query sheet it returns error like “…bla-bla can’t execute macros Obba.xla! bla-bla…”. For example, for “Logon” button it look like “… Obba.xla!DctmInit …”.
May be you placed you custom macros for Documentum in Obba.xla application? But how we can try your tool? Or am I confusing you?
LikeLike
Make sure you have Excel configured to allow macros, and do the Obba install before you open the spreadsheet.
LikeLike
Hello,
Thanks for sharing your knowledge, please help with your tool I’ve followed all the steps but I get the following error: Can not run the macro ” Can Obba.xla DctmInit the macro is not available in this book! . .
Please support , I have my excel configured to allow all macros , first open the DCTM -query- tool.xlsx file and then run the RunExcelQueryTool.bat .
Diagnosis by running the file – Obba for Excel.xls everything works fine. Report attached:
Test Result Status
Client 1 OK OK
Client 2 OK OK
Client 3 OK OK
Server 1 OK OK
Server 2 true LOCAL
Server 3 4.2.2 Version
Server 4 1.8.0_101 Version
Java Test 1 1 OK
Java Test 2 2 OK
Thank you
LikeLike
Pingback: Links to All of My Source Code | dm_misc: Miscellaneous Documentum Information
Hello,
The link for this DQL tool is dead !
Could you post a new one ?
We have a bunch of documented servers here and the web interface is painfully slow to navigate. I’m hoping to make an excel user interface to get right to the goods in one click for our users.
And I have to do this without needing admin rights on my PC since IT wants huge $$$$$$$$$$$$ for the slightest request !
LikeLike
I just checked and the link is live for me: https://app.box.com/s/epz1mds965atolaffbpe. This page gives a warning that no preview is available for the documentum.zip file, but if you click the Download button below the warning the file downloads just fine. Let me know if you still have trouble and I can email it directly to you.
Note that you must download and install Obba before the Excel DQL tool will work.
LikeLike
Hi Scott,
I’m facing the same issue(can’t download). Could you please send the file to my mail? Thank you in Advance!
LikeLike
I tried to reply to the email address listed but the email with the ZIP attachment was rejected.
LikeLike
Hello,
Thanks for sharing your knowledge, please help with your tool I’ve followed all the steps but I get the following error: Can not run the macro ” Can Obba.xla DctmInit the macro is not available in this book! . .
Please support , I have my excel configured to allow all macros , first open the DCTM -query- tool.xlsx file and then run the RunExcelQueryTool.bat .
Diagnosis by running the file – Obba for Excel.xls everything works fine. Report attached:
Test Result Status
Client 1 OK OK
Client 2 OK OK
Client 3 OK OK
Server 1 OK OK
Server 2 true LOCAL
Server 3 4.2.2 Version
Server 4 1.8.0_101 Version
Java Test 1 1 OK
Java Test 2 2 OK
LikeLike
Hello Mr. Scott ,
Thank you very much for all your lessons left until then I ignored Documentum until this year.
I am now using the tool documentun and need your support.
Please contact me at my email :
escorpio_287@hotmail.com ,
The consultation is the DQL query tool based on Excel.
My question eds the next.
1. I have to install the DFC on my desktop PC to connect to remote server Content . ????
2. Where the obba set up to connect to a remote server
LikeLike
Let me take a closer look at this. I recently set up a new laptop without Obba or this Excel DQL Query Tool, so it will be a good test to see if I can follow my own instructions. Stay tuned…
To quickly answer you questions: 1) Yes, the DFC must be installed locally. You might be able to get around this with some clever path variables, but I haven’t tried. 2) the ObbaClient.properties file contains a pointer to the DFC.config file which indicates the location of the DCTM server.
LikeLike
I am sad to report that I no longer have the source code for the Documentum Excel Query Tool. The Excel spreadsheet contained in the download archive on my blog does not contain the Documentum-specific code to interface Obba with the Content Server. I don’t know how this code was omitted from the archive since it was VBA code attached to the Obba macro sheet in the spreadsheet. Nonetheless, I no longer have it. It wasn’t too difficult to create and you have the framework in the spreadsheet if someone wants to take this task on. Again, I apologize for my sloppy configuration management.
LikeLike
Pingback: Jupyter Notebooks and Documentum – Random Eklektos