Calendar Extract (Month, Year and Life To Date patterns) in Jedox

Calendar Extract (Month, Year and Life To Date patterns) in Jedox

by Jedox 4 BeginnersNo Comments
Understanding how to create and use Calendar Extracts in Jedox is crucial for effective time-based analysis. Calendar Extracts are essential for breaking down your data into manageable and meaningful periods, like months, years, or Life to Date (LTD) views. In this guide, we'll walk you through how to set up these patterns in Jedox and explore real-life scenarios where each pattern is most effective. What is a Calendar Extract in Jedox? A Calendar Extract in Jedox Integrator is a feature that allows you to filter and segment your data based on time periods such as months, years, or the cumulative period up to the current date (Life to Date). This is particularly useful for generating your Time dimensions and subsequently reports, dashboards, or performing time-series analysis. In this post, we will focus on 3 common use cases: Month Pattern - MTD captures data from the 1st of the month through to the most recent complete day.: Use Case: Monthly financial reporting, monitoring sales trends,...
Read More
Connection to OneDrive

Connection to OneDrive

by Jedox 4 Beginners1 Comment
This week we will talk about how can Jedox connect to OneDrive. OneDrive is a cloud storage service developed by Microsoft, allowing users to store files and photos securely online, accessible from any device with an internet connection. It integrates with Microsoft Office applications and offers collaboration features for sharing and editing documents in real time. We will showcase two ways of doing it, with a username and password and with a token connection. Connecting with Username and Password This method is less secure but allows users to simply access the files. Upload the file you would like to access from Jedox and specify the path: It is important to know your Azure application ID. you can request it from your IT department or in case you have access to your application Azure find it here: Now, if you test your connection to file it should be successful. Connecting via Token Connection As for the first case, you will need to have existing credentials to access the...
Read More
Mastering File Compression and Extraction in Jedox

Mastering File Compression and Extraction in Jedox

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...
Read More
Report Management in Jedox: Converting report paths to dimension elements

Report Management in Jedox: Converting report paths to dimension elements

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...
Read More
Connection to Snowflake ❄️🔄📊

Connection to Snowflake ❄️🔄📊

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...
Read More
Running ETLs via macro using REST standard

Running ETLs via macro using REST standard

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...
Read More
Cube load modes- (Insert, Add, Delete, Update, Create)

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

by Jedox 4 Beginners2 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...
Read More
How to copy database via ETL

How to copy database via ETL

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....
Read More
Unable to click save button in Jedox ETL

Unable to click save button in Jedox ETL

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....
Read More
Dynamic denormalization

Dynamic denormalization

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