Starting with Jedox Integrator 5.1, Integrator projects are migrated automatically with updates; there is no need for CLI or to import project again. This article describes the most recent updates regarding migration of Integrator projects and how to migrate from one version to the next.
Changes from Jedox 7.0 to 7.1
Filebased connections with location URL
For the filebased connection types File, XmlFile, JsonFile and ExcelFile the former location type URL has been separated more logically in terms of protocols to the types HTTP, FTP and WebDav. The location is automatically migrated to HTTP. For WebDav the location has to be changed manually. Reading from FTP Servers will also work with location HTTP but the location should nevertheless be changed as location FTP uses a different implementation. The write-access to FTP is new with 7.1.
The Hadoop connectivity is integrated in the standard Jedox Setup so there is no need for installation of a separate package. The connection type “Hdfs” for access to the Hadoop Distributed File System is no longer available in Jedox 7.1, it is replaced by a separate location type Hdfs in the filebased connection types File, XmlFile, JsonFile and ExcelFile. This allows the reading and writing of Json, Xml and Excel Files additionally to the CSV files. Connections of type “Hdfs” have to be adapted manually to a connection of type “File”.
– API.setupProcessor : Use API.initSource instead
– API.initProcessor : Use API.getSource instead
– API.getConnection : If used for Relational Extracts use a load of type RelationalSQL instead
– API.getRowCount : Use function RowNumber as input of the Script function instead
– OLAP.saveDatabase : Use OLAP.getDatabase.save instead
– OLAP.cubeConvert : Use OLAP.getDatabase.getCube.activateGpu instead
– OLAP.setProperty : Use API.setProperty instead
Changes from Jedox 6.0 to 7.0
The logic of parameter months offset has changed. Extracts using this functionality and pattern symbol “O” have to be adapted.
The Boolean option “ignore Empty Cells” in 6.0 is replaced with option “Empty cells”, offering 4 possible options to define the behavior for empty and 0 cells. The automatic migration of ETL projects sets “includeEmptyDefaultMapping” if it was not set and “excludeEmpty” if it was set, which should lead to same results as in 6.0. Special attention is required if option “read rules-based values” is used for rules returning value “0”.
For attribute cubes an additional column #_LANGUAGE for localisation is returned. Subsequent transforms or loads may have to be adapted. Note that when reading attributes in a Dimension Extract no manual migration is necessary.
Fieldtransform Function Map
The special mapping argument #space only covers empty values in Jedox 7.0. For NULL values, the mapping argument #null is introduced. The mapping table may have to be extended.
Fieldtransform Function OlapData
For empty cube cells, a NULL value is returned by this function. Before Jedox 7.0, the result was “0” or “” in this case. If necessary, an additional function of type “Map” is necessary to achieve this behavior in Jedox 7.0.
A NULL value in an input column is transferred as NA value to the R Script engine. Note that this is not the same as a NULL value in R. (Before Jedox 7.0, NULL values have been transferred as empty strings to the R Script engine).
Cube load with drillthrough can no longer be used in combination with load mode INSERT. This mode only resulted in consistent results if the source contained one row for each combination of cube dimension columns. A switch to mode ADD should be considered. The equivalent loading to a relational database table can be achieved in Relational load (with mode INSERT and Aggregation option enabled).
For attribute cubes an additional column #_LANGUAGE for localisation is required. Preceeding transforms or loads may have to be adapted. Note that when writing attributes in a Dimension Load no manual migration is necessary.
NULL values are written in this way to a JSON file: “Column1”:null. (Before Jedox 7.0 it was written as empty string “Column1″:””).
The framework for SOAP web services in the Integrator Server changed in Jedox 7.0 from Apache Axis2 to Apache CXF. Customer-specific client applications directly using the ETL SOAP API may have to be adapted. The switch of the framework implies changes in the generated WSDL, but not in the individual API methods itself.
- For PHP clients no code changes should be necessary but testing is required
- For C# clients e.g. in MS Office the connection URL has to be changed in this way:
replace this.Url = “http://localhost:7775/” + “tc/etlserver/services/ETL-Server.ETL-ServerHttpSoap11Endpoint/”;
with this.Url = “http://localhost:7775/” + “tc/etlserver/services/ETL-Server”;
- For Java clients, required code changes depend on the web service client framework that is used.
The character ‘~’ (tilde) is forbidden in the name of integrator projects and components, additionally to character ‘.’. Integrator projects using this character have to be exported and manually be repaired.
Changes from Jedox 6.0 SR2 to 6.0 SR3
Changes of Integrator components:
- Extract Excel
If a “Range” is set with a row index and there are empty rows at the top of the file, these rows haven’t been considered correctly up to release 6.0 SR2. The number of empty header rows has to be added to the Range parameter in 6.0 SR3.
Example: Excel File with 2 empty header rows, range starting with row 3 (first filled row).
• Up to 6.0 SR2: Correct range: Sheet1!$A$1:$H$10000
• 6.0 SR3: Correct range: Sheet1!$A$3:$H$10000
Migration from Jedox 5.1 to 6.0
The Jedox Integrator (ETL) projects are automatically migrated after installation of Jedox 6.0. The following Integrator component changes have to be checked manually.
Changes to Integrator components:
- Calendar Extract
The Time-to-date hierarchy handling has been extended with additional configuration options, resulting in different generated hierarchies. The modes “toNext” and “toRoot” in 5.1 are both migrated automatically to the new mode “timeToDate”, with the upper level as reference level. It is maybe convenient to change to mode “timeToDateDeep” and/or to adapt the reference level.
- ODBC Connection
No longer supported, since Java 8 is required.
- File/Directory Connections
Parameter “Delimiter” is now obligatory (before default “,” was used).
- DateFormat/Calendar Extract Function
The option completeWeeks has been removed. The pattern symbol “w” leads to different results now (the same as in Java standard), the new pattern letter “v” has the logic of the former symbol “w”.
To get the same results as in 5.1:
• In case of completeWeeks=false: You have to replace pattern letter “w” with “v”.
• In case of completeWeeks=true: You have to replace pattern letter “y” with “Y”.
• If no value is returned inside of the script and the name of the function is the same as one of its inputs, then in 5.1 the function has this input as result, otherwise it’s NULL. In 6.0, if no value is returned inside of the script, the result of the function is always NULL. (since 6.0 SR1)
• If the result is set by assigning a the function name to a value (instead of using “return”) and there are additional script rows after this assignment, in some cases the result may be NULL in 6.0. In general, the result should always be set by a “return” statement.
- File Connection
1.) If header row is set and has empty values, the name “column” is used as default value instead of “constant” for the column index.
2.) If SQL statements are used, up to 5.1 the alias “s” is no longer generated for the table name:
SELECT queries using this alias won’t work anymore
SELECT “CustomerID” from “CustomerRegions_file” ORDER BY s.”Region”
SELECT “CustomerID” from “CustomerRegions_file” ORDER BY “Region”
- File-based connections to File Manager
If in File, ExcelFile, or XmlFile connections the (experimental) connection to files of the Jedox component Filemanager has been used:
• The prefix “$FILEMANAGER$” is replaced automatically by a separate parameter file location = “FileManager”
- Metadata Extract
For directory connections, the selector “files” now returns only files of the directory but not subdirectories. To include subdirectories in the output, the new selector “filesAndDirectories” has to be used.
- Cube Load
For persisted drillthrough the Boolean option “Aggregation” has been removed.
Now for load mode “Insert”, aggregation is implicitly done; for all other load modes, no aggregation is required. If so far you’ve used load mode “Insert” without aggregation, you should consider using mode “Add”, because loading performance is increased and consistency between cube and drillthrough data is nevertheless obtained. See more details in article Load Cube with Drillthrough.
- Excel Extract
If the same Excel extract is used in several transforms or loads as a source, you should use the new option for caching of sources to increase the performance due to parsing of the Excel sheet several times. Caching should be set to “memory”. In 5.1, the Excel extract used caching implicitly, even if it was only used once.
- DateFormat/Calendar Extract Function
For language “de” (German), the pattern symbol “MMM” returns for the month of March “Mär” instead of “Mrz”. This is due to a change in Java 8 which refers to the Unicode standard (see Java documentation here).
- SOAP API
With release 6.0, authorization to Jedox Integrator is necessary with a separate login call. A SOAP header containing the session information is required.
This is explained in detail in the article Accessing the SOAP API of Jedox Integrator from PHP. It can easily be adapted to other programming languages. This is also required for macros in Jedox Web spreadsheets.
- Command Line Client
As authorization is necessary with release 6.0, the CLI call to Jedox Integrator has to provide login credentials. They can either be given directly with each CLI call or by defining profiles. For details, see Integrator Command Line Client.
Example: etlclient.bat -u admin -pw admin
The job context parameter “failOnError” has been replaced in version 6.0 with the new job option “Fail on status”, which is available in all job components.
error true (default in 5.1)
warning – (not available in 5.1)
If failOnError has been used e.g.
• in a Command Line Client call
etlclient.bat -p P1 -j J1 -c #failOnError=false
• in a Groovy Job
This has to be converted manually with a logic using “Fail on status”.
You have to install the latest versions of the SAP transport packages for SAP Netweaver releases 7.31 and above, as described in the article Installation of SAP Transport Packages.
Migration from Jedox 5.0 to 5.1
The Jedox Integrator (ETL) projects are automatically migrated after installation of 5.1. This covers all of these changes:
- File Connection
Parameters Skip, Start, End, Columns have moved from File Connection to File Extract (as they do not apply to a File Load).
- Access Connection
Please read the note at the end of article Connection to Access.
- Connections DerbyFile, HsqldbFile, H2File
These file-based relational connections have moved to connections Derby, Hsqldb, H2 with new parameter “Mode” set to “Embedded”.
- Tree Extract
Tree Extract has been replaced by new component ConstantTree, with a different table-based control. It has column level (from 1 to n), element, weight, node type ,and optionally columns for attributes. Note that there is also a similar new component ConstantTable.
- SAPERPHierarchy Extract
Option “read only Masterdata” is replaced with inverse Option “read only groups”.
- CubeSlice Extract
Boolean option “generate Roots” is replaced by the more general option “Slice mode”. True is migrated to “generateRoots”, false to “exclude”.
- Calendar Extract
The numerical values of option “first day of week” are replaced with text representations “Monday”, “Tuesday”, etc.
- TableTransform Transform
This has been replaced by the transforms TableAggregation, TableNormalisation or TableDenormalisation, depending on the mode.
- TableUnion Transform
If a loop source has been defined in the transform, it is replaced with new transform type TableLoop.
- TreeFH Transform
The attributes have to be defined separately with type “numeric” or “string”.
- FieldTransform Transform
The options for UpperLower function are now in lowercase letters.
- Dimension Load
The default mode is omitted. A mode has to be set for each of the steps “Elements”, “Consolidations”, and “Attributes”.
To de-activate a step, option “<No Load>” is replaced with mode “inactive”.
- Standard Job
If option “Run in parallel” is set, it is replaced with the new job type “Parallel”.
Manual steps are necessary only for the following changes:
- File Load
The options “Start” and “End” are omitted. To define a row filter, a separate transform TableView has to be applied on the data source.
The option “Execute in parallel” is omitted. To execute the job in parallel, it has to be included in a job of type “Parallel”.
Migration from version 4.0 to 5.0
ETL 4.0 projects will be automatically migrated to ETL 5.0 projects by using one of the following alternatives after the installation of ETL version 5.0:
- Alternative 1: Use ETL Manager
Select the ETL project to migrate and click on “Download project”. Then click on “Upload project” and select the ETL project file that you just downloaded. During the upload the automated migration takes place.
- Alternative 2: Use the Command Line Client
etlclient.bat –g sampleBiker -o C:\sampleBiker.xml etlclient.bat –a C:\sampleBiker.xml
During the upload the automated migration takes place.
- Alternative 3: Use the Command Line Client with Migration of File
etlclient.bat –g sampletBiker -o C:\sampleBiker.xml etlclient.bat –m C:\sampleBiker.xml –o C:\sampleBiker_migrated.xml etlclient.bat –a C:\sampleBiker_migrated.xml
The migrated project will be written to a separate file before the upload.