Tips and Tricks Archives - Page 5 of 7

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

User settings and properties – language

May 1, 2022May 1, 2022 by Jedox 4 BeginnersNo Comments

In this article, we will explain how to change language via User properties and ways to do it. Usually, when creating a user in Jedox there are a few things you need to add in order to create it: All of this information is stored inside the System database and _USER_USER_PROPERTIES cube. Previously we talked about how to remove the panel from the side which uses appstate dimension element here. Today we are going to focus on how to change the language. Different users are coming from different regions and they would like to see their applications in regional languages. Sometimes it can happen that even decimal delimiters are important to them. For example, Germans use commas (,) for decimal delimiters and other dots (.). There are two ways to sort it: From the User Setting Panel. 2. From the Integrator. This method is much better for a large number of users. Before we start creating the ETL it's important to know where this property...

How to work with the stylesheet

April 21, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this article, we will show you how to import different stylesheet examples inside your report. In order to do this, we will use the stylesheet example given by Jedox which can be downloaded here. If you don't know how to create a model check this article. After that follow the next steps: Step 1: Download the design system model from here: https://design-system.cloud.jedox.com/resources Step 2: Export the DS Stylesheets report from the "//Models/Design System Templates/Configuration Reports/DS Stylesheet.wss" folder in the report designer. Step 3: Import the report into your instance Step 4: Configure the styles based on client requirements in the report by modifying them individually as described here: https://knowledgebase.jedox.com/models/other/configuring-design-elements.htm?Highlight=design Step 5: Save the stylesheet report and tag it as a resource Step 6: Insert the tagged stylesheets report as a resource in the client report where you want to apply the styles Step 7: Use the styles in the client report....

Excel functions in Jedox export – Part 1

March 27, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

While doing different projects we noticed that one of the repeating requirements is to export the Jedox report in Excel and then perform some additional analysis. As you know from before there are a few ways to export to excel. xlsx - standard export xlsx_snapshot - standard snapshot export xlsx_olap_snapshot - OLAP snapshot export In the case of xlsx export, data will be downloaded as a constant value. In this article, we will show you how to use the famous Excel function SUM incorporated in dynaranges. How to create a button for exporting in Excel? SUM in Jedox dynarange Imagine that we have two sheets. The first one contains data in Dynarage which could be editable when exported to excel. The second sheet supposes to read all changes from the first one and sum it up accordingly. In order to do that we will need to set up some steps: Create name range for Products and for considered data. Remember: it is important to select one row before and after dynarange. Create SUM function in the...

How to sort attributes?

February 21, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this article, we will show you how to sort attributes in the way you like. When building dimensions in Jedox it could happen that some of them require a huge number of them. The modeler screen in normal zooming conditions can fit around 10 attributes. For all the rest we will need to use a scroller. If you are someone who works with Jedox for a few years you will notice that adding attributes on the end of the page when scrolling could be annoying. Especially because after adding any attribute manually, the page refresh and bring you back to starting position. We can resolve it by pushing the last attribute at the beginning. There are two ways to do it: Create attribute directly on desired position Moving attribute around in #_MONTH_ cube For the second case, arrows, in the end, will be blocked, so the best way is to drag and drop elements around. Maybe at first sight useless tip, sorting manually really...

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