A quick update for those who have been using the Excel spreadsheet we posted a while back for running ETL processes. Due to some fundamental changes in Jedox 6, that spreadsheet requires some additional information (such as username and password) to run loads and jobs. You can find the latest one here.
Hope you like it. Please let us know what you think.
An update to the last post on running ETL via Excel.
We have modified the combo boxes to List boxes for clarity and updated the code. Note that the current release only runs on 5.1 – there are security changes coming in Jedox 6 which will mean the code will have to change in the VBA module.
You can download the excel file here and the vba module (for customising your own files) here.
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.
Templates should be simple, clean and easy to understand. They can come in a myriad of different formats, styles and cover a multitude of various models. As a template designer, the key is to try and make the template itself disappear into the background, while at the same time coaxing the actual information it contains into the foreground.
So no crazy fonts,
and no crazy colours.
You will give your users a headache, especially if they have to stare at it for a long time.
I have a pathological aversion to large spreadsheets. Probably the most common issue I encounter in consulting is people trying to read or write 20,000+ rows * 150 column spreadsheets from their OLAP system. Then wondering why everything is slow on their client machine.
In my opinion, if you have a decent BI system, and you have managed to create some cultural change in your organisation around how data is surfaced, you don’t really need a 20,000 row spreadsheet on a daily basis. All you do is create further chaos and completely miss the point of a BI system, which should be to simplify how users interact with your organisational data, not make that interaction more complex.
If there is one thing worse than Excel Hell, it is OLAP Hell.
But no matter how much I rant, the fact and the reality is that people do use and need big spreadsheets on a daily basis in their organisations. I cannot escape the reality of reporting pressures that my clients are put under to conform, especially to ‘the way things have always been done’.
So, in light of this, I was pretty impressed with the latest small but important functionality addition in Jedox 5.0 SR1 – the PALO.SETDATA_BULK function. Up until now, we have only had 2 options in Excel: PALO.SETDATA (slow for lots of cells) and Jedox Importer (slow for lots of rows of data, and a bit counter-intuitive for a management accountant). Both of these you can you use in either spreadsheet cells or vba.
The PALO.SETDATA_BULK allows you to take advantage of the same api’s that the ETL uses to load data in excel. PALO.SETDATA_BULK also takes advantage from a single array formula in a single cell (as opposed to 20,000 PALO.SETDATA formulas all trying to execute at the same time).
From my testing, it can send in over 20k rows in excel in less than 1.5 seconds. The same data sent in via normal PALO.SETDATA formulas take over 30 seconds .
Both tests were done against a localhost, so against a client server environment there will be other latencies like networks, etc. This is a massive improvement in anyone’s language and certainly will assist people who have the need for those large spreadsheets.
One note: you need to structure the data in a fact-table format to allow the array formulas to work. See the example against the Demo/Sales cube.
EDIT: I was asked to post the example spreadsheet, sans IF statement. You can grab it from here.
the naked leaf 