Processing cell sets from TM1


#1

You would normally get a cell set when using cubeExecuteMdx() or MDX call to Canvas API. The cell set is a bit hard to read and consume by other libraries like d3, so it’s better to process it before. Below is the function which can help you. You can simply copy and paste it into your controller or service and it will work.

The function is tm1flat(tm1o,attrs,bFilter).
–tm1o is the object which you get in response to MDX query
–attrs (optional). Object. Which specifies what attributes you want to use instead of element principal name. Property name is the dimension name and value is the Attribute name.
For example, {‘Year’:‘Financial Year’,‘Employee’:‘Full Name’,‘Version’:‘Description’}
–bFilter (optional). Boolean. If it’s true then each object will include dimensions from filters. By default, filters are not included.

The final result will look like this
[
{“Employee”:“Pamela Ansman-Wolfe O”,
“Period”:“Year”,
“Sales Quota Measure”:“Amount”,
“Scenario”:“Actual”,
“TM1CubeValue”:1199000,
“Year”:“2006”},
{“Employee”:“Stephen Jiang Y”,
“Period”:“Year”,
“Sales Quota Measure”:“Amount”,
“Scenario”:“Actual”,
“TM1CubeValue”:389000,
“Year”:“2007”},

]

Full Version:

function tm1flat(tm1o,attrs,bFilter){
       //function to convert array into object
       function toObject(val,label) {
          var obj = {};
          for (var i = 0; i < label.length; i++)
            if (val[i] !== undefined && label[i] !== undefined ) obj[label[i]] = val[i];
          return obj;
       }
       //========================================================
       
       //collect all dimensions from rows and columns
       //1) dimensions on columns
       var dims=[];   
       for (var i=0;i<tm1o.Axes[1].Hierarchies.length;i++){
           dims.push(tm1o.Axes[1].Hierarchies[i].Name);
       }
       //2)dimensions on rows 
       for (var i=0;i<tm1o.Axes[0].Hierarchies.length;i++){
           dims.push(tm1o.Axes[0].Hierarchies[i].Name);
       }
       
       //3)OPTIONAL.filters
       if(bFilter != null && bFilter==true){
           for (var i=0;i<tm1o.Axes[2].Hierarchies.length;i++){
               dims.push(tm1o.Axes[2].Hierarchies[i].Name);
           }
       }
       
       //4)add value to the list of dimensions
       dims.push('TM1CubeValue');

       //create auxilary arrays. rc is the main one. it contains all reference points + value as the last element.tm1fo is the array to be returned.
       var r1=[],
           c1=[],
           rc=[];
       var cellCount=0;
       var tm1fo=[];
       
       //loop through rows
       for(var j=0;j<tm1o.Axes[1].Tuples.length;j++){
           for(var j1=0;j1<tm1o.Axes[1].Tuples[j].Members.length;j1++){
             if(attrs != null && tm1o.Axes[1].Tuples[j].Members[j1].Attributes[attrs[tm1o.Axes[1].Hierarchies[j1].Name]] != null){
                 r1.push(tm1o.Axes[1].Tuples[j].Members[j1].Attributes[attrs[tm1o.Axes[1].Hierarchies[j1].Name]]);
             }
             else{
                 r1.push(tm1o.Axes[1].Tuples[j].Members[j1].Name); 
             }
           }
           //loop through columns
           for(var k=0;k<tm1o.Axes[0].Tuples.length;k++){
               for(var k1=0;k1<tm1o.Axes[0].Tuples[k].Members.length;k1++){
                   if(attrs != null && tm1o.Axes[0].Tuples[k].Members[k1].Attributes[attrs[tm1o.Axes[0].Hierarchies[k1].Name]] != null){
                       c1.push(tm1o.Axes[0].Tuples[k].Members[k1].Attributes[attrs[tm1o.Axes[0].Hierarchies[k1].Name]]); 
                   }
                   else{
                       c1.push(tm1o.Axes[0].Tuples[k].Members[k1].Name);
                   } 
               }
             rc=r1.concat(c1);
               
             //include filter values
             if(bFilter != null && bFilter==true){
                 for(var m=0;m<tm1o.Axes[2].Tuples[0].Members.length;m++){
                     if(attrs != null && tm1o.Axes[2].Tuples[0].Members[m].Attributes[attrs[tm1o.Axes[2].Hierarchies[m].Name]] != null){
                         rc.push(tm1o.Axes[2].Tuples[0].Members[m].Attributes[attrs[tm1o.Axes[2].Hierarchies[m].Name]]);
                     }
                     else{
                         rc.push(tm1o.Axes[2].Tuples[0].Members[m].Name);
                     }
                 }
             }
             //add value from Cells
             rc.push(tm1o.Cells[cellCount].Value);
             //console.log(rc);
             
             tm1fo.push(toObject(rc,dims));
             cellCount+=1;
             
             //zero out auxilary arrays
             c1=[];
             rc=[];  
           }
           r1=[];
       }
       
       //console.log(tm1fo);
       return tm1fo;
}; 

Minified version:

function tm1flat(a,b,c){function d(a,b){for(var c={},d=0;d<b.length;d++)void 0!==a[d]&&void 0!==b[d]&&(c[b[d]]=a[d]);return c}for(var e=[],f=0;f<a.Axes[1].Hierarchies.length;f++)e.push(a.Axes[1].Hierarchies[f].Name);for(var f=0;f<a.Axes[0].Hierarchies.length;f++)e.push(a.Axes[0].Hierarchies[f].Name);if(null!=c&&1==c)for(var f=0;f<a.Axes[2].Hierarchies.length;f++)e.push(a.Axes[2].Hierarchies[f].Name);e.push("TM1CubeValue");for(var g=[],h=[],i=[],j=0,k=[],l=0;l<a.Axes[1].Tuples.length;l++){for(var m=0;m<a.Axes[1].Tuples[l].Members.length;m++)null!=b&&null!=a.Axes[1].Tuples[l].Members[m].Attributes[b[a.Axes[1].Hierarchies[m].Name]]?g.push(a.Axes[1].Tuples[l].Members[m].Attributes[b[a.Axes[1].Hierarchies[m].Name]]):g.push(a.Axes[1].Tuples[l].Members[m].Name);for(var n=0;n<a.Axes[0].Tuples.length;n++){for(var o=0;o<a.Axes[0].Tuples[n].Members.length;o++)null!=b&&null!=a.Axes[0].Tuples[n].Members[o].Attributes[b[a.Axes[0].Hierarchies[o].Name]]?h.push(a.Axes[0].Tuples[n].Members[o].Attributes[b[a.Axes[0].Hierarchies[o].Name]]):h.push(a.Axes[0].Tuples[n].Members[o].Name);if(i=g.concat(h),null!=c&&1==c)for(var p=0;p<a.Axes[2].Tuples[0].Members.length;p++)null!=b&&null!=a.Axes[2].Tuples[0].Members[p].Attributes[b[a.Axes[2].Hierarchies[p].Name]]?i.push(a.Axes[2].Tuples[0].Members[p].Attributes[b[a.Axes[2].Hierarchies[p].Name]]):i.push(a.Axes[2].Tuples[0].Members[p].Name);i.push(a.Cells[j].Value),k.push(d(i,e)),j+=1,h=[],i=[]}g=[]}return k}