The good, the bad and the ugly of direct binding in Dashboard Design 4.0 (Xcelsius)


With the release of SAP BusinessObjects 4.0, SAP decided to give Xcelsius a new name and an upgrade. SAP now refers to Xcelsius 2008 as Dashboard Designer 4.0. In addition to its new name, SAP also made a few upgrades to the way the tool interacts with data. The tool now allows developers to directly bind some components to a common semantic layer (CSL) Universe, bypassing the need to interact with the Microsoft Excel spreadsheet bound to the model. At first glance this sounds like a much needed enhancement but there are a few items that need to be discussed concerning its performance benefits and functionality. Below I will outline the process of direct binding, the benefits it offers and the items that it lacks.

Direct binding in Dashboard Designer 4.0 allows developers to create a reusable query directly within the tool. There is a new panel that you have to enable called the “Query Browser” within Dashboard Designer. This panel allows the developer to define one or more graphical queries for use by the spreadsheet or supporting components. The results of the query can then be bound directly to “some of the components”. For example, when you are utilizing a bar chart and defining the series, you can map that series and category to a measure or dimension without mapping to Excel (See Link).

Having the ability to create queries and bind the data is an improvement over the processes required to access live data in Xcelsius 2008. Prior versions of Xcelsius required that you create connections to your data external to Xcelsius and then map the data into the Xcelsius spreadsheet model. Having the ability to create queries directly within the designer interface will make development slightly faster. Chris Hickman, Senior Business Intelligence Consultant with Decision First and SAP Insider Xcelsius Bootcamp presenter (Twitter@chickman72) had the following to say concerning this enhancement:

“The act of direct query binding is critical in optimizing the performance of your dashboards. The less designers rely on Excel to store and serve data, the faster their dashboard will perform. Direct query binding will allow designers to maintain the connected nature of a dashboard while reducing or eliminating the need for cell binding.”

You will also find that the components bound to the query are able to preview the data without requiring that you render and preview the dashboard .swf (Shockwave Flash) file.

Another enhancement to Dashboard Designer is the addition of the “Query Prompt Selector” component. This component will automatically bind to the “list of values” (LOV) and the query filters associated with a query. Any value you select from this new component will automatically filter the query’s data when setup properly. This is much faster than the old process where you had to map the data and LOVs to Excel and the components then develop a trigger to force a refresh (See Link).

The final improvements center on the performance of the developer tool and the way Dashboard files are exported to the repository. The developer tool appears to have several coding improvements that make managing the workspace more responsive to binding and coding requests. Prior versions of Xcelsius would pause for several seconds or minutes when changes were made to the model. Dashboard Designer will reduce this delay because it has less need to bind its data and components to Excel. You will also see an enhancement to the way files are exported to SAP BusinessObjects Enterprise. When you export a dashboard in version 4.0, you are actually exporting two files. You can see this by querying the CMS database (See Link). The first file is the rendered .swf and the second is the .xlf. The .swf file is the actual file that end users view, while the .xlf contains the model that developers work with to update the Dashboard. This enhancement makes storing, securing and managing Dashboards much easier compared to previous versions. In prior versions it was easy to forget to save .xlf model file when making updates and exporting the .swf. You could also misplace the .xlf because the two files were managed separately. Again Chris had the following to say:

“One of my SAP BusinessObjects XI 3.1 clients had a great dashboard and requested that I make changes to update the design. They had the compiled swf and could not find the source xlf. The new Dashboard Design Object fixed this issue by storing the source and compiled dashboards in a single hosted location.”

The usability enhancements listed above will help make Dashboard Designer more developer friendly but they offer little in terms of performance. In general, the direct binding queries offer little enhancements compared to the performance of QaaWS or BIWS calls. When you refresh the direct binding query you are still utilizing a web service call to the SAP BusinessObjects enterprise Java Application server. A quick trace utilizing Fiddler 2 will reveal that the http://;/dswsbobje/* context is called when data refreshes are invoked in Dashboard Designer. While this does not sound like a problem, a quick technical discussion might shed some light on why this matters.

When an Xcelsius .swf file is viewed by a user the flash player plugin on their browser is utilized to render the components and its data. The player connects to the configured web service via http or https and fetches the data. If your dashboard has only one query connection, the performance will likely be acceptable. However if your dashboard has multiple queries, each one will be processed in serial. In addition, the data is transferred as an .xml statement. This .xml statement is then parsed by the flash player and then rendered within the component. When you combine the fact that data connections are processed in serial and that XML data has to be parsed, you can see how a multi-query dashboard will appear slow to the consumer. This is significant because most dashboards will have 5 to 10 query connections to satisfy the business requirements and to work around Dashboard Designer’s lack of an aggregation engine.

Dashboard Designer or Xcelsius is a visualization tool. It produces eye catching interactive dashboards but it has no real aggregation or analytic engine imbedded in its components. To back this claim up lets discuss how the direct binding queries work. If I define a query with two dimensions and three measures the granularity of that query is based on the two dimensions. For example, if the query is defined as YEAR, MONTH, TOTAL_SALES, TOTAL_QUANTITY, MARGIN the results will appear as:

YEAR

Month

SALES

QTY

MARGIN

2009

January

25000

125

1000

2009

February

32000

219

1250

2009

March

28000

189

45

2010

January

27000

135

236

2010

February

31000

204

256

2010

March

45000

254

456

If I bind the YEAR Dimension and TOTAL_SALES Measure to a bar chart, my results will appears like this:

(Click to Zoom)

Notice how the values for YEAR and TOTAL_SALES are repeated for each of the six months. This is due to the lack of an aggregation engine within Dashboard Designer. The query you bind to a component is only aggregated once at the database level and will remain at the granularity of the query (YEAR and MONTH) with the model. If my requirement was to have two charts, one by YEAR and another by YEAR & MONTH, I would have to develop two queries and then bind each one to the appropriate component. If you have ever developed an Xcelsius dashboard using real-world requirements, it is easy to see how a lack of an aggregation engine can lead to multiple queries. According to Chris Hickman, this is a very real problem to contend with.

“The lack of an aggregation engine in Dashboard Designer introduces the opportunity for query proliferation. Best practices state that queries should be combined and minimized, but if the aggregation of data cannot be adequately provided by a single query then multiples must be used. The developer is then left with the choice of using more and more queries or investigating third party tools like XWIS that efficiently aggregate data on the fly.”

I would also argue that direct binding will lead to more queries then QaaWS or BIWS. With QaaWS or BIWS I could use subtotals, VLOOKUP, INDEX and other Excel formulas to somewhat aggregate the data. With Direct Binding I have to create multiple queries because I am bypassing the Excel model. In a recent Dashboard Designer project this problem became evident, and I reverted to using BIWS to help reduce the number of queries and increase the performance. It is worth noting that the queries you define in Dashboard Designer can have their data bound to the Excel model and not directly to the component. Once the data is in Excel, you can devise a model to help aggregate the data, but I don’t see any real value in this option compared to using BIWS which leverages Web Intelligence and can solve many of the problems mentioned in the article. See my posting on the BIWS presentation I presented at the ASUG conference in 2011 for more details.

Another issue with Direct Binding is that it offers no solution to utilize controllable cached query data. The BOE 4.0 and 3.1 SP3 platforms incorporated a new Xcelsius Cache server that dynamically caches data to reduce the load on the database for concurrent user requests. However, this is not the same as utilizing a scheduled report instance to power the data. Live Office and BIWS offer the option to utilize a report instance for the data, a process that bypasses the database and utilizes saved data. With direct binding every query executes against the database unless it can be satisfied by the Xcelsius Cache server. In short, if your database performance is slow, your interactive Dashboard will also be slow.

Hopefully after reading this article you can see both the strengths and weaknesses (the good, the bad and ugly) of direct binding in Dashboard Designer 4.0. However I do not want to leave you on a sour note because there is an option that eliminates all of the problems of direct binding while maintaining all of its strengths. There is a plugin for Dashboard Designer and Xcelsius called Antivia XWIS that has completely changed the capabilities of the tool. In terms of this article, Antivia XWIS provides an aggregation engine, a direct binding alternative, the ability to use a report instance and many other advantages. If you have not seen XWIS in action I would encourage you to give it a review. You can access the Antivia XWIS advantages page for more details. You can also request information and schedule a demo from their North American partner Decision First Technologies. Decision First has an on demand webinar you can view at any time. I plan to post an article dedicated to Antivia in the coming weeks, so stay tuned.

5 comments

  1. We were deeply disappointed with Dashboards 4.0 too. Without aggregation, with a limited number of rows, complicated indicators, we still need Webi scheduled reports, the Excel sheets, and the massive overhead. I usually put as much as possible into the universe, but that’s not always possible.

    Icing on the cake: I’ve found no way to SEE the data that I was querying.

    Add many bugs (SP2) and I still use Xcelsius the way I did in XI 3.1

  2. Yes, despite the fact that you can define a query right inside the tool.. it hardly helps without the aggreagation engine. More surprising, there is no good way to directly bind a multi-series chart to a qurey, there is no way to transform the incoming data to crosstab, ultimately you have to still depend on excel formula or you have to create universe dimension objects for each & every possible values for dimension you plan to crosstab on!!!

  3. Hi,

    I need a help I am facing an issue that is when i change any metric value(i.e prompt from the drop down) the dashboard chart refreshes automatically(without having to press Refresh button).I am unable to whether i have done some wrong binding or is it some too Bug.Please do note that its not the same for all the dashboards.
    Please help me in this.Thanks in advance 🙂
    Xavier

  4. Hello,

    Can anyone tell me if the results from a query can be bound to a single value selector? I’m trying to use some query results to set the Maximum Limit and Alert threshold values on a Gauge component. I can see that you’re unable to use the query result directly (as with normal charts), however trying to bind the query result into a cell on the Excel worksheet and then using that cell doesn’t work either.

    Is this possible to do?
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s