Introduction

In my blog post Making a Case for a Larger SDK Result Set, I described the scenario of a scorecard component as a real-world example of situations where a larger result set may be needed for SDK components than currently permitted.  This follow-on post is inspired by feedback from Jeroen van der A.  Rather than reiterating the SDK result set limit issue, the intent here is to focus on design considerations for developing an SDK scorecard component.  Although a scorecard component is on the latest Design Studio Roadmap, I thought it could make for some interesting discussion to see how we might approach implementing this as an SDK component, since such a component seems to be a common question and request on the SCN.

I will make a disclaimer though that this post will not conclude with a link to a fully functional SDK scorecard component (forgive me 🙂 ), since the scope is only to evaluate the design considerations and engage the community in discussion and feedback as such.

Setting the Scene

Rather than starting completely from scratch, I came across a good example from HighCharts that could be adapted for reference purposes.  I have edited the HighCharts example with my own sample data set as shown below:

ScoreCard.png

A live preview is available here and for those who like to tinker the complete code is here.

Design Considerations

In my view the main design consideration is how to define the layout of the result set to cater for the following scorecard requirements:

1) Columns with a single value

In the scorecard above Scheduled Flights (CY), Delayed Flights (CY) and Average Delay (min) represent examples of such columns.

2) Columns with a sparkline chart which specifies a fixed set of dimension members

In the scorecard above the Delay Groups Column Chart is based on 4 Delay Groups across the horizontal axis: Delay 0 to 14 mins, Delay 15 to 29 mins, Delay 30 -44 mins, Delay 45 – 59 mins.

3) Columns with a sparkline chart which specifies a dynamic set of dimension members

In the scorecard above the Daily Delays Line Chart is based on up to 31 days representing each day of the month.

So how do we best represent this in the underlying data source definition and initial view?  Two possibilities are shown below:

a)  Each dimension member is defined as an individual measure in the columns:

InitialViewColumns.png

Under this approach we would map each of the measure columns in the result set to the corresponding scorecard column with a column index, either individually or by specifying a range.  It has the benefit of a compact result set.  No issues for the single value columns or the Delay Group sparkline column chart with a small fixed number of x-axis dimension members.  However, we need to create individual measures for each of the 31 possible individual time dimension members for the Daily Delays Line Chart column, which is not a typical approach.

Now how do we handle the scenario where the number of days in the month varies so that the sparkline chart displays correctly?:

i)  Specify an “Ignore zeros at end” option but then how do we know if the zero value is due to the day not existing or genuinely has a zero value?  Or maybe this distinction doesn’t matter in context?

ii) Dynamically filter out the redundant day measures at the end based on the days in the month.

b)  Dimension members are listed in rows with a corresponding single measure in the columns:

InitialViewRows.png

Under this approach, since the dimension members are listed in the result set rows, the issue of dynamic number of items for the Daily Delays Line Chart is taken care of but at the expense of a significantly larger result set.  Additionally, because we have more then one dimension across the two sparkline charts (Delay Group and Day of Month), client-side aggregation would be required for each of the charts, which is not ideal as such aggregation should preferably be left to the OLAP engine.

So there’s some food for thought.  I’d be interested in any feedback about the above options and ideas for any other possible approaches.

Blog Series Index:  Design Studio Innovation Series – Welcome

To report this post you need to login first.

6 Comments

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

  1. Ingo Hilgefort

    Hello Mustafa,

    to some degrees it depends on the resultset and some problems you can solve in the resultset and the other half depends on how good the actual component was designed.

    A scorecard is part of our upcoming release as well based on the example you showing here (http://www.highcharts.com/demo/sparkline) and we are in the final testing stages already.

    So I would not necessary call it a “design dilemma”.

    In most scenarios we will see your first example being used – my opinion.

    as mentioned – the scorecard will be there soon.

    regards

    Ingo Hilgefort, Visual BI

    (0) 
    1. Mustafa Bensan Post author

      Hi Ingo,

      Thanks for the feedback.  I agree that option 1 is the best approach of the two, although I don’t really like the idea of having to create individual measures for each time period since this is not how backend queries are typically designed.  I guess it’s the most appropriate compromise for now though.

      Regards,

      Mustafa.

      (0) 
  2. Mike Howles

    This will remain a local projection and/or result set volume dilemma wherever a component needs to show more than one level of granularity at the same time.  (As you have illustrated in your example.)  We could put this dilemma to bed if components supported more than one data source, in my opinion.

    (0) 
  3. Jeroen van der A

    Hi mustafa,

    the main problem is that you got basically a two dimensional table that needs to be translated.

    In BW we arranged some things with using filtered measures, current period, budget current period + an x amount of previous periods for a graph.

    As discussed earlier I used d3.nest to include the hierarchical structure.

    best off course would be multiple datasources. Or a table like component where you could put several different type of components in each field. Each component could hold it’s own datasource, but the table would render a row for each instance of a dimension and automatically passes that filter to the underlying components

    kinda like the foreach feature in D3.

    Jeroen

    (0) 
    1. Mustafa Bensan Post author

      Hi Jeroen,

      That’s very helpful feedback, thanks very much.  With respect to the “x amount of previous periods for a graph“, did you have to set these up as individual measures (as per Option 1 above), or were you able to achieve the desired result based on a single measure combined with the time dimension (as per Option 2)?

      Regards,

      Mustafa.

      (0) 

Leave a Reply