Bind Query to Components in Dashboard

Pros:

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 

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

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 

Cons:

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.