Skip to Content

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!

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Vijay Balagopala

    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?

    (0) 
        1. Karol Kalisz 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

          (0) 
          1. Vijay Balagopala

            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?

            (0) 
            1. Karol Kalisz 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.

              (0) 
              1. Vijay Balagopala

                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.

                (0) 
  2. 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! 🙂

    (0) 
  3. Franck Blais

    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

    (0) 
  4. 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?

    (0) 

Leave a Reply