Author Archives: Chris Mentor

About Chris Mentor

Director at Naked Data, over 13 years now in information management and most of that spent taming in-memory, OLAP technology.

ETL from Excel for Jedox 6

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.

 

 

 

 

ETL From Excel – an update

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.

2015-05-14 09_15_10-ETL-v7.xlsm - Excel

 

You can download the excel file here and the vba module (for customising your own files) here.

 

ETL from Excel

hair salon accountingMonth 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.

2015-02-27 17_14_09-ETL.xlsm - Excel

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.

Managing Web Reporting – Part 2

After being out in the wild for over 12 months, the Performance Dashboard has been one of the most popular posts on our blog. As it was originally written for in Jedox 5.0, we have updated it to reflect the latest functionality in 5.1 SR2. New Features

  • Hits chart now using a Stock type chart, that allows zooming in to specific days
  • Hits by Hour, to allow you to zoom in on the busiest time of the day
  • Doughnut chart now shows the report  for a specific hour, vs overall usage
  • Filters are clearer, and easy to reset.
  • Login stats, to see the numbers of users logging onto the server (including from Excel).
  • On the report tab, you can now open the specific report that you are analysing.
  • The ad-hoc tab now has a heatmap which helps with visualisation of the most popular reports. The drop list on this tab also now has the complete File Manager hierarchies to enable you to navigate easily.

Continue reading

Logging your users

Occasionally during a planning process, disaster strikes. Users can accidentally delete hard-worked-on plans, change some key drivers or alter report values during a printing process. Plans may be altered by unwittingly by colleagues or by well meaning management without understanding the consequences. This is where you need to understand what has been entered in the system and by whom. Jedox gives you the flexibility to create user logs to illustrate what has changed.

Continue reading

Increasing Visibility of Load Errors

The Jedox Task Manager gives you key information about a job’s execution. It notifies you every time a job is run, regardless whether there were errors or not. Sometimes though, you might require a bit more sophistication in the notifications. For instance, you might only want to receive a notification when there are errors or warnings in a job. Maybe receive the jobs’ error log in the body of an email or even attach the full server log as an attachment.

Fortunately, this is all possible with a small piece of groovy script magic.

Continue reading