New WebI feature in BI4.2 – Parallel Queries
I work on the SAP Web Intelligence (Webi) Support team. I want to highlight one of the new features that will be introduced for WebI in SAP BusinessObjects Business Intelligence Platform 4.2 (BI4.2) called Parallel Queries. This allows for parallel query execution to be performed when refreshing WebI documents with multiple data providers.
Prior to BI4.2: Each data provider (query) in a Webi report was run sequentially (when the first query finished executing, the second would run, and so on).
Starting in BI4.2: Parallel query execution is enabled by default for WebI documents that have multiple data providers.
- There is no parallel activity at the refresh preparation (i.e. query script generation, resolution of contexts, and prompts)
- Parallel activities occur on the database at the execution and data fetch part of the refresh process.
- Parallel queries will not be run for dependent data providers.
Parallel queries are supported for the following data provider types:
- Data providers that are based on unv and unx universes.
- Data providers based on SAP HANA Direct Access.
- Data providers that are based on Free-hand SQL and Text Files.
Supported Web Intelligence clients:
- WebI REST SDK
- WebI Rich Client
- WebI HTML and Applet interfaces.
The following screen shot shows what happens before BI4.2 and in BI4.2:
Parallel Query Settings at the WebI level:
- The maximum number of concurrent refresh jobs is set in the WebIContainer_ClientDescriptor.xml file for the Webi Rich Client.
- The maximum number of concurrent refresh jobs is set in the WebIContainer_ServerDescriptor.xml file for WebI running on the server (through BI Launchpad)
- For a default installation location on Windows, the location of the above XML files is as follows:
- C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\config
- The property that is set is called WebiParamMaxConcurrentRefreshJobs.
- By default, the Maximum Concurrent Refresh Jobs is set to 64. This can be set to a value that you find works well for your system. If the value is set to 0, then the parallel data refresh is disabled all together.
The following shows the WebiParamMaxConcurrentRefreshJobs parameter in the Descriptor XML file:
The following describes the behavior at the database connection level relating to parallel queries:
- There can be no more than four concurrent queries running on the same relational connection.
- There can be no more than one concurrent query on the same OLAP connection or text file.
- As of BI4.2 SP01, parallel query setting cannot be changed for each database connection. The setting currently applies to every connection.
A summary of the limitations in SAP BI4.2 are as follows:
- It is not possible to run parallel queries on data providers based on “Direct Access to BEx queries” (BICS).
- It is not possible to run parallel queries on data providers based on “Analysis Views”.
- It is not possible to run parallel queries on data providers based on Excel files.
- It is not possible to change parallel query setting for each database connection. I believe that this will be looked at being incorporated into a support pack later than BI4.2 SP01.
Example of the log file analysis of a parallel query example:
- In this example, a Webi report was refreshed in the Webi Rich Client.
- The WebiParamMaxConcurrentRefreshJobs parameter was set to 64 in the WebIContainer_ClientDescriptor.xml file.
- The webi report is refreshed with 4 data providers that all point to the same relational connection.
- Since there are 4 queries that are all pointing to the same connection which is less than or equal to the limit of 4 parallel queries per connection and since the WebiParamMaxConcurrentRefreshJobs parameter is set to a number of 64 and no other queries are running at the same time as my test, the queries should all be run in parallel.
- Webi Rich Client logging was enabled with “sap_trace_level = trace_debug;” when refreshing the report with the 4 queries running in parallel.
- The .glf log file was saved.
Log File Analysis:
To analyze the log files, I used the FlexiLogReader. This log reader can be used for analysis of BI .glf log files. The FlexiLogReader can be retrieved from the following link:
1. Open the FlexiLogReader by launching the FlexiLogReader64.exe file.
2. Select the Webi Rich Client .glf log file that was created when logging was turned on when refreshing the report with 4 queries running in parallel.
3. Specify the default configuration (PLUGIN: BI 4.x GLF Log Parsing and End to End Analysis (v1.2.0).
4. Specify to load all files that are in the directory of the file that you just selected. In this case, we only have one .glf log file, but if there were more, by selecting this option, all of the files in the current directory would be chosen.
5. When the log files are loaded, notice that one of the ways that the log files are displayed is by “ServerName/PID/TID”. This means that the logs can be broken out by “Server Name = WebRichClient”, Process ID (In this analysis there is only one process ID), and Thread ID (there are many threads). The advantage of this for analyzing refreshes of parallel queries is that there will be one thread that “manages” the creation of all of the refreshes, and a further thread will be spawned for each of the individual refreshes.
6. To find the lines of code that are related to the parallel refresh process, find all of the lines containing the tag [//R]. To do this in the FlexiLogReader, select “Search” – “Search Window” to bring up the search window.
The search term should be added as a tag so that we can filter the logs to only see the entries that include the lines that have the [//R] entry.
Now ensure that you only check the Search Terms [//R] tag and don’t check any of the other ones. After that, select “Show Only Log Entries Linked To Selected Tags”.
Now, show an extra column that refers to the “Thread ID”.
First off, find out what thread ID is associated with the first row that was returned in the [//R] search.
From the above, we can see that the thread ID that is associated with that first row is 9028.
Now, add the “Thread ID” column to the columns that are shown in the trace summary. To do that, select the first row, select “View”, and select “Choose Columns To Show”.
In the “Define Visible Columns” screen, check the “9028” column which stands for the thread ID column and then select “OK”.
The result is that we now have “Column 3” showing which is the “Timestamp”, “Column 12” showing which is the “Thread ID”, and “Column 36” showing which is the “Text”.
7. By looking at the logs that are filtered by [//R], we can see that there are the following 5 different threads:
9028 – This is the thread ID of the “main” thread that manages the refreshes of the 4 different data providers that are refreshing in parallel.
11212 – This is the thread for the refresh of the first data provider (DP0).
11208 – This is the thread for the refresh of the second data provider (DP1).
11776 – This is the thread for the refresh of the third data provider (DP2).
12028 – This is the thread for the refresh of the fourth data provider (DP3).
8. In the main thread (thread ID = 9028 in this case), the following shows the start and finish of each of the queries that are being run in parallel as separate threads.
9. In FlexiLogReader, we can take the thread ID of one of the threads for one of the query refreshes and filter based on that thread ID. So, for example, if we take the thread ID of the first query that is refreshed (thread ID = 11212) and filter based on that thread ID, we see the following (as shown in the below screen shot). We will leave the [//R] search term selected so that in the summary screen, we will see the logs for thread ID = 11212 as well as the logs for the [//R] search term. By filtering on the thread ID, you can see all of the activities that went on during the refresh process. You can see when the thread starts, which query it is referring to, and when the thread end (when the refresh is finished for that query).
You can analyze the other query refreshes in the other threads using the same process as shown above.
I hope that this blog has highlighted the Parallel Queries feature that will be available in BI4.2 including the data providers that it is supported on, how to configure it, how it should lead to quicker refresh times, and how the log files can be examined effectively using the FlexiLogReader. It looks like this will be a great feature!
Kudos! A very detailed blog that describes how to troubleshoot one of the most impactful new feature of Web Intelligence with 4.2!
HI Robert ,
One of awaiting features. very well explained .
Do you know why this feature is not extended for Direct Access to BEx queries” (BICS) ?
It is mentioned that ,
so ,will this be applicable if we use multiple OLAP connections pointing to same BW ?
Thank you very much for information 🙂
Webi parallel queries for BICS OLAP is planned for a future SP on the BI4.2 codeline. we are aiming for SP03 but this is in the planning stage and could be delayed.
Thank you Henry for heads up 🙂
any plans for parallel prompts or LOV values retrieval?
Thanks in advance
I am not aware if/when parallel prompts of LOV values retrieval is planned for.
As directed above I did change the parameter from 64 to 0 as we want to disable the feature at present, however even after restarting SIA I still see that in WEBI Server Metrics the parameter still reflects as 64.
Is is a known issue or it can be ignored?
Hi Henry- Do you know if parallel refresh for BICS OLAP is implemented in 4.2 SP03?
Great job Robert! Very helpful and insightful.
Very well done Robert! Detailed as usual and gives some great, practical tips on how to use the new log reader too!
Very informative article.
Has anyone had issues in their environment with the default value of 64 WebiParamMaxConcurrentRefreshJobs? We have a webi report with 14 data providers and at least 10 of these use the same relational database connection. The report is scheduled to run every 5 minutes. We have noticed that the schedule fails intermittently with "processDPCommandsEx" errors. The webi processing servers crash every time the schedule runs irrespective of the outcome of the schedule execution. We disabled the parallel data provider refresh by setting the WebiParamMaxConcurrentRefreshJobs to 0. The schedule runs without error and no webi processing server crashes observed.
Another test was to increase the WebiParamMaxConcurrentRefreshJobs from default value 64 to 84(a random number), this sort of contained the issue in Test environment but doesn't seem to be working in the Dev environment.
We have 8 cpu cores, 64 gb ram, windows server 2012 r2 64-bit OS running SAP BI 4.2 SP1 Patch level 2. How do we determine the correct value for the WebiParamMaxConcurrentRefreshJobs parameter? Are there any best practice guidelines recommended by SAP?
please work with technical support to troubleshoot your issue.
the pinch here is that you are running a version which was used during the ramp-up program ONLY.
the product went GA with SP02... so that's where fixes are ported to going forward .. not SP01 (which isn't maintained beyond the pre-release program)
I've already logged a ticket with SAP Support and in their response they gave me a link to this SCN article. 🙂 Just wondering if there is a way to measure the consumption of the
WebiParamMaxConcurrentRefreshJobs parameter using the Webi Admin Tool?
I've posted the same questions to SAP Support as well.
We've been told that a lot of bugs have been fixed in the GA release but upgrading to the GA release means a lot of planning, packing of client tools, release management, it's a very time consuming task for our organisation. So we'll have to work with the ramp up version for now.
Thanks for sharing, Shubha,
We installed 4.2 SP4 last week. The scheduled report batch ran just fine the first few days, but the night to Monday, when we have the heaviest scheduling load, all of our webi processing servers went into status "Failed", causing the rest of the scheduled batch to fail and stopping end users from accessing documents.
I will try to disable the parallel querying for scheduled jobs on all the webi processing servers and see if that makes things better!
We are planning to upgrade to BI 4.2 SP4 Patch 1.
Were you able to identify the root cause? Was it due to parallel query being enabled?
We have a WEBI Report with 10+ Data Providers all pointing to 1 Universe with 1 Relational Connection.
Only 4 queries are hitting DB at a point in time. I mean only 4 Queries are running in Parallel.
I reached out to SAP Support and they refer to this blog and concluded that only 4 Queries run in Parallel at Max.
I also tried by updating WebiParamMaxConcurrentRefreshJobs parameter = "128". However 0 Queries are running in Parallel.
We are on SAP BI 4.2 SP02. Can anyone confirm that we cannot run more than 4 Parallel Queries in Parallel in WEBI 4.2 on a Single Relational Connection at a time. Our goal is to have at least 8 Queries/Data Providers to run in Parallel.
My understanding is that the maximum number of queries that can be run in parallel on the same relational connection is 4. So, if you are seeing that you are only able to run 4 queries in parallel against one of your connections, then that would be correct.
Any plans to allow more queries to run in Parallel in BI 4.2 w/SP3? Any way to customize some sort of XML file to get support for more queries?
Any further details would be appreciated.
This setting is per connection per report or only per connection? What I mean is if we have a report which is already refreshing with 4 parallel jobs and someone fires a different report on the same relational connection, all the jobs on the new report will have to wait till the time slots are freed by the 1st report?
Well documented and explained, thanks Robert.
Have a couple usage questions ...
To disable parallel queries, we just configure that concurrent value to 0 in the xml file and restart the SIA, right?
If the thread value is 64 threads, and the max for a concurrent query is 4 then we can conceptually run 16 reports that each had 4 parallel queries and anything after would be queued until threads free up. If there was a mix of reports and queries then it can total 64 concurrent queries at one time to various sources, even 64 reports with 1 query each.
If this is disabled, set to 0, would reports run the same way as they did in 4.1 where each report can run independent and the queries are executed one at a time, but at same time, or would they all be queued such that if there are 16 reports running only 1 query of those 16 is running and 15 are queued?
Finally, is the Parameter setting in the xml file set at the Node (SIA) level, or is it a per-webi process?
Thanks in advance,
You asked: To disable parallel queries, we just configure that concurrent value to 0 in the xml file and restart the SIA, right?
Answer: Correct. Or from the Webi Rich Client, you would set the WebiParamMaxConcurrentRefreshJobs parameter to 0 and the restart the Webi Rich Client.
You asked: If the thread value is 64 threads, and the max for a concurrent query is 4 then we can conceptually run 16 reports that each had 4 parallel queries and anything after would be queued until threads free up. If there was a mix of reports and queries then it can total 64 concurrent queries at one time to various sources, even 64 reports with 1 query each.
Answer: The maximum number of queries that can be run in parellel is 4 per CONNECTION (not report). So, if the 16 Webi reports were each running based on a different connection, then conceptually if each had 4 queries running (based on this same connection), then they coukd all run in parallel and anything after would be queued until threads freed up. The key is that the maximum is 4 per each connection. So, conceptuially you could have 64 reports that each had 1 query running as long as you had 16 connections with 4 reports based on one connection, the next 4 reports based on another connection, etc.
You asked: If this is disabled, set to 0, would reports run the same way as they did in 4.1 where each report can run independent and the queries are executed one at a time, but at same time, or would they all be queued such that if there are 16 reports running only 1 query of those 16 is running and 15 are queued?
Answer: My understanding is that if this is diasabled (set to 0), the Webi reports would run the same way that they did in BI4.1 where each report can run independent and the queries are executed one at a time, but at the same time.
You asked: Is the Parameter setting in the xml file set at the Node (SIA) level, or is it a per-webi process?
Answer: This is set at the entire system level - not for each Webi Processing Server.
If you find that if any of what I have mentioned above is contrary to what you are seeing, then I would suggest that you create an incident with the Webi Support team because what I have mentioned above is what my understanding is of how this behaves.
I hope that this helps.
Sorry to revive an old thread.
I just did a 4.2 SP5 installation and I know they had introduced the setting to specify the number of threads per document inside the WebIntelligenceProcessingServer instead of using the xml files. They also added a checkbox to disable it for schedules. So it is now per webi server and not system-side.
I am having issues with the data source so I tried to set it to 0 to disable it and I am getting the message "Maximum Parallel Queries per Document must be within one of the following ranges (1,64)" So does this mean 1 is disabled now instead of 0? I always assumed that a value of 1 would run 1 at a time across all reports, I guess in this case now per WebI Process, and queue the remaining ones.
Thanks in advance,
Great job Rob.
Your blog helped me a lot in explaining Parallel Query Processing to a customer.
Keep up the good work 🙂
Hi Robert. In BI 4.2 SP3, one can change the number of parallel-queries also in CMC (WebIntelligenceProcessingServer). What is the relation between the settings in the XML-file and what is visible in CMC ?
We have changed the setting in the XML-file but even after a SIA-restart, the change in setting is not reflected in CMC.
Can you please explain ?
Hi, the CMC setting is the way an admin would change it using the Interface, whereas editing the config file is the global override ? (i wouldn't expect it to reflect in CMC either)
It will not reflect in WEBI services itself! Existing WEBI processing services should be stopped and new one should be created! that's the reason I mentioned below to make three major changes in this blog!
I'm facing an issue that I think could be related to this parallel processing:
A client I work for has a new 4.2 DP03 system, with all content having been migrated from a 3.1 SP5 system. There are a load of reports that make use of the 'result from another query' functionality in the query filters - more than once per report. They all run fine on the 3.1 system.
When running these reports, 9 out of 10 times they will fail with an MS SQL ODBC error stating: The following database error occurred: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','..
As these same linked queries often contain custom SQL is it possible that the BOBJ platform is not spotting that the queries are dependent on each other and need to be run in sequence - then when a query that contains code such as: stepareferral.ICSReferralID IN @dpvalue('N', DP2.DO459) is run, it falls over at the ',' because the second DP is not where it should be? The one time it does run successfully could be because the queries just happened to run in the right order?
If you think this is likely what should I look for in the logs and how can I work around it - can I set the number of concurrent queries to 1 in the Webi Processing Servers?
just to confirm - updating the parameter to 0 in the xml file fixed the issue these reports were having
can we know what is path and name of file .
Robert has already described the file and the directory above (in the section immediately underneath the graphic showing parallel versus sequential queries).
However I have since found that -
a: there is a second copy of the file on the server under win64_x64 as well as win32_x86
b: changing either xml file has no effect on queries running in the browser version of Webi - to affect that you need to change the properties of the webi processing servers themselves (change 64 to 1, and disable parallel processing for scheduling).
Sounds like SAP Note 2345997 that's fixed with 4.2 SP04. If your client has access to SAP Support, they can have a look at that Note.
yes it's exactly that, thanks! shame I didn't find that note yesterday when I was onsite - my search terms obviously not precise enough!
Hi Team, I don't think to stop this new feature of parallel query processing only for 1 or 2 reports is nice approach! other solution like redesign report should be planned!
One thing , Is it mandatory to merge the common dimensions which we got from multiple data providers for parallel query execution ?
Do we use same universe as multiple data providers ?
Suppose ,If we don't have common dimension which we got from multiple data providers , how to proceed for parallel execution ?
Kindly give me some example ,If possible .
Three major corrections required in this blog!
Go to <SAP BO installation directory>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\config
I'm corrently work with 4.2 SP04