Slow dataTable rendering

stijn wuyts

Dear,

I have a dashboard made in Pentaho CDE 6.0. The loading takes almost 6 seconds. Views in the dashboard are:

• 1 DataTable, showing Revenue for last 12 months, by Client with posibility to drill down 4 levels (Client, Responsible 1, Responsible 2, Project). The initial view is 280 rows, 14 columns. All 4 levels expanded would be approx. 4000 rows.

• 1 DataTable, showing Revenue for last 4 quarters, same requirements as above. All 4 levels expanded would be approx. 4000 rows.

• 1 Bar chart Graph showing either tables graphically, depending on the selected row. I.e. a clickaction event on either table will trigger the bar chart view the clicked row.

My problem is that the performance of 6 seconds is too bad, especially because each drill down takes the same amount of time. The problem seems at the javascript level when rendering the 2 DataTables. I attached an image of the Google chrome performance debugging.

Could you help me find a solution?

Thanks in advance.

Stijn

allan

Can you show me the code please, or even better a link to the page. I see that _fnAddData() appears to be called quire a lot and that is taking up a lot of time. How are you adding data to the table?

How are you doing your four level drill down? That isn't a feature that is provided by DataTables.

Allan

stijn wuyts

Hi Allan,

Thank you very much to take interest in my question. Sharing a link to the page will be difficult as it is only accessible via the local network + proxy. If you are OK with it, we can do a webex / skype session to fix the access? That would be great.

Let's the take the example of the DataTable showing last 12 month Revenue. I attached the image of the table. Below is the code of the variable holding describing this component. The components are activated in Pentaho with a statement like this:

Dashboards.addComponents([render_RevenueM, render_RevenueQ, render_last12M, render_lastFY, render_MarketOrBeaconnQ, render_MarketOrBeaconn, render_RevenueGraph]);

Dashboards.init();

I believe the table components are fully translated to DateTable rendinging by the Pentaho framework itself, i.e. having the same pre-execution, post-execution and drawcallback functions as DataTables so I don't know if you would also need to have the Pentaho code that does this translation. Maybe this component definition gives you sufficient insight.

1. The data of the Table is fed by a dynamic MDX query. Using the TOGGLEDRILLSTATE function is drills down on a dimension with 4 levels. That's why you can see that there are 4 parameters defined passing the MDX paths. The component has also 2 columns defining the path and ordinality of the set shown. These columns are hidden but the values are taken on a onclickaction. Ypu can see a piece of code on the onclickaction setting the MDX parameters.
   The drawCallback is basically used to give CSS styling to the 4 different levels.

var render_RevenueM = {
  type: "Table",
  name: "render_RevenueM",
  priority: 5,
  htmlObject: "RevenueM",
  listeners: ['param_level1_m','MarketOrBeacon','param_level2_m','param_level3_m','param_level4_m','param_level2_arr_m','param_level3_arr_m'],
  parameters: [["param_level1_m","param_level1_m"],["param_level2_m","param_level2_m"],["param_level3_m","param_level3_m"],["param_level4_m","param_level4_m"],["MarketOrBeacon","MarketOrBeacon"]],
  executeAtStart: true,
  preExecution: function f(){




      this.setAddInOptions("colType","formattedText",function(cell_data){
          
        
            return { textFormat: function(v, st) { return numberWithCommas(v,0); } };
            
        
    
    });

}  
 ,
  postExecution: function f(){
    
    $("#" + this.htmlObject + " table th").eq(0).text('SH > CP > Customer > Project' );
    
    $("#" + this.htmlObject + " table th").eq(12).text(last12M[0]);
    $("#" + this.htmlObject + " table th").eq(11).text(last12M[1]);
    $("#" + this.htmlObject + " table th").eq(10).text(last12M[2]);
    $("#" + this.htmlObject + " table th").eq(9).text(last12M[3]);
    $("#" + this.htmlObject + " table th").eq(8).text(last12M[4]);
    $("#" + this.htmlObject + " table th").eq(7).text(last12M[5]);
    $("#" + this.htmlObject + " table th").eq(6).text(last12M[6]);
    $("#" + this.htmlObject + " table th").eq(5).text(last12M[7]);
    $("#" + this.htmlObject + " table th").eq(4).text(last12M[8]);
    $("#" + this.htmlObject + " table th").eq(3).text(last12M[9]);
    $("#" + this.htmlObject + " table th").eq(3).text(last12M[9]);
    $("#" + this.htmlObject + " table th").eq(2).text(last12M[10]);
    $("#" + this.htmlObject + " table th").eq(1).text(last12M[11]);
    
  
  
    
    // alert(scrollTop);
    
    $('#RevenueMTable tbody').scrollTop(scrollTop);
       
     

    
} ,
  extraOptions: [],
  expandParameters: [],
  expandOnClick: false,
  chartDefinition:  {
    dataAccessId: "OLAP_RevenueM",
    path: "/home/admin/Dashboards/Revenue8.cda",
    clickAction: function f(e){
   
   var page_y =  $('#RevenueMTable tbody' ).scrollTop() ;
   Dashboards.setParameter("scrollTop", page_y);
   
   var lastCol = $("#" + this.htmlObject + ' td:last').index() + 2;
   
   var chosenPath = e.rawData.resultset[e.rowIdx][lastCol-1]; 
   var Ordinality = e.rawData.resultset[e.rowIdx][lastCol];
   
   var i = e.rowIdx;

   /* Column 0 */
  if(e.colIdx === 0) {
  
     if(Ordinality === 0) {
     
         if(param_level1_m === ''){ 
           
           var mdxFragment = "[Revenuedim_Client].[All Clients]";
           var mdxFragment2 = '';
           var mdxFragment3 = '';
        
           
            
         } else {
             
             mdxFragment = "";
             mdxFragment2 = '';
             mdxFragment3 = '';
         
         }
 
     } else if (Ordinality == 1) {
         
          if(param_level2_arr_m === '') {
           
               var arr = [];  arr.push(chosenPath) ;
               param_level2_arr_m = arr;  
             
              
            } else {
                    
                    if($.inArray(chosenPath, param_level2_arr_m) > -1) {
                    
                        param_level2_arr_m.splice( $.inArray(chosenPath, param_level2_arr_m), 1 );
                       
                    } else {
                        
                          param_level2_arr_m.push(chosenPath);
                        
                    } 
    
                }
     
        var mdxFragment = "[Revenuedim_Client].[All Clients]";
        var mdxFragment2 = param_level2_arr_m.toString();
  
     } else if (Ordinality == 2) {
         
          if(param_level3_arr_m === '') {
           
               var arr = [];  arr.push(chosenPath) ;
               param_level3_arr_m = arr;  
             
              
            } else {
                    
                    if($.inArray(chosenPath, param_level3_arr_m) > -1) {
                    
                        param_level3_arr_m.splice( $.inArray(chosenPath, param_level3_arr_m), 1 );
                       
                    } else {
                        
                          param_level3_arr_m.push(chosenPath);
                        
                    } 
    
                }
     
        var mdxFragment = "[Revenuedim_Client].[All Clients]";
        var mdxFragment2 = param_level2_arr_m.toString();
        var mdxFragment3 = param_level3_arr_m.toString();
  
     } else if (Ordinality == 3) {
         
          if(param_level4_arr_m === '') {
           
               var arr = [];  arr.push(chosenPath) ;
               param_level4_arr_m = arr;  
             
              
            } else {
                    
                    if($.inArray(chosenPath, param_level4_arr_m) > -1) {
                    
                        param_level4_arr_m.splice( $.inArray(chosenPath, param_level4_arr_m), 1 );
                       
                    } else {
                        
                          param_level4_arr_m.push(chosenPath);
                        
                    } 
    
                }
     
        var mdxFragment = "[Revenuedim_Client].[All Clients]";
        var mdxFragment2 = param_level2_arr_m.toString();
        var mdxFragment3 = param_level3_arr_m.toString();
        var mdxFragment4 = param_level4_arr_m.toString();
  
     }
 
     
     Dashboards.setParameter("param_level1_m", mdxFragment);
     Dashboards.setParameter("param_level2_m", mdxFragment2);
     Dashboards.setParameter("param_level3_m", mdxFragment3);
     Dashboards.fireChange("param_level4_m", mdxFragment4);
 
  } 
  
    Dashboards.setParameter("chartQorM", "LastPeriods(12, [Date.YQM].LastChild.LastChild.LastChild)");

  Dashboards.fireChange("onHoverPath", chosenPath);
  
} 


  ,
    colHeaders: [],
    colTypes: ["string","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","formattedText","hidden","hidden"],
    colFormats: [],
    colWidths: ["202px","74px","74px","74px","74px","74px","74px","74px","74px","74px","74px","74px","74px"],
    colSortable: [],
    colSearchable: [],
    paginate: false,
    paginateServerside: false,
    paginationType: "simple_numbers",
    filter: false,
    info: false,
    sort: false,
    sortBy: [],
    displayLength: 10,
    lengthChange: false,
    tableStyle: "a",
    drawCallback: function f(){
    var myData = this.rawData.resultset;
  // check if resultset is not empty
  // otherwise we will get an error and no empty table will be displayed
  
  var lastCol = $("#" + this.htmlObject + ' td:last').index();
  
  
  if(myData.length > 0){
    $("#" + this.htmlObject).find("tbody > tr").each(
      function(i, d){
       
       $(d).addClass("drill-down-level-" + myData[i][lastCol+2]);
    
       if(myData[i][lastCol+2] < 4) { 
       if(myData.hasOwnProperty(i+1) && myData[i][lastCol+2] < myData[i+1][lastCol+2]) {
           
            $(d).find("td:first").removeClass("collapsedSW");
           $(d).find("td:first").addClass("expandedSW");  
        } else {
            
             $(d).find("td:first").removeClass("expandedSW");
           $(d).find("td:first").addClass("collapsedSW");  
            
        }
       }
        
        $(d).find("td:first").addClass("drill-down-level-" + myData[i][lastCol+2] + "-node");
      }
    )
  }
} 
  }
};

allan

Hi,

If you are OK with it, we can do a webex / skype session to fix the access? That would be great.

Yes, we could do that. That would fall under the priority support options.

I don't know if you would also need to have the Pentaho code that does this translation

I suspect it would be likely - that draw looks "funny" to me - the fact that it is being called multiple times and each one is taking a fair amount of time. Indeed, I don't really see from the above how the data is being added, so it must be in a binding somewhere.

I don't know Pentaho at all, so I can't guarantee that I'd even be able to find it.

If you disable your draw callback, what happens - is there a significant performance improvement?

Allan