Sum over subset… Jedox !!!

Hi,

A seemingly trivial looking task took me all over the board recently to figure out a solution for the business where users didn’t wanted to create consolidated nodes in the dimensions to get the aggregations, instead they wanted to get the aggregated results via subset which where filtered based upon the attributes flags, phew !!!

Ne ways I thought this should be achievable by some tips and tricks applied in the areas of excel based formulas and get the result. So instead of extending my words further I will first present you with the picture which would directly illustrate the problem and solution which I will describe below more:

From the above as you can see, the “Sum by Subset” row has the longest looking formula, but this formula once broken down to components is very easy to interpret.

This formula is initially using the PALO.TFILTER to generate the filter for the dimension year to include only the elements “2011” and “2012”.

This is then fed to the PALO.SUBSET formula which is responsible to generate the subset required to be fed into  the PALO.DATAV (be careful this is DATAV not DATAC, which is array based retrieval of values from the cube via Excel)

Once we have got the PALO.DATAV working we wrap up the output into the Excel based SUM formula and enter the all content of the formula as array formula (Ctrl+Shift+Enter).

And there you go, you have got the result as desired aggregated summed values for the two years worth of data in excel spreadsheet, this can also be leveraged in paste view generated via Jedox add-in for excel.

Now what you have read so far is part of the story till Excel was in picture, but the same successful story is not so successful in Jedox web, because I do acknowledge that Jedox has made a smart and rich UI to replicate excel on web, but still some glitches are found, which actually elongated this story.

Jedox web is not so good as evaluating the nested array formulas as of yet and I expect it to be fixed up soon in next coming releases, once its done it awesomeness will be increased further…

So the solution to the rescue for Jedox Web for the similar to achive is the custom UDF via Macro.

<pre>
function subset_sum($region,$yearPattern){

// Define constant variables.
define('HOST', 'localhost');
define('PORT', '7777');
define('USER', 'admin');
define('PASS', 'admin');

// Establishes a new connection to the OLAP server.
$connection = palo_init(HOST, PORT, USER, PASS);
// create the variables db_name and cube_name
$db_name = 'Demo';
$cube_name = 'Sales';

$tFilter = palo_tfilter(array($yearPattern),true);
$ySubset = palo_subset($connection, $db_name,"Years",1,null,null,null,null,$tFilter, null);
$ySubset = array_map(function($element){return $element['name'];}, $ySubset);
array_unshift($ySubset,2,1);
$val = palo_datav($connection, $db_name, $cube_name,
"All Products",$region,"Year",$ySubset,"Actual","Units");
unset($val[0]);
unset($val[1]);

// Closes the established connection.
palo_disconnect($connection);
return array_sum($val);
}

Attached along with this post are both the Excel and Jedox web based solutions… And I look forward to the comments if any thing better can be done !!!

Hope it helps…

Download:

http://amolpandey.com/home/downloads/blog23_download.zip