Cube Sync Real Time

Hi,

Recently coming across this issue, and problems relating to it. I took upon a following approach which I would like to share across, helping a wider community and get reviews on the same:

Problem:

Many times designing the cubes there are situations in which the cube may not have all the dimensions required for the reporting purposes reason’s being, the planning users want to splash data only on certain limited set of dimensions, but for analytical purposes they need some additional dimensions (which may be attributes of previously used dimensions for planning cubes).

Or for many other reasons where there are lookups involved or complex computation is involved between the planning and reporting cubes. But the summary of the problem is, keeping the cubes in sync real time. So that the numbers remain homogeneous across the system and do not get out of balance, confirmed by many numerous reconciliation reports which are tedious to keep an eye always.

Some arguments do exist that Rules can help achieve this to link multiple cubes, but to my experience so far, Rules are great when used for computations upon variables within one cube itself (even good when cube sizes are relatively small with look up formulas), but as soon as lookup functions start to appear in the rules like “PALO.DATAC” that’s where none my rules were ever performant from usability of the system (as my projects involved large data set cubes), not just with Jedox event Microsoft confirms this nature of SSAS and suggest to keep the cubes comprehensive of data as far as possible.

Microsoft:

“Any calculation using the LookupCube function is likely to suffer from poor performance. Instead of using this function, consider redesigning your solution so that all of the data you need is present in one cube.”

http://msdn.microsoft.com/en-au/library/ms144720.aspx

Solution:

At work I realized always keep things configurable (Golden Rule), as every day some thing changes like rules, logic, constants. So the best mechanism I planned to suit the flexibility requirements to keep the cubes in sync was to leverage the Jedox ETL but custom designing the ETL project to keep the cubes in sync  by the required variables, and kicking off the ETL by the web service triggered by the Supervision Server on the cell change event, this way we wont loose out on any cube change event and all the affected cubes are also synced up:

The configuration of the set up is as follows:

1. Configure the “PALO.INI” for monitoring cell change events via Supervision Server

Add the following command:

“use-cube-worker”

2. In the “SVS/sample_script” folder of Jedox create a copy of “sep.inc.on_cell_change.php” and place it in the “custom_script” folder by renaming it to your choice, in my case it is “sep.inc.on_cell_change_cube_sync.php”

Amend the code in the file as follows:

/**
* Sample Script for cell change event in Jedox OLAP Server. Version 6.0.0
* SVN: $Id: sep.inc.on_cell_change.php 239 2013-02-12 14:52:17Z vmalicevic $
*/

// adapt protocol, host, port here to match your setup
define('ETL_URL','http://127.0.0.1:7775');

// wait for job to finish prior returning status
define('WAIT_FOR_FINISH', true);

// max execution time of script in seconds used in combination with WAIT_FOR_FINISH
define('MAX_EXECUTION_TIME', 10);

class SEPEventHandler extends SEPEventHandlerBase
{
public function OnUserLogin($username)
{
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< User logged in: $username >>");
}

public function OnUserLogout($username)
{
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< User logged out: $username >>");
}

public function OnUserAuthenticate($username, $password) { // bool
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< User authenticate >>");
return true;
}

public function OnUserAuthorize($username, $password, array& $groups) { // bool
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< User authorize >>");
return true;
}

public function OnWindowsUserAuthorize($domain, $username, array $winGroups, array& $groups) { // bool
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< User Windows authorize, domain $domain, username $username >>");
$groups = $winGroups;
return true;
}

public function OnServerShutdown() { // void
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< Server shutdown handler >>");
}

public function OnDatabaseSaved($database) { // void
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< Databased saved : $database >>");
}

public function OnTermination() { // void
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< Termination handler >>");
}

# setting the cubes or part of cubes which should be triggered
public function InitCubeWorker($database,$cube)
{
if($database == "Demo")
{
# set triggering area for cube ‚Sales‘
if($cube == "Sales")
{
$AreaA = array(DIMENSION_TOTAL,DIMENSION_TOTAL,DIMENSION_TOTAL, DIMENSION_TOTAL,DIMENSION_TOTAL,array('Units','Turnover'));
$this->WatchCubeArea($AreaA,'SalesCube');
}
}
}

public function OnDrillThrough( $database, $cube, $mode, $arg ) { // string
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< OnDrillThrough >>");
return "not implemented;\r\n";
}

public function InitDimensionWorker()
{
sep_log("<< USING SAMPLE SCRIPT PLEASE ADJUST TO MATCH YOUR CONFIGURATION >>");
sep_log("<< Get triggering dimensions >>");
}
}

function SalesCube($database, $cube, $areaid, $sid2, $coordinates, $value, $splashMode, $additive)
{

include_once('etl_code.php');</pre>
<pre>$user = get_user_for_sid($sid2); // user who made the change
$groups = get_groups_for_sid($sid2); // groups of the user who made the change

if ($coordinates[5] == 'Units')
{
# read the Price of target product from it's attribute cube
$unit_price = palo_dataa("SupervisionServer/$database", '#_Products',array('Price Per Unit',$coordinates[0]));

# calculate turnover
$turnover_cell = array ($coordinates[0], $coordinates[1], $coordinates[2], $coordinates[3], $coordinates[4], 'Turnover');
$turnover_value = $unit_price * $value;

# starting to lock the database (needed to be allowed to change data)
# (notice that the time the server will be locked for writing has been minimized to the minimum time needed)
event_lock_begin($areaid,$sid2);

# get old units value
$oldUnitsVal = palo_data("SupervisionServer/$database", $cube, $coordinates[0], $coordinates[1], $coordinates[2], $coordinates[3], $coordinates[4], $coordinates[5]);

# write original dataset at given coordinates in 'Sales' cube
# (notice that in case of a triggered event you may decide yourself whether the input data should be written to
# database or not. In case you want it written you must not forget to write it into the database)
$write = palo_setdataa($value, FALSE, "SupervisionServer/$database", $cube, $coordinates);

# write calculated turnover into column 'Turnover' in 'Sales' cube
$write = palo_setdataa($turnover_value, FALSE, "SupervisionServer/$database",$cube, $turnover_cell);

# ending lock of affected database
event_lock_end();
}
elseif ($coordinates[5] == 'Turnover')
{
# Throws a popup notice in Excel
sep_error("Warning", "No one is allowed to change the Turnover");
}

//Put the ETL Kickoff code at the bottom of the script so that once the changes intended by the user are saved to the cube can be picked up by the corresponding ETL event.
//Location of ETL kick off code is vital to address the requirements.

sep_log("ETL Function is about to be called !!!");
sep_log(dirname(__FILE__));

startJob();

}

Also addtionally in the same folder create the following file called “etl_code.php” for custom code relating to ETL kick off

</pre>

function connectSoap()
{
 $wsdl_url = ETL_URL . '/etlserver/services/ETL-Server?wsdl';
 $server = @new SoapClient($wsdl_url, array('exceptions' => true, 'location' => $wsdl_url));
 return $server;
}

function startJob(){
sep_log("ETL Function is called...");
$server = connectSoap();
$project = 'LearnDummy';
$type = 'jobs';
$name = '[J][JustFromEvent]';
$locator = "$project.$type.$name";
 $response = $server->execute(array('locator' => $locator));
 sep_log("ETL Kick off has now been called...");
}

3. Following the above changes amend the “SVS” configuration file for the script “sep.inc.php” to point to you new custom script:

include './custom_scripts/sep.inc.on_cell_change_cube_sync.php';

4. Finally restart the MOLAP service and , fire up a Jedox spreadsheet and with your inputs flowing in to the cube and keep an eye on the Jedox ETL log accumulating, indicating the process works and your ETL is being called upon any changes occurring in the targeted cube region. (In my case the ETL name was “LearnDummy” with the Job name as “[J][JustFromEvent]”)

Note: One thing to be very careful for this process is, to ensure that this mechanism is primarily built for user based inputs to the cube from spreadsheets, to track them and sync them over. But for any ETL based cube load, the SVS event must be ensured to be de-activated for the optimal performance in the nightly load processes, if missed can lead to huge overburdening of the process which it wasn’t designed to cater.

The main benefit I experienced from this solution is that its an async operations hence user don’t realised any performance impacts, as the user inputs trapped are only used for kicking off the ETL job which works on its own, effectively by queuing jobs under heavy loads and ensuring all operations are accomplished with the logs in place to review and audit.

Hope it helps, Enjoy !!!