same question here, would be nice to have an option to order elements.

this can only be done with extensive ETL = rebuilding the complete dimension/cube(s)

and after that handcrafting the dimension...

normally I only tweak the hierarchy in the modeler by hand (tolerable since my dimensions do not change much) after the ETL-job

I recently faced the same issue. I had a very huge dimension looking like this:








And did want to order it by its attribute like this:






Notice, that I only wanted to change the order of base-elements in elements, which did not contain another consolidated element.

„palo_emove“ seemed not the right way, because it did not update the dimension’s structure properly. Instead I used in my php code „palo_eupdate“.


Please find attached a Jedox web-spreadsheet for the task described above. (NOTE: You should substitute .txt file extension with .wss)

This is the php code piece:


############PHP####################



function order(){



$host = activesheet()->range('C2')->value;

$port = activesheet()->range('C3')->value;

$user = activesheet()->range('C4')->value;

$pass = activesheet()->range('C5')->value;

$db_name = activesheet()->range('C6')->value;

$dimension_name = activesheet()->range('C7')->value;

$attribute = activesheet()->range('C8')->value;



$connection = palo_init($host,$port,$user,$pass);

$element_array = palo_dimension_list_elements($connection, $db_name, $dimension_name);



for($i=0; $i < sizeof($element_array); $i++){

if($element_array[$i][type] == 'consolidated'){

$children_array = palo_element_list_children($connection, $db_name, $dimension_name, $element_array[$i][name]);

$order = array();

$change = 1;

for($i2=0; $i2 < sizeof($children_array); $i2++){

if($children_array[$i2][type] == 'consolidated'){

$change = 0;

}

array_push($order,palo_data($connection, $db_name,'#_'.$dimension_name,$attribute,$children_array[$i2][name],'~'));

}

array_multisort($order,$children_array);

$new_children = array();

for($i4=0;$i4<sizeof($children_array);$i4++){

array_push($new_children,array($children_array[$i4][name],$children_array[$i4][weight]));

}

if($change == 1){

palo_eupdate($connection, $db_name, $dimension_name,$element_array[$i][name],'C',$new_children,0);

}

}

}



palo_disconnect($connection);

}

####UPDATE#####

When updating huge dimensions, you may get a timeout error from php after 90 seconds. So I added set_time_limit(xx) to my php script. Please find an updated file in the attachments.

#########################################################

function order(){
$host = activesheet()->range('C2')->value;
$port = activesheet()->range('C3')->value;
$user = activesheet()->range('C4')->value;
$pass = activesheet()->range('C5')->value;
$db_name = activesheet()->range('C6')->value;
$dimension_name = activesheet()->range('C7')->value;
$attribute = activesheet()->range('C8')->value;
$timeout = activesheet()->range('C9')->value;
set_time_limit($timeout);
$connection = palo_init($host,$port,$user,$pass);
$element_array = palo_dimension_list_elements($connection, $db_name, $dimension_name);
for($i=0; $i < sizeof($element_array); $i++){
if($element_array[$i][type] == 'consolidated'){
$children_array = palo_element_list_children($connection, $db_name, $dimension_name, $element_array[$i][name]);
$order = array();
$change = 1;
for($i2=0; $i2 < sizeof($children_array); $i2++){
if($children_array[$i2][type] == 'consolidated'){
$change = 0;
}
array_push($order,palo_data($connection, $db_name,'#_'.$dimension_name,$attribute,$children_array[$i2][name],'~'));
}
array_multisort($order,$children_array);
$new_children = array();
for($i4=0;$i4<sizeof($children_array);$i4++){
array_push($new_children,array($children_array[$i4][name],$children_array[$i4][weight]));
}
if($change == 1){
palo_eupdate($connection, $db_name, $dimension_name,$element_array[$i][name],'C',$new_children,0);
}
}
}
palo_disconnect($connection);
}

Hi !
Here's a way to sort dimension without any attribute. And btw @Gerwin Haas your code doesn't work well with consolidate element.