Improving performance of the excel document upload functionality

upload
excel
canvas
performance
tm1ui

#1

Quick note in regards to the performance issue in excel upload to Canvas.
Usually excel upload needs validation for some column elements.
If the validation is done through nested api dimensionElement > attributeGet > cellGet > cellGet calls inside validate function retrieving cell values for every row element can cause a performance issue (see validation function code below with performance issue)

$scope.validate = function() {`
      $tm1Ui.dimensionElement("dev", "Department", record.Department).then(function(value){
                    if (value == undefined){
                  
                    }else{
                    
                    $tm1Ui.attributeGet("dev", "Current Department", record.Department,"Department Status").then(function(value){
                        if (value.Value != "Active"){
                          
                        }
                        
                    });
                    $tm1Ui.cellGet("dev", "Setting", "Current Planning Year - Forecast", "String").then(function(PlanningYear){  
                        $tm1Ui.cellGet("dev", "Forecast", "All Projects", $scope.selections.scenario, PlanningYear.Value, record.Department,"1","Flag").then(function(value){
                            if(value.Value != "Y"){
                      
                            }                     
                        });                      
                     });

                }
            });  
            
            $tm1Ui.dimensionElement("dev", "Account", record.AccountCode).then(function(value){
                if (value == undefined){
                 
                }else{
                    
                    $tm1Ui.attributeGet("dev", "Account", record.AccountCode,"Status").then(function(value){
                        if (value.Value != "Active"){
                           
                        }
                        
                    });
                }
            });  

     
            $tm1Ui.dimensionElement("dev", "Project", record.ProjectID).then(function(value){
                if (value == undefined){
               
                }else{
                    $tm1Ui.attributeGet("dev", "Project", record.ProjectID,"Project Status").then(function(value){
                        if (value.Value != "Active"){
               
                        }
                        
                    });
                    
                }
            });  
}

This caused a lag in performance since the calls are staggered and are not fired all at once for all rows but one at a time and extra time to wait for the next call after the first as rows are validating.
To fixed the issue with validating the record.Department and record.Account via the tm1-element-list + for(){} loop through the array of elements to find if the current row element exists in the element list
And for the cellGet removed the above code from the controller and have included
DBR (ng-model) and a DBRA (ng-model) and ng-if statement with ng-init=“item.selected=false” if the expression from either of the dbr or dbra is not true.
html file code

  <tm1-ui-element-list 
  tm1-instance="dev" 
  tm1-dimension="Account" 
  tm1-subset="tm1AccountList"
  ng-model="lists.tm1AccoutList">
  </tm1-ui-element-list>

controller validation= funcion(){}

    for(var nn= 0 ; nn < $scope.lists.tm1DepartmentList.length; nn++){
                var obj = $scope.lists.tm1DepartmentList[nn];
                   
                if((record.Department+"") === (obj.key+"")){
              
                    $scope.departmentValidated = true;
 
           
                    
                } 
                
            }
          
                for(var vv= 0 ; vv < $scope.lists.tm1AccoutList.length; vv++){
                    var obj = $scope.lists.tm1AccoutList[vv];
                  
                    if(record.AccountCode === obj.key){ 
                        $scope.accountValidated = true; 
                    } 
                }
	if($scope.accountValidated === true && $scope.departmentValidated === true){ 
		 record.validated = true; 
	 } else{ 
		 if($scope.accountValidated != true && $scope.departmentValidated === true){ 
			 record.selected = false; 
			 record.errorMsg = record.errorMsg + record.Account + " is invalid.”; 

 		}else{ 
			 if($scope.accountValidated === true && $scope.departmentValidated != true){ 
				 record.selected = false; 
				 record.errorMsg = record.errorMsg + record.departmentValidated + " is invalid.”; 
			 }else{ 
				 record.selected = false; 
				 record.errorMsg = record.errorMsg + record.Department +' and '+ record.Account + " is invalid.”; 
			 }
		 } 
	 }

Final step of validation on the html side inside the final column where validation icons and messages are displayed in side the <td

    <span  class="label label-default" style="font-size: 0.95em; margin-right: 5px;"  ng-if="!item.saved && !item.validated && item.errorMsg ===''">Pending... </span>
    <span  class="label label-danger" style="font-size: 0.95em; margin-right: 5px;">...</span>
    <span class="label label-success" style="font-size: 0.95em; margin-right: 5px;" ng-if="item.saved"> saved!</span>
                             
        <span ng-show="false">
                                   <tm1-ui-dbra 
                                      tm1-instance="dev" 
                                      tm1-dimension="Project" 
                                      tm1-element="{{item.ProjectID}}" 
                                      tm1-attribute="Status" 
                                      tm1-read-only="true" 
                                      ng-model="item.ProjectStatus">
                                   </tm1-ui-dbra>
                                    <tm1-ui-dbr 
                                      tm1-instance="dev" 
                                      tm1-cube="Forecast" 
                                      tm1-elements="All Projects,{{selections.scenario}},{{$root.financialYear}},{{item.Department}},L1,Flag" 
                                      tm1-read-only="true"
                                      ng-model="item.DepartmentValid"
                                    ></tm1-ui-dbr>
         </span>
        <span ng-if="item.DepartmentValid && item.ProjectStatus && (item.ProjectStatus+'') === 'Active' ">
        	<span class="label label-success" style="font-size: 0.95em; margin-right: 5px;" ng-if="!item.saved && item.errorMsg ===''" ng-init="item.selected = true"> 
        		<i class="fa fa-check" aria-hidden="true"> </i> VALID 
        	</span>
        </span>
        <span ng-if="item.DepartmentValid && item.ProjectStatus &&  item.errorMsg ==='' && (item.ProjectStatus+'') === 'Inactive'">
        		<span class="label label-danger" style="font-size: 0.95em; margin-right: 5px;" ng-init="item.selected = false; ">{{item.ProjectID}} -  NOT VALID!</span>
         </span>
        <span ng-if="item.DepartmentValid && item.ProjectStatus &&  item.errorMsg ==='' && (item.DepartmentValid+'') != 'Y'">
        		<span class="label label-danger" style="font-size: 0.95em; margin-right: 5px;" ng-init="item.selected = false; ">{{item.Department}} - NOT VALID!</span>
        </span>
    <i class="fa fa-spinner fa-spin fa-fw" ng-if="item.saving"></i> 

</ td>
Performance has improved from 7.3min to a mind blowing 4 sec


Performance Issue on excel upload to Canvas
#2

Look at the http://localhost:8080/Budget/#/forecast-upload

Forecast Upload.html and Forecast Upload.js files have the optimised code.
Thanks