Sort Dimension Elements

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • 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:




    ElementAttribute
    A1
    B2
    C6
    D5
    E7
    F4
    G3




    And did want to order it by it’s attribute like this:



    ElementAttribute
    A1
    B2
    F4
    D5
    C6
    E7
    G3



    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);

    }
    Files