Design Studio BIAL Question: How to iterate all cells? (UNX-based datasource)

[alfons_gonzalez]

Hi,

I have what does it seems a simple request that i am not able to fulfil. The idea is so simple: you have a simple data source with more than dimension acting as a row, and a mesaure as a column (see attached picture sample)

What I need is to know the values of dimension columns retrieved on the data source (Button Key, Button Title) for each row . I have tried 2 options:

1) To retrieve the dimension members using getMembers function: this option does not work properly since you need to retrieve 2 arrays: 1 for each column and to assume that the arrays would be ordered following same order than values show in the final query. I have checked that sometimes this is not true

var buttonkey_arr = DS_NAVBAR_BUTTON.getMembers(buttonkeyID, maxNumber);

var buttonntitle_arr = DS_NAVBAR_BUTTON.getMembers(buttonTitleID, maxNumber);

2) Iterate all data cell values and to consider only the ones with data cell values <> 0 (see sample schematic code below)

This option would work fine if it was possible to manage the error that appears when we try to access to data with no values (e.g: some kind of try statement that unfortunately does not exist in BIAL). So, at script completion it does its job but an error message appears.

var buttonkey_arr = DS_NAVBAR_BUTTON.getMembers(buttonkeyID, maxNumber);

var buttontitle_arr = DS_NAVBAR_BUTTON.getMembers(buttonTitleID, maxNumber);

LOG_SCRIPTS.logInfo("Starts Button Iteration");

buttonkey_arr.forEach(function(element, index) {

  var buttonKey = element.internalKey;

  buttontitle_arr.forEach(function(element2, index2) {

    var buttonTitle = element2.internalKey;

    var data = DS_NAVBAR_BUTTON.getData("_cdUc0MVHEeW8o4CzqyR0tQ", {"_E9QR8MTdEeW5hcDK-Ktfzw":buttonKey, "_E9SHIMTdEeW5hcDK-Ktfzw":buttonTitle});

  LOG_SCRIPTS.logInfo("buttonKey: " + buttonKey);

  LOG_SCRIPTS.logInfo("buttonTitle: " + buttonTitle);

  LOG_SCRIPTS.logInfo("Cell data: " + data.value);

  });

});

Question is: has someone succesfully managed a request such this one? I am using data sources based on UNX relational universes, so I have no option to use "magic" tricks coming from BW or similar.

Thanks,

Alfons

[Former Member · Accepted solution]

Hi Alfons,

One possible workaround is to build another dimension which concatenates the two original dimensions in your model (i.e., key+text).

And then use the code similiar to the following in the application.

var dim_key=DS_1.getMembers("KEY", 10000);

var dim_key_text=DS_1.getMembers("KEY_TEXT", 10000);

dim_key.forEach(function(element, index) {

  var key=element.internalKey;

  var key_length=key.length;

  dim_key_text.forEach(function(element, index) {

  if (Convert.indexOf(key, element.internalKey)!= -1 ) {

            var text=Convert.subString(element.internalKey, key_length);

            var data = DS_1.getData("XXX", {"_E9QR8MTdEeW5hcDK-Ktfzw":key, "_E9SHIMTdEeW5hcDK-Ktfzw":text});

  }

  });

});

Hope this helps.

Best regards,

Alfred

[alfons_gonzalez · Accepted solution]

Hi Alfred,

Thanks for your contribution. I am quite sure it should work (I also considered it)  but I have been finally able to fix it by using members attributes (see post above).

Thanks anyway for your helpful answer,

Alfons

[mike_howles4]

Hey Alfons, your code looks very similar to what I've had to do in the past, using getMembers and forEach to iterate over the tuples.  I agree this seems to be unnecessarily complex, and prone to throwing warning/error messages.  I'd also love to trap exceptions with a try{ ... } catch but alas, nothing like this exists in BIAL.

I've been meaning to write a true "row" iterator to make this easier, as iterating over tuple selections/getData is a tad silly.  I could make this iterator using SDK if that would be an acceptable workaround for you?

[alfons_gonzalez]

Hi Mike,

  Sounds nice. i think that it would be a wonderful enhancement!

  At the meanwhile I have been able to workaround my issue playing with attributes. That is I have added all addidional columns (except the one corresponding to the key) as attributes of the key dimension on the UNX universe. By this way I have a single getmember call with a single array. Each element has its attributes but the order does not longer matter (as ech attribute is well linked to its key).

Sounds quite logical but it has take me a while..... anyway the iterator you mention could be very useful on some other cases., so I would vote for it.

Thx