пятница, 17 января 2014 г.

SAP BO DI Installation

If you decide to install SAP BusinessObjects Data Services, you can choose Linux or Windows version. I show how to perform installation and setup of DI.

Firstly, some words about DI, from WIKI

Business Objects's Data Integrator is a data integration and ETL tool that was previously known as ActaWorks. Newer versions of the software include data quality features and are named SAP BODS (BusinessObjects Data Services). The Data Integrator product consists primarily of a Data Integrator Job Server and the Data Integrator Designer. It is commonly used for building data martsODS systems anddata warehouses, etc.
Additional transformations can be performed by using the DI scripting language to use any of the already-provided data-handling functions to define inline complex transforms or building custom functions.
Data Integrator Designer stores the created jobs and projects in a Repository. However, Data Integrator Designer also facilitates team-based ETL development by including a Central Repository version control system. Although this version control system is not as robust as standalone VCSs, it does provide the basic check-in/check-out, get latest, version labeling and undo checkout functionality.
The DI Job Server executes, monitors and schedules jobs that have been created by using the Designer.

Visit service.sap.com and download distributives of SAP BO DI. But before installation, you should have SAP BO platform, because DI will connect to SAP BO. Moreover, we should download temporary keys from service.sap.com.

Installation master asks us some information:

  • license key
  • SAP BusinessObjects credentials
  • DI repository creation (miss this point, will create later)
  • DI JobServer creation (miss this point, will create later)

After successful installation we can add DI Repository and JobServer.

The repository is a database that stores Designer predefined system objects and user-defined objects including source and target metadata and transformation rules. In addition to the local repository used by the Designer and Job Server, you can optionally establish a central repository for object sharing and version control.
The Designer handles all repository transactions. Direct manipulation of the repository is unnecessary except for

  • Security administration Data Services uses your security at the network and RDBMS levels.

  • Backup and recovery You can export your repository to a file. Additionally, you should regularly back up the database where the repository is stored.

The Job Server is an application that launches the Data Services processing engine and serves as an interface to the engine and other components in the Data Services suite.

Lets's create DI repository. We need new empty database. I use MS SQL Server for this task.

Connect to SQL Server as "sa" (super administrator)

Than Add new database ->BODI

Than I can create local repository using SAP Data Services Repository Manager.

Input all credentials and "Create".
I will get message "The local repository was successfully created"

Than we can bind BO and DI by repository, we need to go in CMC and we find new label "Data Services"

Again we have to enter our credentials:

But here is one trick with port, be aware with port, in my case - 1433, i specially add it in MS SQL Server Control Manager in TCP/IP tab:

Last one, creating of Job Server in SAP Data Services Server Manager->Job Server->Configuration Editor
Again input our credentials of our repository

That's all. Now we can start Data Services Designer.

The Designer allows you to create, test, and execute jobs that populate a data warehouse. It is a development tool with a unique graphical user interface. It enables developers to create objects, then drag, drop, and configure them by selecting icons in a source-to-target flow diagram. It allows you to define data mappings, transformations, and control logic. Use the Designer to create applications specifying work flows (job execution definitions) and data flows (data transformation definitions).

четверг, 9 января 2014 г.

SAP BO 4 Performance issue

I got real problems with performance in BI Launch PAD. For instance, my reports were opening very long time, navigation in BI Launch Pad was awful. Business Users begat to claim.

At the end I fixed this problem. And I got very useful and helpful recipe for this problem.
Let's start.

1. Of course, we should start from updating SAP BO. Last available version is SAP BO SP2. Just took last one.

2.  Let's modify the Tomcat setting, because by default they have low value.
o   JavaHeapSize (-Xmx) from 2G to 4G 
o   MaxThreads from default (200) to 900 
2.1 JavaHeapSize we should modify:   
cd <bo inst folder>/sap_bobj/tomcat/bin 
nano setenv.sh

# set the JAVA_OPTS for tomcat
JAVA_OPTS="-d64 -Dbobj.enterprise.home=${BOBJEDIR}enterprise_xi40 -Djava.awt.headless=true -Djava.net.preferIPv4Stack=false -Xmx4g -XX:MaxPermSize=384m -XX:+HeapDumpOnOutOfMemoryError -Xloggc:/data/bo4dev/sap_bobj/tomcat/logs/tomcat.gc.log -XX:+PrintGCDetails -XX:+UseParallelOldGC"

2.2 MaxThreads we would modify:
cd /data/bo4dev/sap_bobj/tomcat/conf
nano server.xml

Define a non-SSL HTTP/1.1 Connector on port 9090
<Connector port="9090" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" maxThreads="900" URIEncoding="UTF-8"/>
<!-- A "Connector" using the shared thread pool-->
    <Connector executor="tomcatThreadPool"
               port="9090" protocol="HTTP/1.1"
               redirectPort="8443" />

3. Cleaning of old installed patches and service packs from BO. What does it mean?
Just go to <bo inst folder> and run ./modifyOrRemoveProducts.sh (for Linux):

It is clear that i have many various installation on my server. But it is important to delete old installation!
Now you see, that i deleted old one:

4. Cleaning of logging directory, in  <bo inst folder>/sap_bobj/logging
o   Deleting Trace-Files
o   Deleting Logs old than 30 days
5. Tuning APS, for start we can use CMC Wizard. 
6. APS properties for tracing set to unspecified (-> means bo_trace.ini are used)
Force the use of BO_trace.ini to be used
Only critical events such as failures will be logged
Ignores warning and status messages
Only Status messages with least important will be ignored
Includes all the logging messages
7. Correction of blocking auditing activities, delete auditing events old 30 days:
In CMC go to Auditing and change parameter "Delete Events Older Than (Days)" on 30 from 365

8. Disabling of unused services in the CMC, for instance, Crystal, Analyses services.
9. Reorganisation of BO-Repository and BO-Auditing in DB2  (runstats) (Script is available in the filesystem). This one is high important point. As you know, every database has to manage its statistics according to number of rows. And if you do not collect stat, than you performance as well as refresh rate are decreasing. I used DB2 db, because it was by default with distrib for Linux.

Now we have to collect stat on all DB2 tables in our BO repository as well as AUDIT repository. 

Go to DB2 folder, you can find it in home folder of BO linux user:
cd <user's home folder>/sqllib/bin
There are two files:


They are responsible for collecting of DB2 statistics.
Just perform these scripts:
#db2 -tvf runstats_audit.out
#db2 -tvf runstats_repo.out

You have to schedule this task, and perform these scripts once per week.
You can do it via CMC:
CMC->Applications->Right Button on CMC and choose Program Objects Rights.

Mark "Run Scripts/binaries" and enter credentials for OS.
Or you can schedule it via your OS. For instance use crontab in Linux:

0 23  * * 7 /opt/bo4dev/sqllib/bin/runstats.sh >/dev/null 2&>1

For instance Shell Script for Linux:

db2 connect to boe14
db2 -tvf runstats_repo.out
db2 connect to audboe14
db2 -tvf runstats_audit.out

Than schedule this sh by cron.

In summary, performance of BO was increased dramatically.