VBA | the naked leaf

Month ends are like haircuts. Towards the end of the process, there are a lot of little snips and clips. Enter month-end adjusting journal into ERP, run Jedox Integrator, rinse, repeat. The adjustments can go on for a long time, so an easy way to process them is critical. Having a custom Jedox web page to run jobs helps, but sometimes customers are so wedded to their Excel environments, that they would rather a “one stop shop” in a spreadsheet.

Necessity, as always, is the mother of invention. For various customers we have created from time to time excel driven ETL processes. We have used a couple of approaches but this latest one uses SOAP calls and web services to execute the jobs, just like the web macros do. This approach has many advantages : It is fast (as it uses the native Jedox ETL api), it is clean (you are not trying to build batch files on the fly, for example) and you can circumvent security issues (like Impersonation) when trying to talk to the server remotely.

Jorge Mendoza, our legendary Excel expert put the following files together. The Excel file is more of an ETL control centre. It allows you to refresh all jobs, review any object (including variables) and run , stop and check the status of jobs. It is pretty cool.

Instead of using this interface, you can attach the following function to your own code to run a job of your choice. As long as you have the module in the file, you are good to go:


CallETL(sServer As String, sProject As String, sType As String,
sName As String, Optional vVariables As Variant)

The vVariables is a 2 dimensional array, specifying the variable name and the variable value you want to pass.

You can download the Excel file here. You can also download the .bas file and import it into any excel vba project. You can get the .bas file here. We tested this on Jedox 5.1 SR3, but not on any other versions.

The final point is to get this to work on your server from a client machine, you must ensure that the ETL port is available outside the server. By default, is it not.

On the server, open the tomcat/conf/server.xml file. Search for “7775”. This is the default ETL port. Change it from “127.0.0.1” to “0.0.0.0”. This will not allow you to communicate via web services to this port.

NOTE: What we are doing here is essentially opening up an unsecured port on the server. If this is an issue in your environment, you can restrict access to a specific IP address, password protect the Excel file, or both. In Jedox 6, a new security layer will be introduced around ETL, which will mitigate this issue.

The Jedox Web Macro Engine is one powerful beast. It is based on PHP Hypertext Pre-processor (PHP5) and it enables power users and Jedox BI developers to write PHP code which is executed within Jedox Web’s Core run-time. What this basically means is it gives you the power of something like VBA on the web. It allows you to script event driven macros that can be executed by the user on demand.

Why on earth would you want to write macro scripts in Jedox web? For all the same reasons that you would write VBA in an Excel workbook. It helps provide an additional layer of sophistication, flexibility and power to the users of your reports.

Before you get started with Macros, a couple of points to note:

You cannot publish an Excel workbook with VBA macros to the web and expect Jedox to parse and re-write the VBA into PHP. This may sound obvious to some, but it is a common assumption that a lot of people make.
The debugging capability is limited in the Macro interface and there is no debugging niceties that you have in VBA, like a Watch window or an Object Explorer. There a some basic debugging capabilities, but you generally have to code and debug by hand. Once you get into the swing of it, it is straight forward.
When debugging code, a quick tip is to keep the core.log open in Notepad++ or some other editor. The Core log will tell you where you error is occurring and give you a bit more information as to why. This is handy, as often you will run your macro and nothing will happen on the user interface.
Like Excel, the Jedox Web spreadsheet has a different calculation routine to the Macro engine. This means that sometimes (especially when referring to cells on a spreadsheet), macro calculation may be out of sync with the cell calculation. There are a number of ways around this, but for the moment, it is just important to recognise that this can sometimes be an issue.
In terms of debugging, the message box is your friend. Use it to view variables and array values at run-time and certain points in the code.

Macros can be assigned to all Form Elements (check boxes, date pickers, buttons, drop lists). These objects, when accessed by the user, triggers the macro. As well as this, you can define your own UDF’s (User Defined Functions) which can be embedded in cells as formulas on a spreadsheet. In addition, you can actually distribute your own custom functions into the Macro Core engine, to make them available server wide.

If you come to Jedox web from the world of PHP, you are in luck. The Macro Engine gives you complete freedom in terms of the PHP you can write, as well as additionally implementing an extension to refer to spreadsheets. Existing PHP extensions can be added, which allow you to extend your application way beyond just reporting off cubes.

If you come to Jedox web via VBA (like me and I suspect a vast number of people reading this) , PHP can be a little daunting at first, but if you persevere you quickly get the hang of it.

I will be publishing a few examples over the next little while. Some examples are simple and some may be a little more advanced. If you are new to macros, then this may be a good place to start.