The new SAP Management Console (SAP MC) in SAP BusinessObjects Planning and Consolidation, version for Microsoft offers several useful features for analyzing the Web server, database, and logging. However, it does not offer monitoring for some processes, including monitoring the size of write back (WB), Fac2, or fact tables of SAP BusinessObjects Planning and Consolidation applications. It also does not monitor record level locking that may occur in Data Manager scheduled jobs or standard tasks such as Lite Optimization or Currency Translation.

To fill these gaps, SAP BusinessObjects Planning and Consolidation administrators can use my Excel-based performance monitoring tool on a day-to-day basis to monitor performance. You can see how to download, customize, and run this tool.

The monitoring tool focuses on the following areas:

• Fact table sizes (write back, Fac2, and fact) and indexes for a given SAP BusinessObjects Planning and Consolidation application set
• The locks on Data Manager, Send Governor service, Lite Optimization, admin optimization, and Currency Translation
• Backups on the SQL SAP BusinessObjects Planning and Consolidation application set database and audit database
• Any SAP BusinessObjects Planning and Consolidation-related SQL database jobs
• Scheduled jobs
• SQL users, sessions, and process (a SQL standard stored procedure to list information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine)

Prerequisites to Configuration

You need these software components:

• Microsoft Excel 2003 or newer to run the Excel-based performance monitoring tool
• SQL database user ID and password to access database information
• Visual Basic (VB) and SQL script knowledge for enhancing or modifying the tool
• Access to SAP Business Objects Planning and Consolidation, version for Microsoft 5.0, 5.1, 7.0, or 7.5 clients to fix the issues discovered by the monitoring tool.
• Microsoft Outlook to send and receive emails from your laptop or desktop

Now I will show how to configure my Excel-based performance monitoring tool and customize it for any SAP Business Objects Planning and Consolidation application environment. It is designed for a Microsoft platform and I have tested it with versions 5.x and 7.x. You configure and use it by taking these four steps:

• Step 1. Download the monitoring tool
• Step 2. Customize the settings and email addresses
• Step 3. Execute the tool
• Step 4. Understand the content and resolve the issues

Step 1. Download the Monitoring Tool

Click this link to access the monitoring tool. The tool is designed to use in Microsoft Office Excel, and is developed with a combination of VB and Microsoft SQL scripts. Modification to the VB code embedded in the tool requires careful consideration of Excel cell reference (which refers to content from another cell) and Excel conditional formatting, which is used to highlight with different colors and fonts. Modification to the VB code in Excel tool is restricted with password protection, preventing from accidental changes and typo. Customization is required before executing the tool.

Step 2. Customize the Settings and Email Addresses

The Excel-based tool is customizable to your SAP Business Objects Planning and Consolidation environment. The prerequisites for customization include a user ID and password to access the application’s database. Work with your database administrator to have a user ID created and to be granted access with the SQL roles db_owner and public. The roles db_owner and public are SQL authorizations that are required to access database-level information from SAP Business Objects Planning and Consolidation and SQL servers. Access to the database can be restricted to read-only permission. It is advisable to have read-only access to the SQL database so that unauthorized user cannot update the database.

The first thing to do after getting database access with a user ID and password is to set the parameters according to your company’s Business Objects Planning and Consolidation environment. Figure 1, section 1, shows the Settings Excel tab.

Figure 1

Performance monitor Excel tool

Figure 1 has a lot of content, but once you understand each section, it is easy to analyze and resolve any problems that the monitoring tool identifies in your environment. The balloon numbers in Figure 1 are referred to as sections in my steps:

• Section 1: Main dashboard Excel sheet tab (Email addresses tab and Settings tab are two sheets that are customizable)
• Section 2: Relational OLAP database records size
• Section 3: Multidimensional OLAP database records size 
• Section 4: Application locks
• Section 5: Database backup status
• Section 6: Scheduled jobs
• Section 7: SQL session information

As mentioned earlier, the first thing the user would do is to have a user ID created to access SAP Business Planning and Consolidation. In this example it is referenced as bpcadmin with password adminAccess! Likewise, you may have a user ID password from your SQL database administrator to access the SAP Business Planning and Consolidation database.

You need to customize Figure 2 next. You can change the configuration parameter values by unlocking the setting. To do so, click the Unlock button. To validate the settings (column B, rows 3 to 11), click the Validate button. To lock the settings, click the Lock button.  The lock and unlock features are embedded to protect the values entered by the user and protect the settings from accidental typographical errors.

Figure 2

Performance monitor Settings tab

Based on your application version, select the desired SAP Business Planning and Consolidation version from the drop-down list for cell B3 under the Settings tab. Enter the Database Server, Appset name, SQL user ID, and password. The tool is designed to have a maximum of eight SAP Business Planning and Consolidation applications. Enter the applications on row 9, columns B, C, and D, respectively.

The Send email switch can be set to No or Yes based on whether you want to send the information to the email addresses. You set up the addresses in the EmailAddr tab later. There might be one or more users on a team assigned to monitor the applications. In our company, four users support the application, including the manager, and they all want to receive the emails periodically. Therefore we set up four email addresses under the EmailAddr Excel tab. Once validation is complete, you are ready to test the tool.

As you see in Figure 3, you can have a maximum of 10 email recipients who can receive the status email whenever the tool runs. To get to this screen, click the EmailAddr tab. The email content is dynamic with whatever text you customize. To change the email settings, unlock the tool, make your change, and lock it again from changes.

Figure 3

Email address settings tab

The only customizable parameters in Figure 3 are the email address and the email content text, which is highlighted. The email content is generated based on the issues found during the execution of the tool (Figure 4).

Figure 4

Performance monitor sample email

Step 3. Execute the Tool

After downloading my Excel-based monitoring tool in Step 1 and changing the settings according to your application environment in Step 2, open the monitoring tool. An Excel security warning may pop up (Figure 5). Normally, an Excel file with VB code displays a warning message. If you disable the macro setting, then the VB code embedded in the tool may not work.

Figure 5

Normal security alert in Excel

Also, if the security is set too high under the Excel setting for macros, the tool may not work. Have your Excel security set appropriately to run the macro in the performance monitor tool. Check the Enable this content button to enable the macro.

As you see in Figure 1, the Dashboard tab shows all seven key information items about SAP Business Planning and Consolidation and database activities. After validation is complete, click the Refresh button on the Dashboard tab screen (Figure 6) and wait for the content to refresh. The system opens a Microsoft Outlook pop-up window (Figure 7). Click Yes to send emails and No to cancel the sending.

Figure 6

Click the Refresh button

Figure 7

Microsoft Outlook email pop-up window

Step 4. Understand the Content and Resolve the Issues

Figure 1 shows all the content refreshed by the tool. Based on the amount of transactional data in SAP BusinessObjects Planning and Consolidation, it is important to monitor the record sizes and keep the system optimized for maximum performance. Based on my experience, I categorize the issues into five areas:

• Table size, including WB, Fac2, and fact table sizes
• Locks, including Data Manager, Lite Optimization, Send Governer, and Currency Translation locks
• SQL database, including database backup performed in the last two days and SQL jobs related to SAP Business Planning and Consolidation
• Scheduled packages, including Job by Application, Package ID, Start Time, End Time, User Cancel, Status (Completed, Cancelled, Running, Warning), and schedule name in the last two days
• SQL process activities, including SQL WHO, process activity by SPID, ECID, Status, Login Name, Block, Command, and Request

Let’s look in detail section by section. Section 2 in Figure 1 is about WB and Fac2 table size (also shown in Figure 8). If anything goes over the set limit, execute the Lite Optimization task during the day. The Fac2 size is not considered an issue. However, the Full Optimization task needs to be executed at least once a day.

Figure 8

Dashboard tab with WB and Fac2 table size

Section 3 in Figure 1 details the FACT table size and index size (also shown in Figure 9). This shows the size of the SAP Business Planning and Consolidation app set. No action is required.

Figure 9

Dashboard tab for Fact table size

Section 4 in Figure 1 shows information about locks (also shown in Figure 10).

Figure 10

Dashboard tab for locks

During the Data Manager activity, the application may be in locked status for some reason. The data load will not be successful until the locks are removed. The locks are stored in a corresponding lock table. Within SAP Business Planning and Consolidation, the locks may occur in Data Manager, Lite Optimization, or Send Governor. FX Translation is the standard Currency Translation task.

Following are more details about the locks:

• Data Manager locks are stored in table lck<Appname>. Normally you would expect to see zero if there are no activities. If the locks stay for long and a user complains about records not getting into SAP Business Planning and Consolidation, then you can consider analyzing the Data Manager lock table.
• Lite Optimization locks are stored in tblAppOptimize. Normally you would expect to see zero. Any value other than zero prevents the table from optimization. You may need to reset the value to zero if Lite Optimization does not work.
• Send Governor locks are stored in sgData<Appname> in version 5.0x and sgData0<Appname>, sgData1<Appname> in version 7.x. Most of the time you would expect to see zero, if everything is normal. You may consider clearing the Send Governor locks and restarting the windows Send Governor service if the locks stay for too long. If the users are seeding numbers to the application, several hundred records may end up in the Send Governor queue. It should become empty within a few minutes, although sometimes it can take as long as 30 minutes.  If the user constantly seeds numbers, then the queue may be busy for a while. In a nutshell, if no users are seeding numbers, then the queue should be empty. The Send Governor service is located in the application server under services. Microsoft Windows standard services are located under the Admin Tools section in the Windows server.
• FX translation is normally handled internally using the Source field in all three WB, Fac2, and FACT tables. Anything other than zero in this field is considered an issue. You need to carefully analyze those records, possibly remove the bad records, and perform the FX translation to fix the issue.

Section 5 in Figure 1 is about SQL database backup and SQL jobs (also shown in Figure 11) This section is informative and no action is required.

Figure 11

Dashboard tab for database backups

Section 6 in Figure 1 is about scheduled packages (also shown in Figure 12). This section is informative about any scheduled jobs. The statuses Complete, Warning, Running, and Cancelled are the most common. Attention is required for any job that is taking a longer time or has the status of Cancelled. (Based on the user environment, if the scheduled job normally takes 10 minutes and the current status shows it has been running for more than 20 minutes, then it is considered a long time.)

Figure 12

Dashboard tab for scheduled jobs

Section 7 in Figure 1 is about SQL process (WHO) (also shown in Figure 13). WHO is SQL database terminology that refers to the process and sessions. This section is informative and little action is required to kill any unused process. Careful consideration is required when killing the SQL jobs. Only database administrators have knowledge of the unused sessions and processes in the server. This part is informative for the administrator to know the different sessions that are consumed for SAP Business Planning and Consolidation.

Figure 13

Dashboard tab for SQL WHO status

Shameel Mohamedali

Shameel Mohamedali is a senior BI/BPC/EPM independent consultant and lead who specializes in financial planning and consolidation applications and management dashboards. He holds an MS degree in computer applications. He started his career as a programmer analyst in the manufacturing industry, and then moved to different positions during the last 16 years. During that time, he has worked with Big-5 companies in the pharmaceutical, medical and life science, oil and gas, and automotive industries. He is currently working on a SAP implementation project with SAP Business Planning and Consolidation, version for SAP NetWeaver, in Pennsylvania. 

You may contact the author at shameelm@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.