Tag Archives: Business Intelligence

Jedox and R – an interview

There has been excitement building around the upcoming Jedox 5.1.With so many new features it could be called Jedox 6. One ​very powerful addition is the upcoming marriage of Jedox and R.

 I’ve seen the enthusiasm among Jedox partners and clients​, ​but what benefits does R bring to the business place of a Jedox user? As a prelude to Chris Mentor’s technical articles on Jedox 5.1 (starting soon), I speak here with Vladislav Malicevic, Head of R&D at Jedox, on what happens when the world’ s most innovative and easy to use BI solution meets probably the world’s ​most powerful  statistical language.

Let’s start by looking at R. ​

​Sam Perrin: What is R?

Vladislav Malicevic: R is a language and environment for statistical computing used for complex predictive analysis. With a community of over 2 million, you find R used from credit risk analysis in financial institutions, to reducing customer churn and optimizing your marketing spend.


Continue reading

Dynamic Reporting – Introducing Subsets

Jedox has a quite a unique way of building dynamic reports. In either Excel or File Manager on the web you can take advantage of Subsets. Subsets are what they say they are:  a sub-set of elements from a particular dimension (set). They are dynamic and executed when the user runs the report.  A couple of examples of what you can achieve with Subsets:

  • Hide empty rows on a report (zero suppress)
  • Show a dynamic Top 20 report, based on users selections
  • Display children of a parent (that the user selects) as rows on a report
  • Filter Elements on rows by their attributes

You can pretty much go crazy with Subsets. You can make a report completely dynamic based on what the user selects. Technically, subsets are spreadsheet array formulas, which means that you have to use the ctrl+enter to apply the subset to a range and shift+ctrl+enter to un-apply it (or just delete it).

You have the option of using the Subset Editor to develop the Subset formula, or you can have a go at writing it yourself (not recommended for young players). The great thing is, that because the wizard generates a formula, you can use the wizard to generate the formula and then modify it once you paste it out.

The subset Editor allows you to sort and filter your element list dynamically based on the following filters:

  • Hierarchy Filter
  • Text Filter
  • Picklist Filter
  • Attribute Filter
  • Data Filter
  • Sort Filter

or, a combination of any of these.

Jedox Web Subset

A lot of the components of the various filters accept ‘variables’. This allows you to specify cells, named ranges or Jedox variables on a spreadsheet to dynamically pass through into the subset, greatly expanding the functionality and flexibility.

When you click paste, it writes out the subset formula that you can then use for your row elements.

Jedox Web Subset Report

Check out this example here. It is a .wss file that you can import into Jedox Spreadsheet Manager. This is a very basic example, which allows you to double click on cell F8 (Region) and the children of this element will be displayed in Column A.

Subsets can be saved as either Private or Global. Global subsets can be shared with everyone with access to subsets, and Private are only available to the person who created them.

There are some other subset related functions available, including SUBSETSIZE (which returns the number of elements in a subset )  and SERVER_SUBSET  (which allows you to refer to an existing subset and its elements).

The one thing to consider with Subsets, is that you need to define the maximum range of where the subset ends. In some situations this is fine and not an issue; but it can become messy if you need to format all the rows using conditional formatting to hide the fact you have some blank space under the returned values. The solution to this is to employ Dynaranges. Subsets work really well with Dynaranges and we will convert of a bunch of the different types of Subset filters in the context of Dynaranges.

Managing Web Reporting

Jedox is a broad platform. There is a huge amount of functionality under the covers that you have access to. Sometimes the manuals are a bit cryptic in their description of what is available. So over the next little while, I plan to expose a fair bit of this functionality and how it is used in a business context. Some of it is straightforward and some is quite complex, so bear with me and just take on board what you can understand. Feel free to ask questions.

A key part of Jedox is its Excel add-in. It is by far the most popular and used front end for Jedox, given the ubiquity and flexibility of Excel.  The main game, however, is the Jedox Web interface. It is powerful and flexible. More development effort has been put into the web interface than any other Jedox front end, which sort of indicates where Jedox AG sees the future of the product.

Ok, so let’s get into it.

 Performance Dashboard

Every wondered who is using your web reports? How often? What are the usage peak times, peak users? All that information is available in the core.log. It is a standard log file format, which is about as useful as an ashtray on a motorbike when it comes to reporting. So we need to parse it with the ETL to get something beneficial out if it.

Download the Performance Dashboard. This .pb file (A Jedox report application bundle) is a handy application which enables you to monitor and react to users hitting your Jedox web reports. It is essentially a cube and a report built by parsing the core.log file. Once you have downloaded it, do the following:

1. Log in to Jedox Web (File Manager)

2. Select an appropriate group to import the .pb file into and import it.

3. Once imported, you should see the following:

Imported Files in File Manager

4. Download the Jedox-Stats.xml. This is a Jedox ETL Project. Open ETL Manager and import the project.

Import Project into Jedox ETL

5. Download the Jedox-Stats.zip. Unzip this file. This is a Jedox database that you need to add to your Jedox data folder (stop the Jedox MOLAP Service first).

6. Start the MOLAP Service; change the path in the ETL Projects parameters / variables to match your system settings. The file path for the Core log is the most important one at this point.

Jedox ETL Variables

7. Run the job-[00]:CoreStatsBuild job. This will populate your cube with the settings from your system. The default ETL captures 2 measures – reports successfully loaded and reports that have been attempted to be loaded by users.

Checking the success of the ETL Job

8. Once you have ran the ETL, open the Performance Dashboard. It should look something like this:

Jedox Wed Performance Dashboard

The dash is pretty self-explanatory: the line chart on the left represents all hits and avg/hts per day as per your core.log. You can change server/date period at the top. The middle doughnut represents successful loads vs attempted loads. The chart on the right represents the top 10 reports by that selected period. The real interesting stuff here is the Top Times, Users, Reports. The top times are top hours in the day users hit the reports. Hover over the >> to see more information and click to filter by users for that time. You can select a user for that time and click the >> to see that users top reports for that time. Click on the report and it will take you to a simple analysis of that report by user. Select Top or Flop (bottom reports). Click on a report and it will take you to further analysis by user of that report.

Remember, this is just a template or starting point – it is just a Jedox Web page, and you can edit this yourself and customise it as required. Note: I use cell A1 to hide/unhide rows and columns (via Jedox (HIDEROW() and HIDECOLUMN() functions) used but not displayed. Change cell A1 to 0 to display all hidden rows/columns; enter 1 to hide again.

This type of information is handy, especially when you are centrally deploying an enterprise rollout of Jedox to a wide user base and you need feedback on what reports that users are using and which ones they are not. This ensures you are targeting your reports correctly and it provides insight into how users are interacting with the reports.