Design Studio 1.6 SDK - Data Iterator - Read your ...

mike_howles4 · ‎2016 Feb 08

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!