1 2 3 20 Previous Next

BI Platform

290 Posts

Over the past few years, I've been asked to create or assist in creating various scripts to automate a task in SAP Business Intelligence 4.x (BI4).  Most of these scripts were loose files in the form of .jsp or .vbs that had to be run manually.  Lately, I've taken a personal liking for Program Files which are uploaded and stored within the BI environment.  The last request was for a script that would disable any user that hadn't logged into the CMS within the past 90 days.  Rather than hard-coding the 90 days into the program file, I created it to take the number of days as an argument (or input parameter).  And if the argument is set to 0, then the script will only display the user info without committing any changes.


If you're interested in creating your own Program File and want more information on how to do this yourself, I wrote another blog titled How to Create a Program File in BI4.  The blog describes the steps and requirements for creating a java program in Eclipse as well as provides a project template to start with.




The following script can be used to disable users in bulk that have not logged into the BI system for the past X number of days.  This is equivilant to logging into the CMC manually, navigating to a user's property page and clicking the 'Account is Disabled' checkbox under the 'Attribute Binding' section.


Where to Download

The source code and .lcmbiar file can be downloaded from the following SAP Note#:  http://service.sap.com/sap/support/notes/2097401


How to import the script into BI 4.x


The script can be imported into your BI environment using Promotion Management.  The zip downloaded from the kbase has the .lcbiar file within it.  Follow the steps below to import it using Promotion Management.


  1. Log into the Central Management Console (CMC) as Administrator
  2. From the home page, click on Promotion Management
  3. Click on the Import dropdown menu and choose the option, Import File
  4. The Import from File dialog box appears.  Ensure the 'File System' radio button is selected and click on 'Browse'
  5. Unzip the .lcmbiar file you previously downloaded.  In this box, navigate to the .lcmbiar file and click OK.
  6. There will be a 'new job' tab opened with some of the information from the .lcmbiar file automatically filled out (such as Name, Description, etc).  Select the Destination menu and choose the CMS you'd like to import the Program File into.  You will be prompted to provide permissions for this CMS.
  7. Click Create.
  8. Now that the import job is created, you will need to run the promotion.  Click on the Promote button in the toolbar.
  9. The summary page opens showing exactly which objects will be promoted.  You should see 2 items.  A folder object and a Program object.  Click the Promote button at the bottom of this page.
  10. When the job is finished running, the Status column will show 'Success' as a result.  The job should take less than a minute to run, so if you don't see it succeed within a few minutes, make sure you have an Adaptive Job Server running that contains a Promotion Management Scheduling Service.


Running the Program File


Once the .lcmbiar file is imported (see the steps above), you will see a folder called "Admin Scripts" under the top level root folder.  Be sure to set permissions on this folder accordingly so its not accessible for non-Admin users.  To schedule, follow the steps below:


  1. Navigate to the 'Admin Scripts' folder and right click on the Program File underneath called 'DisableInactiveUsers' and choose Properties.
  2. Under the 'Schedule' option on the left side, choose the Program Parameters option.
  3. Set the Arguments field to the number of days since last logon.  This value must be a positive integer.  ( In other words, 10.5 days will not work. )
  4. Click Schedule.



RECOMMENDATION:  The first time you run the program file, run it with the argument of 0.  This will give a full output for all users and how recently each user has logged in.  The output can be read as-is in text or easily copied into Excel and sorted as needed.  Once you know the value of days old you want to run the job under, set the 'Argument' value accordingly.


Other Info


As with any script, there can be unexpected results.  Test the script first in a lower level environment such as a Test or Development system.  Be sure to have a backup of your CMS system database in case you need to quickly revert the changes.  And lastly, if you'd like to see the source code, the Eclipse project files are included in the zip file attached to the same SAP Note.


The Arguments field by default is set to 0.  This value represents the minimum number of days since last successful login.  The script will query for the SI_LASTLOGONTIME value stored in the CMS repository for each user.  It will determine how many days ago this SI_LASTLOGONTIME was (based on current time where the Adaptive Job Server is running).  Any user that has not logged in within this number of days will be disabled when the script is run except for one condition.  Any user that has never logged at least once will not have a SI_LASTLOGONTIME property and thus will be left unaltered. For these users, the output file will show the equivalent of the Java Date(0) value which is either 1969 or 1970 depending on your locale settings. 


If you leave the days value as 0, then the script will not commit any changes.  In this case it will only output the full user list with their last login date/time and if that user is currently disabled or not.  If any value greater than 0 is used, then the script will attempt to disable any users that match the criteria except for those that have never logged into the CMS at least once.


Once the program file is scheduled, clicking on the successful instance within the history window will show the output results similar to viewing a report instance.




Except for the header info, the data in the output file is in a comma separated value format.  Below is an example of the output file:





If you have a larger user list, opening the csv within excel can make the data easier to work with.


I've written a bit of jsp, vbscript, and java based applications in the past and lately I've really enjoyed writing simple java apps that can be run as a Program File (.jar) within the BI 4.x environment.  A couple examples of these Program Files were shared earlier this year called the 'biUserSessionKillScript' and 'DisableInactiveUsers'.


How to Delete Stale BI4 User Sessions with biUserSessionKillScript

How to Auto-Disable Inactive Users in BI4


In BI 4.x, Program Files can be in the form of a batch file, vbscript, javascript, shell script, or a jar file.  The examples above were compiled as .jar files and below I'll explain what it takes to create your own java Program File.  I've created a template that can be imported into Eclipse to help get you started.  The source code is attached to a Kbase which can be downloaded separately here:


SAP Note 2099941


In this example, its assumed you know how to import a java project into Eclipse and how to export it to a jar file.


The What and Why:

Developing a BI4 Program File has many benefits.

  • For one, your application will be executed by the AdaptiveJobServer which means it will look to the BI4 java classpath used by the AJS.  There's no need to package all the extra dependent jar's as you would a standalone application.  Your import statements will automatically look for and find these libraries in the "C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java\lib" directory assuming you are only using the BI SDK based libraries.
  • Second, a Program File offers all the benefits of historical instances.  But instead of having a Webi or CR report as your scheduled instance, a .txt file is generated in the Output FRS location with all of the System.out.println() information within your program.  This can be beneficial for both debugging your application as well as giving a historical look at whatever your application did when it was scheduled.
  • Lastly, since the program is stored, executed and managed within the BI system, the security for running the application can be set just as any other infoobject.


Attached at the bottom of this article is an Eclipse project template for creating a new BI4 program file.  Feel free to use this as a starting point for creating your own Program File.  Below is an explanation and breakdown of how this application template works.


How it works:


In the top portion of the example you'll find the class definition and main method.  The difficulty in writing a new program file is the application needs to be able to run standalone in order for you to develop it, alter it and debug it within Eclipse.  When the application is run as a program file within BI, the standalone portion of this application will never be executed.  In other words, the main() method is never actually called.  Instead, the Adaptive Job Server calls the run() method directly.


The example starts off with the class definition and the main() method.  The class implements the IProgramBase interface.  This is a requirement for all BI4 program file applications because of how the program job server service calls the run() method directly.


The majority of the main() method code does nothing more than create an enterprise session and pass it to the run() method.  Be careful of what you include in your main() method as this will only be executed when your application is run outside of BI (within Eclipse, via command line or any other means).  All code you want executed when the application runs within BI must be inside the run() method or in another method called from this point down.  Again, think of the run() method as your starting point.


In order to run the application from the command line in standalone mode, you will need to create an enterprise session.  This requires 4 command line parameters passed to your application.  The code below first ensures that 4 values were entered.  It compensates for a 5th value in case something else needs to be passed, but this isn't necessary for the code below to work.



There's not much going on here in the run() method.  In this example, its assigning the username within the enterprise session to a variable and then writing it to the console.  However, every time you output text to console via System.out.println() the Adaptive Job Server will write this to the text file created in the Output FRS when the program file is scheduled.  There's no need for additional code to output your information to a text file.  This is all built into BI and automatically done at schedule time.




Although this really doesn't do much, keep in mind its just an example.  The program will run as-is and output to console the user name held in the enterprise session.  To test this within the Eclipse IDE, you will need to create a Run Configuration.


  1. To do this, right click on the project name within the Package Explorer, choose Run-As > Run Configurations.
  2. Highlight Java Application on the left and click the 'new launch configuration' button.
  3. Under the Main tab:
    • The project name should be auto filled in if you had right clicked on the correct package within the Package Explorer.  If its not, then browse for it and select the project.
    • You'll need to search and select the main class in the 2nd box.  Click the Search button to do this and select the ProgramObjectTemplate classname.
  4. Under the Arguments tab:
    • Add the 4 command line arguments separated by a space.
    • The values specified in the main() method are:  <username> <password> <cms name> <auth type>
  5. Once the Run Configuration is created and saved, you can run the application within the Eclipse IDE and test it.  Once tested, then you'll be ready to export it to jar and import the jar into the Central Management Console.




Import the Program File into the CMC

To import the Program File into the CMC, you'll need to follow these steps:

  1. Log into the CMC
  2. Navigate to a folder where you want to import the file. Right click on the folder, choose Add > Program File
  3. Choose Browse and select the .jar file you exported from Eclipse
  4. Select the Java radio button and click OK
  5. You will now see the new Program File within the folder.
  6. Before you can schedule it, you'll need to set the default Program Parameters.  To do this, right click on the Program File, and select Default Settings > Program Parameters
  7. In the Class to Run box, type the name of the class - ProgramObjectTemplate.
  8. If there were any additional command line parameters after the first 4 we created (user, password, cmsname and authtype), you would set these in the Arguments box. The BI4 Java SDK will automatically create a user session at schedule time based on the user that schedules the program file.  So passing these here isn't required.  The only arguments you would need in the Arguments box are the additional command line parameters passed beyond these 4.  See either of the examples mentioned at the beginning of this blog as both use an additional command line argument.



One of the key concepts to understand in the BI Platform Monitoring Application is the Health State metric.  A number of different aspects of the monitoring application rely on the Health State metric and without a clear understanding of how this is supposed to work, it makes effective monitoring and troubleshooting the application a frustrating task.  In this article, I will describe the concept of Health State in great detail and I will also describe how to correct the Health State in your BI Platform Monitoring application.



Health State Metric


In the Central Management Console, when creating a new watch, there are two types of Health State metrics that can be used as threshold criteria:

  • Server Health State - The Server Health State indicates the health of a particular server.  This metric can be used to understand whether the server is up and running, whether the server is overloaded, and whether the server is still able to take additional requests.  The Health State of the server can indicate to the BI administrator if they need to take action to troubleshoot a problem on that particular server



  • Topology Health State - The Topology Health State indicates the cumulative health of all servers of a particular type (Categories health) and also all servers in a particular server group.  The Service Categories include CrystalReports, Analysis Services, Dashboard Services, Promotion Management Services, Core Services, Explorer Services, Connectivity Services, and SIA nodes





How the value for Health State is determined


In the case of the Server Health State metric, the value is determined by the result that particular server's watch.  Anytime you create a new server manually or use the System Configuration Wizard to create your Adaptive Processing Server configuration, the system will automatically create a new watch for each server using the nomenclature of NODENAME.SERVERNAME Watch.  This is a "system" created watch and cannot be manually deleted.  You may have noticed in the Central Management Console that the system created Server Watches are also displayed for ease of access under CMC -> Home -> Servers --> Servers List.




Health State Evaluation

Depending on value returned by the server's watch formula, the server health will display one of the following five states.


GREENServer health is good and no action is necessary
AMBERServer is slightly overloaded, nearing peak values as defined by the caution rule
REDServer resources are over used, unable to take new requests, or the server is stopped or disabled
DISABLEDThe watch is marked as disabled in the BI Monitoring application.  Select the watch and click the enable button to re-enable the evaluation of this watch
FAILEDThere is an error in the watch formula or the BI Monitoring service is disabled or not running

Topology and Categories Health States


In order to provide the BI administrator a quick path to troubleshoot issues in the BI Platform landscape, the server health states are aggregated into service category health states.  This makes it much more simple to tell if any particular product type is available for the end users that are using the system.  For example, if your BI system mainly processes Crystal Report view-on-demand requests, then it is vital in order to achieve maximum up-time that all the Crystal Reports Processing Servers in the BI landscape are available to process these jobs.  The Crystal Reports category health state depends on the aggregated health state of all the Crystal Reports server watches.  This can be seen by editing the Crystal Reports category watch formula where you will find in the formula the health state of all Crystal Reports servers.




In the case of the Crystal Reports category, all of the servers required to process Crystal Reports are grouped together in the topology map so that you can tell at a glance which server watch may be causing the overall category state to change.




Fixing the Overall Health Watch and the Health State Hierarchy


On the BI Platform Monitoring Dashboard, there is an Overall Health state indicator (also known as the Consolidated Health Watch).  You may have noticed that this is quite often not showing a valid state (Green, Amber, or Red) and instead is giving a state of Failed.  In order to fix this, it is important to understand how this particular Health State is determined, then make the necessary underlying watch formula corrections that this watch is dependent on.  In the monitoring application, there is a large hierarchy of Health State watches and if any of these dependent watches is broken or invalid, the Overall Health will show a state of Failed.  In order to help the BI Administrator to correct their BI Platform Monitoring application and Overall Health, I have created a diagram showing each level in the Overall Health hierarchy which you can use to track down the broken watches and correct the formula. 

In this example, you can see that the Overall Health state is Failed. 



If any of the dependent Health Watches below the Consolidated Health Watch are failed, then the watch in the next level up will also be failed.  Therefore, you must start at the bottom of the hierarchy and correct this watch.  In this example, the server APS 2 has a failed watch, therefore the SIA Node 2 watch is failed, the Enterprise Nodes watch is failed, and so on.




After correcting the APS 2 Health State watch formula, all of the parent watches are now also showing a correct value and the Overall Health is Green (OK).  Note that, after you correct the child watch formula, wait for a few minutes as there is a metric refresh internal of 60 seconds (by default) where the Monitoring Service will update the status of all watches in the system.  In otherwords, the change in Overall Health will not happen immediately after correcting the dependent watches so be patient.




Repairing the Server Watch formulas


When creating a new server or using the System Configuration Wizard, you will find that the automatic routine that handles this is not perfect and depending on which service you are creating, the automatically generated system watch may contain either the wrong server name reference, and in some cases (such as the Connection Server), the wrong metric altogether.  When you edit the watch's danger rule or caution rule you will see in red, the erroneous contents in the formula that needs to be corrected.


A server Health State watch should contain at the very least a check to make sure the server is running.  Depending on the granularity that you desire you can create a two state watch, or a three state watch.






If you want to see a yellow caution state when a server is stopping and starting then you should use a three state watch, if you are only interested in seeing green state for running and red for any other state, you can use a two state watch.  Using the server metric Server Running State, you can easily create a new server watch based on whether that server is available or not.



Server Running State Values
















Running With Errors


Running With Warnings




See below an example of both two state and three state watches that check for server availability.  In this example, my SIA node name is NODE and the server name is SERVERNAME.



Two state watch formula:


Danger RuleNODE.SERVERNAME$'Server Running State‘!=3


Three state watch formula:


Caution RuleNODE.SERVERNAME$'Server Running State'==1 || NODE.SERVERNAME$'Server Running State'==2 || NODE.SERVERNAME$'Server Running State'==4 || NODE.SERVERNAME$'Server Running State'==6 || NODE.SERVERNAME$'Server Running State'==7
Danger RuleNODE.SERVERNAME$'Server Running State'==0 || NODE.SERVERNAME$'Server Running State'==5



Factoring in performance to the server health state


In some cases such as the Central Management Server, the load on the CMS server is used to determine the server health state.  Depending on which type of server you are editing the watch for, there are a variety of different metrics that can be used to determine load.  You may want to also include in your server watch formula some thresholds for these metrics so that the server health state metric is dependent also on how well the service is performing and whether it is able to take on more jobs.

Refer to the BI Platform Administrator Guide for more information on server metrics to determine which metrics are suitable for your BI landscape.


Update 27/11/2014: Please read if you have SAP Data Services and/or SAP Information Steward - see below

Update 26/11/2014: Released today, the updated SAP BusinessObjects BI 4.1 Supported Platforms - see below

Update 24/11/2014: Free-Hand SQL Extension for Web Intelligence for SP05 article released.





SAP has released on Monday November 17th 2014, as planned in the Maintenance Schedule, Support Package 05 for the following products:

  • SBOP BI Platform 4.1 SP05 Server
  • SBOP BI Platform 4.1 SP05 Client Tools
  • SBOP BI Platform 4.1 SP05 Live Office
  • SBOP BI Platform 4.1 SP05 Crystal Reports for Enterprise
  • SBOP BI Platform 4.1 SP05 Integration for SharePoint
  • SBOP BI Platform 4.1 SP05 NET SDK Runtime
  • SAP BusinessObjects Dashboards 4.1 SP05
  • SAP BusinessObjects Explorer 4.1 SP05
  • SAP Crystal Server 2013 SP05
  • SAP Crystal Reports 2013 SP05


This comes five months after the release of SAP BI 4.1 SP4 (SP04) back in June 2014.


You can download these updates from the SAP Marketplace as a Full Install Package or Support Package (Update).


E.g.: Full Install



E.g.: Support Package (Update)



Download Location: Software Downloads | SAP Support Portal



What's New?


The updated What's New document has been released early this time on 06/11/2014.  This is a good read, there are few good new features in this update but the ones that are significant to me are:


  • SAP Lumira integration with the BI Platform
  • Free-hand SQL (FHSQL) for Web Intelligence via the SDK and UI Extension Points*


* I was told at SAP TechEd that a sample will be made available to help us doing this.  This new feature is expected to be out of the box with no SDK required with SP06.  Articles:




There are tons of fixes (356 to be exact).



Supported Platform (Product Availability Matrix)


The SAP BusinessObjects BI 4.1 Supported Platforms (PAM) document has been released on November 26th 2014.







The usual documents have been made available:










Forward Fit Plan


SAP is no longer updating the SBOP Forward Fit Plan so I'm unable to confirm for the moment which updates are included here...  One would hope it's as it used to be and will include SAP BI 4.1 SP04 Path 3...

To be confirmed...

Maintenance Schedule

SAP BI 4.1 SP05 Patch 5.1 (Week 51 - December 2014)

SAP BI 4.1 SP05 Patch 5.2 (Week 4 - January 2015)


SAP BI 4.1 SP06 is scheduled to be released late July 2015 (Week 30 2015).


Source: Schedules for Support Packages and Stacks | SAP Support Portal



Installing Updates

This training server has a clean installation of SP04 with the English language only installed.  This is how long it tool to install everything.

Note: For those who have read my previous post about the release of SAP BI 4.1 SP04, the timings below will seem much quicker.  The reason for this is that I'm using a training server with the same specs but different pre-installations of SAP software...  Or SAP have made things a lot quicker now!




    • SBOP BI Platform 4.1 SP05 Server
    • SBOP BI Platform 4.1 SP05 Client Tools
    • SAP BusinessObjects Explorer 4.1 SP05




    • Windows Server 2012
    • 4x Virtual Processors (Hyper-V)
    • 20 GB RAM




1. As always, the Preparing to install screen takes a while...  about 6-7 minutes for me.


Please wait.png

2. This chart shows the time it took waiting for the Preparing screen to disappear then the installation time.

install time.png

3. As always, when you click Finish, do NOT reboot straight away.  Wait for setupengine.exe to go away in your Task Manager.  This can take a minute or so.


Task Manager.png



Past Articles


For information, I wrote the following articles about previous SAP BI Support Packages:




SAP Data Services and/or SAP Information Steward



Those of you with SAP Data Services (DS) 4.x and/or SAP Information Steward (IS) 4.x installed on your SAP BI 4.1 server will not be able to install this update.


At the "Check Prerequisites" screen you will get the following message:



SAP Note 1740516 is currently unavailable.  It is likely going to say that SAP BI 4.1 SP05 is compatible with SAP DS / IS 4.2 SP04 (to be released).


  • SAP DS 4.2 SP04: no release date yet (probably at the same time of IS)
  • SAP IS 4.2 SP04: release date Week 51, 2014





It's still early days and there are couple of documents that need to be updated but looking forward to have a look at the Free-hand SQL in Web Intelligence!


As always, please share your how it went for you in the comments below.  I'm sure this does help many people.



Please like and rate this article if you find it useful!



Thanks and good luck!



Have you ever wanted to use a CSV delimiter other than the standard comma, semi-colon or Tab?  Maybe a pipe or tilde character?  Me too.  I didn't think it was possible, but a Google search of "web intelligence custom delimiter" yielded this...


Cleartelligence blog | Adding custom column delimiter characters to CSV export options to WEBI


To be sure, this would not be supported or even recommended by SAP.  So use at your own risk, but I have to say that is a pretty sweet customization.





Dear Community,


I'm happy to be able to announce that SP05 has been shipped today!

It is available both as a "Full product installation", or as an "Update / patch" (for existing 4.x installations).







p.s. The download location for the full installation can be directly accessed here: SBOP


p.p.s. The update/patch can be obtained from this location:

Support Packages and Patches | SAP Support Portal > Analytics Solutions > SBOP BUSINESS INTELLIGENCE PLATFORM > SBOP BI PLATFORM (ENTERPRISE) > SBOP BI PLATFORM 4.1


p.p.p.s. the full "what's new" in BI4.1 can be found here: http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41_whats_new_en.pdf

Hello everyone,


With the large number of issues and various KBA's/Notes that cover DCP (Deski Compatibility Pack), I decided it would be best to try and consolidate all of this information into one location.


I have created the following wiki that details different DCP issues that may occur in each currently supported release (XI 3.1 FP6.x/FP7.x, BI 4.1)


List of Known KBs and Notes for DCP (Deski Compatibility Pack) - Business Intelligence (BusinessObjects) - SCN Wiki


I will be updating it as issues are discovered and then their resolutions and appreciate any input or feedback that you may have.




Stephen Yemm

Using IIS to redirect to Tomcat


Having some experience with Tomcat's isapi_redirect plugin for IIS, I recently found a new way to enable redirection to Tomcat from IIS7.0+ using Application Request Routing (ARR) module.


This new method is does not require multiple configuration files (uriworkermap.properties, workers.properties, regkeys), IIS configurations and DLL files.

Excerpt from Microsoft:


"Microsoft Application Request Routing (ARR) for IIS 7 and above is a proxy-based routing module that forwards HTTP requests to content servers based on HTTP headers, server variables, and load balance algorithms. ARR relies on the URL rewrite module to inspect incoming HTTP requests to make the routing decisions. Therefore, the URL rewrite module is required to enable ARR features."

There are several features of this module which you can read about here:
Using the Application Request Routing Module : The Official Microsoft IIS Site



Read on to learn how to configure ARR in a simple environment:






Configuring ARR to redirect to Tomcat


These steps assume that you've already installed XI3.1 or BI4.x which already includes an installation of Tomcat and the appropriate BI web applications and that you already have IIS7.0+ installed and running.  I also make reference to Localhost, but you can replace this with your hostname




  1. Download and install the ARR extension from Microsoft:
    Application Request Routing : The Official Microsoft IIS Site

  2. Launch IIS Manager:
  3. Open "Application Request Routing Cache":

  4. Once opened, on the far right column, select "Server Proxy Settings":
  5. In The Application Request Routing Screen, Check the box "Enable Proxy" and click Apply.  Leave all the default settings.

  6. Next Open the "URL Rewrite" dialog:

  7. Once open, select "Add Rule" on the far right and choose a BLANK rule to start with, select OK.
    Use the following configuration to redirect all requests to Tomcat on the localhost:

    Name: Tomcat
    Pattern: .*
    Rewrite URL: http://localhost:8080/{R:0}

    Select Apply

  8. Test:





For me and my installations with IIS to Tomcat redirect, this configuration has simplified the previously cumbersome install of the mod_jk isapi filter for IIS.  In addition, the ARR module does not require any additional configuration on the Tomcat side, (including opening the 8009 AJP13 redirect port).


The configuration for ARR can be extended to support a multitude of load balancing configurations and can be configured to redirect to any number of Web Application servers (not just Tomcat!)




For more information:

See the related articles section on this page:




I have recently built a new dashboard which monitor other Dashboards.   It monitor SQL (Query browser) but also what I call TRACES inside all your corporate dashboard in BO 4.0. I will try to explain concept and the steps I make.


You will all agree that customer experience in navigating a Dashboard need to be as fast as possible and also stable. Like we have (most of the time) when surfing on the web. If the times it takes to refresh components is too slow, customer will be complaining.  Or if customer are used to wait 20 secondes for display 4 updated graphics on first page, but now it take 1 minute, it will complaint.


And because Dashboard Design involve a lot of technology to run,. it can sometimes become slow. IT department need to know as fast as possible to make corrections and have customer happy.


To illustrate this let take a very simple dashboard that contains

- 2 Filters box (which trigger graph information changes)

- 3 graphics

    - 1 retrieve information from query browser (so SQL lives on database)

    - 2 retrieves information from a web service (Kind of cache web Intelligence content)

- Some Excel functions ... Oh! , developer becomes very imaginative in this area . I see dashboard that contains 500 lookups () excel function call.  The power of excel


User Dashboard example

dashboard example.jpg


In this example, when user changes filters, all graphics are updated (1 SQL, 2 Web service call) . Very common,...


Common things that slow down the dashboard

- Database query

- BO Web server (Tomcat in my case)

- BO Engine server (we have very fast servers)

- Network

- PC

- File system


Audit module in Business Object


The query browser can be audited with the audit module if you install it on BO 4.0. That part have good tools already with BO Enterprise. Then , you can easily see which SQL is slow. My new dashboard monitor show that SQL information but see next what I do for Internal traces.


Internal traces


Now, if other components (web server, network, BO engine, etc...) become slow, it's more difficult to monitor with the standard tool that BO provides. That's why I built a way for traces events between a begin and End of something inside the dashboard. We develop a Java Web service which can be call from Dashboard (like other web service) which insert a trace log into a table. So, when you have 2 timestamps, you can know the durations between.


So, developers can by example insert call at the beginning of a filter selection, and an "END" trace at the end.


If you like to know more about the Java code, send me personal email.


Another example, you can call the traces web service when first opening the dashboard and call again the traces when all components are loaded, this will give you what you can call "Initial load times".  This includes in my example, flash load, 1 query browser, 2 web service call, somes excel functions etc...


With all this information written on tables, you can then build trend of what happen and when.


Threshhold agreement with customer


Another good part of this new tools is the ability to make an agreement with a customer for all SQL and / or traces. By example, You can ask what is the time Initial load should take for a dashboard X to run. If a customer said 10 seconds is good (green), 15 is OK (yellow) and more than 20 seconds (red) it's critical. Then you can build a dashboard like I have shown you just for reference.



Dashboard Monitoring (main screen) in reel time



Show TOP 6 SQL and TOP 6 Traces.  For 1, 3 and 12 hours interval in reel time

dashboard example 2.jpg


Dashboard Monitoring (Detail screen)


Show average, max time SQL and traces.  Also, present the threshold aggreement with cutomer (top - right)


dashboard example 3.jpg


Call web services TRACES


sc6 (call traces).jpg


To complete the solution, we build a exploration view (Explorer) on a universe connected to this new tracelog tables.


When all this is done, the support team can visualize in reeltime if some dasboard become slow.  For sure, this also involve some changes when develop the dashboard, you need to call web services.  Minimum effort...!


Hope this give you more ideas then questions



If you’re not completely familiar Promotion and Version Management, or you’d like to improve your understanding of ‘Best Practices’ around this topic, then please join me for an hours webinar on Thursday 23rd October.


As part of the BI 4.1 Upgrade Webinar events I will discuss, at a high level, the following topics:


High Level Architecture

  • Separation of environments
  • Version Management Architecture
  • Dedicated System for Promotion Management
  • Promotion Management Architecture


Promotion Management Best Practice

  • Promoting Content
  • Ad-hoc Content
  • Deleting
  • Connection Mapping
  • Lots more tips and advice


Register for this event, for free, at https://enablement.sapevents.com/

View the list of other webinars in the series at http://scn.sap.com/docs/DOC-56308 (including recordings to previously held webinars)


I have just one hour to cover a very large topic, but I will fill it with some great advice. It’s a must for anyone upgrading to BI4, especially those new to BI4. If you're already familiar with BI4 and Promotion/Version Management, then you should still hope to see some great hints and tips.


Hope to see you then.


This blog is an implementation of the official SAP documentation : http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp4_bip_admin_en.pdf

1. Introduction

The Monitoring Application is a new application in SAP BusinessObjects 4.x. You will find the application in the CMC under Manage. By default, the monitoring trending data are stored in four tables in a Derby (Java) database and we have the option to transfer this data to the Audit database.

If you want to use the auditing database for your monitoring data, and you have existing data in your Derby trending database, you will need to migrate the Derby database to the auditing database.


This provides several advantages:

  • The Derby tables will only store three months worth of data. The Audit DB tables will store as much data as you want.
  • Connecting to the audit tables, to build a universe, is much easier than connecting to Java tables.




2. Trending database schema

The following Trending database diagram and table explanations show you the tables where the metric, probe, and watch data will be recorded and how these tables are related.


19-10-2014 16-50-33.png

3. Trending database tables

  • MOT_MES_DETAILS: This table records the information about subscription breaches and alert delivery information. For example, breach time and alert delivery time.



Primary Key


DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)



Unix Epoch date

NATime at which data was collected
AlertTypeSMALLINT or NUMBERNASubscription notification delivery type (for example, email)


  • MOT_MES_METRICS: This table records information about watches and the metrics belonging to the watch equations. Every metric belonging to the watch will have one entry in this table.



Primary Key


DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)
CUIDVARCHAR(64)NACUID of the watch
NameVARCHAR(255)NAName of the watch


  • MOT_TREND_DATA: This table records the trending data from metrics, watches, and probes. For example, metric value and time.





DetailsIdINTEGERForeign key (from MOT_TREND_DETAILS)
Time or TimeT


Unix Epoch date

NATime at which data was collected
ValueFLOAT or DOUBLE or NUMBERNAValue of the metric / subscription
MessageKeyVARCHAR(32)NAError message key or null if successful. For Watch, it can also be either "watchEnabled" or "watchDisabled". It is a "key" because it is ultimately used to fetch localized messages before displaying the UI.

Time at which data is written to the databaseMOT


  • MOT_TREND_DETAILS: This table records information about managed entities, probes, and watches. For example, CUID and metric names.





CUIDVARCHAR(64)NACUID of the InfoObject that exposes the metric or is related to the metric
MetricNameVARCHAR(255)NAName of the Metric

One of "Subscription", "ManagedEntityStatus", or "Probe"


Name of the watch when the type is "ManagedEntityStatus". Otherwise, default to the same string as in Type, except in all capital letters; for example, "PROBE" or "SUBSCRIPTION".


Therefore, it is recommended to migrate the data from the Derby tables, to the Audit database. Let’s talk about how to do that.

4. Migrating the Trending Data

Before you start migrating your data, verify these prerequisites:

  • The auditing database is working, and auditing is running properly.
  • You have sufficient authorizations and database client applications on the target database to create new tables, import CSV dumps, and so on.
  • The auditing database supports the import of comma-separated values (CSV) files.



    4.1 To export the data into CSV files

This section explains how to generate the CSV dump files required for migration. The CSV files contain comma-separated values of the embedded Derby database data content.


  • In the Manage area on the CMC home page, click Applications.
  • Double-click Monitoring Application to open the properties page.
  • In the Trending Database Settings area, beside Export Data from Embedded database as CSV files, click Export.


19-10-2014 16-49-28.png


The following four CSV files are generated in the default Trending Database location, which is:

<BOE_Install_Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0/Data/TrendingDB

19-10-2014 17-42-54.png


      4.2 To create the monitoring tables in the MS SQL Server Audit DB

Follow these steps to prepare the target auditing database:


After installing the BI platform, DDLs related to all the supported CMS auditing databases are available in the <Install Dir>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\Data\TrendingDB location. You will find seven different (.sql extension) files with the respective database name.

19-10-2014 17-48-06.png

Go to the MS SQL Audit DB (in this case, the target database is the database where CMS auditing has been configured) and run the .sql file. The following four Monitoring tables are created: MOT_TREND_DETAILS, MOT_TREND_DATA, MOT_MES_DETAILS, and MOT_MES_METRICS. The required indexes are also created, along with the tables.

19-10-2014 18-01-58.png


If all the tables are created with correct data types as mentioned in the .sql file, the database schema required for the Monitoring application is created.

19-10-2014 18-09-37.png




    4.3 To restore contents to the target database

The following steps need to be performed in order to restore the content to the target database:

  • Enable Identity Insert

The Monitoring tables contain a number of IDENTITY columns. These are columns that auto-generate their values. MS SQL Server do not allow explicit insertion of values to these columns.

During data migration, even these identity column values need to be migrated however. Users therefore have to enable the explicit insertion of these values using the following SQL command:



  • Import the CSV dump file to the target table

All software provided by database clients enables users to import the data from CSV to the table using either a menu option or a command. The user needs to use this option to import the data from the CSV file to the corresponding table. Import the data files into the new tables in the following order:



  • Disable Identity Insert

Once the data has been imported, the user needs to disable the identity insert on the table using the following SQL command:


Users have to disable the identity insert on a table after the data import in order to enable the identity insert on the next table. This is because the identity insert operation can be enabled on only one table at a time.




MSSQLServer scripts list :
19-10-2014 18-59-19.png
19-10-2014 19-08-27.png
19-10-2014 19-16-35.png
19-10-2014 19-20-45.png





4.4 To configuring SBO files


Internally, the Monitoring application uses Connection Server libraries, and the SBO configuration is required for the Connection Server to establish connectivity to the database driver. You need to specify the database driver and its location in the SBO file to establish this connectivity.


The connection name field configured in the CMC Auditing page is an ODBC DSN, so the driver should be configured in: <Install_Dir>\dataAccess\connectionServer\odbc\sqlsrv.sbo


Typically, the ODBC libraries are already configured in the SBO files and you just need to add the alias names. If this is not the case, follow this example to perform the configuration in the SBO file:


19-10-2014 20-04-20.png




4.5 To switch to the auditing database


Switch the database so that Monitoring trending information will be stored in the auditing database:


  • In the Manage area on the CMC home page, click Applications.
  • Double-click Monitoring Application to open the properties page.
  • In the Trending Database Settings area, select Use Audit Database.


19-10-2014 19-29-48.png

Restart your Monitoring APS.




4.6 To check the data entered into the audit DB

Here is a sample query to see if your monitoring entries are added:


19-10-2014 19-57-23.png

We currently have BI 4.1 SP01 Patch 6 and are considering upgrading, but to what version, support package and patch?  I know that there are lots of "What's New" resources like this one for 4.1 SP04.  But how do I know what fixes from previous patches are being carried forward?  There used to be something called the Forward Fit spreadsheet that would lay this out.  However, I was unable to find it.  I did find this very helpful blog post by Patrick Perrier though...


SAP BusinessObjects Business Intelligence Suite 4.1 SP04 (SP4) Released


And within that I found a link to the Forward Fit spreadsheet I sought, but it came with a bit of a surprise.



So I have to look at SAP notes to know what releases it is forward fit to?  In my view BI upgrades are a roll of the dice anyway in that while some things are fixed you never know what is going to get broken.  Not maintaining the Forward Fit spreadsheet going forward makes things even more difficult.


Here is what I would like to see.  I would like SAP to provide an interactive web page that allows me to enter the version, support package and patch I am on as well as what version, support package and patch I am to which considering upgrading.  The web page would then show me the new features and fixes I would gain and also what features and fixes I would forfeit.  I know this would take considerable effort, but I know that we would find it very helpful.


Anyone else?



We recently upgraded from BO 4.0 SP4 to BO 4.1 SP4. This was my first upgrade and I would have loved to find something like this on SCN (maybe there is and I just didn’t search hard enough). We all know 4.1 is old news, but I thought there might still be a few people who haven’t gone through the upgrade yet, so here goes:

We originally planned to go to SP3, but ran into a few bugs that were only fixed in SP4 at that time, so ended up at SP4.


1. Dashboard Design:



Our existing dashboards gave the following error (when launched in EP Portal):


" While trying to invoke the method java.lang.String.toUpperCase(java.util.Locale) of a null object loaded from local variable 'memberCountLimitNull' "


We used SAP Netweaver BW Connections. The errors were caused by BEx queries that had undergone some sort of “structure” change after the BW upgrade. We had about 5 connections in 2 dashboards that changed; each connection was a different case than the one before.


Solution: Delete and recreate the connections causing errors.





It was hard to find the connections that were causing the errors because another thing we picked up is that the connection preview did not work anymore. It gave the following error:


"Could not connect to BI Query. Make sure the request URL is valid and verify the network connection. "


We haven't found a solution for this yet. We had to delete each connection one at a time, publish the dashboard and then check if there is one less error.



We also had another error in EP Portal when launcing the dashboards:


" Error while executing function module: BICS_PROV_GET_RESULT_SET "


Solution: This was caused due to timeouts in BW. We applied this note in BW 1980998 - GETWA_NOT_ASSIGNED during generic aggregation MIN/MAX


2. Client Tools:



We had the following logon errors with the client tools:


IDT - " Communication error occured when trying to connevt to server xyz "

UDT - " Cannot access the repository "

Webi RC - " Logon failure due to an internal error "


Solution: From the CMC we manually configured the ports of the services. (By default they were set to auto assign)





In IDT we are also unable to create a relational connection to SAP ERP 6:

" Fail to create an instance of Job : com/businessobjects/i18n/locale/BoLocale "

Solution: This is a bug, it's resolved in one of the patches.


3. Launchpad:



The Folder links did not work automatically. We configured the default folder URL (new feature in 4.1 SP3) by following this link:

Solution: Direct Links to Folders (4.1 SP3) (Comments)



We had an issue with scheduling Webi reports, they did not run for the dates we specified in the prompts, it was way out (more than a century). This was a big issue for us.


Solution: Upgrade to SP4. This was the main reason we went from SP3 to SP4.




Searching for documents on the BI Launchpad returned an error:


" An error occured while searching.: Unable to find servers in CMS xyz and cluster @xyz with kind pjs and service PlatformSearchService0CA. All such servers could be down or disabled by the administrator "

Solution: Recreate the index files on OS level then stop start the APS.


4. Webi:




We had a few Java issues with the Applet mode in the BI Launchpad. First error occured when we tried to create a new webi report in applet mode:

" Unspecified error ivoking method or accessing property "onChangeDocID" "

Solution: We had to upgrade our Java to version 7 update 51 or higher. 1933283 - Error: "Unspecified error invoking method or accessing property "onChangeDocID" " when refreshing a new WebI report after choosing query elements



After doing the above, the users came across an error when using Applet mode:

" Web intelligence Applet cannot be loaded, please make sure you have installed a Java virtual machine. "

Solution: Something else we picked up is that if you run 64bit IE, you need to have installed 64bit Java. If you run 64bit IE, and you have 32bit Java installed, you get the error.




When refreshing a webi report (on a BEx Query) in HTML mode and filtering in the BEx prompts, we picked up that if you select a value and then decide to remove it from the selection, the blank selection is then automatically populated with [EMPTY_VALUE]. this causes the refresh to fail when you run the report:

" System xyz: Incorrect value "[EMPTY_VALUE]" for data scurce variable abc "


Solution: This is a bug. See this post for more info Webi BEx Query Prompt default [EMPTY_VALUE] when removing selected values BO4.1



Some of our existing Webi report variables were not calculating correct. This this was easy to spot in the charts.

Solution: Recreate the variables. Clear the entire formulae and recreate it.



Some of the existing reports were empty (no data).

Solution: Remove and readd the filters on the reports components



When refreshing an existing report or creating a new report based on a universe to BW, we had the following error:

" Database error: Unable to connect to SAP BW server Could not convert from 4103 codepage to 1100 codepagerc = 2048. "

Solution: This was due to using SSO to connect to BW. This is a bug. We created a system connection user and maintained the uthorizations from the CMC. See this note for more info (did not resolve our issue) 2037630 - Webi reports based off of SSO enabled universes fail with codepage conversion error


5. CMC:




Promotion manager was unable to connect to an upgraded system and so we were unable to promonte:

" Could not reach CMS xyz. Specify the correct host and port and and check for network issues "


Solution: Upgrade BO4.0 to BO4.1. Promotion Manager: Could not reach CMS





There are a few more where these came from. So why upgrade? Was it worth it?

Absolutely! BO 4.1 is much more stable than BO 4.0. The added features also makes it well worth your while.


Hope there is someone out there who found this usefull.




One of the frequent questions from our customers is how to create a customized SSO enterprise authentication for BI LaunchPad and for Open Document queries. One of the most easiest way is to use servlet filter in Tomcat. You can find below the steps needed to create, develop and deploy the SSO filter:


Create a new project in Eclipse:


Enter the project name and pick the Tomcat runtime (same as your BI web server):



Add a new class to your project


Type the package name, class name and click on Add button


Type filter and choose the interface javax.servlet.Filter


Click on finish


Copy the SDK libraries from the folder below to a project folder:


Go to configure build path of your project and add the SDK files, using Add External JARs button:


Copy and paste the code below into the SSOFilter.java file:

package com.xxx.yyy.aaa;


import java.io.IOException;


import javax.servlet.Filter;

import javax.servlet.FilterChain;

import javax.servlet.FilterConfig;

import javax.servlet.ServletException;

import javax.servlet.ServletRequest;

import javax.servlet.ServletResponse;


import com.crystaldecisions.sdk.exception.SDKException;

import com.crystaldecisions.sdk.framework.CrystalEnterprise;

import com.crystaldecisions.sdk.framework.IEnterpriseSession;


import java.io.UnsupportedEncodingException;

import java.net.URLEncoder;


import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;


public class SSOFilter implements Filter {

private static FilterConfig filterConfig = null;


public SSOFilter() {}



public void destroy() {

// TODO Auto-generated method stub





public void doFilter(ServletRequest request, ServletResponse response,

FilterChain chain) throws IOException, ServletException {

// TODO Auto-generated method stub

HttpServletRequest servletRequest = (HttpServletRequest)request;

HttpServletResponse servletResponse = (HttpServletResponse)response;


String userName = "test";

String password = "password";


        String requestURL = servletRequest.getRequestURL().toString();




if (requestURL.contains("openDocument.jsp") && servletRequest.getParameter("token")==null) {


servletRequest.getParameter("sIDType")+"&token="+createEncodedToken(userName, password));

} else if (requestURL.contains("BOE/BI") && !(requestURL.contains("start"))) {

((HttpServletResponse) response).sendRedirect(requestURL+"/logon/start.do?ivsLogonToken="+createEncodedToken(userName, password));

} else {

chain.doFilter(request, response);


} catch (SDKException e) {

chain.doFilter(request, response);

} catch (UnsupportedEncodingException e) {

chain.doFilter(request, response);






public void init(FilterConfig config) throws ServletException {

// TODO Auto-generated method stub




public String createEncodedToken(String userName, String password) throws SDKException, UnsupportedEncodingException {

IEnterpriseSession enterpriseSession = CrystalEnterprise.getSessionMgr().logon(userName, password, "localhost", "secEnterprise");

return URLEncoder.encode(enterpriseSession.getLogonTokenMgr().createWCAToken("", 750, 9999), "UTF-8");




private void setFilterConfig(FilterConfig config)


filterConfig = config;



private static FilterConfig getFilterConfig()


return filterConfig;





It should look like the following:


Export the project as JAR file:


Select only source files, like below:


Copy the jar file into the following folder:


Modify the file web.xml from the folder to add the new SSO filter:


Add the class name and the filter and filter-mapping xml nodes as following:


Restart the tomcat server and the filter should be running!

In our previous blogs we have analyzed various components required for Designing security model such as List of available rights, possible user categories and finally the Security models. Before taking deep dive in to each model we need to organize BusinessObjects content at below levels


  • Reporting folders
  • Universe folders
  • User hierarchy


You can have a look at below articles to know more about organizing contents (Content Management Plan)


BusinessObjects Administration - Content Management Procedure

BusinessObjects Administration - Content Management Plan


And the next step would be identifying required rights for each user category. You should make use of Custom Access Levels for each user category.  Refer my external blog here to understand about Custom access levels


Based on the requirement create list of rights necessary for each user category.  Consolidated list of all the rights and a custom access level grouping for admin group is depicted below.


BI 4.x Master rights




Delegated Administrators Access level




I also attached prototype of the document with this blog.  Since .xls doesn't supported, I have exported it to .txt format . Make use of it if you want to utilize it to your requirement. Thanks for reading once again.

Related blogs

BusinessObjects Administration - Setting up security model – An easy way to configure and manage

BusinessObjects Administration - Setting up security model – Part II


Filter Blog

By author:
By date:
By tag: