January 2022 - Jedox for beginners

Error while restoring the database

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

When having two servers that are running on different versions, it could happen that restoring the database backup from a newer one to an older one can lead to an error. In our case, it appeared when trying to restore the database in Modeller from 2021.4 to 2021.2. If you are not familiar with how to restore the database from one server to another without including support, the article can be found here. The process is simple: Log in on your server from where you want to take the database Go to desired database in Modeler Right Click on it and choose Download Database... Go to second server where you want to perform restoring the database activity Right click on it and choose Restore Database... IF the scenario is smooth you are ready to celebrate, but in case it's not (like below) CODE: M_DB3 - zip operation failed : not a valid backup file,subfolders found Perform the following steps: Unzip the database Find folder checksum and remove it Zip it again Try to perform...

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

Building Dynamic Calendars in Jedox

January 5, 2022November 12, 2023 by Jedox 4 BeginnersNo Comments

In this guide, we'll walk you through the process of crafting a calendar widget within the Jedox platform. This endeavor requires the presence of a Day dimension enriched with an additional attribute named 'NextDay.' The overarching goal is to fashion a dynamic calendar capable of accommodating additional dimensions, effectively transforming it into a versatile data planning hub. This widget can prove invaluable for various use cases such as task planning, reminders, and monitoring planning cycles availability, all achieved seamlessly within a spreadsheet environment without the need for any coding expertise. Basic familiarity with Excel is all that is required. Creating the Time Dimension The foundation of our calendar widget is laid with the creation of a Time dimension. Utilizing a straightforward dimension creation approach, we will employ the Dimension Template 'Day.' This template will undergo augmentation with an extra attribute known as 'NextDay,' dynamically generated through Extract, Transform, Load (ETL) processes. The 'NextDay' attribute serves as a representation of the following day...

Add element to the Jedox database via macro

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

In this post, we are going to show you how to use multiple PALO functions in the macro to successfully add element in the dimension. For this purpose we are going to use the Biker database and elements will be added to the Product dimension. Have in mind that example is for the users who use the cloud instance of jedox. First we need to establish connection with the OLAP. $host = $_JEDOX['OLAP_HOST'];$port = $_JEDOX['OLAP_PORT'];$conn = palo_init($host, $port, $_JEDOX['OLAP_SESSION_ID']); Then we need to formulate Palo.EADD function. this function consists of the following parameters. PALO.EADD(Server/Database, Dimension, Type, Element, Parent Element, Weight, Clear, Error suppr.) Possible values for Clear are 0 (or FALSE), 1 (or TRUE) and 2 0: Removes no elements of the dimension before importing 1: Removes all elements of the existing dimension before importing 2: Removes all existing elements of the C-dimension before importing. Basic elements are not deleted. More about this and other functions can be found here. When these are combined code should look something like this: function...

Excel download using macro

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

Here is one quick way to download a file as an Excel. In normal conditions, Jedox offers two possibilities to Export to Excel: XLSX Snapshot XLSX OLAP Snapshot More about differences can be found here. Sometimes, we want to limit end users from seeing these buttons. That can be done in this way [see bellow] by clicking on report properties and unchecking Interface checkboxes. Now, when the report does not contain anything related to Jedox bars we can install the macro for exporting. The code behind the macro is very simple: return array ( array('actn', "exportToXLSX",2), ); The final output looks like this: The whole project can be downloaded from here. ...

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

Mapping Matrix

January 2, 2022November 12, 2023 by Jedox 4 BeginnersNo Comments

When working with dimensions like products there must be a use-case when mapping matrix is necessary. This little mapping matrix is used when there are certain years where some products are plannable and some others not. For this example, we will use the Mapping_Matrix database and cube Mapping Products. This cube will contain dimensions like Years, Products, and Measures [not necesarry]. In some terminologies "Control cube " is also used. As we could see from the image above in hidden columns we will store PALO.DATA formula. Behind the Check and Uncheck icons we could see the following code: =HYPERLINK("SELF",IF(I8>0,iCheck,iUncheck),"",IF(I8=0,"!1","!0"),I8) This hyperlink function just make difference if the value is 0 or higher. In case it's zero then every click on the iUncheck image will splash ! 1 on PALO.DATA function. Otherwise !0 would be splashed. More on splashing commands can be found here. The whole project can be downloaded from here. ...

Best way to copy data without using the ETL

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

During the planning process, there will be activities that require to copy data from the previous year or another version. There are multiple ways to achieve it via ETL but in this article, we are going to show you how to do it via hyperlink. Copying using native copy/like function If you ever attended any Jedox training you have probably learned splashing commands like Copy and Like. Both of them can be found on the knowledge base and this is good pre-knowledge for the following segment. Copying using hyperlink function Copy and Like function could be converted into the hyperlink and do the same job. In the cell where you would like to create a hyperlink copy the following code. =HYPERLINK("SELF","Copy Data","Copy Data","Copy "&F$5,$G6) This code is another way to represent "Copy 2017" and G6 is a cell to where we want data to be applied. Copying using hyperllink in the button A similar thing is done in the next example but only instead of the hyperlink, we are...

Setting up default page for groups – 3 ways

January 2, 2022January 10, 2022 by Jedox 4 BeginnersNo Comments

Setting the default page in Jedox for a specific group is not a big issue if your application contains a smaller number of groups. When a number of groups enlarge, we should look for a more alternative solution instead of checking the boxes one by one. This represents 1st way. Information of the default homepage is stored in the dimension GROUP_PROPERTIES, more specifically in the prefs element. More about the system database could be found here. Once we check this box from picture one, XML code is immediately generated and information on the default page is written. This code could be now pasted in every other cell. In that way, we could significantly decrease the time of checking and as well automate a bit process. This represents 2nd way. 3rd way would be to use this code and copy it via ETL. Import to have in mind is that once a new start page is chosen code needs to be arranged. Also, if the Start...