Design Studio 1.6 – Data Blending with Data Iterator & Bring Your Own Data Source

Finally! After Mike has created ans posted the ultimative DataIterator (Design Studio 1.6 SDK – Data Iterator – Read your data row by row finally!) I could find a good way how to use the Bring Your Own Datasource (Design Studio SDK (1.4) – Bring Your Own (BYO) Datasource) and create a scenario which were not available until now.

* Data Blending / Joining / Mixing – creation of one (new) data source based on 2 data sources (independent of the source system)

Short History

For the data blending / mixing / joining I wrote a blog some time ago Mixing Data from 2 Result Sets together (join, select) with a prototype component Design Studio SDK: Result Set Mixer Component. This was prototyping – which works, but has a lot of restrictions.

The Scenario

Very often I hear from customers, also I have on my own requirement list, following requirement – how to bind some data which are available in 2 separate data sources (queries) and IT will not put them together – like project spends (controlling query) and project status (program management query). All are searching for putting such data together.

Another example I will use is following: one query has sales data (what, where, quantity, price) and second query has buying data (what, price). I would like to have a query with revenue on what and where.

In Pictures, I want to have:

Query 1:

Query 2:

Result:

How is the revenue calculated?

I use standard scripting for that:

  var soldValue = quantity * price;
  var buyValue = quantity * buingPrice;
 
  var revenue = soldValue - buyValue;

This script executes per row of the first data source. And for this the Data Iterator is coming into work!

Technical Setup

From technical side, I need to have 3 data sources – the first 2 are bringing the data, the 3rd one is waiting to be filled in. In the attached example I use BYO data source to have it easier explained and offline.

Both data sources which are bringing the data are connected to data iterator – it means, as soon the iterators are filled in (which is currently going through browser) the work can start.

Script in both Data Iterators (Event: onDataChanged)

if(MIX_READY) {
  GLOBAL_SCRIPTS.merge();
}
MIX_READY = true;

I use the global variable as semaphore to assure both are initialized

the “merge” function makes the magic:

var sellRows = DATAITERATOR_SALES.getRows();
DS_MIX.clear();
sellRows.forEach(function(row, index) {
  var product = row.getDimensionValueKey("Product");
  var store = row.getDimensionValueKey("Store");
 
  var quantity = row.getMeasureValue("Quantity");
  var price = row.getMeasureValue("Price");
  APPLICATION.log("Line: " + product + ", " + store + ": " + quantity + " x " +price);
  var buingPrice = 0.0;
 
  var buyRows = DATAITERATOR_BUY.getRows().containing({ 
    "dimensions": [ 
        { "key" : "Product", "value" : product } 
      ] 
    });
 
  // assuming only one!  
  buyRows.forEach(function(buyRow, index) {
  buingPrice = buyRow.getMeasureValue("Buying Price");
  });
  var soldValue = quantity * price;
  var buyValue = quantity * buingPrice;
 
  var revenue = soldValue - buyValue;
 
  DS_MIX.addRow(product+","+store, ""+revenue);
});

Logically:

1. get the “sales” rows

2. clear the target data source

3. loop and read out required information

4. pick up the buing price

5. calculate

6. add rows to the target data source

THAT’S IT!

Now, as this is “real data source”, you can bind it to other components and you do not need to care about any update scripts for it. E.g. I have bound it to

* UI5 Table

* Nice Chart

* and … the standard component Scorecard!

Outline:

The App:

I am sure some of you will be happy with this procedure. Of course, it is not for mass data, but works very well with reasonable number of rows (also thanks to  Mike who has improved the size of data stream in the data iterator component.

The example application can be downloaded from the repository.

applications/SCN_OWN_DATASOURCE_BUILDER-20160310173344.zip at master · org-scn-design-studio-community/applications · Gi…

Have Fun!