Design Studio Innovation Series – Topic 1: Making a Case for a Larger SDK Result Set

To put this subject into context, lets start with the data source limitations.  There is currently an overall restriction on data returned to a Design Studio application based on the data source itself.  So for BusinessObjects Universes, this is 50,000 data cells and for BW BEx Queries there’s a safety net of 500,000 data cells, as noted in the following discussions (I wasn’t able to confirm if there is a limit for HANA):

Maximum Result Set

Result set is too large; data retrieval restricted by configuration Universe Connection

Components not showing large data

Working with HANA Datasource

In comparison, as mentioned previously, the Design Studio SDK framework only allows a result set of 10,000 data cells to be passed to a custom component, even if the connected data source itself has a higher limit.

An Example Use Case for a Large Result Set

When the topic of large result sets comes up, the usual question is “Why do you need to display such a large amount of data on a dashboard?”.  It’s not necessarily that there’s a desire to display all of the data at once in a single crosstab for instance but if you’re following the approach of a single data source for an application (a recommended practice) to feed multiple components in multiple tabs, as well as providing the flexibility to filter and swap dimensions and measures, then you can easily need a large result set.  A large result set would also allow for local filtering, resulting in more interactive dashboards like we are used to from Xcelsius.  Further examples are given in the above discussions.

Now, in the case of SDK components, I’d like to work through one use case example where the 10,000 data cell limit is simply too low.  A common requirement is to display a scorecard table with microcharts.  So I’ve mocked one up in Excel with actual airline on-time performance data.  Here, we simply want to represent on-time performance KPI trends over a single month by day, for each origin state.  This represents 53 rows by 12 columns, resulting in 636 “cells” as shown below.  Perfectly reasonable for a dashboard I think.

However, the actual number of data cells required to generate this scorecard is 17,808 as shown in the Initial View below, calculated as 53 Origin State rows x 12 Measure (KPI) columns x 28 days per KPI:

So here, I think we have a legitimate example in support of why we would need more than 10,000 data cells for an SDK component.  The number of “cells” displayed in the scorecard is certainly not very high, at 636.  However, the underlying data required to generate it exceeds the 10,000 cell limit.

Putting It To The Test

So now, just to make a point, let’s see how much data an SDK component can handle if it could be fed a large result set.  The limits on the data source as well as the SDK component are based on the number of data cells.  If however, for test purposes we define an Initial View on a BW data source without specifying any measures (technically resulting in zero data cells), it turns out that not only is the SDK 10,000 cell limit ignored but so is the 500,000 cell limit on a BW data source.  As a result I was able to load an SDK table component with over 2.5 million cells of data as shown below (again using real airline on-time performance data).

Here’s the Initial View of the data source:

Here’s the first page of the loaded SDK component:

And here’s the last page:

With my test data that’s 445,822 rows x 6 columns, representing 2,674,932 cells.  Admittedly, it took a while to load from the back-end because I’m running my BW dev system on a medium spec VM and there’s also the client-side component load time but I think on a production system or with HANA in particular the performance would have been significantly better.

Once the data was loaded however, the table component responded very well.  Navigating back and forth through the pages was instantaneous while sorting and filtering columns was quite fast too, especially considering the data volume.