Customizing the login mask for the BI Launchpad

This post shows how to customize the login mask of the BI Launchpad.

The default BI Launchpad login mask looks like this:

Changing the BIlaunchpad.properties

Some of the most common modifications for the login mask can be made in a file called BILauchnpad.properties in <CATALINA_HOME>webappsBOEWEB-INFconfigcustom. To start with your modifications, just create this file in this directory.

There are a lot of different parameters which you can set in this file (see the admin guide, chapter 18.2.1.2). Let me explain those parameters which change the login mask (by the way – if you want to test these steps, I recommend to use the tomcat manager to  reload the web app – this is faster than restarting the tomcat service).

Setting the parameter app.custom.product.name=c-BIP BI platform results in:

Setting the parameter app.name.short=Welcome to the BI Launchpad at c-BIP results in:

Setting the parameter authentication.visible=true to get this login mask adds a drop-down field to change the authentication type:

Setting the parameter authentication.default=secWinAD (only visible if authentication.visible=true) changes the default authentication type:

Setting the parameter cms.visible=false removes the CMS text field:

Setting the parameter cms.default=@cBIP (only visible if cms.visible=true) changes the default system name – this is useful when you change the cluster name in the CCM:

Another parameter I want to mention here is app.url.name – this parameter changes the URL for the BI Launchpad (be careful: If you try to call the launchpad with :/BOE/BI, you will see an error):

Setting the parameter app.url.name=/c-BIP results in:

These are the most common parameters to change in the BIlaunchpad.properties.

Changing the logo

The logo in the left bottom corner can be replaced in <CATALINA_HOMEwebappsBOEWEB-INFecplipsepluginswebpath.InfoViewwebimages – just replace the file called SAP_logo_reg.png and restart Apache Tomcat.

And also the banner icon is changed:

Changing the default logon instructions

The default logon instructions can be changed in a file called  LogonResources.properties – but this is hidden very well into the file structure: It is located in <CATALINA_HOME>webappsBOEWEB-INFeclipsepluginswebpath.BICoreWebwebWEB-INFlibBICoreWeb.jar. Just change the file extension from jar to zip, then open it and navigate to combusinessobjectsbipcoreweblogoninternalLogonResources.properties. Change the parameters called logon.firstInstruction and logon.secondInstruction:

This results in:

Changing the Cascading Style Sheet

If you want to use a custom style sheet, you can modify the default.css in <CATALINA_HOME>webappsBOEWEB-INFeclipsepluginswebpath.InfoViewwebcss.

Important

Be careful with all of these changes – especially the logon instructions are hidden deep into the system, so please do these steps on a sandbox or test system first.

If your login mask looks like you wish, then (and this is an important step) copy BIlaunchpad.properties to <BIP_INSTALL_DIRECTORY>SAP BusinessObjects Enterprise XI 4.0warfileswebappsBOEWEB-INFconfigcustom.
You should also replace the logo files SAP_logo_reg.png in  <BIP_INSTALL_DIRECTORY>SAP BusinessObjects Enterprise XI 4.0warfileswebappsBOEWEB-INFecplipsepluginscom.businessobjects.webpath.InfoView.jarwebimages.
Also, modify the LogonResources.properties in <BIP_INSTALL_DIRECTORY>SAP BusinessObjects Enterprise XI 4.0warfileswebappsBOEWEB-INFecplipsepluginscom.businessobjects.webpath.BIPCoreWeb.jarwebWEB-INFlibBIPCoreWeb.jar.

Finally, run the wdeploy tool to be sure not to loose these changes.

Securing the SAP BO BI platform 4

In this post, I’ll try to give an overview about the different security areas in the SAP BO BI platform.

Why should you care about the security? Most bi platforms are secure from access from outside the company network. That is true, but for more and more companies, BI has become a business critical task and more and more companies collect and analyze real sensitive records. Therefore, you have to think about how to make your bi platform secure.

There are several main areas where you should think about security:

• Spend some time to get a good user and content management plan
• Spend some time in protecting your environment
• Spend some time in protecting your network

User and content management

The user and content management plan answers the question: Who can access which bi tool and what can he/she do in this tool?

Of course, you’ll have your user managed in groups and you give those groups access to the content. Here you also restrict the access in the semantic layer and you protect your environment against bad login attempts by using password and user restrictions and you do not enable the guest account.

One more thing here is: Do not use the administrator user! Why? Because it is a no-named user and you cannot audit no-named user for sure. Also, if you lose the administrators password, you have no “proper” way to reset it (even if you can). So, write the administrators password down on a paper, put it into an envelope and then store the whole thing in a swiss bank locker 😉

A good content management plan describes the following parts:

• Document folder and category structure (including naming convention)
• Universe and connection folder structure
• User and group structure
• Access Level structure
• Applications rights
• Deployment process (including Ad Hoc vs. Standard report)

Most of these parts are collected in the user security concept. I recommend to create a simple MS Excel sheet to create and document this concept. This may look like this:

Environment protection

In this section, we will speak about how to protect the server. It is really important, that no one else than the authorized employees can access the platform server. This is usually done by your server crew. Work together with them to have a save environment. This includes:

• Revoke remote desktop access for unauthorized employees
• Make sure that the server is not accessible and/or visible from outside your company’s network (if you have a bi mobile server into a DMZ, then hide the server behind a reverse proxy)
• If you use network shares for the file store, log- and audit file location, properties files (like the BILaunchpad.properties), security files (like KRB5.ini) etc., make sure that this network share is protected against unauthorized access
• Make sure that the installation directory, file store- log- audit file location are not reachable over UNC
• Setup an activity tracking mechanism for these files – remember, nearly everything that is outside the CMC will not be audited by the bi platform
• Make sure that the backup medias are stored in a save place and that they are encrypted. Check your backup solution vendor manuals for further information
• Follow the security considerations for your web application server – if you use Apache Tomcat, then you can use this blog
• Activate FIPS mode – see the admin guide, chapter 8.1.11.3
• Make sure that your database (system and operational) are secured. Check your database vendor manuals for further information

Network protection

Although the communication between the single server (processes) on the bi platform is now protected by the cryptographic keys, you should also protect the data transfer between the client (which can be an iPad too!) and server,  communication between the server and the database, as well as the communication between the web application server and the platform server. If your BI environment contains servers in different subnets, then you definitely should place some firewalls between and around them.

The communication between the client tools (including Crystal Reports etc.) and the bi platform server is made with CORBA; if you are using a web browser to connect to the BI Launchpad or the CMC, HTTP is used to connect from the client to the web application server. Using a web applications includes also CORBA, because this is the way how the web application server connects to the bi platform server. To secure this communication, you should setup SSL for HTTP (=HTTPS) and encrypted CORBA SSL.

To do this, you have to proceed the following steps:

• Create a certificate key file for each machine in your environment (clients included) – use the sscl and/or sslconfig command line tool for this step (see the admin guide, chapter 8.13.1) or use the manuals from your certification authority
• Configure SSL protocol in the CCM (see the admin guide, chapter 8.13.3)
• Configure SSL protocol for the web application server (see the admin guide, chapter 8.13.3.3
• Configure SSL protocol for the client machines (see the admin guide, chapter 8.13.3.4)

Then you should also make sure that the communication between the server (and client) machine and the database is secured. Check your database vendor manuals for further information.

Apache Tomcat and the SAP BO BI platform 4

In this post, I’ll try to give you some hints working with the default web application server in the SAP BO BI platform.

In a default bi platform 4.0, Apache Tomcat 6 is included as the default web application server. In bi platform 4.1 is is Apache Tomcat 7.

Background information

The Apache Tomcat serves as a “gateway” between the client (web browser) and the bi platform on a server. E.g. if a user tries to login the BI Launchpad, the process is as follows:

1. The browser (web client) sends the login request via the web server to the web application server, where the web application is running.
2. The web application server determines that the request is a logon request. The web application server sends the username, password, and authentication type to the CMS for authentication.
3. The CMS validates the username and password against the appropriate database. In this case, Enterprise authentication is used, and user credentials are authenticated against the CMS system database).
4. Upon successful validation, the CMS creates a session for the user in memory.
5. The CMS sends a response to the web application server to let it know that the validation was successful.
6. Thewebapplicationservergeneratesalogontokenfortheusersessioninmemory.Fortherestof this session, the web application server uses the logon token to validate the user against the CMS. The web application server generates the next web page to send to the web client.
7. The web application server sends the next web page to the web server.
8. The web server sends the web page to the web client where it is rendered in the user’s browser.

As you can see, Tomcat plays a very important role! Therefore, it is very important, that it is configured well to proceed the request fast and secure. That means, you should do some work if you are responsible for a bi platform (and of course, you test every step on a test system first):

Clean up

The default Apache Tomcat comes with the following web applications:

• AdminTools
• BOE
• BusinessProcessBI
• clientapi
• docs
• dswsbobje
• host-manager
• manager
• MobileBIService – automatically deployed in 4.1 only
• MOBIServer – automatically deployed  in 4.1 only
• ROOT

Some of them are depreciated or not in use in a typical bi environment, so they can be deleted (maybe! It depends on your environment):

• BusinessProcessBI (depreciated) – only needed to support legacy crystal reports web services and sdk scripts
• clientapi (depreciated) – only needed if you use custom sdk scripts
• docs – are available online, so you do not need them locally
• host-manager – only in use if you have some virtual Apache hosts

To clean up these web applications, you should do should do the following:

1. Stop Apache Tomcat
2. Remove these folders from <CATALINA_HOME>webapps
3. Remove these folders from <bip_install_dir>tomcatwebappsworkCatalinalocalhost
4. Remove the log files
5. Remove the file with the same name in <catalina_home>confCatalinalocalhost – e.g. remove the clientapi.xml)
6. Start Apache Tomcat

CATALINA_HOME is the directory where your Tomcat is installed – in my test environment, it is C:BIP40Tomcat6 (in BO BI platform 4.0) or C:BIP41tomcat (in BO BI platform 4.1)

Security considerations

Regardless if your web application server is not reachable from outside your company’s network, you should consider some minimal security tips (see http://tomcat.apache.org/tomcat-7.0-doc/security-howto.html for further information).
(If you web application server is reachable from outside, you definitely should use HTTPS / SSL!)

Running Tomcat under a special user account that have only access to CATALINA_HOME.

Therefore do the following steps:

1. Create a new user (e.g. SRVACC_TOMCAT), set the password to never expire and add this user with the minimal rights (Read) on <CATALINA_HOME>
2. Go to Local Security Policies and > Local Policies > User Rights Assignment and set the policy “Logon as a service”
3. Stop the Tomcat service, add this user as the logon user for startup
4. Delete the log files
5. Start the Tomcat service

Disable the shutdown port

Go to the <catalina_home>confserver.xml and set the shutdown port from 8005 to -1: <Server port=“-1” shutdown=”SHUTDOWN”>

Set a user for the manager

You should change the default content of <CATALINA_HOME>conftomcat-users.xml to:

<?xml version=’1.0′ encoding=’utf-8′?> <tomcat-users> <role rolename=”manager-gui”/> <user username=”<Username>” password=”<Password>” roles=”manager-gui”/> </tomcat-users>

You can use any name for user and password. If you restart Tomcat, open a browser and type http://localhost:8080/manager, then you will be prompted to enter the name and password specified in the tomcat-users.xml file.

Prevent server type and version check

To prevent other users to get your web application server type and version, add the following string to the connector tag in <CATALINA_HOME>confserver.xml:

<Connector

port=”8080″
protocol=”HTTP/1.1″
connectionTimeout=”20000″
redirectPort=”8443″
compression=”on”
URIEncoding=”UTF-8″
compressionMinSize=”2048″
noCompressionUserAgents=”gozilla, traviata”
compressableMimeType=”text/html,text/xml,text/plain,text/css,text/javascript,text/json,application/json”
server=”<AnyText>”

/>

Activate Access Log Files

Remove the comment signs (<!– –>) around the following string (if you can’t find it, just add it):

<Valve className=”org.apache.catalina.valves.AccessLogValve” directory=”logs”
prefix=”localhost_access_log.” suffix=”.txt” pattern=”common” resolveHosts=”false”/>

Timeout Settings

You should also set the timeout setting in the <CATALINA_HOME>confweb.xml file – just search for “session-timeout” and change the value to the preferred value:

<session-config> <session-timeout>30</session-timeout> </session-config>

Upgrade

Although SAP updates important files for Tomcat as well during a patch or support package installation, you should think about manually update the tomcat files periodically, especially if your Tomcat is on a public server. See http://tomcat.apache.org/migration.html for further information.

Performance tuning

The first thing is to give the Tomcat some juice! Therefore, you should be sure to have enough memory reserved for Tomcat – see my blog Java and the SAP BO BI platform.

Then, it is also very important that you split the dynamic and static content – this gives you lots of speed. There are some really good manuals like Quick Step-by-Step Guide to Apache Split Deployment with BI 4.1

Java and the SAP BO BI platform 4

In this post, I’ll try to clarify the mystery beyond the usage of java within the SAP BusinessObjects Business Intelligence platform 4.

A short overview about java

The most important thing is the Java Virtual Machine (JVM) which executes java bytecode (.class files, may be collected into .jar files) and contains a just-in-time compiler which compiles the java bytecode to be used by the operating system. The JMV is part of the Java Runtime Environment (JRE). To start a java program, you use the application launchers java.exe (if using a console) or javaw.exe (if using a window).

Java used by the SAP BO BI platform

The installation of the SAP BO BI platform installs two JREs in the following locations:

• 1.6.0_xx in <bip_inst_dir>SAP BusinessObjects Enterprise XI 4.0win64_x64sapjvmbin
• 1.7.0_xx in <bip_inst_dir>SAP BusinessObjects Enterprise XI 4.0win32_x86jrebin

The following processes uses java on the platform server:

• WebApplicationContainerServer
• AdaptiveProcessingServer
• XcelsiusServer
• JobServer Child Processes
• CrystalReportsProcessingServer
• Tomcat
• wdeploy

On the client, you use java only if you are using Web Intelligence with the java viewer.

In a default bi platform, you see java processes for the following threads in the windows task manager:

• WebApplicationContainerServer
• AdaptiveProcessingServer
• XcelsiusServer
• CrystalReportsProcessingServer

Of course, these processes are only running in a demo- or test environment, because in a production system, you split the adaptive processing server and you stop other (unused) server.

How to monitor these java.exe processes

To monitor the hardware consumption of these java.exe processes (including tomcat7.exe), you can use the jconsole tool, which is located in <bip_inst_dir>SAP BusinessObjects Enterprise XI 4.0win64_x64sapjvmbin. Just start the console and choose the local process that you want to monitor – in my example, I select the process with the PID 9980, which is my wdeploy java process.

 

One important thin to know: You cannot directly compare the memory shown in the console with the memory shown in the windows task manager! That is because the windows task manager shows the whole memory that is allocated by the java.exe program. The monitoring console, however, splits the memory in Heap-, Non-Heap and Native parts.

Another important thing: If you start the jconsole, you just get those java processes that are owned by the user you are logged into the system – so, this is a good reason, why you should always run the bi platform under a domain user, not the local system. If you want to monitor other processes, you can start the jconsole within a dos prompt window with the command jconsole

Tuning

Each java process allocates some memory, based on the JRE parameter calls „Xms“ and it takes more and more memory if needed until it reaches the amount of memory specified in the JRE parameter „Xmx“. If the heap size almost reaches the max heap size, it removes the non-used or non-referenced objects (the „garbage“) from the memory. This is called the „garbage collection“. If your heap size is too low, the JVM is often busy with performing garbage collection. When this is the case, you have to increase the maximum heap size. You can see how many GC happened in the past in each server metric page. A high value indicates a heap size that is too small.

You have 3 opportunities to specify the heap size:

• Command line
• Tomcat Configuration Console
• Java Control Panel (only if you have installed java manually)

Set the heap size in the command line of each server

In the CMC, server section, you can set the java heap size for every server, mentioned above. Just double-click on the server, click into the command line, search for the parameter „-Xmx“ and change the value to the required value.

By the way: You can specify the “Xmx” value in GB or in MB. To allocate a maximum of 12 GB, you can use “-Xmx12288m” (12*1024) as well as “-Xmx12g”.

Set the heap size for the web applications

You can specify the amount of memory that Apache Tomcat will be able to allocate to run the web applications. Just use the tomcat configuration console, switch to the java tab and type the value for the heap size.

Set the heap size for Web Intelligence java viewer

The java control panel by default shows only the JRE that is used to run java programs manually. You can add the JRE used by the bi platform, but this does not make sense, because all java programs used by the bi platform are started with a command line, so the values set in the java console are overwritten and not used. Use the java console only, if you use Web Intelligence with the java viewer directly on the server. (On the client site, it makes sense to set the java options in the java console on the client, if you are using the java viewer for Web Intelligence.)

Just open the java console, go to the JRE tab and type „-Xmx12288m“ if you want to reserve a heap size of 12 GB for the java viewer.

Check the maximum heap size for each java process

You can check the maximum heap size for every java process with the jconsole. Open the java.exe process in the jconsole, go to the VM Summary tab and check the value for “Maximum heap size”. This value is always a little bit smaller than the value you specified in the Xmx parameter.

One more thing…

If you want to use jconsole remotely or if you do not want to search for the changed PID each time you want to use jconsole, then start each JVM with the following arguments:

• -Dcom.sun.management.jmxremote.port=<PortNum>
• -Dcom.sun.management.jmxremote.authenticate=false
• -Dcom.sun.management.jmxremote.ssl=false

Go to the CMC, server section, double-click on each server that uses java and add these parameters

You should also set the startup parameters in the tomcat configuration console

Of course, you shouldn’t use ports that are already in use – test this within a command line window using the command telnet <IP> <portnum>.

Using these parameters, you can open jconsole, click on “Connection”, Remote Process: and type :.

you can also open more than one connection.

Install SAP Host Agent to use the host metrics in SAP BO BI platform 4

This post will show you how to set up the sap host agent and how to get the host metrics available in your monitoring application.

The first step is to download the sap host agent from www.service.sap.com/swdc > Support Packages and Patches > Browse our Download Catalog > SAP Technology Components > SAP Host Agent. In my case, I just downloaded the newest version. Then you need a tool to “unzip” the SAR file. You can do this with SAPCAR, which you can download from www.service.sap.com/swdc > Support Packages and Patches > A – Z Index > S > SAPCAR.

Then you need to create a new user in the admin group called “sapadm”:

Then move the downloaded files into a new directory (e.g. C:SourcesSAPHOSTAGENT), open a command line window and switch to this directory. Then, type the following command: sapcar.exe -xvf – e.g. “sapcar_315-20010453.exe -xvf SAPHOSTAGEN176_176-20005735.SAR” and press return. This will extract the sap host agent files, especially the file “saphostexec.exe”, which you need to install the sap host agent. Simply use the command “saphostexec.exe -install” into the command prompt window to install the sap host agent. During the installation, you have to enter the password for the user “sapadm”. If you see the message “succesfully installed”, you can test the installation by typing “saphostexec.exe -status”. If you see 3 entries (SAPHostExec, SAPHostControl, saposcol), sap host agent is working fine on your system.

Now go to the CMC, Application section and double-click on “Monitoring Application”. Activate the box next to “Enable host metrics”  and type the path to the SAPOSCOL library (including the file name and extension)- in my case it is
C:Program FilesSAPhostctrlexesaposcol.exe:

Then restart the adaptive processing server that hosts the monitoring service.

Finally, you should have a new metric category calls “Hosts” in your monitoring application in the CMC:

That is it. You can use these new metrics to make your daily work with the SAP BO BI platform much more easier.

Enable the monitoring application in SAP BO BI platform 4

In this post, I’ll show you how to enable the monitoring in SAP BO BI platform 4 and I’ll also give some best practices to use it.

First of all, lets talk about the storage of the monitoring information. By default, the BI platform uses a java derby “database”. This is not a traditional database, but a collection of individual files. On one hand, this makes it more difficult to use it as a  reporting data source, on the other hand, you’ll have more knowledge about traditional databases. And of course, you have one “database” less to maintain (backup/restore/size etc.). Therefore, switching to the audit database should always be a post installation task.

So, there are several steps you have to do:

1. Make sure that auditing is enabled and that it is working fine
2. Create 4 additional tables in the audit database
3. Modify some SBO files
4. Restart the APS

Step 1: This should not be an issue. If you have not already enabled the auditing, check the admin guide to do it. To make sure that the auditing is working fine, you can use a report that connects to the audit database or use a database management tool to view the records in an auditing table like ADS_EVENT. If you don’t have an audit universe, have a look on the blog “Import the audit unx universe in SAP BO BI platform 4“.

Step 2: Use a database management tool to execute some SQL statements. These statements depends on your audit DBMS and can be found under SAP BusinessObjects Enterprise XI 4.0DataTrendingDB. If you use MS SQL Server for example, then use the sql commands in SAP BusinessObjects Enterprise XI 4.0DataTrendingDBmssql.sql. This should create 4 tables in you audit database:

• MOT_TREND_DETAILS
• MOT_TREND_DATA
• MOT_MES_DETAILS
• MOT_MES_METRICS

Step 3: In this step, you have to add the alias name in the so-called SBO file in SAP BusinessObjects Enterprise XI 4.0dataAccessconnectionServerodbc. You have to use the following Aliasses, depending on your audit db:

DBMS > Alias Name

• SAP HANA > Hana
• MS SQL Server > MS SQL Server
• My SQL > MySQL
• SAP Max DB > MaxDB
• IBM DB2 > DB2
• Sybase SQL Anywhere > Sybase SQL Anywhere
• Sybase Adaptive Server Enterprise > Sybase Adaptive Server Enterprise
• Oracle > Oracle

So, if you are using MS SQL Server as the audit db, you have to modify the file SAP BusinessObjects Enterprise XI 4.0dataAccessconnectionServerodbcsqlsrv.sbo. In this file, you just add the text “MS SQL Server” (the same name as you can see under “Type” in the picture above. The file looks like this:

For further information, check the admin guide.

Step 4: Restart the adaptive processing server that hosts the monitoring service.

Now you just have to enable the monitoring application. Therefore, open the CMC and select the applications  section. Double click on “Monitoring Application”. Activate the monitoring application by selecting the check box next to “Enable Monitoring Application”, then switch to the audit database as data store by selecting the radio box next to “Use Audit Database”.  Also, it is a good practice to lower the “Metrics Interval Refresh” from 60 to 15 seconds. The Monitoring Application Properties should looks like this:

To check that monitoring is working fine, go to the monitoring section in the CMC – if you see a dashboard with a green bullet in the upper left corner, it works (attention: this step requires adobe flash player, so you should use the web browser on your client, not on the server machine).

Finally check that you have some records in one of the monitoring tables like the MOT_TREND_DETAILS.

That is it, you have successfully setting up monitoring.

Some other best practices for productive environments:

• Create one dedicated adaptive processing server that hosts the monitoring server
• Use a minimum heap size of 2 GB – more is better
• Place the dedicated APS near the machine that hosts the audit database

Create a monitoring report with SAP BO WebIntelligence 4

In this post, I’ll show you how to create a new monitoring report (let’s say an “Early Bird report”) with SAP BO Web Intelligence 4.0.

Prerequisites

First of all, you have to make sure that your monitoring application is up and running and that you use the audit database to store the monitoring information (see my blog Enable the monitoring application in SAP BO BI platform 4).

The second step ist to modify the audit universe (if you do not have one, check this blog: Import the audit unx universe in SAP BO BI platform 4) or create a new one:

Import the following tables into your universe:

• MOT_TREND_DETAILS
• MOT_TREND_DATA
• MOT_MES_DETAILS
• MOT_MES_METRICS

Now join them over DetailsID and create the following objects in the business layer.

• Type (MOT_TREND_DETAILS) – This can be “Metric”, “Probe” or “ManagedEntityStatus” etc. The last value is useful if you want to report your watches or probes
• Metric Name (MOT_TREND_DETAILS) – This attribute is useful if you want to monitor single metric values. If you filter the Type attribute to “ManagedEntityStatu”, then the Metric Name attribute is always “Managed EntityStatusId”
• Name (MOT_TREND_DETAILS) – This is the name of the watch, probe or metric
• Value (MOT_TREND_DATA) – This is the value of the watch, probe or metric and should be a measure with sum() as the aggregation function
• Ts (MOT_TREND_DATA) – This is the timestamp when an action was written to the database
• CUID (MOT_TREND_DETAILS – This is the CUID of the object that “triggers” a monitoring event

So, if you publish the modified universe, you can start with the report.

Part 1: System Health

The first part of the report should be an overview about the system that answers the question: Is every server up and running or not? To get this information, create a new document and add Name, Value and Ts to the query and filter Type to “ManagedEntityStatus”. Then run the query. Now add a max function for Ts to see only the newest record for each watch. Your report should look like this:

Because of the max function around the Ts object, we get only one (the newest) timestamp of each Name and Value combination. That means, that if we have a watch with more than one value (in my example it is the OutputFileRepository Watch), we get two records, which is not helpful, because we just want to know, if the system is running right now. To solve this problem, create a view (e.g. “v_MaxTS”) that contains only the DetailsId and the newest timestamp (without the value attribute):

CREATE VIEW v_MaxTS AS (SELECT DetailsID, max(Ts) as TS FROM MOT_TREND_DATA WHERE DetailsId IN (SELECT DISTINCT DetailsID FROM MOT_TREND_DETAILS WHERE MetricName = ‘ManagedEntityStatusId’) GROUP BY DetailsId);

Then we use this view to get only one timestamp in the report (use custom SQL or edit the audit universe). The query should look like this:

Finally, the report shows some information about watch name, value and the timestamp. You can simply test this report by stopping a server (e.g. the event server), wait 15 seconds (or the time you set in the monitoring application), then refresh the report. If the value of the event server watch turns to 1 or 2, then the report is working. If you like, you can use conditional formating to have green bullet points or something like this.

One important thing is, that a value of 0 is good, 1 is a warning and a 2 is an error – this is different to the monitoring dashboard in the CMC, where a 2 is good, 1 is warning and 0 is an error.

Part 2: System Performance

In the second part of the  report, we want to have an overview about the actual performance of the platform. Therefore, we can use monitoring probes. In my example, I use the CMS Logon Logoff probe and the Interactive Analysis probe which I schedule every 5 minutes (in production environment, maybe it is better not to run these probes too often, because they generates load). The Interactive Analysis probe needs to be configured first, in my case, I just use the CUID of the Formating Sample report in the default folder “Web Intelligence Samples”. This CUID I inserted in the properties of the Interactive Analysis probe.

Then create a new query in your early bird report and add Name, Value and Ts. Filter Type to “Probe” and MetricName to “RoundTripTimeID”. Run the query that looks like this:

Now you have a table that shows the time a scheduled probe takes to run. Then duplicate the table and filter each one for one probe. Then, turn both tables into a line chart. With some formatting work, the report can look like this:

Part 3: Disk Space, Memory and CPU

The third part of the report should help you to do the daily house keeping tasks like disk space and/or memory usage. In my point of view, the following tasks are very important:

• Make sure that you have enough free disk space on your servers – if your File Store is on another server than the CMS, then you need this information from both machines
• Make sure that you have enough free memory
• Make sure that your system is not running on 100 % CPU usage

The first point is not a big issue: For the File Store, you have a metric calls “Available Disk Space in Root Directory _GB_”. For the machine that hosts the CMS, you can create a derived metrics that uses the following calculation:

CentralManagementServer$’Disk Size _GB’ – CentralManagementServer$’Used Disk Space _GB_’.

You can create a watch that use this derived metric; e.g. a watch that shows a warning if the free disk space of the CMS machine is less than 10 GB and an error if it is less than 5 GB.

The second and third points requires SAP HOST AGENT for each node that you want to use in your early bird report. If you have not yet installed the SAP HOST AGENT, read my blog Install SAP Host Agent to use the host metrics in SAP BO BI platform 4.

If you have the host metrics available, you should create a watch for free memory and one for the CPU idle time. If you do not create these watches, the metrics won’t be written to the audit database!

So, add a new query with the following objects:

• Ts
• Metric Name
• Value

Filter CUID to .OS  to get all the host metrics:

With some formatting work, your report can look like this:

Conclusion

So, I hope to give you a short overview about how to create a monitoring report with Webi. If you have any questions, post the question as a comment, visit www.c-bip.ch or join my network on LinkedIn or XING . 

Import the audit unx universe in SAP BO BI platform 4

This post will show you how to get the new audit universe from the SAP Community Network up and running in your new SAP BO BI platform 4.

First of all, you have to download it from http://scn.sap.com/docs/DOC-6175 – if you click on “View Document”, a ZIP file will be downloaded. Extract the files to a temporary directory.

Start the information design tool (which is included in your client tool installation) and start a new project.

Then retrieve the universe from the temporary folder:

Now it depends on your database management system which stores the audit database. If you have MS SQL Server for example, then you have to use the file called “BEOXI40-Audit-MSSQL.unx.

Now you should  see 3 files (the file name may vary depending on your audit DBMS):

• The business layer file called BOEXI30-Audit-MSSQL.blx
• The data foundation file called BOEXI40-Audit-MSSQL.dfx
• The connection file called oledb.cnx

The universe will not work now, because it points to another database. So, you have to modify the connection to use your own audit database. Open the connection file with a double-click on the file oledb.cnx. The connection opens in the main window and provides you 3 buttons. Click on “Change Driver”, then select the ODBC driver (or your prefered method to connect to your audit database) – if you use the default DBMS for the audit db (Sybase SQL Anywhere 12), then you should download the driver from the Sybase Homepage:

If you click on “OK”, the “Edit Connection” window appears. Insert you credentials and test your changes. Remember: The audit connection must be a 32 bit data source!

Save the connection or the whole project (if you do not save at least the connection file, you will face some errors in the next step).

Now you should check the integrity of your data foundation. Right click on the data foundation (the DFX file) in the project area and select “Check integrity”:

Select all boxes in the Check Integrity window and press the “Check Integrity” button. You can ignore all “cardinality” and/or “not linked to…” error message. If you have more messages, like “Invalid expression”, then probably you have to change the table qualifier and owner.

Do this by selecting all tables (expect the derived tables), right-click and select “Change Qualifier/Owner…”.

Insert the audit db user name as the qualifier and as the owner:

Also, check the derived table called ADS_EVENT_DETAIL_V2 – this table contains a SQL expression, that use the “old” owner:

Change the SQL statement and save the derived table.

Run the “Check Integrity” test again. If you don’t see any errors expect those about “cardinality” or “linked”, you can proceed to the next step.

So, you have a universe that is working fine on your client. To publish the universe, you have to use a secure connection… it will not work with the local connection that you use at the moment. To get a secured connection, you have to publish the connection file first.

In the Publish Connection window, open a session to your repository and publish the connection to a connection folder. You can overwrite an existing one if you’re asked. If you get the message to create a local shortcut connection, click “Yes”. Now you should have a new connection file with the file extention “.cns”. This is your shortcut to the secured connection.

Now you have to use this shortcut in your data foundation (that is because you cannot export a data foundation that uses a non secured or locally stored connection). Open the data foundation and click the “Change Connection” icon – this is the blue icon with a pen, the first one on the left:

Select the shortcut connection and click “Finish”.

Delete the “.cnx” connection (else you cannot publish the business layer).

Finally publish the business layer file:

That is it. Have fun with your new audit reports 🙂