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 its 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

    The post was edited 1 time, last by Gerwin Haas ().

  • ####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);
    }
    Files

    The post was edited 1 time, last by Gerwin Haas ().

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

    PHP Source Code

    1. function order($db_name, $dim_name, $timeout = 90)
    2. {
    3. set_time_limit($timeout);
    4. $sid = palo_init($_JEDOX['OLAP_HOST'], $_JEDOX['OLAP_PORT'], $_JEDOX['OLAP_SESSION_ID']);
    5. $els = palo_dimension_list_elements($sid, $db_name, $dim_name);
    6. for ($i = 0; $i < sizeof($els); $i++)
    7. {
    8. if ($els[$i]["type"] == 'consolidated')
    9. {
    10. $children = palo_element_list_children($sid, $db_name, $dim_name, $els[$i]["name"]);
    11. $children_name = array_column($children, "name");
    12. array_multisort($children_name, $children);
    13. $new_children = array();
    14. for ($k = 0;$k < sizeof($children);$k++)
    15. {
    16. array_push($new_children, array(
    17. $children[$k]["name"],
    18. $children[$k]["weight"]
    19. ));
    20. }
    21. palo_eupdate($sid, $db_name, $dim_name, $els[$i]["name"], 'C', $new_children, 0);
    22. }
    23. }
    24. palo_disconnect($sid);
    25. }
    Display All
    Hello everyone if you are stuck and you can't post anything on this forum just contact info@jedox.com and request for account activation ! You're welcome !