Understanding Query Performance in NW BI and BIA

Understanding Query Performance in NW BI and BIA

Query performance has always been a hot topic but with the advent of the BI accelerator (BIA) it has become scalding hot. Customers, consultants and colleagues report performance improvements well beyond factor 100 but also of factors like 4.7 and even 1. People are sometimes puzzled by such numbers: why is it so different? This blog attempts to make you understand what’s going on behind the scenes.

First of all, there are three rules of thumb that apply when looking at query performance with BIA:

• query processing time basically comprises three components: data access incl. aggregation, calculations (OLAP), client rendering;
  BIA addresses the data access time (albeit in special situations also the calculations time)
• slow queries (when running w/o BIA) can significantly benefit by using BIA;
  fast queries won’t benefit that much, i.e. if the query is already fast w/o BIA then don’t expect factor 100 – but if it’s already fast then who cares?
• beside better performance you can expect stable performance;
  this is a consequence of the simple processing paradigms inside BIA that (a) avoid the volatility of a DB optimizer and that (b) guarantee linear scalability: more blades or faster CPUs directly translate into better performance.

Now, let’s dive into more details and find out where BIA helps by looking at a query example. In figure 1 below, a query can be seen that has been used in some demos. Sales figures are shown by countries and quarters. In figure 2, there is a little box showing some statistics behind the processing of that query. Understanding those numbers will enable you to understand query processing performance, not only for BIA but also in case of an RDBMS-based approach:

1. total rows (TOTAL): this is the number of facts in the underlying infocube. So this is basically all the data that is available to answer the query. In this example, there are just over 1 bio records in the fact table. This is the starting point for the query.
2. selected rows (DBSEL): this is the number of records that are actually relevant to the query. On the screenshot you see that some filters have been set on Product Group and Cal. Year/Quarter. In other words: out of the 1 bio records only 142,384,965 records are actually relevant. So the first thing, the BIA or an RDBMS has to do is to filter the 1 bio records and to identify those 142 mio records for further processing. Both, BIA and RDBMS, typically use indexes for that purpose. Both are probably equally capable in doing this. However, identifying those records is one thing, processing them is the other. Here, column-wise processing, compression and parallelization inside BIA are a clear differentiator. See Comparing the BI Accelerator (f.k.a. HPA) to Traditional RDBMS Technology.
3. transferred rows (DBTRANS):  this is the number of result rows when you summarize (aggregate) the records identified under 2. In the example, records are aggregated by C-store (see lines in result grid) and Cal. Year/Quarter (see columns in result grid). The statistics reveal that there are 2500 combinations of C-stores and Cal. Year/Quarter. As a little calculation on the side: there are 5 quarters; so there must be 2500/5 = 500 C-stores in the hierarchy that is displayed in the lines of the result grid. Here, the BIA does tremendeously well, even if DBSEL is huge.
4. cells in result grid (CELLS): there are 35 cells in the example – see the red box labeled with 4 in the figure – all refering to a (basic) key figure; in this case, there is only one, namely No. of Sales. This means that the under 3. aggregated rows (coming from the BIA or the RDBMS) have been further aggregated to 35 key figure values. In this example, the reason for this is that the hierarchy logic imposed through the display hierarchy on C-store (i.e. a C-store has been grouped by its respective country). The hierarchy logic is processed outside the aggregation layer (represented by the BIA or the RDBMS) and inside the calculation layer. The latter is implemented on the application server.
   If the result is visualized, i.e. digested by an end-user and not subsequently processed by a machine, then the number of cells must be small as nobody – other than Rain Man – can derive business decisions from a huge and unmanageable result set. This is an important fact!

Figure 1: Example of a query and its result grid.

Figure 2: Statistics for the query of figure 1.

In the simplest case, the rows calculated under 3. are displayed one-to-one in the result grid under 4., thus 2500 rows translate into 2500 cells (per requested key figure). In practice, queries comprise some additional logic, like the display hierarchy in this instance. Other options are exception aggregations, internal business revenue elimination, formula calculation before aggregation, zero elimination, certain conditions like top-N or bottom-N, …

Overall the following rules apply:

^* The values given here are very rough indicators on a row-level. Frequently, they also depend on the size of the row or the underlying technical infrastructure (hardware resources, OS and DB platforms etc.). So consider them with care.

Conclusions

The table above indicates where the BIA is strong but also describes situations that should be avoided. Infocube modelling and query design are still relevant albeit to a fairly reduced extent. In that sense, it’s like with any other feature: it can be sensibly used and create an outrageous benefit. But if pushed to the boundaries then things can become odd – my favourite example in that respect is an empty database table with a huge number of partitions that cause queries to run for minutes even though the table is empty. Nobody will question the benefits of table partitioning just because of that.
Anyway. I hope that, with this blog, you have gained some coloured insights into the components that determine query processing performance. Use those “colours” when confronted with those who try to make you think that it’s all around black and white.