OLAP Data Access Logging: Part 1 - Introduction and basic settings - Introduction and basic settings
OLAP Data Access Logging: Part 2 - Modules and Components - Modules and Components
OLAP Data Access Logging: Part 3 - API call timings - API call timings
Part 4 - Query logging
OLAP Data Access Logging: Part 5 - Log Formatting - Log Formatting
OLAP Data Access Logging: Part 6 - Assertions, Unix and 64 bit Windows - Assertions, Unix and 64 bit Windows
OLAP Data Access Logging: Part 7 - Choosing the correct settings and example defaults - Choosing the correct settings and example defaults
Full article also available in SDN's Article section here.
Query Logging
One of the most useful diagnostic tools is looking at what the OLAP Data Access component is asking the OLAP server for. If the OLAP server is Microsoft Analysis Services or other ODBO based data access and SAP BW/Netweaver BI then this is in the form of MDX queries. Is the data source is Essbase then this is in the form of report scripts.
ODBO and SAP
The MDX executed by the ODBO and SAP data drivers, as well as being logged in the main log file, can be logged in a separate log file which just contains MDX queries. To enable MDX logging to a separate file an additional entry must be made in the registry. The additional entry is a string value with the name MDX Query Log. The contents of this string value are the path and name of a file to log the MDX to. The location of the key depends on the data source that MDX logging is required for. For logging MDX sent to an ODBO based OLAP server, the string value is located under ODBOMODULE folder. For logging MDX sent to an SAP BW OLAP server, the string value is located under the SAPMODULE folder.
For example here is what the registry settings look like for an MDA 12 system:
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesODBOMODULE]
"MDX Query Log"="C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0LoggingODBOMDX.log"
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesSAPMODULE]
"MDX Query Log"="C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0LoggingSAPMDX.log"
Adding this to the registry will log MDX queries for ODBO data sources to a file ODBOMDX.log and MDX queries for SAP data sources to a file called SAPMDX.log. Both these files will be located in the following directory:
C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0Logging
Essbase
Although it's not possible to log out the Essbase report scripts to a separate file it is possible to reduce the amount of logging in the main to the point where the Essbase report scripts are one the very few or only things logged out. The Essbases report scripts are logged under the DATASOURCE component under the ESSBASEMODULE module. There are logged out at verbosities of 4 (Info) and higher. The following is an example of the registry settings from an MDA 12 system that would log at this level.
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesESSBASEMODULE]
"Verbosity"=dword:00000000
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesESSBASEMODULEComponents]
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesESSBASEMODULEComponentsDATASOURCE]
"Verbosity"=dword:00000004
Query Timing
For the MDX query logging there is an additional setting that can be added to log out the time a query took to execute. This can be turned on by adding an additional DWORD to the registry called MDX Query Clock. This is located in either the ODBOMODULE or SAPMODULE folders. A value of 1 will log the query times and a value of 0 will turn the query time logging off. Here is an example for a MDA 12 system.
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesODBOMODULE]
"MDX Query Log"="C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0LoggingODBOMDX.log"
"MDX Query Clock"=dword:00000001
[HKEY_LOCAL_MACHINESOFTWAREBusiness ObjectsSuite 12.0MDALogModulesSAPMODULE]
"MDX Query Log"="C:Program FilesBusiness ObjectsBusinessObjects Enterprise 12.0LoggingSAPMDX.log"
"MDX Query Clock"=dword:00000000
This example registry setting has the query clock turned on for ODBO MDX logging and off for SAP MDX logging.
