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.

Simple Profit and Loss Planning Template

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

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

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

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

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

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

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

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.