How to trouble shoot poor performance of BO reporting tools?

In this blog post we will discuss on how to trouble shoot performance of business objects reporting tools like Voyager, Web Intelligence (Webi) etc. when running SAP BI reports on these tools.

SAP has provided MDX interface for third party tools to access the data stored in SAP business intelligence systems. MDX language is a standard developed by Microsoft to access data from cubes (multi dimensions), it is not SQL. All of BO tools use MDX interface to fetch data from SAP BW systems.

The performance of MDX queries on SAP BW is mediocre at best. Same report that takes seconds in bex analyzer might take several minutes in MDX. Because of this, the performance of BO tools like Voyager, Webi is not good with SAP BW.

However, there are improvements with each upgrade on both BO world and SAP world. SAP recommends to upgrade to Enhancement pack 1 + Support Pack 3 for BI 7.0 and XI 3.1 and Service pack 2 on BO side. There are further improvements in fix pack 1 for service pack 2 which will be released in October 2009.

How to trouble shoot poor performance of BO tools like voyager and BO with SAP BW?

First step in troubleshoot process is to identify the MDX query that is passed for voyager or webi to SAP BW. For this you need to add  following entries to registry file on BO server. Check with your BO administrator if you are not sure where this registry file is located.

---------------------------------------------------------------------------------------------------------------------
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log]
"OverWrite"="Yes"
"AppendPID"="No"
"LogFile"="C:\\Program Files\\Business Objects\\BusinessObjects Enterprise 12.0\\Logging\\MDA.log"
"Location"="C:\\Program Files\\Business Objects\\BusinessObjects Enterprise 12.0\\win32_x86\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules]

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\APIMODULE]
"Verbosity"=dword:0000000a

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\APIMODULE\Components]

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\APIMODULE\Components\INFO]
"Verbosity"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\ASSERTION]
"Verbosity"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\RORMODULE]
"Verbosity"=dword:0000000a

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Log\Modules\SAPMODULE]
"Verbosity"=dword:0000000a
"MDX Query Log"="C:\\Program Files\\Business Objects\\BusinessObjects Enterprise 12.0\\Logging\\SAPMDX.log"

[HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\MDA\Asserts]
"Model"=dword:00000001
---------------------------------------------------------------------------------------------------------------------------------------------

MDX queries will be written to to SAPMDX.log file after adding above entries to registry file. There is no need to reboot the server after adding these entries. You can run your problematic query in voyager and see and find corresponding MDX query from the log file SAPMDX.log. A sample MDX query looks like below query

[CUBE/BWQUERYTECHNICALNAME]: SELECT HIERARCHIZE( UNION(  { [0MATERIAL].[LEVEL00].MEMBERS } , [0MATERIAL].[All].CHILDREN  ) )  DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , HIERARCHIZE( SUBSET( [0MATERIAL__0MATL_GROUP].[All].CHILDREN , 2, 1239 )  )  DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM [CUBE/BWQUERYTECHNICALNAME] WHERE [Measures].[4EVEXRAL545HFCHCV929ZCVUK] SAP VARIABLES [Z_PRDFR] INCLUDING [0FISCPER].[012008001] [Z_PRDTO] INCLUDING [0FISCPER].[012008012]

You can run this MDX query in MDXTEST transaction in SAP BW to see how it runs there. Please see below screenshot.


If your MDX query runs slow in MDXTEST transaction, it means that there might be a bug in SAP BW , search for OSS notes and upgrade to latest patch. It is also possible that voyager is not sending optimized MDX query, check with BO support for this.

Leave a comment if you have any questions, I will try to answer them.


Comments (2)

ralvarado
Said this on 4-27-2011 At 02:26 am

Hi i want to know how to run a mdx query in mdxtest!!

I try to run my query doing the same that you have in your photo but it show me the following message:

 "MDX statement not valid in: The name of my bw query".

Can u hepl me please??

Robert Pinillos
Said this on 6-9-2011 At 10:43 am

Your post was written with regards to BusinessObjects Windows installation but do you know how to activate the log files for MDX on a BusinessObjects Linux installation ? Thanks....

Post a Comment
* Your Name:
* Your Email:
(not publicly displayed)
Reply Notification:
Approval Notification:
Website:
* Security Image:
Security Image Generate new
Copy the numbers and letters from the security image:
* Message:

Email to Friend

Fill in the form below to send this blog to a friend:

Email to Friend
* Your Name:
* Your Email:
* Friend's Name:
* Friend's Email:
* Security Image:
Security Image Generate new
Copy the numbers and letters from the security image
* Message: