Overview
Here I go again, yapping about performance. It's one of those topics that never really goes away. Inevitably, as a BI consultant, you will have to perform performance tuning either on your reports, or your back end. Fortunately, I've already covered a few back end tips and tricks on a previous blog post (Backend Performance Tips) . In this post, I will be covering a few performance tuning tricks that I've used in previous projects, to improve the performance of WEBI reports, connected to BW via BICS connection and BEx queries. There are a plethora of guides on SCN on how to improve WEBI performance, which uncovers some not so obvious places where performance can be improved.
This is by no means a comprehensive list of everything you can do to speed up those WEBI reports, but they are a few tips that have been very effective in my projects. These tips are valid up to BW 7.3 on HANA, and BOBJ 4.0 and above.
With BW 7.4 on HANA and HANA Studio, some of these tips might still be relevant, and some might not, as we have more back end modelling options, the HANA Views provide tremendous analytical capabilities, which in certain scenarios might just make these tips irrelevant.
For those of you who haven't yet taken the plunge into BW 7.4 on HANA, and HANA Studio, and are using BEx through BICS to connect to WEBI, hopefully these will be of use to you. So let's get to it.
Logical Partitioning
Although this is really a change to the back end data modelling, it is one that can improve significantly the performance of your reports. So much so, that in more recent versions of BW, SAP introduced a tool to automate the process of creating logically partitioned infoproviders, the Semantic Partitioned Objects. I have covered this in my previous blog (Backend Performance Tips) as well as referenced a great article by Rakesh Dilip Kalyankar on SPOs:
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/50e09876-d902-2f10-1db6-ce161ab7f...
Focusing on the Front End, the reason logical partitioning improves performance is quite simple, as illustrated by the example below:
A user has a report that outputs current YTD key figures, and previous YTD key figures. All the data resides in a cube which has 10 years worth of data (say 2005 - 2015), sitting under a multiprovider. For simplicity, let's say that there's 50 million records, 5 million per year.
When the user runs the report for 2015, we expect to see 2014 and 2015 YTD figures. The SQL statement has to sift through 50 million records to identify only the 2014 and 2015 records and will take x amount of time.
Now let's imagine that in the back end, we have 10 cubes, identical in structure, logically partitioned by year (2005 cube, 2006.... 2015, 2016 etc.). Each cube will contain 5 million records, all of them sitting under a single multiprovider. When the user runs that same report, the multiprovider is intelligent enough to direct the read to the 2014 and 2015 cubes. Rather than sifting through 50 million records, we're now only going through 10 million records (5 million in each cube).
This will result in faster running reports due to less volume of data to read. This is an effective and recommended approach to improving query performance.
Aggregation Flag on HANA
BEx queries can be complex beasts. With aggregations, exception aggregations, counting documents, customers, items, restricted and calculated key figures, cell calculations, etc., they can become very onerous on the OLAP Analytic Manager. The OLAP manager has an orchestration role, in which it translates the BW query into an OLAP calculation graph. See below:
Source: openSap
With a non HANA DB, these are the steps taken to calculate exception aggregations:
Source: openSap
And here are the steps for a BW on HANA system:
Source: openSap
The performance gains from enabling this setting are huge.
For those of you running BW on HANA, there's a nifty little setting hidden in the multiprovider properties. It allows us to push the aggregation and exception aggregation functions down to the HANA DB.
1. Go to the multiprovider to be modified, and make sure you're in change mode. Once in the design screen, go to Extras > InfoProvider Properties > Change:
In the next screen, under SAP HANA/BWA Ops, make sure to select 6 Exception Aggregation. This will enable the push down of the functionality to the HANA DB
OSS note 2063449 - Push down of BW OLAP functionalities to SAP HANA provides the list of operations that can be pushed down to the HANA DB, and is constantly being updated. Below is the list of functionalities as of the publication of this post:
| Analytic Manager operation | Release* |
| Aggregation | BW 7.0 + BWA 7.00 |
| Cell-based calculations (FEMS) | BW 7.0 + BWA 7.00 |
| Hierarchy-processing (part 1) | BW 7.0 + BWA 7.00 |
| MultiProvider-UNION | BW 7.3 + BWA 7.20 |
| Exception aggregation COUNT | BW 7.3 + BWA 7.20 |
| Exception aggregation for keyfigures w/o currency/unit conversion | BW 7.3 + BWA 7.20 |
| Exception aggregation for keyfigures w/ currency conversion | BW 7.3 + BWA 7.20 |
| Exception aggregation for keyfigures w/ unit conversion without reference InfoObject | BW 7.3 + BWA 7.20 |
| Time-dependent currency conversion | BW 7.3 (SP10) on HANA |
| Avoid intermediate result set materialization (Layer ”Q” virtualization) | BW 7.4 (SP05) on HANA |
| “Current member” calculation | BW 7.4 (SP05) |
| CompositeProvider (e.g. JOIN between fact data) | BW 7.4 (SP05) on HANA |
| Stock coverage keyfigures | BW 7.4 (SP06) |
| Unit conversion with Reference InfoObjects (see SAP Note 2001947 to switch off) | BW 7.4 (SP08) on HANA |
| Parts of Handling of inventory keyfigures (see SAP Note 2001947 to switch on) | BW 7.4 (SP08) on HANA |
| Leverage HANA hierarchy processing | planned with BW 7.5(SP00) on HANA |
| List-based calculations (like conditions) | under discussion |
| Time-Series calculations | under discussion |
| Temporal Hierarchy JOIN | planned |
| Internal Business Volume elimination | under discussion |
| Formula exception aggregation for non-dimensional keyfigures | planned |
| Formula exception aggregation | planned |
Use Selection of Structure Elements
For queries that use many restricted and calculated key figures, BW provides a nice little checkbox, Use Selection of Structure Elements which will allow the SQL statement to only select and calculate the Key Figures or structure elements that are actually being used, which will result in less time for the select to run.
To access this, go to RSRT, enter the query name and then hit the properties button.
BEx - Free Characteristics
This is a really simple tip. Make sure all of your characteristics are in the Free Characteristics section of BEx. As we're simply passing along the data to BOBJ, we don't need the overhead of BEx calculating intermediary results by placing characteristics in the rows section. The final presentation will be done in WEBI, and that's where you will create the layout the end users are looking for.
BEx - Suppress Results
Very frequently, before the BOBJ days, we'd want to have fancy looking BEx reports, which included totals either by the end of the rows or columns. That was a legitimate requirement. However, similar to the Free Characteristics above, if we do not suppress the results, we have an overhead in the OLAP engine to calculate them. Even though we're putting all the characteristics in the Free Characteristics section, it's good practice to highlight them all, and suppress the results.
A quick example to show how performance is affected by having results, is to create a simple BEx report, put a couple of characteristics in the rows, and enable results for all of them. Run that report in RSRT with the debugging option, and note down the execution times. Then highlight all the characteristics in the rows and suppress the results, and re-run the report in RSRT, and compare the execution times. You should note a difference in the execution times.
BEx - Calculated and Restricted Key Figures
WEBI gives us the option to do a lot of calculations using variables and built in functions, somewhat similar to how you'd do formula calculations in Excel. It is very tempting to completely ignore BEx's OLAP capabilities, as we did, and do all the calculations to WEBI and the BOBJ server.
However, as described above, the OLAP Analytical Manager is very powerful, and with HANA in the mix, I would argue it's a pre requisite to push down if not all, the most complex calculations to BEx, via Calculated and Restricted Key Figures (CKF and RKF).
Although it might be simpler or easier to use variables in WEBI, performance will be significantly hindered using the BOBJ server. It is not made to handle huge volumes of data, and also does not have the same capabilities of the OLAP Analytical Manager.
Once we pushed all the calculations back to BEx, and used WEBI purely as presentation, with minimal logic in the variables, most of our complex reports began to run in under a minute, whereas they sometimes would simply timeout.
Key lesson - send it all back to BEx!
BEx - Variables
As a general rule, we the BW developers, instruct users to always filter the data they're running, so they don't try to run a report with wide open criteria, and fetch the entire table.
A way we enforce it, is by making some filters mandatory, say Fiscal Year, Fiscal Period, therefore limiting the amount of data users can run a report by, and improving performance that way.
When we have a scenario with WEBI using BEx via BICS connection, we are faced with the option of using BEx variables, or WEBI filters. We should always strive for the former (BEx variables).
If we simply set filters in WEBI, all the records from the cube will be selected, passed along to the BOBJ server, and then parsed for whatever values are in the filters. This makes for unnecessary data being transferred.
Conversely, if we have all the filters as variables in BEx, the records that will be passed to BOBJ are those which have been selected in the BEx selection screen. The illustration below might help to view this:
Using filters in WEBI, will result in the entire data in the cube being selected, and parsed within WEBI.
By using filters in BEx, only the filtered data will be passed to BOBJ, resulting in a smaller data set and better performance.
Navigational Attributes
One of the great functionalities of WEBI is that you can easily use Master Data Navigational and Display attributes. It's really a matter of dragging them in to the results area and then into the report itself once the report is executed. There are some functionality trade offs between the two, such as applying drill down and formula calculations to navigational attributes but not display attributes. That will not be covered here.
If we know that the user will have a requirement for reporting on attributes, and they will be a part of the standard layout, we've found that flagging them as navigational attributes will result in better performance.
The reason for that is, if we keep the attribute as Display, and add it to the report layout, the report has to go back and go through each parent characteristic of that attribute (Ex, color would be the display attribute and Material the parent characteristic) and then determine the output of the report.
If we flag the attribute as Navigational, the report treats it as it's own dimension, and therefore doesn't have the overhead of going to the parent characteristic to calculate the report output.
Query Stripping
This functionality within WEBI enables reports to 'pay for what they consume'. Many times, we design WEBI reports with many available fields, but only a selected few are part of the standard report layout, see below:
In the example above, all the available objects in the left are being retrieved from BW, although we're really only using a few in the default report.
Query stripping will optimize fetching of data to only the objects actively being used in the main report. With query stripping enabled, this particular report would only fetch the 5 fields actively used (Fiscal Year/Period, Material, Base Unit, Currency/Unit and Net Val. in stat curr).
By reducing the amount of objects being fetched from BW, we will improve report performance.
To activate this functionality, we must first do so at the WEBI query level, by going to Query Properties in the Query Panel, and selecting Enable query Stripping
And within the active document, under properties tab > Document
And then select Enable query stripping
Once these are enabled and you refresh the WEBI report, you will noticed that all the non active objects will be bold to indicate they have been 'stripped'
If you then add one of those objects into the report, you will get the message #REFRESH indicating that you need to refresh the report, so that it can go back to BW, and fetch the data for that object
The main challenged of using query stripping is educating the users of this functionality, why the report behaves the way it does, and why we're using it. Usually the performance trade-off of using the functionality turns users around the nuisance of having to refresh the report every time they drag a new object into it.
Summary
If end users aren't bugging you that WEBI reports are running slow, well then good for you, you've done a great job in creating awesome performing reports! For everyone else, hopefully this guide can help with the some simple yet highly effective and fast ways to get those slow running reports to run faster.
Like mentioned earlier, there are many blogs out there with more advanced ways in which you can create even better running reports.
Please post your comments to make sure I'm being kept true to all that's being mentioned here. Let me know what you've done in your projects and hopefully will can make this an interesting discussion on real world solutions to this never ending topic.
Happy reporting!
- SAP Managed Tags:
2 Comments
