Tag Archives: Jedox ETL

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.





Combining filter criteria for multiple selections in cube extracts

Our good friend, world champion sailor and Jedox expert Sebastian Moser has offered up a cool post around getting a little more sophisticated with cube extracts in ETL:

Sometimes, you might want to Export data from a Jedox database to a DWH or other systems. Occasionally, you don’t quite get the correct result from ETL cube extract with provided filter functionality. Or exporting data in all possible combination does take a long time.

For example you would like to export all legal entities in their corresponding local currency. But in the cube you have translated your P&L to all other currencies to compare and consolidate them:



Therefore you would need to make a link between the legal entity and the currency in the cube extract. You are facing the situation that in the standard filter of the ETL extract you could define something like this:


You would expect Company USA in USD, Company Germany in EUR and Company Australia in AUD. But your result would rather look something like this:


So basically you are exporting all the possible combinations between legal entity and currency dimension. The key to solve this is creating a table of the correct combinations of what you would like to see. This could be a cube, a SQL table, a flat file or any other source. In my case I am using the elements attributes as the currency only depends on the legal entity.

First of all I have to create an attribute for in the legal entity Dimension:


Maintain the correct local currency for each legal entity:


Please make sure your entries in the attributes are correct spelling according to the currency dimension:


Entries like this will cause Errors in the ETL:


As we have defined all of our correct combinations we now need to bring it into the ETL. Therefore we read the dimension including it’s attributes:



In the next step we have to define the variables and place them into the cube extract as we now want to iterate through our combinations and extract the data from the cube.
Create the variables in ETL:


And place them into the cube extract:


This would only give you one set of combination of variables at a time. Therefore we want to feed the variables in the cube extract with all sets of our table (attributes).
You will need to map the column names of your set of combinations according to the variable name in the ETL:



In the last step you need to define the loop through the set of combinations to iterate the export of the data cube in a transform and union the data.


This transform will map every line of the “set of combination” loop source to the variables and executing the export “Cube_extract” for each set.

Your result will look like this:


So the legal entity is now linked to the currency and your extract show the data of all companies in their own currency.

You could use this procedure for as many combinations you need. If the process is not dependent on one dimension, (in this case Legal Entity) you could think about creating a cube for the combinations or a flat file. All you need is a table in ETL for mapping columns and rows to variables.

Click on the links for the database and ETL project that I used in the example above.

For further questions please don’t hesitate to contact Sebastian.Moser@consysmo.com

Jedox to SAP Connectivity Part 1 – The SAP Java Connector

Amongst other available connection types to other source systems, Jedox also provides connection to SAP R3 system.

The first step of configuring the Jedox SAP Connector is to setup the SAP Java Connector.

At the time of Jedox All-in-one suite installation, you can select the Jedox SAP Connectivity option. This consists technically of two components: A Jedox ETL Add-On which defines the SAP connection and extract types, such as SAP ERP content and SAP BW and SAP Add-On which has to be transported to the SAP System.

  • You need to have the SAP Java Connector Adapter Version 3 or higher downloaded on the machine where Jedox is installed. You will need to have a valid account to download it from SAP Service Marketplace. You need to download the relevant file based on your operating system.
  • Extract the relevant zip folder to a directory where Jedox is to be installed.
  • You will need to set the Environment Variables. Go to “Environment Variables” in the System Properties:


  • Create a new System Variable called: “CLASSPATH” which should specify the sapjco.3jar file in the SAP JCO install path, the below screenshot shows this:


  • In the existing System Variable called: “Path”, you need to add the entire SAP JCO install path in the variable value. The below screenshot shows that folder: sapjco3-NTAMD64-3.0.9 contains the SAPJCO3 dll, jar files etc:


  • To verify that the installation of SAP JCO has been done correctly, you can enter the following using command prompt:  java -jar {sapjco-install-path}/sapjco3.jar.
  • Then the following screen of SAP Java Connector comes up. This indicates that the SAP JCO installation has been done successfully. If there was any error, you can see the errors in the window:


Note: For more information on SAP JCO Connector, you can open the Intro document in your SAPJCO install-path in a web browser: file:///c:/sapjco3-install-path/javadoc/intro.html 

To follow on to the next steps in Part 2 of this post, please click here