Make the most out of Query Execution – Part 1.
The approach for the same has already been detailedin a Execute BW query using ABAP Part I by Durairaj Athavan Raja – this is a very nifty blog which is extremely useful to many. I have used the same in many scenarios. I am just building on top of the existing knowledge base by identifying possible usage scenarios for the same.
Both the Query using ABAP and Query as a restful service can be further enhanced upon by using it for other purposes. I am sure that most of you must have come across this blog some time or the other but then thought that I could make better use of the same by applying the same principles here over a wider variety of scenarios and also identify possible reasons why people would want to run queries using ABAP instead of BEx.
There are many times when we feel the need to use the output of a query but then the options available seem limited. Some of the options where a query output can be used are :
Analysis Process designer – here the query output can be used to send the output to another table for further processing.
Some of the typical uses of APD are :
- Retraction to the source system
- Multiple aggregation of data using query output
- Data Mining
- Possibly increasing query performance by persisting large and performance hungry queries.
All this is known to us but then the APD is kind of hard to debug as it runs – we cannot find out how we can improve the performance further since the whole APD works more of a black box with certain dials about it affording possible controls , the full effect of which can be seen only in production and each small change necessitates a transport.
Let me start by saying that this blog is not about what an APD is or what it does – there are enough blogs and articles and SAP help content about it. There are other ways by which Scenarios 1 , 2 and 4 can be realized without the use of an APD which are :
Using function modules and RFC enabled function modules.
It all starts with this blog written by Durairaj Athavan raja
The standard function module for executing a query is the RRW3_query_view_data. Many people would also know this as the function module which is also used for the query as a web service example for widgets and Xcelsius. Well , the same can be used instead of an APD also.
We need to retract some data from BW back into the source system – this is typically done for CRM systems to be able to analyze the data better. The same can be done otherwise by having a function module created in BW which in turn calls the
RRW3_QUERY_VIEW_DATA function module.
Before doing this lets explore the function module parameters which are:
E_AXIS_INFO – AXIS information in terms of rows and columns – number of rows / columns etc
E_CELL_DATA – data in the cells
E_AXIS_DATA – Data for the axis
E_TXT_SYMBOLS – not sure of this one….
Of the above you will notice that when you execute the FM , you will be able to see the contents of E_CELL_DATA and find that this has the query output. Also the function module has ways by which parameters like queries , variables etc can be passed to get the output. Passing Variables to Function Module
This gives you a flexible way of passing parameters to a query and then executing the same and also affording the possibility of including the same into a program with greater control over variants etc.
Documentation on query as a restful service also illustrates the usage of parameters that can be passed to this function module.
In the blog series I will outline steps by which you can perform the query as a datasource without using APD for pushing data into a transaction / direct update DSO and also look at the using the function module to automate your upgrade testing for BW queries.