Design Studio 1.6 SDK - Data Iterator - Read your data row by row finally!

[mike_howles4]

Change Log

• 02/26/2016 - All improvements now bundled for download.


• 02/26/2016 - Major optimizations on compression of data payload.  Data sent to BIAL layer is no longer enlarged with URIEncoded bloat, which eats into the 200k property limit.  I am now compressing using LZString compression on frontend (client) and decompressing on backend (BIAL engine).  Compression rate on average is right at 200%.


• 02/25/2016 - Pushed flattening of data to the ZTL layer.  This means larger data volumes can be used.  (See Restrictions for details)


• 02/09/2016 - New containing() method for row filtering, now you can create trellis-like/grouping-like logic, as well as cascading filters.  See Grouped Scorecard example at bottom.


• 02/08/2016 - Blog posted

Planned Enhancements

• Up for discussion!


• Iterate via columns?

Restrictions

• Requires 1 or more Measures to be present


• Data 'payload' cannot be higher than 200KB to server.  I've optimized this to use the compressed 'tuple' payload and perform the flattening there.  (Feb 25)  This means a previously flattened payload of flat data (226 rows) in one example of 49,075 bytes can now be relayed in 12,726 bytes.  The compression is not linear, meaning the higher the amount of rows and also the cardinality of your dimension members will play into how compressed it can be.  There is still the potential to hit the 200KB limit but these would be for quite large sets of data.  I cannot overcome this Design Studio limit, so use reasonable volumes of data.

Description

Use Case:

Design Studio, being a multi-dimensional client, likes to decompose data into 'tuples' which basically defines each measure as a cell value that is represented by indexed tuples that map back to a dimension member.  There's not really anything "wrong" with this, except that at least in my opinion, this is not the easiest way for me to work with the data in a 2D tabular form.  There have been many SCN threads where people have struggled with basically looping through (aka iterating over) the rows in order to apply some script logic.  Even in my other extensions, the first thing I do is "flatten" the dataset into a 2-dimensional (or table-like) form to work with.  I start thinking about why couldn't I just open this convenience up to BIAL scripting?  This is what I have written this component called 'Data Iterator'.

Welcome to the Data Iterator

The Data Iterator is a Technical Component available to add to your Design Studio application.  It is very simple to configure, in that you simply assign it a Data Source, and you are ready to use it in your script.

Step 1: Add a Data Iterator Component



Step 2: Assign it a Data Source



Optional Event to note is 'On Data Change' - This essentially fires an event any time the Data Source changes (via filtering, navigation, etc.)

That's it.  You're done configuring it.  But what does it do?  It's time to see what we can do with this at script-time!

Script-Time Methods

Before explaining the methods, I need to explain conceptually what it is doing with the data.  Let's start with a typical Data Source's result set:



As mentioned in the use case, the first thing I do is 'flatten' the data internally into an easier to use form.  This means I have some of my own conventions going forward:

• getDimensions - Returns a list of dimensions currently in Rows.  This is a simple call that you can do today with DS_1.getDimensions(Axis.ROWS); but there are some subtle differences in some cases.  See example below:


• getMeasures - Returns a list of items in columns.  Most of the time this is usually Key Figures/Measure selection members, however it will go ahead and flatten in cases where you add a dimension in your Columns as well.  This is where you would begin to struggle using standard BIAL calls to pull this off:


• getRows(optional offset, optional maxRows) - Returns rows optionally from a given row offset, and optionally a maximum amount of rows.  This is where things can get interesting!  Example:
  So, let's consider the rows here... we have a rows.forEach loop, which returns an element (named row in my example and an index that just comes with forEach, starting at 0...)  The row element itself has some BIAL methods we must explain:
  
  
  
  
  
  • getDimensionValueKey(dimensionKey) - Returns the member key for a given dimension key.
  
  
  • getDimensionValueText(dimensionKey) - Returns the member text for a given dimension key.Example:
    (In this case, key and text was the same.)
    
  
  
  • getMeasureValue(measureKey) - Returns value in float format for the current row's column for the passed measureKey.
  
  
  • getMeasureFormattedValue(measureKey) - Returns formatted value in String format for the current row's column for the passed measureKey.
    
    Example:
    A few things to note above.  On the 3rd line, you see that we can dynamically determine in this case, the measure "key" in the first column position by saying getMeasures().atIndex(index) where 'index' indicates the column you want (beginning at 0).  This is also available for getDimensions().atIndex(index).  This means that you don't HAVE to hard-code a key (like we are doing with 0D_MATERIAL) and have it adapt to whatever the contents are in the result set.  Also note that I switched the output from 'Formatted Text' component to my 'Rapid Prototyping' component.  This is because 'Formatted Text' was stripping out certain HTML markup and I wanted to show some conditional formatting quickly.
  
  
  
  


• getRows().containing(options) - Filters rows and returns subset based on selection criteria (See Grouped Scorecard example)
  This is a really cool method.  It allows you to select a subset of data and even chain them together as such:Any filters in each containing clause are treated as an OR, with subsequent containing clauses operating as an AND.
  
  
  

  var rows = DATAITERATOR_1.getRows().containing({  "dimensions": [
  
    { "key" : "0D_CO_CODE", "value" : "1000" },  { "key" : "0D_CO_CODE", "value" : "2000" }
  
    ]
  
    }).containing({
  
  "dimensions" : [{ key : "0CALMONTH", "value" : "04/2004"}]
  
  }) ;

  
  

Putting it all together:

After seeing these basic, fundamental methods, what else can you do?  You can go as wild as you want!  If you are accustomed to writing in languages such as JSP, BSP, ASP, etc, the use cases are endless when using Data Iterator along with Rapid Prototyping, as an example.  Below are two proof-of concept examples.  With some additional CSS-clean up and more time, you could come up with some super-easy to create visuals!

Simple Table showing only first 10 rows (Could enhance to paginate with buttons etc):

// Get flattened rows from Data Iterator

var rows = DATAITERATOR_1.getRows(0,10);

// Get dimensions (Rows) and Measures (Cols)

var dimensions = DATAITERATOR_1.getDimensions();

var measures = DATAITERATOR_1.getMeasures();

// Start a simple HTML table

var html = "<div style='height:400px;overflow:scroll'><table class='example'><tr>";

// Draw headers for dimensions and measures

dimensions.forEach(function(element, index) {

  html = html + "<th>" + element.text + "</th>";

});

measures.forEach(function(element, index) {

  html = html + "<th>" + element.text + "</th>";

});

html = html + "</tr>";

// Loop through the rows...

rows.forEach(function(row, index) {

  // Draw a new row

  html = html + "<tr>";

  // Write out the dimension texts

  dimensions.forEach(function(member, index) {

  var dimText = row.getDimensionValueText(member.key);

  html = html + "<td class='dimension'>" + dimText + "</td>";

  });

  // Row striping example

  var stripe = "even";

  if(index/2 == Math.floor(index/2)) {

  stripe = "odd";

  }

  // Write out the measure formatted values

  measures.forEach(function(measure, index) {

  var measureVal = row.getMeasureValue(measure.key);

  var measureText = row.getMeasureFormattedValue(measure.key);

  html = html + "<td class='measure " + stripe +" '>" + measureText + "</td>";

  });

  html = html + "</tr>";

});

html = html + "</table></div>";

RAPIDPROTOTYPE_1.setHTML(html);

Simple Table/Micro Chart:

// Get flattened rows from Data Iterator

var rows = DATAITERATOR_1.getRows(0,250);

// Get dimensions (Rows) and Measures (Cols)

var dimensions = DATAITERATOR_1.getDimensions();

var measures = DATAITERATOR_1.getMeasures();

var firstMeasureKey = measures.atIndex(0).key;

// Figure out Max in BIAL for rendering chart bars

var max = 0.0;

rows.forEach(function(row, index) {

  var v = row.getMeasureValue(firstMeasureKey);

  if(v>max){ max = v; }

});

// Start a simple HTML table

var html = "<div style='height:400px;overflow:scroll'><table class='chart'><tr>";

// Draw headers for dimensions and measures

dimensions.forEach(function(element, index) {

  html = html + "<th>" + element.text + "</th>";

});

// Draw first measure header

html = html + "<th>" + measures.atIndex(0).text+"</th>";

var w = 300;

html = html + "</tr>";

// Loop through the rows...

rows.forEach(function(row, index) {

  html = html + "<tr>";

  // Draw a new row

  dimensions.forEach(function(member, index) {

  var dimText = row.getDimensionValueText(member.key);

  html = html + "<td class='dimension'>" + dimText + "</td>";

  });

  var measureVal = row.getMeasureValue(firstMeasureKey);

  var measureFVal = row.getMeasureFormattedValue(firstMeasureKey);

  var barWidth = w * (measureVal / max);

  html = html + "<td style = 'width:" + (w+200) +";'>";

  html = html + "<div style = 'display:inline-block;width:" + barWidth + "px;background-color:#006699;'> </div>" + measureFVal;

  html = html + "</td></tr>";

});

html = html + "</table></div>";

RAPIDPROTOTYPE_2.setHTML(html);

Runtime Example of both:



Super Goofy Scorecard Example:

// Get flattened rows from Data Iterator

var rows = DATAITERATOR_1.getRows(0,10);

// Get dimensions (Rows) and Measures (Cols)

var dimensions = DATAITERATOR_1.getDimensions();

var measures = DATAITERATOR_1.getMeasures();

// Start a simple HTML table

var html = "<table class='example scorecard'><tr>";

// Draw headers for dimensions and measures

dimensions.forEach(function(element, index) {

  if(element.text != "Key Figures"){

  html = html + "<th>" + element.text + "</th>";

  }

});

measures.forEach(function(element, index) {

  html = html + "<th>" + element.text + "</th>";

});

html = html + "</tr>";

// Loop through the rows...

rows.forEach(function(row, index) {

  // Draw a new row

  html = html + "<tr>";

  var priorValue = DATAITERATOR_1.makeNull();

  // Write out the dimension texts

  dimensions.forEach(function(member, index) {

  var dimText = row.getDimensionValueText(member.key);

  if(member.text!="Key Figures"){

  html = html + "<td class='dimension'>" + dimText + "</td>";

  }

  });

  // Row striping example

  var stripe = "even";

  if(index/2 == Math.floor(index/2)) {

  stripe = "odd";

  }

  // Write out the measure formatted values

  measures.forEach(function(measure, index) {

  var trend = "";

  var measureVal = row.getMeasureValue(measure.key);

  var measureText = row.getMeasureFormattedValue(measure.key);

  if(index>0 && !DATAITERATOR_1.isNull(priorValue) && !DATAITERATOR_1.isNull(measureVal)){

  var delta = Math.round(measureVal / priorValue * 100) + "%";

  var icon = "";

  if(priorValue > measureVal){ // Down

  trend = "downward";

  }else{ // Up

  trend = "upward";

  }

  measureText = "<div class='icon'></div><br />(" + delta + ")</span><br />" + measureText;

  }

  if(!DATAITERATOR_1.isNull(measureVal)){

  priorValue = measureVal;

  }else{

  priorValue = DATAITERATOR_1.makeNull();

  measureText = " - ";

  }

  html = html + "<td class='measure " + stripe + " " + trend + "'>" + measureText + "</td>";

  });

  html = html + "</tr>";

});

html = html + "</table>";

RAPIDPROTOTYPE_3.setHTML(html);

Grouped Scorecard (webi-like sections and blocks)

var members = DS_2.getMembers("0D_CO_CODE", 100);

var html = "<div style='height:100%;overflow:scroll'>";

members.forEach(function(member, index) {

  var item = member.externalKey;

  // Get flattened rows from Data Iterator containing certain company code

  var rows = DATAITERATOR_1.getRows().containing({

  "dimensions": [

  { "key" : "0D_CO_CODE", "value" : item }

  ]

  });

  html = html + "<h2>" + member.text + "</h2>";

  // Get dimensions (Rows) and Measures (Cols)

  var dimensions = DATAITERATOR_1.getDimensions();

  var measures = DATAITERATOR_1.getMeasures();

  // Start a simple HTML table

  html = html + "<table class='example scorecard'><tr>";

  // Draw headers for dimensions and measures

  dimensions.forEach(function(element, index) {

  if(element.text != "Key Figures" && element.key !="0D_CO_CODE"){

  html = html + "<th>" + element.text + "</th>";

  }

  });

  measures.forEach(function(element, index) {

  html = html + "<th>" + element.text + "</th>";

  });

  html = html + "</tr>";

  // Loop through the rows...

  rows.forEach(function(row, index) {

  // Draw a new row

  html = html + "<tr>";

  var priorValue = DATAITERATOR_1.makeNull();

  // Write out the dimension texts

  dimensions.forEach(function(member, index) {

  var dimText = row.getDimensionValueText(member.key);

  if(member.text!="Key Figures" && member.key !="0D_CO_CODE"){

  html = html + "<td class='dimension'>" + dimText + "</td>";

  }

  });

  // Row striping example

  var stripe = "even";

  if(index/2 == Math.floor(index/2)) {

  stripe = "odd";

  }

  // Write out the measure formatted values

  measures.forEach(function(measure, index) {

  var trend = "";

  var measureVal = row.getMeasureValue(measure.key);

  var measureText = row.getMeasureFormattedValue(measure.key);

  if(index>0 && !DATAITERATOR_1.isNull(priorValue) && !DATAITERATOR_1.isNull(measureVal)){

  var delta = Math.round(measureVal / priorValue * 100) + "%";

  var icon = "";

  if(priorValue > measureVal){ // Down

  trend = "downward";

  }else{ // Up

  trend = "upward";

  }

  measureText = "<div class='icon'></div><br />(" + delta + ")</span><br />" + measureText;

  }

  if(!DATAITERATOR_1.isNull(measureVal)){

  priorValue = measureVal;

  }else{

  priorValue = DATAITERATOR_1.makeNull();

  measureText = " - ";

  }

  html = html + "<td class='measure " + stripe + " " + trend + "'>" + measureText + "</td>";

  });

  html = html + "</tr>";

  });

  html = html + "</table>";

});

html = html + "</div>";

RAPIDPROTOTYPE_4.setHTML(html);

Runtime Example:



Oh, and PS here's the CSS for my examples:

.example {

  border-collapse : collapse;

}

.example .dimension {

  background-color : #006699;

  color : #FFFFFF;

}

.example th {

  background-color : #006699;

  color : #FFFFFF;

  font-weight : bold;

}

.example.scorecard th {

  /*white-space: nowrap;*/

  padding : 20px;

  background-color : #0099CC;

  color : #FFFFFF;

  font-weight : bold;

  font-size : 20pt;

}

.example .measure {

  text-align : center;

}

.example .icon {

  display : inline-block;

  width : 48px;

  height : 48px;

}

.example .downward .icon{

  background-image : url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAABmJLR0QA/wD/AP+gvaeTAAAAB3RJTUUH2QcVCAsthDpI/AAABQ9JREFUaIHtV0tsG0UY/ma9XnudJq1Tp2ppRRMeF6jopVKFEIWq9NACogipQE9wQEgc4AYmAqkXWpUb0CvigopED1CqJgdSFBCoIFBJk5YWEhKnjR/JOonf+5wZDrEbs971ay0liHzSL8+u5//n++af+WcW2MAGNrCBDfyfQbw49x89zTtBQjDIlumRaLYdX9Hr4CdeeBIoy+AAOOcrjxxgnCOTLa6+A8D5Sp9K+/ufxnQv43sWAA6M/Z12/otzKIs5V9eH+rd6Hl7wGoDx9lcR9+BbgWcBXjh4EV/BGmfA6+gdEOAF62IJMdY+CQ+ud7G2e6ADCtZ0D3QCHciAlyW0LjLgwXc9LCEvWB9VyMs54HVwrPUe6MAScr3M9R89tR8gPwMACEqOnbi3Mlq+mQYgEaX/6dOmQ/wQAAggj0wPRSecYtT9Hhh45tRrASlw9sBjeyVBEEAZW70Ws5Vfvyji9kLehWD92+i2cAiU0ruTwCsZIYBlUsRisxQMJ2aGol+6xfDVE5D56/LV7vsOkXxe3b9je8SfzurIFk0UVAtFbcVyJaNuFkqq+3Vf1S1oBr1ruslAGYcoEMzdiReYyd6eGY5+Vo9jXQEAkJkc+SGw6/E9Jd144N6dEX9JM0EZL6e/8RKqJ4DbjAgEoaCIZDxZ0E3zXOxS9P1G/JraxHJIfSWRUMZmYwlja48MQjx9iToTIQRdQT8WkoquqfqVWHDgjab8mul04/xJg2j8yOTMXFxRFlm4O+CNrQ2EAHJARHYpYxaKxSldUI/h/HHajG/TZXR6JJoVKTlw42ZsuVgs8c1dUvuMbQhKIkrFAltaWlJESg8mLp50rnoOaOkcmBp+Z44wdnB84s8Csyx0Bb1/Ugf8PliGgfS8UoLADk0NDyqt+Ld8kE0PRScsgx67Nnar6PetEGgXkiiAMIpUMqEyxp+LXRy81WqMtkbPTl2e6bn/YGpxKXd4184+P+Xup6pbFfIJBKIgIBlPqJzS12PDgxfa4dL29C1PXv5908ATvYWCunfH9ohkUuZYUp0ECIQg4BegJJMFy6JnZobe/aRdHu3nH0BmcuTb0O7v9pkmHeiLhEXTYjV97AIIAQKSiMWFhaKh69/MXIq+5YWDx8sc4bKsvTQ/nx5PJhaMUEBEoyNCEn3IpNO6pqoTsqy96m381jNAyj4VE5U/Rokk9X1VIvKLXaFQd1dIFiy6monqDEiigFI+T3PZbLw0O/5UbPSsXo4jlK3lE7IZAZXgYtl8VW0RgL+4cJ0GN22/mDcDL/ds6ZYDkp/Q8qauCBB9Akxd40uLi8ta4vrh+fFzy1XkfbZ2RQxBg8+GegKqA4u2do2QQmpCDfbcM5ozfMfDvWG/KPrAGEdJ1eETCLhlQZlPqVrq5vOpq5/P2khXW4V4dRtuQtwE2INWi3GyFRHJa8ty5MHJvIbDka29fggEqmqAcIp0KqHpmdtvJn/59FfUzrKTOaFGhJOAivp65jRzAgBf/s5vsdC2PYWiRh/t7QuLqqohnUpqRkb5MP7jx1/XIedIsBHcMlBvZhoSyM1euSbv3BfWVOvhfC5n6MXsF3OjZz4CwMrGq9rM9r6R1RB1g9PadHu2r1sAsjBw5L0LDFyYHf7gWUCtlKYKeY5/C7GLchJZg2bKlhNZt81WnSESiuyWeGCzT42Pq1idPfuM2sU4iXJFq3XXjXANeRd/NxFOgpom1Ak0Q74Cu4gN/KfxDxmvmYQNdjkRAAAAAElFTkSuQmCC)

}

.example .upward .icon{

  background-image : url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAABmJLR0QA/wD/AP+gvaeTAAAG/klEQVRoge2YbYxU1RnHf8+5d3bZl8qLoryJKC2sChHEZdkXikBQKVIxqQnQNumX1gZDU+OKxk/W+KH9oI39aG3TmhoTP2FCSkJTG0SWLW2atMpGKtiFEXZZXnf2ZWZn7j1PP9x7d+7MzsosM7Rpus/m5Jw5M/c8//95XvfCtEzLtPxPi9ysg9s6nW2oHHDVW/zh6yRvlh5zMw5tfbZmpesk3n2gaa21jnN4zQvMvBl6AJxqH7h+L3OpoXv7hl2zH2raYNLeUMPlgb6NLQvtb3t60Grrq6oFtu6l1q91P9jw0DfmzL11vhw+9S7rVmyuuevOZavOLXHeqqauSKppAbljg/veyuXNLa33b6rp7n2fkewg1zIXWH/f9sTZ86eWzXlw1E922aNV1Fk9Au2d7isL71j63e3tu+r+mjzIaHYQVctI9hrp3Ahr792S+OzMJ63z1uZOJLv0ZLX0VoVA6/POrlmNt/1s92N76k9ePMaV0fNYLIrFqmUwPUCNW8sDS9tqPu39+JuLmuVQstuer4buimOgozPRUpuofXP31j1151MnGRg+C4AU/X16oZsMKbZ27KjD1YMt+1hUMXoqJNCyj0UYDu7c8sPGjB3izNUeBGFCeREQEf6WPER9Y4O0rd40x1XncMtebqlEP1TgQg/voZGE8+dt63fNmz93sZzoO4KvPqqB24Bi1UdRrFpULb569KdOsfqejWKtV38xNdC6boF9p5L0ekMWeOopnGyju79t1ZaFK+5eaz7pOxKCDkREokXBc4Iw5qU5cvo9Hryvo/bOeUvazi1x3rxR8HCDFpi9zXlj+V2rntjW+u0Zfz/3RzLeSNHN2/HP8TkaGW+UK6Pn+fqKJxNf9Pcuu3V1Zih5zB7/jxBo70w8ffvsBS9957Ef1/3z4nFSmUuFQJkIvJiADdPrWG6Y1nsfrTl1pufh+c3ex8ljU0+vUyLQ1uluapjxld98f8dL9ReGP2dg+MwEoIHflyZgYzGiKIOZi9QkZrDqax2Jz872bF/QbA580e0PTAVT2THQ+mztVx3H7P/e4881jHqD9KVOA4qi47MIiAFEERMfwb5I9Mt8zPb0HyVtr7Kl/fEGce2fpppeyyLQ8SKzjesd3vnInsaG+gb+dfkfqCqqGiRNA8YBcYLZcQQxgnEMxpHxPeOAGh8xtiC+/3L299Q11Ev76k0zHdxDa16mvlwC13Wh+1+mpm7M/eCRlm8tXbl0ndPT34WvORQfxKImmm24p6goiB0fViw5m8bTTGgdCawlAgKqSn/qc9bcs9n4vjfzyoWB9nLT63UJLGt2fr26qWPjo807a0/0HyHnp1HsOGjEglFULIiC+LE9nzE7QtpPYcmNF7RgUEDEtzn6Uqdpa9rmXL42MO+cO3h7sksPVkSgrdN94e55y5/ZveVHdScHjjOauxYCtTHAFhEFk99TsWTtCEPZi+Q0AyYEa8AYmWCBaJ3101wZ6WPDyicTyb7eleWk10n/pWzrdJ6Y1XjbO8/v/nmD6yTw7FiQXcKgNcagWA73/g6LF44cvua4mukj7Q0hkPcBJYwbUAtqlR1N+4LP4X7Bb3x4+8Abo+n0yI6jr3l/mAynOyk1lf2pkWv85Fc/KGRsyKhasrnsjFef+WUQkCEpz89wKZPEag7Hjd2NEgINhygautEv3n4VMWQm6gdV6oFDX3bRkxLoes0T8ICxAvwdLzIrfPQKKJgAfM4f5lK2FzWKiVyD0AIhcMLbtya44fA3vjjeglIYBPDHSpArh8Akoh/9lKsAbc+FGkTJ2hEuZ88iRgMfL25Io5u3oAbwA2JhXOSiM29EpkqgkA0WKzkuZXpBLMYJM4wp7OPi4KOeL0i1lWgPpCICiHIpcwY1XljM4hmGsPQqaOg2FiyKQbBU56VURQTSXgqPsfH0aIwgjmBMPkWigqqCzWckiyIqFLfb/wUCg/nCNJ7nCy0RuU/QZoNoZCGtBv7KCFhsrBARIxL0PWIEtYpFgpqnIFbzLlYFqe6rxZKgbtrrV2DqFjAE7Ue+BQliNF+krKJCcOtW83vROjZiUg9YwA+HpUwph0AE2I2tHeKgibUISBCkUZqJ2oOwfYjacM0XNwEaY+Cj4cXWN0TAhN9HwIvX5PwMLgnG7Fh464E5RCmqxAEBayMiAZmYGeqKQHuhnmjtMYlVJiMgFN62W2JQ59zC5sVPx9pkxoM6f0wAMt+sBRZBwffHCdSEIEu8VAobrfwoi0BZ8vpbr1TyeCS1lTz8ZSniei5UKi6c8DlD4W1Gt2cpDNZif4/PxeuSLlROjpssiIsBR2spGnECcSJRtilFqOwgnmqSjqfRYtDxudTZ+U4iT6KYzJTTaDWqTHRG/PYnK5AR6Ag4lAjMaZmWafk/kn8DlgvUqMpTMAIAAAAASUVORK5CYII=)

}

.example .downward {

  color : #FF0000;

}

.example .upward {

  color : #009966;

}

.example .measure.even {

  background-color : #FFFFFF;

}

.example .measure.odd {

  background-color : #DFDFDF;

}

What you have seen is available for download in the usual spot (details here: SCN Design Studio 1.6 SDK Components (ver 3.0)). 

 

 

Questions/Comments/Feedback always welcomed!

[Former Member]

Come on Mike ... You have to slow down or we won't have anything to develop for Ds ...

Again, a lot of use-cases for this one, even if, if I am not mistaken, you can (with a lot of scripting) reproduce the same behavior with getDimensions and getData.

Your solution is definitely better :razz:

Looking forward to my next dashboard to use it :smile: Nice work !

PS: Do you support hierarchies ? (Call me Mr Hierarchy please).

[mike_howles4]

you can (with a lot of scripting) reproduce the same behavior with getDimensions and getData

I think even then, getData will throw Warnings if you issue a getData command where you request a selection that there is no data value.  Imagine if Calendar Year were in columns and you had Division and Key Figures in rows and if a Division did not have a certain key figure for a certain year, it will generate an ugly warning.  I never liked that.

Also, yes a TON of scripting even at that :smile:

PS: Do you support hierarchies ? (Call me Mr Hierarchy please).

It supports hierarchy leafs/collapsed nodes, with an option of ignoring/including expanded nodes.  Not sure if it'll fit your use case or not, but let me know!

[MustafaBensan]

Also, even with the scripting approach, I'm not sure that you can reproduce the exact dimension sort order of the result set either, so I expect this would be another benefit of the Data Iterator.

[Former Member]

True ! Thanks for that Mike :smile:

[mike_howles4]

Now with a new 'containing()' method, you can do this!  (Details in blog)

[alfons_gonzalez]

Thanks. This component opens a endless number of options.

[Former Member]

Thank you Mike!

This so useful!!!

[former_member131154]

Hi Mike,

another WOW from my side...!

That really looks impressing.

Cheers

Dirk

[Former Member]

Hi Mike,

Thanks for sharing. We are planning to use this approach to achieve our tabular requirement with conditional formatting. we are able to meet the requirement (table with sections and conditional formatting) using above approach).

out next step is to evaluate if we can interact with (filter data in) any other component in the dashboard based on the selection of a value from this table. if it doesn't work, I think we will have to forgo the option.

Any input would be appreciated.

Thanks in advance,

Kotesh.

[Former Member]

Hi Mike,

can you apply this component on Design Studio 1.5

Regards

Ali

[mike_howles4]

I don't have any plans to port this back down to 1.5, but it's open source, feel free to do so with modifications where needed.

[alfons_gonzalez]

Hi Mike,

Just a collateral question regardin the above sample. I am trying to use the HTML prototype component but I don't see (it sounds me have seen it in the past) it in the list of components installed on 3.0 release.

Am I missing something or the component is not longer supported?

[alfons_gonzalez]

I'm sorry. Just another question: could be Data iterator be used on SDK Custom data sources as the brilliant BYOD component that you also developed?

Thanks again.

[mike_howles4]

Did you select 'Prototypes' when installed the addons?

[Karol-K]

Hi Mike,

we currently do not package prototypes in 3.0 as we have not cleaned it up. I am currently using the HTML prototype for me (starting from dev env). so, wither we clean up a bit in prototypes and release it (then only this one component will stay basically) or move this one as it is more usable than prototype into basics.

Karol

[mike_howles4]

Karol,

Each time I've built the package, I've included Prototypes plugin.

EDIT: Reason I've not moved the 'Rapid Prototyping' out of the 'Prototypes' feature, is the issue of namespaces.  Currently it's inside the prototyping plugin, which wouldn't that mean if we were to "graduate" it to 'basics', that it would break existing people's dashboard references?

[Former Member]

any suggestion or documentation in how to do that?

[alfons_gonzalez]

Hi Mike,

I am having some issues when using the data iterator components in the following scenario:

Data iterator (DS_ITERATOR) is binded to a data source (DS_1) that contains prompts variables and that is defined to be loaded on script (load in script = true).

When the datasorce is loaded and the the prompts answered in the BIAL code seems that data iterator is not updating its content with new loaded datasource.

My question is: does data iterator component support this scenario (binding to data source loaded on script with prompt variables answered on script). If yes, do we need to do something to make him aware of changes in datasource it is linked to (I haven't found any method)

Thx

[mike_howles4]

Hmm good question. This is either a bug on my side or some sort of SDK limitation. (Probably a simple bug though on my side.) I never use prompts so it never occurred to me to check. I will check.

[mike_howles4]

Yes it works on custom data sources.

[Former Member]

I had similar issue to update Data Iterator after initial load. on selection of a different value (Date) from drop down Data source getting refreshed but Data Iterator is not being updated Or atleast a click (date) lag for update of Data Itetrator.

[mike_howles4]

Let me see if I can reproduce on my end.

[alfons_gonzalez]

Hi Mike,

We have performed additional tests to clarify if the issue was caused by the use of prompts or the Data source property load in script.

We can confirm that prompts work fine with iterator (at least on our case). Problem seems to be clearly focused on the background processing and the "load in script" data source property.

When load in script property is set to true and load source is deferred to the onbackground processing event following error message appears

Message: org.mozilla.javascript.EcmaError: TypeError: Cannot read property "key" from undefined (getRows#20)

Note: we are currently calling getRows method on data Iterator "On Data Change" event.

Our question is: Would be possible to use the data iterator component on this scenario (data source background processing)?  we use it very frequently to optmize the app performance.

thx

[mike_howles4]

Hey Alfons - That helps narrow it down.  I'm quite frankly surprised how much interest this particular component got so quickly, so let me see if I can nail down this fix for you.

[mike_howles4]

Just an update -

A BW data source works fine for me using Load in Script = true during 'On Startup' but not during 'On Background Processing'.

I've not tested a UNX, nor can I reproduce the error you get.  Can you provide a snippet of the BIAL?

[alfons_gonzalez]

Hi Mike,

When using UNX data sources and Load is script = True the situation is the same you describe above with BW's one. Data iterator works fine when data source is loaded on startup phase but fails when it is loaded on "background processing"

the BIAL code is very simple. Scenario summary (to reproduce the error)

1) Data source (e.g: DS_1) with property Load in script = true

2) On Startup Event: APPLICATION.doBackgroundProcessing();

3) On Background processing: DS_1.loadDataSource();

4) Data Iterator (e.g: DI_1) binded to DS_1

5) Data Iterator on Data Change Event:   DI_1.getRows()

where steps 2-3 are the standard implementation of background processing data load (best practice described on this link)

We have noticed that with this approach the Data Change event (on data iterator) is triggered 2 times when DS_1.loadDataSource() is executed The first call is  the one that cause the error to appears, so we have found that a workaround is to process the code on Data Change event after the first call (this skips the error allows us to execute the getRows statement properly)

Therefore the question that appears to you is: why the Data Change event is called 2 times in this scenario when data source is loaded? It is the expected behavior at this situation or a bug on the component?

Note: Frankly, this interest  on the component does not surprise me. From my own experience on UNX data sources, this component fills the gap that prevented migrating definitely tons of Xcelsius into DS, so you are a hero!

Thanks,

[Former Member]

Hi Mike,

the data iterator sounds great. Would it be possible to apply a logic that only returns records that are selected by the user in a crosstab component? That would be a great option for planning application purposes where I often have the requirement to filer planning functions based on selected rows.

Best Regards,

Tobias

[mike_howles4]

You could either use standard scripting logic to filter a second data source/data selection to perform that filtering, or incorporate that filtering logic in the getRows().forEach loop.

[Former Member]

Workaround mentioned by Alfons worked perfectly in my case. Thanks a lot!!

Just one more question, Is there a straight way of capturing selected values from Rapidprototype after assigning html data (through Data Iterator) to it ? Just checking before going in a tough route.

Thanks again Mike!! This component is such a value add to DS.

[mike_howles4]

Just one more question, Is there a straight way of capturing selected values from Rapidprototype after assigning html data (through Data Iterator) to it ? Just checking before going in a tough route.

I might not be totally following the question here (sorry!) - But the Rapid Prototyping component basically lets you write out HTML/JS and has some find/replace placeholder properties and 10 single cell data selection options to replace with, but that stuff is entirely optional, and probably doesn't make sense to use in conjunction with the Data Iterator.  (However I've been surprised in the past with some creative applications of the Rapid Prototyping one, so who knows :smile: ?)

If you could give a little more detail I might be able to answer but I'm not sure.

BTW - I've also considered for maybe in future a "merged" Data Iterator + Rapid Prototyping (Data Writer has a nice ring to it) component that just allows you to do it all in one component instead of two.  All the same scripting APIs would be available, but in one place instead of two.  This would come after any kinks are worked out of this current, though.

[mike_howles4]

Update:

02/25/2016 - Pushed flattening of data to the ZTL layer.  This means larger data volumes can be used.  (See Restrictions for details)

[alfons_gonzalez]

Hi Mike,

I was preparing a question that you have maybe fixed with the latest version (it seems we think on the same :smile: ). I have noticed that for large volumes of data (e.g: 6000 rows with 2 dimensions a 1 column) data iteratore crashes with errors such this one

Message: Form too large: 2111213 > 200000

Stack trace: java.lang.IllegalStateException: Form too large: 2111213 > 200000

Reading restriction details I understand that you have improved the compression to allow putting more rows on this 200kb. There is some way to roughly estimate the number of rows that may be included on this limit?

In other words, can we assume that the limit of rows using the data iterator will be the same we have currently on DS data sources (20.000 rows / 200.000 cells) ?

Thanks

[mike_howles4]

Hey Alfons - I've not pushed the update into a feature bundle yet - Whoever does that next (me or Karol), it'll show up then, though.

To answer your question, yes the error you are getting is a hard limit imposed by Design Studio, I cannot increase it, only "shrink" the overhead.

By the way, there's a "safety belt" warning for now that will tell you that your data is too big instead of it trying to send it, so this will stop crashes.  I may actually turn this warning into its own BIAL event like 'On Data Overflow' or something - What do you think?

I cannot estimate how small/large the tuple payload will be on rows alone, and not even rows times columns, because it depends on the dimension cardinality also.  I could, if you like, expose a property called 'payload size' to give you a sense of how close you are getting, if that's of value to you...

[alfons_gonzalez]

Hi Mike,

I am impatient to have the bundle available to test the flattening of data on data iterator component!

I am confident that this "shrink" may help us a lot to workaround our current issues managing medium volumes of data (very common on data sets with lots of potential dimensions of analysis).

With regards the features you mention:

- On data overflow event: It could be useful to display some kind of widows box dialog asking user to reduce the number of rows to be selected (when data source result comes from a combination of selections, as it usuall on our case)

- payload size property:good to have, but only if the value does not longer appear on trace logs in order to have an estimation about how much value exceed from the limit.

Alfons

[alfons_gonzalez]

Hi Mike,

Just another annotion: in the restrictions details paragraph you state that data set can not overcome DS limit (200k). Following blog shows that the limit may be largely exceeded if any measures is included on the dataset.

On the other hand the list of components requirements specify that the data source binded to data iterator component must contain 1 or more measures.

My question is: would be possible to enhance the data iterator to support data sets with no measures? By removing this restriction it would be possible to bypass the aformentioned limit (200k) in some cases (e.g: cascade filters)

Regards,

[MustafaBensan]

Hi Alfons,

I suspect what Mike is referring to here by the 200K limit is related to SDK component custom properties rather than the data result set itself.  My understanding from Mike is that a single SDK component property cannot exceed a length of 200K (although the data result set itself may) and I guess the row iteration data is being returned via a custom property, hence the restriction.

Regards,

Mustafa.

[alfons_gonzalez]

Hi Mustafa,

I see.....Definitely we are on dead end. I will explain you our current situation.

We are developing a DS application where the iterator component is used in combination with the custom data source component (another Mike's brilliant contribution) to complete a merging of 2 UNX data sources

Biggest of 2 data sources involved on merge may frequently retrieve up to 20.000 rows (2 dimensions/1 fact by row = 20.000 cells). This amount of data may be comfortably managed on DS data sources (it is still far away of 50.000 rows limit) but from my rough estimations will be impossible that fits into the 200 KB limit established by the SDK component limit.

Do you think that could take sense promote an entry on SAP Idea site to allow SDK Component property to exceeds this value?

Thx

[Former Member]

Hi Alfons,

How many characteristics are you reading from your datasource ?

If not much, you could probably use getData with a loop on the members. It's not the best solution but it could help you. Thoughts ?

As proposed, It could be quite useful to be able to read the data without the measures. getData (with its good and bad) could be used afterwards to access those measures.

[MustafaBensan]

Hi Alfons,

There's certainly no harm in raising an idea to request this, although I think a property size limit exceeding 200K may be considered an exception case.  Just to clarify, as I understand it there is a difference between the limit for a data-bound result set property vs a custom property.  The data-bound result set property limit is configurable.  It used to be 10,000 CELLS but can now be set to virtually no limit, as I understand it.  However, separate to this, there is a 200K non-configurable limit on regular custom properties.  So, I believe the issue in this case is not that we are exceeding the data-bound property limit of the data source but since an array of rows is being returned, presumably via a regular custom property, this is where we hit the limit.

In any case, if your scenario is related to a custom D3 chart, I'm not sure how you plan to apply the Data Iterator to this.  Doesn't your custom chart connect directly to a data source, in which case you would perform any necessary data manipulation within the chart component itself?

Regards,

Mustafa.

[mike_howles4]

Mustafa is right - The limit I speak of that you are hitting, isn't a datasource volume limit, rather a property update limit.  Essentially, because BIAL/ZTL methods do not have direct access to data values of their own component at the script layer (a HUGE gap, in my opinion), I am "looping back" client-side and just passing the value back to the script layer as a string, and then re-assembling it there.  It's definitely a workaround for this gap, but there is no other way I have found.  And as we've all seen, getData techniques are lacking when you basically just want to loop over rows, etc...

Let's talk about where the significant "spend" of the 200KB "budget" comes in:

If you have 20k cells, that'll be 20k tuples.  Here's an example of the one tuple for two dimensions:

[0,44]

Depending on indece lengths, this one tuple will "cost me" around 6 characters of the 200KB "budget".  Here's a tuple example with 4 dimensions:

[0,29,5,1]

In this example, 6 (Edit 10, I cannot count today!) characters to express one cell.

The second significant part the "spends" characters is the metadata for each dimension member

{"key":"20020116","text":"1/16/2002"}

That's 37 bytes spent.  So here you can see depending on the cardinality (unique members) of your dimensions, this will add up.

And then finally, the last part that spends an amount, is the data values itself:

Unformatted:

10000

5 bytes

Formatted:

10,000 USD

10 bytes

So here is maybe a piece that could save you some excess.  I could drop also formatted values to save you some space...

[Former Member]

Or just give the possibility to leave them blank ? I see a lot of potential with the formatted values ...

[mike_howles4]

Well yeah that's what I mean, I can totally drop the whole formattedData array of the JSON string.  This accounted for 1,892 bytes of 12,726 bytes in my sample dataset.

There's also an unused (for me, when flattening) axis_rows and axis_columns section that shaves off another 2,847 bytes.

These things would I think optimize it the most.  I think at this point, if someone is still approaching 200K, it's time to write their own extension, or reconsider the data volume, unfortunately.

[mike_howles4]

Update, it's actually even a little worse, the payload is urlencoded which will add to the bloat.  I'll see what compression tricks I can do.

[mike_howles4]

Eureka.

LZWString compression works well.  I get 200% compression rate using compressToEncodedURIComponent method.

[mike_howles4]

Hi Alfons,

Please try the newest version, I've employed compression to get about 200% compression ratio, and you can optionally drop formatted values for even more space savings.  I've also increased the cell limit from 10k to 1 million (however you'd probably hit the property limit well before that, anyway).

Let me know if you see better results.

[alfons_gonzalez]

Hi Mike,

Thanks for your update. We have been checking your latest release. Effectively your latest changes (improving data compression) seems that allows to initialize the data iterator on scenarios with large volume of data (our tests with about 20.000 rows). Therefore the previous Message: Form too large:  error does not longer appear.

On the other hand a new issue seems to appear now on the iteration phase.When we began to iterate over the iterator (using the for each clause) following error appears after around 2650 iterations (I suspect that the number will depend of the amount of info contained on each row).

Do you think this issue may be fixed?

Note: We have also noticed that now the iterator events are called only one time when datasources load in script property is set to true.

-----------------------------------------------------

!ENTRY com.sap.ip.bi.zen 2 0 2016-02-29 15:35:59.037

!MESSAGE Error during script processing. Contact the Application Designer to resolve the issue

!ENTRY com.sap.ip.bi.base.application 4 0 2016-02-29 15:35:59.037

!MESSAGE Error during script processing: "onDataChange" "com.sap.ip.bi.zen.rt.framework.jsengine.JsEngineTimeoutException: com.sap.ip.bi.zen.rt.framework.jsengine.rhino.RhinoJsEngineError

  at com.sap.ip.bi.zen.rt.framework.jsengine.rhino.RhinoJsEngine.doRunScript(RhinoJsEngine.java:83)

  at com.sap.ip.bi.zen.rt.framework.jsengine.JsEngine.runScript(JsEngine.java:32)

  at com.sap.ip.bi.zen.rt.framework.jsengine.rhino.RhinoScriptInterpreterBialService.interprete(RhinoScriptInterpreterBialService.java:188)

  at com.sap.ip.bi.base.command.impl.Command.interprete(Command.java:191)

  at com.sap.ip.bi.webapplications.runtime.impl.page.Page.processCommandSequence(Page.java:4839)

  at com.sap.ip.bi.webapplications.runtime.impl.page.Page.doProcessRequest(Page.java:2580)

  at com.sap.ip.bi.webapplications.runtime.impl.page.Page._processRequest(Page.java:851)

  at com.sap.ip.bi.webapplications.runtime.impl.page.Page.processRequest(Page.java:5255)

  at com.sap.ip.bi.webapplications.runtime.impl.page.Page.processRequest(Page.java:5248)

  at com.sap.ip.bi.webapplications.runtime.impl.controller.Controller.doProcessRequest(Controller.java:1226)

  at com.sap.ip.bi.webapplications.runtime.impl.controller.Controller._processRequest(Controller.java:1082)

  at com.sap.ip.bi.webapplications.runtime.impl.controller.Controller.processRequest(Controller.java:1048)

  at com.sap.ip.bi.webapplications.runtime.impl.controller.Controller.processRequest(Controller.java:1)

  at com.sap.ip.bi.server.runtime.sevice.impl.BIRuntimeServerService._handleRequest(BIRuntimeServerService.java:822)

  at com.sap.ip.bi.server.runtime.sevice.impl.BIRuntimeServerService.handleRequest(BIRuntimeServerService.java:1205)

  at com.sap.ip.bi.server.execution.engine.runtime.LocalBIExecutionService.executeRequest(LocalBIExecutionService.java:34)

  at com.sap.ip.bi.zen.rt.client.handler.designer.LocalExecutionAdapter.executeRequest(LocalExecutionAdapter.java:39)

  at com.sap.ip.bi.zen.rt.client.handler.RuntimeRequestHandler.handleRequest(RuntimeRequestHandler.java:28)

[mike_howles4]

Re this error:

JsEngineTimeoutException

I should at this time have already known that you would find the next (Design Studio imposed) limitation :wink: -- This is another one I won't be able to help you overcome.  From what I can tell, any given snippet of script has some predetermined amount of time to execute before some sort of timeout governor kicks in and kills it, presumably so that no single piece of BIAL/ZTL can take down an entire server process/thread.  I think again in this regard you are finding the boundaries of what I can do for you, however I encourage you to keep finding them :wink:

[Former Member]

how to do you use getData & getdimension to produce similar work.?

[Former Member]

Hi Ali,

You would have to use nested forEach with a getMembers on each dimension in your datasource, and then use enough getDatas for each measure.

Something like that:

var dim1_members = DS_1.getMembers(DIM1).

var dim2_members = DS_1.getMembers(DIM2).

dim1_members.forEach(dim1_mem, dim1_index)  {

    dim2_members.forEach(dim2_mem, dim2_index)  {

       var measure_value1 = DS_1.getData("Measure1", ["DIM1";dim1_mem.internalKey, "DIM2";dim2_mem.internalKey ].

}

Note that as pointed out by Mike, you'll have to hide the warning on the application (if the measure value is not available, a warning will be thrown).

If you have 4 dimensions, you'll need 4 nested. If you have 5 key figures, you'll need 5 getData statements.

[Former Member]

Nice Mike :smile:

Too bad I don't have any use-case right now ... but again, looking forward to test and use it !