Additional Blogs by SAP
cancel
Showing results for 
Search instead for 
Did you mean: 
tfxz
Product and Topic Expert
Product and Topic Expert
0 Kudos

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.

No.DescriptionAbbreviationValue
1.total rowsTOTAL1,002,752,234
2.selected rowsDBSEL142,384,965
3.transferred rowsDBTRANS2,500
4.cellsCELLS35


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:

Performance SituationExample / Rule of Thumb*What to do?
RDBMSBIA
DBSEL is very higheg. DBSEL > 1 miodefine aggregates (materialized views, indexed views, summary tables), especially using filters[no problem]
DBTRANS << DBSELeg. DBSEL / DBTRANS > 100define aggregates[no problem]
DBTRANS is very higheg. DBTRANS > 50000In both cases the following applies:
  • If CELLS is significantly lower than DBTRANS then check the additional logic that requires to read much more details than are displayed. In some cases, there are opportunities for this logic to be avoided, bypassed or shifted - e.g. from the query processing to the loading stage. In practice, we sometimes see query definitions that comprise a number of (orthogonal, independent) pieces of logic that could be distributed if there was a query for each piece that then sit side-by-side in a dashboard.
    Known cases for which this situation currently cannot be avoided are display hierarchies, exception aggregations, internal business revenue elimination, formula calculation before aggregation, zero elimination, certain conditions like top-N or bottom-N.
  • If CELLS is equally high as DBTRANS then it is likely that either the manageability constraint has been violated (thus the query is likely to not provide you with the benefit that you expect) or the context is a different one, such as extracting mass data that is to be fed into a subsequent, machine-driven process [+]. In the latter case, it makes sense to switch to an RDBMS-based processing.
  • RDBMS support cursors and thus can transfer mass data in portions. While this does not translate into an immediate performance benefit it, at least, allows the data to be transferred in a resource-efficient manner. This is why it makes sense to switch to a RDBMS-based processing mode in an extraction scenario (see [+] above).
  • The BIA processing time in such a situation is dominated by the communication between BIA and NW BI servers which should run over a 1 Gbit connection. As an example: 1 mio rows of 1 kB each will then need 8 sec.
  • Furthermore, data is currently transferred in one portion; there is no cursor-like mechanism as in the RDBMS case.

* 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.

15 Comments