Tips and Tricks Archives - Page 4 of 7

Connect Jedox Cloud and Power BI

April 24, 2023November 20, 2024 by Jedox 4 Beginners4 Comments

To connect Jedox to Power BI, you will need to follow these steps: Make sure that the oData Hub service is running. (check Cloud Console) Obtain the Jedox OData API URL: usually, add odata before the cloud name. Example https://odata.test-jedox4begginers.cloud.jedox.com/DATABASENAME. It is also essential to add a database name at the end. In our case, we will put Biker as a database. Connect to Jedox from Power BI: Here, click on the "Get Data" button in the Home tab, and select "OData Feed" from the list of available data sources. Paste the Jedox OData API URL into the "URL" field, and click "OK" to connect to the Jedox server. Add the username and password. Depending if your team is using SSO choose Windows or Basic for standard Jedox credentials. Import data into Power BI: Once connected to the Jedox server, you can select the Jedox cubes or views you want to import into Power BI. Select the cube or view from the list of available tables,...

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

Jedox password policy

December 14, 2022December 14, 2022 by Jedox 4 BeginnersNo Comments

Starting from 2022.2 version Jedox implemented password policy change. That means that users cannot have abc123 passwords. Something more sophisticated should be done. Therefor certain rules needs to be followed: at least 10 characters at least one digit at least one of the symbols !@#$%^&* at least one uppercase or lowercase letter [A-Z] [a-z] cannot contain a period or space Example: Q20J!b5ei0951 This policy can be changed if the company has different set of rules. in that case parameter password-pattern in palo.ini allows you to change the password settings concerning the password length and the password pattern/complexity. Any attempt to change the password that does not match the defined pattern will result in an error displayed in the Change Password dialog. The password pattern can be defined by the key password-pattern <regular_expression>. If the new password does not match the pattern, an error message (error code 1004) is returned. <according to KB>. if in any case you built up the ETL which automatically assign the passwords to newly created users, groovy should look something like this: def user = API.getProperty('user'); def generator = { String alphabet, int...

How to hide System database to users

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

System database is vital part of Jedox modeler. It contains all information of users, groups, roles and their interactions in cubes. By default, it is visible by every user with modeler rights. In some environments admin user do not want to make this database visible. In _GROUP_DATABASE_DATA cube is not possible to choose System database, since this cube is in fact in this database and therefor not possible to put N on the group. Workaround will be to play with the Roles for a bit. Usecase is that user should see Modeler, some Databases inside, but not the System. That we can achieve by doing this rule rights: Modeler is Full Access or Read and Rights is no access. Now, when our role is active and updated we should see modeler like this:...

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

October 2, 2022June 28, 2023 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...

License and user activation assignments

September 28, 2022September 30, 2022 by Jedox 4 BeginnersNo Comments

Jedox is fastly growing software and with every new version many cool features are released. That brings a bigger market and eventually more customers. Recently many projects are done for big corporations which handle a large number of users. In order to manage and control all of these users and licenses customers frequently ask for some type of report where all this information is displayed. In the below picture, you can see a list of all users split by activity or in other words, are they allowed to use the system or not. As you can see there are possibilities to assign or unassign the license, activate or deactivate the user, and search for them in case the list becomes super long. Speaking of super, take a look at the newest super planning program by Jedox. It is worth mentioning that license assignments can be found in the System database under the _USER_USER_PROPERTIES. More about system cubes can be found on the following...

How to use hyperlinks in Jedox?

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

In a Jedox, a hyperlink (or link) is an item like a word or button that points to another location. When you click on a link, the link will take you to the target of the link, which may be another report, excel document, or other online content. There are two possibilities to create a hyperlink in Hedox: Searching the hyperlink in functions Clicking on the right click and selecting the hyperlink icon Hyperlink function The syntax for the function is HYPERLINK(link_location, friendly_name, screen_tip, source1, destination1[, source2, destination2, …]). link_location - > here we can define where will our link takes us to. It can be on the same page "#SELF", different sheet "[SELF]Sheet2!A1", a particular report "//Demo Spreadsheets/Demos/Bikers Best/Navigation/Bikers Best Navigation" or a web page like "https://jedox4beginners.com/". friendly_name - > How will link the world to the outside world <Link Friendly Name> Tool_tip -> What is visible when covering the link? In case you don't want to see any addresses or strings just...

Creation and last login time of the Jedox user

July 30, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this article we will show you how to pull the report will all your users and their creation and last login time. This could be useful in case you are wondering which of your users actually use Jedox and utilize the "Free seat" license. In case you are interested more in how licensing in Jedox works you can check it out here. As we learned in some of the previous articles details about Users can be found in the System database and _USER_USER_PROPERTIES cube. SO therefore we will paste a view with this information: As you can see both data values are stored in Serial number time of date. Example: =DATEVALUE("01-02-2015") returns Unix time stamp 1420156800, which represents 02-Jan-2015 (=60*60*24*16437 seconds since 1.1.1970). Obviously, we will need to convert these values into something more readable. There are two ways to do it: Convert from UNIX to normal date using excel formula =(A1/86400)+DATE(1970,1,1) Option to convert the value with Rule. For this option, I suggest...

Palo.ini standard configurations

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

Palo.ini is a configuration file where superusers (the ones who administrate the server) could add or remove some functionalities of Jedox. It is located in data folder and on-premise could be accessed at this address <instalation folder>/Jedox/Jedox Suite/olap/data. Edit is possible if you do the right click and edit it. For this purpose, we will open it via standard Notepad. Standard palo.ini Lines starting with # are commented configurations and therefore will not be executed. The line will be skipped and the next one will be processed. More about palo.ini can be found on this link. Here are the explanations of the lines: http line represents the IP and port where jedox can be accessed.log sink is the location of the file where OLAP logs will be writtensplash-limit 1000 shows an error if splashing requires more space than the first number. 500 shows a warning if splashing requires more space than the second number. 100 Shows info if splashing requires more space than the...

Ascending and Descending sorting in Jedox

June 20, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this blog post, you will be able to learn how to create ascending and descending sorting in Jedox based on the data value. This type of sorting is very valuable in cases when the end-users would like to make an analysis based on the best, worst-performing product, country, or customer. It can be combined with a top 10 analysis as well. This type of report we already covered in this post. As you are probably guessing, we will be heavily relying on the subset for this example. Our goal will be to create a report where the same data can be sorted based on 3 measures (Sales, Cost of Sales and Units). Ascending and Descending sorting will be based on countries. Like always, Biker's best will be our testing database. In our subset, we can see that sorting will be using PALO.D and there we will read from the orders cube. Only 10 countries will be selected and in the sort...