Skip to Content
Author's profile photo Karol Kalisz

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:

w1.PNG

Query 2:

w2.PNG

Result:

w3.PNG

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:

w5.PNG

The App:

w6.PNG

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!

Assigned tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Karol,

                Brilliant concept and design. Thank you for sharing this with us. I have a question though. The SCN Components that you have used; are there any pre-requisites that must be completed before I install them on the platform? Because I'm able to use them on my laptop but when I say "Install on Platform" it throws an error. Can you help me understand what I'm missing/doing wrong?

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Do you see some detailed error message in the error log? Could be permission issue.

      Author's profile photo Former Member
      Former Member

      Please find the screenshot of the error message I get when trying to install the extensions.

      Extensions Error.PNG

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Hi VIjay,

      check the actual version, we have fixed this issue - it is a wrong flag in the feature.xml definition:

      <plugin

            id="org.scn.community.utils"

            download-size="0"

            install-size="0"

            version="0.0.0"

            unpack="false"/>

      )

      but in current version it is fixed already.

      Karol

      Author's profile photo Former Member
      Former Member

      Hi Karol,

               I downloaded these SCN components a couple of days back. Unless something has changed over the past 3-4 days, I believe I have downloaded the latest version. The feature.xml file has the unpack setting as "false". Can you tell me where would I need to change the setting if I'm looking at the wrong place?

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      but in this case, for me looks like your client has not upgraded correctly. this issue is only coming when this flag is wrong. I would suggest, you uninstall and install the newly downloaded version. and then try again.

      Author's profile photo Former Member
      Former Member

      Karol,

            Never mind I figured it out. Out of 5 feature XMLs I had only one XML file with the unpack property correctly assigned. I corrected the remaining and updated the file. Thanks for the guidance.

      Author's profile photo Former Member
      Former Member

      Thank you for the sample script!! Design Studio becomes much more powerful now 🙂

      Author's profile photo Mike Howles
      Mike Howles

      Karol,

      Great explanation of what is possible!  I had some similar plans also of blogging on this topic, but you've done a great job of explaining this possibility and showing each step along the way.

      Using the Data Iterator and BYOData component, you basically get back your 'calculation/transformation/merge' layer you had in Xcelsius at the spreadsheet level, or even can accomplish equivalent of 'Merge Dimensions' if you think about it in Webi terms.

      I am glad it is getting some use! 🙂

      Author's profile photo Former Member
      Former Member

      Hello Karol,

      Actually I missed your post. I was planning to write something as well !

      On my side, I created a TOP N + Others (Which is so annoying to do in BW) with both of the components: Works like a charm ...

      If anyone interested:

      var rows = DI.getRows();

      //get top value from interface

      var top  = Convert.stringToInt(IF_TOP.getValue());

      var content = "Business partner, Net amount";

      var nl = "~|~";

      content = content;

      var row = "";

      var mes_other = 0.0;

      rows.forEach(function(element, index) {

        var dim = element.getDimensionValueText("0D_NW_BP");

        var mes = element.getMeasureValue("A83Y2FPNS2H75XV62JXWUFI6Q");

       

        if (index < top){

        //Still in the top

        content = content + nl + dim + "," + mes;

        } else {

        //others

        mes_other = mes_other + mes;

        }

      });

      //Add last other line to the data

      content = content + nl + IF_OTHER_TXT.getValue() + "," + mes_other;

      //set data

      DS_BYOD.setDataString(content);

      And the result:

      Screen Shot 2016-06-29 at 11.32.52 AM.png

      Author's profile photo Mike Howles
      Mike Howles

      Still makes me so happy people use this one 🙂

      Author's profile photo Ralph Knoops
      Ralph Knoops

      Hi Karol,

      Great stuff! For technical reasons I don't have the possibilty to do the calculation on the backend so I'm using this setup (BYOD & Data iterator) for a trend graph.

      In the On Data Change script i've got an simple getrows and for each script so i can push it to BYOD in the correct format.
      Only issue i've got is performance. It looks like this part is executed at the end. The whole Design Studio application is loaded and after 1 - 1.5 second the trend graph is loaded.

      Already tested with "waittime for loopback" but didn't notice any change.

      Total rows: around 120
      Total columns: 3

      Any ideas?