With the general availability of the pushdown of BW query execution to the database level, I would like to provide you with some basic technical information about how this works and why you should care.
BW reporting queries may contain global restrictions and local restrictions for the different cells in the output of the report. We also refer to queries with local restrictions as “FEMS queries”. (FEMS is short for Form EleMent Selection.) Why should you be interested in FEMS queries? Well, depending on where they are processed (application server or database), this might have considerable performance benefits.
Queries with local restrictions might require a lot of data transfer between the database and the application server and additional processing in the OLAP processor, which makes them expensive to process. As you might guess, implementing the direct execution of such queries in the database can therefore speed up performance.
As of SAP BW 7.5 SP 4, queries with local restrictions can be completely processed in the IBM Db2 database. So far, we’ve offered this as part of a pilot program. Now direct execution of FEMS queries on the database is generally available with SAP BW 7.5 SP 4 and higher. So, if you are an SAP BW customer, this new feature might be worth a look.
Direct processing of FEMS queries in the database often speeds up performance, but it depends on the amount of data that is transferred between application server and database.
It’s faster than the traditional processing when the FEMS restrictions are defined on additional characteristics that do not occur in the output report. Then the amount of data transferred between database and application server to evaluate the FEMS restrictions is considerably reduced. If the amount of data transferred between application server and database cannot be reduced, then a pushdown to the database does not make sense.
Let me guide you through the process: First of all, you need to make sure that the technical prerequisites are met (see below). Then it's pretty straight-forward. You can do the following:
First of all, you need to set the RSADMIN parameter USE_FEMS_IN_DB to X in program SAP_RSADMIN_MAINTAIN:
This enables FEMS query processing in the database. Now you can decide whether you want to disable FEMS query processing for selected DataStore objects, InfoCubes, or queries again.
If you want to enable FEMS query pushdown for selected InfoProviders, you do this in the Data Warehousing Workbench (transaction RSA1). For queries, you use transaction RSRT. For example, if you want to enable FEMS query pushdown for a single query, you set the option Operations in BWA/HANA to 2 in the query properties:
For more information how to enable and disable FEMS query processing for InfoProviders and queries, check the database administration guide for SAP BW on IBM Db2 for Linux, UNIX, and Windows.
You would need SAP BW 7.5 SP 4 or higher for InfoCubes and SAP BW 7.5 SP 8 for DataStore objects. Your SAP Business Warehouse system must run on IBM Db2 10.5 or higher. There might be more SAP Notes that you need to implement, check the current database administration guide for SAP BW on IBM Db2 for more information. Don't worry if the guide says that the pushdown of FEMS queries is still in pilot mode. This has changed with SAP Note 2636129 and will be updated in the guide soon.
Note that the pushdown of FEMS query execution only works if you have Db2 BLU Acceleration, and it only works with flat cumulative InfoCubes and DataStore objects.
I can really recommend reading our database administration guide for SAP BW on IBM Db2 for Linux, UNIX, and Windows and SAP Note 2636129, which provide you with all the details.
BW reporting queries may contain global restrictions and local restrictions for the different cells in the output of the report. We also refer to queries with local restrictions as “FEMS queries”. (FEMS is short for Form EleMent Selection.) Why should you be interested in FEMS queries? Well, depending on where they are processed (application server or database), this might have considerable performance benefits.
Queries with local restrictions might require a lot of data transfer between the database and the application server and additional processing in the OLAP processor, which makes them expensive to process. As you might guess, implementing the direct execution of such queries in the database can therefore speed up performance.
As of SAP BW 7.5 SP 4, queries with local restrictions can be completely processed in the IBM Db2 database. So far, we’ve offered this as part of a pilot program. Now direct execution of FEMS queries on the database is generally available with SAP BW 7.5 SP 4 and higher. So, if you are an SAP BW customer, this new feature might be worth a look.
When should you push down the execution to the database?
Direct processing of FEMS queries in the database often speeds up performance, but it depends on the amount of data that is transferred between application server and database.
It’s faster than the traditional processing when the FEMS restrictions are defined on additional characteristics that do not occur in the output report. Then the amount of data transferred between database and application server to evaluate the FEMS restrictions is considerably reduced. If the amount of data transferred between application server and database cannot be reduced, then a pushdown to the database does not make sense.
How do you implement the pushdown?
Let me guide you through the process: First of all, you need to make sure that the technical prerequisites are met (see below). Then it's pretty straight-forward. You can do the following:
- Enable the entire system for FEMS query processing in the database
- Disable it for selected DataStore objects and InfoCubes
- Disable it for selected queries.
First of all, you need to set the RSADMIN parameter USE_FEMS_IN_DB to X in program SAP_RSADMIN_MAINTAIN:
This enables FEMS query processing in the database. Now you can decide whether you want to disable FEMS query processing for selected DataStore objects, InfoCubes, or queries again.
If you want to enable FEMS query pushdown for selected InfoProviders, you do this in the Data Warehousing Workbench (transaction RSA1). For queries, you use transaction RSRT. For example, if you want to enable FEMS query pushdown for a single query, you set the option Operations in BWA/HANA to 2 in the query properties:
For more information how to enable and disable FEMS query processing for InfoProviders and queries, check the database administration guide for SAP BW on IBM Db2 for Linux, UNIX, and Windows.
What are the prerequisites for the pushdown?
You would need SAP BW 7.5 SP 4 or higher for InfoCubes and SAP BW 7.5 SP 8 for DataStore objects. Your SAP Business Warehouse system must run on IBM Db2 10.5 or higher. There might be more SAP Notes that you need to implement, check the current database administration guide for SAP BW on IBM Db2 for more information. Don't worry if the guide says that the pushdown of FEMS queries is still in pilot mode. This has changed with SAP Note 2636129 and will be updated in the guide soon.
Note that the pushdown of FEMS query execution only works if you have Db2 BLU Acceleration, and it only works with flat cumulative InfoCubes and DataStore objects.
You want to know more?
I can really recommend reading our database administration guide for SAP BW on IBM Db2 for Linux, UNIX, and Windows and SAP Note 2636129, which provide you with all the details.
- SAP Managed Tags:
