Data Mashups/Dummy Data:

  • In Lumira we have Data Prep Room.
  • In Excel and Xcelsius we have Worksheet Ranges/Formulas
  • In Webi we have Merge Dimensions

In Design Studio we have support for multiple data source support but no way to mash/merge the data up or create mock-up data like we can do with Excel/Xcelsius/Lumira Excel spreadsheet imports.  I decided to enhance my datasource test component to do the following:

  • Offer a spreadsheet-like interface to mock up data.
  • Offer BIAL Methods to programatically add data in Script.

So here is what we now have:

/wp-content/uploads/2015/01/byod1_630135.png

Let’s look at the Property Sheets first:

/wp-content/uploads/2015/01/byod2_630136.png

The properties in the default property sheet are:

  • Number of Dimensions – This indicates how many columns beginning on the left should be designated as Dimensions.  The remaining columns will be treated as Measures.
  • Measures in Rows (true/false) – This indicates whether Measures should be places on Rows or Columns Axis.  The dimensions will occupy the opposite.
  • Sort Method – Options are Alphanumeric Ascending or Descending, and None.  Alphanumeric will sort by 1st Dimension, then 2nd, and so-on.  None will just take order of occurrence.

If we then look at the Additional Properties Panels, we see a nice Excel-like view of our default data.  I have included some helpful row and column menus illustrated below.  As you see we can insert Dimension/Measure columns and rename headings.  We can also insert/delete/update Rows.

/wp-content/uploads/2015/01/byod3_630137.png

I also threw in some Preset data when we do not care what the data is during mockup of a dashboard:


/wp-content/uploads/2015/01/byod4_630138.png

And also if you want a CSV view you can see you have that option in the ‘Options’ Menu.  This is a more copy and paste-friendly option and then you can switch back at any time to the Table View:

/wp-content/uploads/2015/01/byod5_630139.png

So as we can see we have most of what we need to make dummy data.  Mission accomplished here.  What about mashing up data from multiple data sources?  We’ll need to add some BIAL Methods:

  • setHeaders(string) – Set your data source column headers by a comma-separated string (e.g. DS_1.setHeaders(“Department”,”Product”,”Sales”);
  • setDimensionCount(integer) – Set how many columns should be considered dimensions.
  • getDimensionCount() – Returns number of columns that are considered dimensions
  • clear() – Delete all rows
  • addRow(dimensions string, measures string, overwrite – true/false) – Add or overwrite a row with comma-separated dimensions then measures, and an optional overwrite flag (true/false).  If you set to true, any existing row with the supplied dimension values is overwritten, otherwise it accumulates.
  • deleteRow(dimensions string) – Deletes row(s) with specified comma-separated dimension string.

Let’s see an example in a BIAL snippet.  I have 2 BW datasources and one BYOData datasource shown below.

Basically in each datasource I have 0CALMONTH.  I then want to iterate over the 0CALMONTH values and grab a Measure from each datasource.  There may be a more efficient way to do this, but for sake of simplicity (for me), this is what I came up with.

We also see that I am performing a calculation to generate a 3rd calculated measure (profit) on the fly.  So even if you do not have 2 datasources, you could perform in-flight calculations and use the BYOData component as a data augmenter.

/wp-content/uploads/2015/01/byod6_630144.png

Let us see the end result:

/wp-content/uploads/2015/01/byod7_630145.png

And there we see the standard Design Studio charts component using our BYOData component at runtime with mashed up data from two different sources!

And with this BIAL scripting we can also do runtime perhaps ‘What-if’ scenario mocking up.  Runtime before/after:

/wp-content/uploads/2015/01/byod8_630146.png

And here is the simple BIAL for the ‘Add’ Button:

/wp-content/uploads/2015/01/byod9_630147.png

I hope that this BYOData component can be found useful for folks.  It will be updated into the SCN Community Repository shortly for anyone to use.  Details here: SCN Design Studio SDK Development Community

Questions/Feedback/Comments always welcome!

To report this post you need to login first.

16 Comments

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

  1. Donnie Burhan

    Thanks Mike.

    I immediately update the component after reading this blog. I found some bugs/issue while testing this component.

    1. I can load the data to charts, but not to crosstab.

    2. I also can’t use the Swap Axes property within the chart, but using Measure in Rows property within the BYOD component can fix the problem. The Displayed Series Format on the Additional Properties also showing blank.

    3. At the second screenshot, there is Sort Method property, but on mine is Simulate a Hierarchy. Did my component is on a wrong version?

    If this somehow become a complete component, then there would be no problems in prototyping using Design Studio.

    Cheers.

    (0) 
    1. Mike Howles Post author

      Thanks for the feedback, Donnie.

      A few responses:

      1. I can load the data to charts, but not to crosstab.

      This is a documented SDK Data Source limitation.  Also will not work with Filter Panel or Dimension Filter components.

      2. I also can’t use the Swap Axes property within the chart, but using Measure in Rows property within the BYOD component can fix the problem. The Displayed Series Format on the Additional Properties also showing blank.

      Yes, I’ve noticed this as well, however it seems to also be an incomplete implementation or undocumented SDK limitation.  To get around the ‘Swap Axes’ limitation, I provided the ‘Measures in Rows’ option in my component to allow you to rotate the Axis there, instead.

      3. At the second screenshot, there is Sort Method property, but on mine is Simulate a Hierarchy. Did my component is on a wrong version?

      An updated version with this property removed and the BIAL methods mentioned will be made available tomorrow.  I just ran out of time today.  I’ll definitely get this updated though.  Did not expect anyone to try it within 24 hours however lesson learned 🙂

      If this somehow become a complete component, then there would be no problems in prototyping using Design Studio.

      I’d love for an official data mockup or data prep component to be made available by SAP however until then we have SDK 🙂

      Try downloading again in about 24 hours and you should have the version I blogged about unless Karol Kalisz can do it overnight sooner for us 🙂   (Karol: it’s ready for release and the Source Code version is up, but I didn’t rebundle yet)

      (0) 
    2. Madireddy Rahul Reddy

      Hello Donnie,

      I am currently using BYO data source and would like to understand how to solve the swap axes issue? Can you please explain ? i tried turning the swap axes property to true but is that the only step? After i do this when i execute the application, i just see a blank screen

      (0) 
  2. Donnie Burhan

    Hi Mike,

    Need your help on accessing the data within the datasource.

    Assume that I got datasource like below:

    DIM, VALUE

    A, 10

    B, 20

    C, 30

    Now I want to get value for item “B” and show it on a text component.

    TEXT_1.setText(DS_1.getDataAsString(“VALUE”, {

        “DIM”: “B”

        }

    ));

    Am I doing it right? Cause when I run it, the data is not showing.

    Also, when I tried to use the content assistance to select members, I got error like below:

    An error has occurred. See error log for more details.

    while trying to invoke the method com.sap.ip.bi.bics.dataaccess.base.constants.MemberType.isNode() of a null object returned from com.sap.ip.bi.bics.dataaccess.consumer.selector.IInfoObjectMember.getType()

    (0) 
  3. Phillip Biermann

    Hey Mike,

    nice work, like the component 🙂 !

    Have you implemented a way to put in NULL-Values? So far it always returns 0 values. My ideas was something like this:

    Measure Jan Feb Mar Apr
    Actuals 1 2 NULL NULL
    Forecast NULL NULL 3 2

    Best regards,

    Phil

    (0) 
  4. Alessandro Baiocchi

    Hi Mike,

    great post and very, very useful. I’ve downloaded the component and now I’m able to create from Design Studio a Custom BYO Datasource. I’m facing the same issue of the post, I need to merge data by use 3 BW Datasources, but I cannot understand where to find the Edit Script Function Environment you used.

    Could you please explain it?

    Thanks in advance.

    Alessandro

    (0) 
    1. Vi Tran

      TThe edit script you see in his blog is a global rips that was created in design studio.  He’s just editing his update script Function.

      (0) 
  5. Patthamawan Montasereewong

    Hi Mike,

    Thank you for your post. I have used this custom component to merge two data sources but i am facing an issue after i swap the axes. In my bex query, i moved the measures to rows and dimensions to columns. After this, i set the measures in rows property of the component to “true”. After i do this, i am unable to add more than one measure to the data source. Can you please help with this?

    Is there any limit on the number of measures we can add if we set the property “measures in rows” to true? This issue arises only if the measures are in rows. if the measures are in columns, i am able to add as many measures as i wish to.

    Thanks in advance for your help with this.

    (0) 
    1. Mike Howles Post author

      Hey sorry for the delayed response – The ‘Measures in Rows’ (essentially a swap axis concept) has a strange bug in it that I’ve never been able to solve.  I don’t want to place blame on the SDK databuffer, however, I essentially just ‘gave up’ and stopped using it.  I’ve actually ‘deprecated’ this option along with the sorting, as I also had problem with that piece, too.

      I’m glad people been able to get so much mileage out of this one as they have, and I may revisit this one soon to make it more usable.

      (0) 
      1. Madireddy Rahul Reddy

        Thanks Mike for your reply to this query. i was wondering if there is any workaround for the sorting issue. I am displaying a chart with months on x axis. the data source for this chart is BYOD which is combining data from 2 bex queries. The problem is that the data appears sorted when i execute app in local mode but when i execute the app in BI platform mode, the months on x axis are sorted alphabetically(starts with APR,AUG etc).

        Thanks in advance for your time.

        (0) 
  6. Mr Pynk

    Dear Mike,

    I’m using BYOData component to set specific Text.

    But if I execute my application I get the error message

    “DATAPROVIDER” of type “org_scn_community_datasource_BYOData” could not be generated”


    Do you know this error message?


    Thanks and best wishes!

    (0) 

Leave a Reply