The new SAP Design Studio 1.5 is packed with a lot of performance enhancements and new features. The most notable performance enhancement is the ability to load queries in parallel. In the earlier versions of SAP Design Studio, data sources of an application were executed in sequence thus resulting in longer initial load times. With SAP Design Studio 1.5, we now have an option to load queries in parallel by assigning them to different processing groups. One of the main advantages of this feature is that it reduces the initial load time of the application to a great extent. In addition, it also improves the application performance whenever the result sets of data sources are retrieved. However, this new feature processes queries as groups and involves some cost in terms of server resources/sessions.
When parallel query processing is enabled,
- Variable unmerge will be applied across processing groups. Queries that belong to different groups cannot share variables.
- Each Processing Group will consume one session in the backend system. For example, an application with 10 queries split in to 5 processing groups will use 5 sessions in the backend system.
- If Traces/Profiling is enabled, individual traces will be created for each processing group.
NOTE: This feature is available only in SAP BusinessObjects BI Platform.
Now let us take a look into the how-to(s) and the info(s) related to parallel query processing.
Steps to enable Parallel Processing
Create a sample application and add queries as desired. I have made use of 6 different BEx queries based on BW data source here (as seen in the screenshot). Each of these queries bring in retail data.
Select the first Data Source, navigate to its properties, and select the property “Processing Group” under “Data Binding”. Under this property, you can either enter a name of your choice to define a new processing group under that name, or you can use the dropdown to select an existing Processing Group (if you already have some defined in the application). Here, we will go ahead and create a new processing group.
Enter the name of the “Processing Group” in this column (I have named it as ‘ONE’ in this example).
Repeat Step 2 for the remaining Data Sources – you can either create a new Processing Group for each Data Source, or select one of the previously created groups as well. I have gone ahead and created a separate Processing Group for each of the Data Sources EXCEPT PQRY_03 and PQRY_04 – both of which have been assigned to the same Processing Group “THREE” (as seen in the screenshots below).
Now assign queries to new/existing processing groups as per performance requirements. As mentioned earlier, each processing group will consume one session on the backend server. So each processing group will execute with its own memory and CPU cycles.
Now, select the application properties and disable “Variable Merge” by setting “Merge Prompts” to “False”. This will enable all the processing groups to execute in parallel. This is because queries that belong to different processing groups cannot share/merge variables.
Now the processing groups will execute in parallel and this improves the application performance by reducing the initial load time.
Note: If processing groups are used to enable parallel processing and variable unmerge is not enabled, then the application will use sequential load and the following error will be thrown.
As mentioned earlier, each processing group will use one session in the backend server. If parallel processing is not enabled, then only one backend session is used for the entire application irrespective of the number of queries.
When the sample application shown above is executed in sequential mode, only one session is consumed on the backend server. Below is a screenshot from the backed server, listing the session details of an application executed in sequential mode.
When the sample application shown above is executed in parallel mode, five sessions are consumed on the backend server and all of them are started at the same/closer timestamp.
When profiling is enabled for applications that use parallel query processing, separate traces are created for each processing group.
Loading Data Sources on Demand:
We can all load these data sources on demand through scripting.With respect to processing group attributes of data sources, a method called loadDataSources() has been introduced in SAP Design Studio 1.5. Earlier we used loadDataSource() method to load a particular data source on-demand (sequential processing). But with the introduction of parallel query processing, we can now load data sources in parallel using APPLICATION.loadDataSources(). This method would be a lot faster compared to calling loadDataSource on each data source.
Query parallel processing seems to be a promising option; however it involves a lot of overheads in terms of server resources and sessions. There are a few factors to be considered while using parallel processing:
Proper sizing of backend server is recommended to handle the memory needs of parallel sessions and session cleanup should be done periodically to close active sessions.
SAP BusinessObjects should be sized effectively to handle parallel query loads in order to ensure that the performance gained on parallel load is not spent on rendering.
Use on Need
Parallel processing can be used when there is a critical need and can be avoided in scenarios where it is not required. This will conserve valuable server resources from being over-loaded.
I hope this blog helps in providing you an overview of this very useful new feature – Parallel Query Processing. Keep following us for more!