Scripting in ETL

Some ETL tools out there use pure scripting to extract, load and transform data. Jedox has a classical ETL interface which allows you to re-use components and structure things in a modular, logical way.

If scripting is the thing that you are used to, you can certainly go crazy in Jedox. There are a number of example of scripting jobs and transforms in ../ETL/samples.

For a long while, Jedox has been able to take advantage of the power of Java and Groovy to extend functionality in the ETL. With the advent of Jedox 5.1, we finally have access to the complete Jedox Java API. The implications of this is that you can (much more simply) refer to objects within the Jedox db in transforms and loads. For instance, you might want to look up the element index , or test what parent the element belongs to.

You can find the full description of the Java API in the Jedox Knowledgebase.

I have created a small project with a few examples of doing this within a transform. You can download here.

Examples of using the Java API in transformations. Note: In the script examples, the line

OLAP.getDatabase(“Jedox”)

“Jedox” refers to a Jedox connection name in ETL (either Jedox or JedoxGlobal connection types).

1. Get the Child count of a particular element


IDatabase db = OLAP.getDatabase("Jedox");
IElement e = db.getDimensionByName("Products").getElementByName(_input1,false);
if(e==null){
throw new RuntimeException("Element " + _input1 + " does not exist.");
};
return e.getChildCount();

2.  Get the Parent count of a particular element


IDatabase db = OLAP.getDatabase("Jedox");

IElement e = db.getDimensionByName("Products").getElementByName(_input1,false);
if(e==null){
throw new RuntimeException("Element " + _input1 + " does not exist.");
};
return e.getParentCount();

One thought on “Scripting in ETL

  1. Julien Delvat (@jdelvat)

    Thank you, Chris, it was very useful. I used it to automatically increment by 1 my batch number during loads. Here’s the code:


    // Connect to the Database via Connection
    IDatabase l_db = OLAP.getDatabase("c_Jedox");

    // Read all base elements of the dimension "Run_Number"
    IElement[] lt_e = l_db.getDimensionByName("Run_Number").getBasesElements( withAttributes = false );

    // Handle errors
    if( lt_e == null ) {
    throw new RuntimeException("Error while reading dimension Run_Number");
    }

    // Search for the highest element
    f_Run_Number = 0;
    String l_Name ;
    int l_Run;
    for( l_e in lt_e )
    {
    l_Name = l_e.getName();
    l_Run = l_Name.toInteger();

    if( f_Run_Number == 0 ) {
    f_Run_Number = l_Run;
    } else if( l_Run > F_Run_Number ) {
    f_Run_Number = l_Run;
    }

    }

    // Increment by 1
    f_Run_Number = f_Run_Number + 1;

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s