ETL (Extract, Transform, Load) Archives

Mastering File Compression and Extraction in Jedox

December 17, 2023December 17, 2023 by Jedox 4 BeginnersNo Comments

Welcome, Jedox enthusiasts! In today's tutorial, we'll dive into file compression and extraction using Jedox, specifically, the powerful FILE.unzip function. As a prerequisite, we will need to create some folders in our backend which will be used for testing purposes. Connect to your backend with some tool like WinSCP. If you don't have your credentials check the cloud console. If you do not have access to the cloud console - contact cloud support. Once you are connected create 2 folders in /etl_data/files/etl_data/files/For Zipping/etl_data/files/For Unzipping In "For Zipping" folder add Excel files 1,2,3. In the "For Unzipping" folder add two zipped files "ZippedWithoutPass" and "ZippedWithPass". The password for unzipping is in the file pass. (Download the whole project at the bottom of the page) At the same time we need to create some directory and file locations in Jedox which will be used as a path for the following scenarios: Scenario 1: Compressing a File into a Zip Folder The first scenario we'll explore is the basic compression of a...

Report Management in Jedox: Converting report paths to dimension elements

October 15, 2023November 20, 2023 by Jedox 4 Beginners4 Comments

Are you looking for an efficient way to manage and organize your reports in Jedox? This guide will explore how to export folders and files from Jedox Report Designer and transform them into a dynamic dimension of elements within the Modeler. By leveraging the paths as attributes, we can create an easily navigable system to represent all reports in the Jedox environment, enhancing user experience and productivity. Export the project from here. Exporting Folders and Files from Jedox Report Designer Organize Your Reports: Ensure your reports are systematically organized within the Jedox Report Designer for exporting. Export Data to Modeler: Utilize the ETL Integrator to transfer folders and file names as dimension elements to the Modeler. Check the project for the details. Creating a Dimension with File Paths as Attributes Set Up the Dimension: In the Modeler, create a new dimension with the exported file paths as attributes, allowing for a representation of the report structure. Configure Attribute Settings: Customize the attribute settings to ensure navigation and...

Connection to Snowflake ❄️🔄📊

September 6, 2023September 7, 2023 by Jedox 4 BeginnersNo Comments

The upcoming release of Jedox in 2023.3 is set to bring exciting advancements to the ETL (Extract, Transform, Load) process by introducing a seamless connection to Snowflake, one of the leading cloud data warehousing solutions. With this integration, users can enjoy efficient data handling with two authentication options, enhanced by considerations for roles, warehouses, and databases. After choosing the connection there are certain credentials which need to be added. Authentication Options: Username and Password: This traditional method allows users to authenticate their access to Snowflake by providing their username and password. This secure authentication process ensures that only authorized individuals can access and manipulate data within Snowflake. Private Key Configuration: For those seeking enhanced security, the new connection also supports private key authentication. Users can configure their Snowflake accounts to accept private key authentication, adding an extra layer of protection to their data. The Jedox-Snowflake integration doesn't stop at authentication methods. It also provides valuable features to help manage roles, warehouses, and databases seamlessly: Role: Users...

Running ETLs via macro using REST standard

February 12, 2023June 28, 2023 by Jedox 4 BeginnersNo Comments

The Integrator Server SOAP API has been deprecated as of Jedox 2022.4. If you used this standard to run ETL via macros you will need to change it now. There are two ways of doing that: Running ETLS via action buttons (highly recommended) Running ETLs via macro using Rest standard Action buttons are now already well incorporated in many Jedox projects and running ETL via them should be an easy task. However if you have issue to build such a connection, check out this link. Running ETLs using Rest standard takes a bit of coding but nothings super complex. IF you had SOAP connection-build-macro-report, make a backup of it and then delete all the code. Have in mind that if you had any IF or any other statements in the code before, make sure to save them. First we need to call the library require library('integrator-rest'); After that code for running looks like this: function sampleETL() { $etlProject = "PROJECT NAME"; $etlJob = 'JOB NAME'; // define array...

Cube load modes- (Insert, Add, Delete, Update, Create)

October 2, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In my numerous Jedox projects career, I was constantly getting questions related to cube load modes. Although is very well explained in the knowledge base, users frequently asked for clarification. When is a great moment for the update, where for add, and where for delete + insert combination? It is important to start with the type of load modes for the cube: Add - Values from the data source are aggregated and added to the existing values in the cube. Insert - Existing values in the cube are overwritten with the values from the data source. Create - The existing cube is deleted and created anew with the dimension order as defined in the source. Delete- Values from the data source are deleted in the cube. Update - The existing cube is emptied; values from the data source are aggregated and written to the cube. How it practically looks is something which is in the best way explained in the below EXCEL screenshot: Based on this image you...

How to copy database via ETL

June 6, 2022June 28, 2023 by Jedox 4 Beginners4 Comments

In this article, we will show you how to copy databases via ETL. This method does not require a service restart. All you need is a groovy script that is run in Groovy job. For this example, we will use the "Demo" database. Groovy script copyJedoxDatabase( 'Demo', 'Demo_Copy' )void copyJedoxDatabase( String jedoxConnectionSrc, String jedoxDatabaseDst ) {String jedoxDatabaseSrc = OLAP.getDatabase( jedoxConnectionSrc ).getName()String backupFilename = API.getProperty( "Demo_Copy" ) + jedoxDatabaseSrc + '.zip'// Remove backupFile if exists (e.g. from a previous run)new File( backupFilename ).delete()IDatabase db = OLAP.getDatabase( jedoxConnectionSrc )LOG.info( 'Saving database ' + jedoxDatabaseSrc + ' to ' + backupFilename )db.backup( backupFilename )IConnection conn = OLAP.getConnection( jedoxConnectionSrc )LOG.info( 'Restoring database ' + jedoxDatabaseDst + ' from ' + backupFilename )conn.addDatabase( jedoxDatabaseDst, backupFilename )boolean fileSuccessfullyDeleted = new File( backupFilename ).delete()} Once the job is run, the new database will appear in the modeler. ETL job can be downloaded from this location. ...

Unable to click save button in Jedox ETL

January 29, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

Hi Jedox beginners, How many times has it happened that while working on your super complex ETLs, the save button becomes unclickable? It can become really annoying problem, especially if you spent a decent amount of time in front of the PC. Until now, the problem could be solved by: Clicking Test button before and then pop up with "Save button" will appear Log out, delete cache and log in again. Since both solutions bring anxiety to your hands, simple CTRL + S typing on the keyboard is enabling the SAVE button again. Click now and thank us later. ...

Dynamic denormalization

January 2, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

One of the commonly used transformations in ETL is Table denormalization. Denormalization is the reverse of normalization. Row-based data with key-value pairs is transposed into column-based data. This allows an OLAP model with one measure in each row (with the measure name as a key) to be arranged into a relational model in which the measures are held in various columns. Jedox knowledge base In typical table denormalization, Measure fields are populated by the rows which we want to see in columns. Usually, it's enough to do it one by one but sometimes it happens that there are 30 or 50 elements that needed to be transposed into the columns. For that reason, we will use a little trick that can be found in Advanced settings. In case you are not familiar with this type of transformation here is the link that can help. Our file contains 3 columns and one of them contains data that should be denormalized. Instead of using static measures, we will introduce...

How to rename elements from Jedox ETL?

October 5, 2015June 28, 2023 by Jedox 4 BeginnersNo Comments

Sometimes it could happen that there is a request to rename element in modeler outside of the spreahsheet or manual. For that puspose we could use ETL and more speciefically groovy job. In following example we will show how to rename element from User dimension using groovy job. API.executeLoad("Users_Load"); LOG.info("Start renaming elements"); source = API.initSource("Users_Extract","EA",0); String oldNames = "joe"; String newNames = "Martin"; OLAP.erename("olapsystem","#_USER_",oldNames as String,newNames as String); LOG.info("Finished renaming elements"); In the graph we could see that for this action we need: Extract from dimension Connection to dimension ETL is extracting all elements and then searching for joe and replacing it with Martin. ETL project could be downloaded on this link. Other PHP APIs could be found here ....