etl Archives - Jedox for beginners

Connection to Google Sheets

March 16, 2024March 16, 2024 by Jedox 4 BeginnersNo Comments

In today's data-driven world, integration between different platforms is essential for efficient workflow management and data analysis. With the latest integration of Google Sheets within Jedox with version 24.1, users can now combine the power of Jedox's data management and analytics capabilities with the collaborative features of Google Sheets. In this beginner's guide, we'll walk you through the steps to set up this integration and harness its full potential. Setting Up Google Cloud Project The first step in integrating Google Sheets with Jedox is to create a Google Account if you don't have one already. Then, head over to Google Cloud Console (https://console.cloud.google.com/) and create a new project. Creating OAuth 2.0 Client ID Once the project is created navigate to the Credentials section in your Google Cloud Console https://console.cloud.google.com/apis/credentials and create an OAuth 2.0 Client ID and Secret. Make sure to specify the authorized UI as https://developers.google.com/oauthplayground to generate Refresh and Access Tokens. Generating Tokens with OAuth 2.0 Playground Utilize the Google OAuth 2.0 Playground to...

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...

IOUtils.setByteArrayMaxOverride() issue

November 2, 2022November 2, 2022 by Jedox 4 BeginnersNo Comments

Upgrading to the 2022.2 version brings more perks and features. One of these is also the IOUtils.setByteArrayMaxOverride() issue. Usually, it comes in this type of form and states that the maximum length for this record type is achieved. The fix is very simple. If you have access to the config file it is necessary to add this line: Afterward, you just need to restart the ETL service and you are ready to go. In case you don't have access, please write to Jedox Cloud Support. ...

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. ...

JDX_SID not found

January 17, 2022January 17, 2022 by Jedox 4 BeginnersNo Comments

There is a well-known bug in Jedox in which the error pop-up ends with JDX_SID not found. It is usually happening in the extract component of Integrator and it is related to a connection to a file, especially if the file is located in the JedoxFiles folder. The way to resolve this is very simple and it required some adjustments in the config.php file. More about configuration files can be found here. cookie secure flag set to false; //cookie secure flagdefine('CFG_COOKIE_SECURE',false); cookie samesite policy set to "Lax"; (Don't forget to add " " ) //cookie samesite policy('Lax','Strict' or 'None')define('CFG_COOKIE_SAMESITE','Lax'); After making those two changes your extract should be working. The change will take effect after a full-service restart. ...

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...

Audit in Jedox

December 30, 2021June 28, 2023 by Jedox 4 BeginnersNo Comments

When the cycle of planning starts it is important to track all inputs. One reason is for monitoring purposes [Who did when what and why] and the second is to keep the log of how data looked before that step. For that purpose, we will use Audit functionality. This functionality can be found in the Administration tab: By default, an audit is not started until the user enables it in Settings Panel. Auditing can be done for 1,7,30 or 365 days. Have in mind that a larger auditing period is selected, the file on your server will take more memory. Once you select the cube which should be monitored, add some value inside. In our case, we will add 400 Units for Handlebars inside Orders Cube which we previously enabled in Audit. After hitting that splash functionality new value is visible in the Audit section. Now, you can check why the values are appearing in the report since everybody claims it was not their...

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 ....