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}