Author Archives: Chris Mentor

About Chris Mentor

Director at Naked Data, over 13 years now in information management and most of that spent taming in-memory, OLAP technology.

Jedox 5.1 – a Consultant’s Perspective : Part 2 – Security

1 Reply

Blue Doors LockedSecurity is critical in any Jedox implementation. Efficient ‘lock down’ of your models are just as important to an organisation as the data they contain. There has been a need for an additional layer of security in Jedox’s database architecture for a while now.

Pre-5.1 , you could lock down cubes , dimensional elements, even cells. But you could not actually hide or restrict databases themselves. This is a hugely important step – now you can hide all assortment of databases from certain user groups. A great example that has come up time and time again for me is around Payroll models. Even though Jedox security stops unauthorised access to cubes and dimensions within a Payroll database, the fact that unauthorised users could ‘see’ the database folder name made administrators nervous. This is now a thing of the past with the Jedox developers re-writing the security model to cater for  databases. There is now a separate security object (#_GROUP_DATABASE_DATA) that allows admins to restrict by group access to a database.

This new security development also opens the door potentially to a more multi-tenanted approach to model development. I have updated our security templates for database restriction and made them available :

Web Security Template

Excel Security Template (pw:jedox)

Enjoy!

Jedox 5.1 – a Consultant’s Perspective : Part 1

2 Replies

jedox5-1-logo_blau1So after a long wait, and a partner Preview since December 2013, Jedox 5.1 is finally out. Great! There has been a fair bit of noise in the last few weeks by Jedox, us (Naked Data) and a few other partners about this release. On the surface, 5.1 has some cool new features (re-skinned ETL, R Integration, Data Driven OLAP, etc), but peeking under the covers, there has certainly been a serious amount of work by the Jedox Dev team on lots of other features too.

Over my next few posts, I am going to look at them in detail – some pretty obvious and some hidden away. As usual, I will try to provide examples where relevant and a bit of context around where these can be used in real life situations.

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.

Time and Time Index

Leave a reply

Time-PhotoA fair number of our clients run Jedox models down to a day level. Retail Merchandising models for example, typically require a Daily Sales Cube. Same for the Sales component of a Manufacturing model. Daily Sales models are on the large side for Jedox – in my experience, anywhere between 4 – 8GB in memory, depending on the dimensionality and the volume of data loaded. Like all Jedox models, density is your friend. Keeping the data model efficient and compact will ensure rules and access times to the data are optimised.

A Time dimension down to an individual date level is therefore a critical component to building these types of models. But having the dates in a dimension is not nearly enough – you need the consolidations and attributes and measures to make it a truly workable within the model. For example, you will most probably require Month-To-Date (MTD) calculations (or sometimes Week-to-Date). However, MTD consolidations in a Date dimension are not desirable. You can potentially end up with hundreds (or even thousands) of crazy consolidations that can confuse the end user. It is usually far more efficient to utilise either a measure to hold the Month to Date values for a particular measure, or use a separate “Time Index” dimension.

Time Index Dimension

A separate time index dimension allows you to work in conjunction with a Date dimension to calculate MTD or YTD values. Year to Date is especially problematic – when you are dealing with a Daily model, users require “Date Year-To-Date” calculations – not just the year-to-date consolidation of the months.

When employing a Time Index dimension, data is loaded against the specific date in the Date dimension:

tti1

and the element “Date” in the Time Index dimension:

tti2

The MTD, YTD (and potentially LTD) can then be calculated and stored against the appropriate elements. MTD, YTD and LTD values are typically calculated in 2 ways: you can do the calculation in the SQL if your data is coming from a relational source (fastest) , or you can use groovy and loop through your cube data to perform the calculation (slowest). I have seen the MTD calculation also done in rules, but this would be inefficient for all but the smallest models.

The key element here is MTD. If you are running out of time/resources on the server, both YTD and LTD can be back calculated on your templates using attributes and the MTD values. But if you have the option, if it certainly nice to have these elements calculated for your end user by the system.

A word of warning – as you are essentially hardcoding the MTD / YTD data in the cube, be prepared for the size and load time of the model to grow.

Building the Date Dimension

So, back to the Date dimension. I have attached an ETL Project which builds a date dimension based on parameters (start and end year) and the calendar extract. It also builds for you automatically a bunch of attributes (next, prior) . For the Prior attribute, if the element is base, it will give you the prior date, month, prior month, etc. It also calculates the Last Day of the Month attribute which can be useful if you are rolling back exactly one month from any point in time, for example. You will note that the Date elements are all numeric. It is much easier to perform calculations on a numeric than a string on a template. There is a few different formatting attributes that you can modify to suit your date format.

Download the Date project here.

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.

Combining filter criteria for multiple selections in cube extracts

Leave a reply

Our good friend, world champion sailor and Jedox expert Sebastian Moser has offered up a cool post around getting a little more sophisticated with cube extracts in ETL:

Sometimes, you might want to Export data from a Jedox database to a DWH or other systems. Occasionally, you don’t quite get the correct result from ETL cube extract with provided filter functionality. Or exporting data in all possible combination does take a long time.

For example you would like to export all legal entities in their corresponding local currency. But in the cube you have translated your P&L to all other currencies to compare and consolidate them:

cfs_sebastian1

cfs_sebastian2

Therefore you would need to make a link between the legal entity and the currency in the cube extract. You are facing the situation that in the standard filter of the ETL extract you could define something like this:

cfs_sebastian3

You would expect Company USA in USD, Company Germany in EUR and Company Australia in AUD. But your result would rather look something like this:

cfs_sebastian4

So basically you are exporting all the possible combinations between legal entity and currency dimension. The key to solve this is creating a table of the correct combinations of what you would like to see. This could be a cube, a SQL table, a flat file or any other source. In my case I am using the elements attributes as the currency only depends on the legal entity.

First of all I have to create an attribute for in the legal entity Dimension:

cfs_sebastian5

Maintain the correct local currency for each legal entity:

cfs_sebastian6

Please make sure your entries in the attributes are correct spelling according to the currency dimension:

cfs_sebastian7

Entries like this will cause Errors in the ETL:

cfs_sebastian8

As we have defined all of our correct combinations we now need to bring it into the ETL. Therefore we read the dimension including it’s attributes:

cfs_sebastian9

cfs_sebastian10

In the next step we have to define the variables and place them into the cube extract as we now want to iterate through our combinations and extract the data from the cube.
Create the variables in ETL:

cfs_sebastian11

And place them into the cube extract:

cfs_sebastian12

This would only give you one set of combination of variables at a time. Therefore we want to feed the variables in the cube extract with all sets of our table (attributes).
You will need to map the column names of your set of combinations according to the variable name in the ETL:

cfs_sebastian13

cfs_sebastian14

In the last step you need to define the loop through the set of combinations to iterate the export of the data cube in a transform and union the data.

cfs_sebastian15

This transform will map every line of the “set of combination” loop source to the variables and executing the export “Cube_extract” for each set.

Your result will look like this:

cfs_sebastian16

So the legal entity is now linked to the currency and your extract show the data of all companies in their own currency.

You could use this procedure for as many combinations you need. If the process is not dependent on one dimension, (in this case Legal Entity) you could think about creating a cube for the combinations or a flat file. All you need is a table in ETL for mapping columns and rows to variables.

Click on the links for the database and ETL project that I used in the example above.

For further questions please don’t hesitate to contact Sebastian.Moser@consysmo.com

Subsets : Data Filters

Leave a reply

The Jedox Data Filter (DFilter) is one of the more important filters that you can use in subsets. The Data Filter allows you to query the cube dynamically query a cube and order/filter your dimension based on this query. This is really handy  when you need to build a report based upon data, such as a top/bottom element listing, a zero suppressed report, or sorting your report based on the data.

When setting up this subset, you need to select the cube and dimensions that the dfilter will be based on. Make sure you select the right one, or you may not get the result you expect…

d-filter1

After you have selected the cube, the next thing to do is to select the dimension elements from each dimension for your dfilter.

To optimise performance of the data filter, ensure you specify an element for each of the dimensions . Like all filters, you can make your selections dynamic based on cells on your report:

d-filter2

Finally, you can select calculation options (sum, count the result, etc), specify the top or bottom count or choose how to sort the result. A completed report performing a top/bottom filter and sorting is here.

Data filters can really improve the dynamism of your reports and once you start using them , you will find that a lot of reports can benefit by the inclusion of this filter in your subsets.

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!