Category 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.

 

 

 

 

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.

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

Time and Time Index

Time-PhotoA fair number of our clients run Jedox models down to a day level. Retail Merchandising models for example, typically require a Daily Sales Cube. Same for the Sales component of a Manufacturing model. Daily Sales models are on the large side for Jedox – in my experience, anywhere between 4 – 8GB in memory, depending on the dimensionality and the volume of data loaded. Like all Jedox models, density is your friend. Keeping the data model efficient and compact will ensure rules and access times to the data are optimised.

A Time dimension down to an individual date level is therefore a critical component to building these types of models. But having the dates in a dimension is not nearly enough – you need the consolidations and attributes and measures to make it a truly workable within the model. For example, you will most probably require Month-To-Date (MTD) calculations (or sometimes Week-to-Date). However, MTD consolidations in a Date dimension are not desirable. You can potentially end up with hundreds (or even thousands) of crazy consolidations that can confuse the end user. It is usually far more efficient to utilise either a measure to hold the Month to Date values for a particular measure, or use a separate “Time Index” dimension.

Time Index Dimension

A separate time index dimension allows you to work in conjunction with a Date dimension to calculate MTD or YTD values. Year to Date is especially problematic – when you are dealing with a Daily model, users require “Date Year-To-Date” calculations – not just the year-to-date consolidation of the months.

When employing a Time Index dimension, data is loaded against the specific date in the Date dimension:

tti1

and the element “Date” in the Time Index dimension:

tti2

The MTD, YTD (and potentially LTD) can then be calculated and stored against the appropriate elements. MTD, YTD and LTD values are typically calculated in 2 ways: you can do the calculation in the SQL if your data is coming from a relational source (fastest) , or you can use groovy and loop through your cube data to perform the calculation (slowest). I have seen the MTD calculation also done in rules, but this would be inefficient for all but the smallest models.

The key element here is MTD. If you are running out of time/resources on the server, both YTD and LTD can be back calculated on your templates using attributes and the MTD values. But if you have the option, if it certainly nice to have these elements calculated for your end user by the system.

A word of warning – as you are essentially hardcoding the MTD / YTD data in the cube, be prepared for the size and load time of the model to grow.

Building the Date Dimension

So, back to the Date dimension. I have attached an ETL Project which builds a date dimension based on parameters (start and end year) and the calendar extract. It also builds for you automatically a bunch of attributes (next, prior) . For the Prior attribute, if the element is base, it will give you the prior date, month, prior month, etc. It also calculates the Last Day of the Month attribute which can be useful if you are rolling back exactly one month from any point in time, for example. You will note that the Date elements are all numeric. It is much easier to perform calculations on a numeric than a string on a template. There is a few different formatting attributes that you can modify to suit your date format.

Download the Date project here.

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:

cfs_sebastian1

cfs_sebastian2

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:

cfs_sebastian3

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:

cfs_sebastian4

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:

cfs_sebastian5

Maintain the correct local currency for each legal entity:

cfs_sebastian6

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

cfs_sebastian7

Entries like this will cause Errors in the ETL:

cfs_sebastian8

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:

cfs_sebastian9

cfs_sebastian10

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:

cfs_sebastian11

And place them into the cube extract:

cfs_sebastian12

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:

cfs_sebastian13

cfs_sebastian14

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.

cfs_sebastian15

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:

cfs_sebastian16

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