Category Archives: Jedox Web

Managing Web Reporting – Part 2

3 Replies

After being out in the wild for over 12 months, the Performance Dashboard has been one of the most popular posts on our blog. As it was originally written for in Jedox 5.0, we have updated it to reflect the latest functionality in 5.1 SR2. New Features

  • Hits chart now using a Stock type chart, that allows zooming in to specific days
  • Hits by Hour, to allow you to zoom in on the busiest time of the day
  • Doughnut chart now shows the report  for a specific hour, vs overall usage
  • Filters are clearer, and easy to reset.
  • Login stats, to see the numbers of users logging onto the server (including from Excel).
  • On the report tab, you can now open the specific report that you are analysing.
  • The ad-hoc tab now has a heatmap which helps with visualisation of the most popular reports. The drop list on this tab also now has the complete File Manager hierarchies to enable you to navigate easily.

Continue reading →

Simple Profit and Loss Planning Template

Leave a reply

Templates should be simple, clean and easy to understand. They can come in a myriad of different formats, styles and cover a multitude of various models. As a template designer, the key is to try and make the template itself disappear into the background, while at the same time coaxing the actual information it contains into the foreground.

So no crazy fonts,

2014-07-15 14_55_21-Book1 - Excel

and no crazy colours.

2014-07-15 14_56_40-Book1 - Excel

You will give your users a headache, especially if they have to stare at it for a long time.

Continue reading →

Jedox 5.1 – A Consultants Perspective : Part 4 – Wrap up

Leave a reply

jedox5-1-logo_blau1

A final note on some small(ish), cool features in Jedox 5.1. The new write back functionality allows you to directly write back to a cube via controls such as drop lists, hyperlinks and macros. If the target cell is a palo.data, Jedox will try to write the passed value into the intersection in the cube. Check out some examples here.

And web report fans will be happy (and relieved) to have available to them a custom colour palette and palette history:

2014-07-07 16_55_56-Jedox Web

The Jedox 5.1 release has been a solid step up from Jedox 5, bringing with it new  innovative features to make the Jedox consultant’s life easier. I have highlighted a small selection of new functionality which enhances your existing Jedox toolset. There is a lot more here as well, such as Data Driven Modelling and R Integration which I will focus on separately in later posts. In case you missed them, here are the links to Part 1, Part 2 and Part 3 posts on 5.1.

 

 

 

Jedox 5.1 – a Consultant’s Perspective : Part 3 – Data Validation

3 Replies

Data Validation
One of the big changes for existing Jedox web users and consultants is the advent of Data Validation. This opens up a lot of possibilities for development of web entry templates and forms. The validation is essentially a format (like Excel), so therefore you can easily copy it across to other cells, or use in a Dynarange report. The list validation is similar to data validation in excel but with an important tweak: you can add additional columns to the validation array which allows you to display one value and enter another. I will run through a couple of example below:

Continue reading →

Macros : Manipulating Worksheets

Leave a reply
macro_flash_tube_prototype

hmmm….. not that type of macro…

In macros, the Worksheets object allows you to manipulate individual worksheets inside a workbook. This can be advantageous when you are doing things like running a macro to prepare a management briefing book which might be spun out via a customised batch printing process to Excel. You can take existing sheets and add new, delete , rename as required.

Here are a few examples:

Example 1: Returning the name of the current worksheet

$value = ActiveSheet()->name();
return __msgbox($value, 'Worksheet Name', 'Info');

Example 2: Renaming the current sheet, and display the value in a message box:

$RenameWorksheet = ActiveSheet()->Range('E10')->value;
$value = ActiveSheet()->name($RenameWorksheet);
return __msgbox($RenameWorksheet, 'Renamed Worksheet', 'Info');

Example 3: Adding a new worksheet to the workbook, and display the value in a message box:

$AddWorksheetName = ActiveSheet()->Range('E13')->value;
$value = activeworkbook()->WorkSheets()->add($AddWorksheetName);
return __msgbox($AddWorksheetName, 'New Worksheet Added', 'Info');

Example 4: Count the worksheets in the workbook, and display the value in a message box:

$value = activeworkbook()->WorkSheets()->count();
return __msgbox($value, 'Worksheet Count', 'Info');

You can download a copy of the above here

Jedox Touch – Designing HTML Combo Boxes for Mobile Devices

Leave a reply

Jedox Touch is the skin that replaces the Jedox Web when you browse to a Jedox page on a mobile device.  The optimised view of the Jedox Web report allows users to pinch , zoom, enter data and a lot more while browsing their data on  phone or tablet.

Generally, combo boxes look like this on both the web and on the Mobile Touch skin:

mobile drop lists1

This can be fine depending on the application you are building, but sometimes a different type of list format is required. Wouldn’t it be great to be able to use a standard HTML drop list? Well, with a bit of macro code, you can.

Download the example here.

Basically, what we are doing is using a bit of macro magic to pass parameters to a widget that contains the hmtl code to generate the drop list. We are passing the parameters (in this case a dimension name and a list of elements) and the widget is using this information to generate the drop list.

mobile drop lists3

We can then retrieve the information (ie the selected element) back from the widget to do something more with it. for instance, we can pass to a variable or named range.

What you end up with is a nice mobile style drop list that the user will see on their phone or tablet:

mobile drop lists2

Have fun! If anyone has any further examples of this type of customised coding for mobile pages, feel free to share.

Jedox Load Balancing – The Master and the Slave

1 Reply

I had the pleasure the other week of visiting and working closely with the Jedox Team at Dawin Consulting in Seoul, Korea. They are a fantastic group of professionals working hard on their first major Jedox project for a very large Korean company. It is a big project and a team of four consultants are on it full time. 

Their client had the requirement of setting up Load Balancing as well as full Failover for their Jedox Servers. Load Balancing allows the incoming traffic to the Jedox Server to be spread over multiple web servers, which is helpful only when you have a big volume of web users.

The Load Balancing requirement architecture was as follows:

Load_Balancing_Inverted

As per the diagram above, the requirement was that users will be accessing the Jedox system via multiple Web Servers, but actually connecting to the same OLAP Server.

Setting up Jedox to Handle Load Balancing
To do this, you require 2 or more servers in which Jedox you can install Jedox. The Server that will have the active MOLAP server will be referred to as the “Master”. The additional servers that will just have the Web Service running will be called the “Slaves”. In this example, we will assume that we have 2 servers only – a Master and 1 Slave. Also, ensure that either all relevant ports are accessible through the firewalls of each server or just turn the firewall off completely for the purpose of this exercise.

Install Jedox on both Master and Slave Server. Ensure you do a full install on each server. However, as you will probably only have one licence key, install this on the Master server. You will not need a licence key for the Slave server, as the slave servers will not be running the MOLAP server.

Once you have done a full install, you need to tweak some settings on both servers. As the Slave server will only be using its HTTPD Service (ie the web service), you can stop all the other services (ie Core, MOLAP, MDX Interpreter, etc).

NOTE: Before changing any of these files on any servers, ensure you have a backup up all of the original files so you have a roll-back position in case of something going wrong.

Settings to Change on the Slave Server(s)

STEP 1:
in file C:\Program Files\Jedox\Jedox Suite\httpd\conf\httpd.conf , confirm this setting:
ServerName 127.0.0.1:80 (or other port number set during installation)
Should be
ServerName 0.0.0.0:80
STEP 2:
in file C:\Program Files\Jedox\Jedox Suite\httpd\conf\httpd.conf, we will be replacing the 127.0.0.1 to the address of the Master server. Change the following block of code: 


#ProxyPass /ub/ ajp://127.0.0.1:8194/ min=2
ProxyPass /ub/ ajp://127.0.0.1:8194/ retry=0
ProxyPassReverse /ub/ ajp://127.0.0.1:8194/
ProxyPassReverseCookiePath / /ub/

### ETL
Alias /tc/web-etl ../tomcat/webapps/web-etl
ProxyPassMatch /tc/web-etl/app/service/(.*)$ ajp://127.0.0.1:8010/web-etl/app/service/$1
ProxyPass /tc/web-etl !
ProxyPass /tc ajp://127.0.0.1:8010/
ProxyPassReverse /tc ajp://127.0.0.1:8010/
ProxyPassReverseCookiePath / /tc/

### Palo Pivot
ProxyPass /web-palo-ng ajp://127.0.0.1:8010/web-palo-ng
ProxyPassReverse /web-palo-ng ajp://127.0.0.1:8010/web-palo-ng

to


#ProxyPass /ub/ ajp://<address of master molap server>/ min=2
ProxyPass /ub/ ajp://<address of master molap server>:8194/ retry=0
ProxyPassReverse /ub/ ajp://<address of master molap server>:8194/
ProxyPassReverseCookiePath / /ub/

### ETL
Alias /tc/web-etl ../tomcat/webapps/web-etl
ProxyPassMatch /tc/web-etl/app/service/(.*)$ ajp://<address of master molap server>:8010/web-etl/app/service/$1
ProxyPass /tc/web-etl !
ProxyPass /tc ajp://<address of master molap server>:8010/
ProxyPassReverse /tc ajp://<address of master molap server>:8010/
ProxyPassReverseCookiePath / /tc/

### Palo Pivot
ProxyPass /web-palo-ng ajp://<address of master molap server>:8010/web-palo-ng
ProxyPassReverse /web-palo-ng ajp://<address of master molap server>:8010/web-palo-ng

STEP 3:
Open C:\Program Files\Jedox\Jedox Suite\httpd\app\etc\config.php. Change


define('CFG_PALO_HOST', '127.0.0.1');

to


define('CFG_PALO_HOST', <ip of master molap server>);

Settings to Change on the Master Server
OK. So at this point all of your slave servers are now pointing to the Master server for their various components. Now we need to make some changes on the Master server to allow incoming connections from the Slave Servers.

STEP 1:
Open …\core2\ui_backend_config.xml Change


 <tcp address="127.0.0.1" port="8194" />

to


 <tcp address="0.0.0.0" port="8194" />

This allows connections to the core server from outside the master server.

STEP 2:
Go to C:\Program Files\Jedox\Jedox Suite\tomcat\conf\server.xml. To Allow connections to the ETL from outside the master server, change the line


 <Connector port="7775" address="127.0.0.1" connectionTimeout="20000" protocol="HTTP/1.1" redirectPort="7743" />

to


 <Connector port="7775" address="0.0.0.0" connectionTimeout="20000" protocol="HTTP/1.1" redirectPort="7743" />

In the same file, change this line for AJP (for Report Manager, File Manger)


<Connector port="8010" protocol="AJP/1.3" address="127.0.0.1" redirectPort="7743" />

to


<Connector port="8010" protocol="AJP/1.3" address="0.0.0.0" redirectPort="7743" />

Connection Manager
Once this is all in place, you will need to change the Jedox Connections in Connection Manager. Assuming you have the same connection names as per the default install, change the localhost and localhost_static to the Master IP address. You can also use the IP address for the Load Balancer here, which is useful, particularly if you are also implementing a Fail Over regime on top of the Load Balancing. Once all your changes are in place, restart all services on the Master Server, and restart the JedoxSuiteHttpdService on the Slave server(s).

Now go to the IP address of the Slave server in a browser. You will now be using the web service of the Slave server but connecting to the MOLAP server of the Master. Type in the IP of the Master, and you should now be connecting to the Master web server.
Follow the above again for each slave server that you have.

The Load Balancing bit..
Once we completed the configuration, we handed the IP addresses over to the clients IT Department for configuration inside the Load Balancer. There are many load balancing software products out there, but in this case the Windows Server 2012 Network Load Balancing (NLB) was used. The NLB has an IP address of its own, and the IP addresses of the Master and Slave are routed to this address, so the server access is seamless as far as the users are concerned.

Please note that the above changes to the Jedox filesystem comes without warranty and does not guarantee that future changes to the binaries could potentially impact this approach to load balancing.

Adventures in Relational Data

Leave a reply

In some models, the data you are presenting is not multidimensional. Lists, records, transactions are not necessarily made for storing in cubes. Some data just makes more sense in a table. Although Jedox’s bread and butter is multidimensional data (ie cubes, dimensions), you still have the capability to display relational information when you need it in Jedox web.

3 Ways to Use Relational Connections

1. Combo Boxes
If you have used Combo Boxes on the web, you would have noticed that there is the option to point to a relational ODBC source to populate the list.

rel1

This is very useful when you have a report and the data you need from the drop lists cannot be generated from a subset of dimension elements.

rel2

Additionally, you can use variables to store the selected value, and pass it into another relational drop list. This way you can generate cascading drop lists based on user selections.

2. ODBC Paste Query
This automatically generates ODBC.DATA formulas that point to the relational source. As your query changes (by variables for example), the data will automatically update. This is excellent for dynamically pulling through the result-set of a relational query into Jedox web and combining it with other data for a report or dashboard. When using the ODBC Paste query, make sure you have set up the ODBC connection in Connection Manager and have tested the connection.

rel3

3. PHP Macro
Finally, If the top two methods do not return you what you need, you can always go direct with a Web Macro. The advantage of this approach is that you can get the data back to the macro as an array and you can then display it anyway you like. For instance, you could have a message box popping up with data from a query. Or you could dynamically validate some data entry against a relational data source as users are entering it on the web.

rel4

You can download an example of the PHP macro code here.

Exploring Row and Column Properties

Leave a reply

There are a whole bunch of web report properties that you can play with in Jedox Web Macros that can add a touch of professionalism to your web reports. Every small addition of function that you can add to your reports increases the likelihood that your users are going to embrace what you are giving them.

The file here illustrates some possibilities with row and column adjustments. You can set up a macro to allow the user to size the column or move it to an exact width. 

Here, I am adjusting the row  height in pixels, retrieved from another cell on the workbook:


$height = ActiveWorkbook()->names->item('example1_height')->value;
 $row = ActiveWorkbook()->names->item('example1_row')->value;
 ActiveSheet()->row($row-1)->height($height);

..and now the column width:


$width = ActiveWorkbook()->names->item('example3_width')->value;
 $col = ActiveWorkbook()->names->item('example3_col')->value;
 ActiveSheet()->column($col-1)->width($width);

Enjoy!

Macros : Variables

Leave a reply

Jedox Web variables are session based, in-memory objects which allow users to transfer values between and around reports. Variables allow you to do things like:

  • Set a default value on a report
  • Transfer selected filter values from one report to another
  • Pass user selected parameters to a relational Dynarange

Setting a Variable value – 3 Ways

1. Directly on the spreadsheet. If you type in “=@hello” into a cell , and you would have created your first variable, called hello. If you check the Used variables Manager (on menu, click Data, Variables) , you will see that you variable has been created. It has #VALUE as a value as you have not yet passed a value to it:

macro_variables1
Type over the cell again with a value , and you will see the Variable has now changed to that value. You can now refer to @hello anywhere in your spreadsheet or macros and it will return whatever value that you have assigned to it:

macro_variables2

2. Using a control to assign a value to it. You can use the date picker, combo box, check box or button to assign a value to a variable:

macro_variables3
3. Finally, you can assign a variable & value via a macro:

function __open()
 {
 //variable name is hello and hello’s value is world
 define_variable('hello', 'world');
 }

You can also retrieve a variable like this:

function __open()
 {
 //get variable value
 $value = retrieve_variable('hello');
 }

You cannot assign a cell value to a variable (for instance, a result of ENAME formula), but you CAN write you own small user defined function to return the cell value to a variable:

function CellToVar($variable, $value)

{
 define_variable($variable, $value);
}

you user defined function looks like this in the spreadsheet:

=CELLTOVAR(“variable1”, D3)

Once you assign a variable, you can use it in many areas of you report. One of the more interesting is the relational drill through:

macro_variables4

This functionality means that you can construct a variable in a macro and then filter your relational query with this.

When a user logs out, the session variable value will disappear unless  the variable has been marked as a private variable. If it is marked private, Jedox stores the last assigned value and the next time a user logs in, that value will be used.

You can see variables in action in a web spreadsheet here.