Combining filter criteria for multiple selections in cube extracts (Table Loop)

Home  >>  Allgemein  >>  Combining filter criteria for multiple selections in cube extracts (Table Loop)

Combining filter criteria for multiple selections in cube extracts (Table Loop)

On April 9, 2019, Posted by , In Allgemein, With No Comments

Due to a specia request I uploaded some older post I published already on

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

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.