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.
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.
You can download the excel file here and the vba module (for customising your own files) here.
Month 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.
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.
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.
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.
A 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:
and the element “Date” in the Time Index dimension:
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.
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
Continuing on from my prior post on SAP Connectivity Part 1, the next step is configuring the SAP Connector in Jedox ETL.
After you have successfully installed SAP JCo Connector, you need to select the SAP Connector component during the Jedox all-in-one install process. This will install the Jedox ETL SAP Connector where it establishes the connection to SAP and supports several extract types.
At the time of the Jedox installation, you need to tick the check box for SAP Connectivity:
The following screen comes up where you would need to specify the directory path where the SAP JCO connector files are stored. Select the location:
During the installation of the SAP Java Connector, the files sapjco3.jar and sapjco3.dll are installed on the server. When you install Jedox with the SAP Connector, the installer copies these files to the folder {Jedox-Suite-install-path}\tomcat\webapps\etlserver\WEB-INF\lib_external.
For Jedox to be able to connect to SAP, you need to confirm that your Jedox Suite licence key has SAP Connectivity enabled. Go to System Manager , then click on Licences:
If you do not see SAP Connector listed, you cannot connect to SAP via the Jedox SAP Connector. If it is not listed, please contact your Jedox representative for further information.
Ok, we are nearly there! In Part 3, I will step you through how to establish connectivity directly to the SAP system and confirm your connection in Jedox ETL. At ths point, We will need assistance from the SAP team (the BASIS Administrator) to import transport requests.
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.
java -jar {sapjco-install-path}/sapjco3.jar.
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
the naked leaf 