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

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