Skip to Content

Hi All

First of all thanks to Manikandan Elumalai, Rahul Rao and of course to SAP Admin Guide.

SAPBO SP4 14.0.4.738

Tomcat 6

SQL Server Version 10.0.1600.22

OS: Windows Server 2008 R2 Enterprise

CMS DB Name: BOE140

Created two System DSN in Server using both 32 bit Datasource Administrator and Data Sources(ODBC)

Start Menu> All Programs>SAP BusinessObjects BI Platform 4> SAP BusinessObjects BI Platform >
32-bit Datasource Administrator > Sysytem DSN> Add

Select SQL Server

Name given as BusinessObjects Audit Server 140

Server: CMS DB (SQL Server Instance name (BOE140) given while I installed SAP BO or it will display in dropdown)

Next > SQL Server Authentication > Give ‘boeuser’ and its password. The boeuser is created while installing BO.

Next > Check the box ‘Change default DB to’ give it as BOE140_Audit.

Next>Finish

Control Panel> Administrative Tools> Data Sources(ODBC)>System DSN>Add>

Select SQL Server

Name given as BusinessObjects Audit Server 140

Server: CMS DB (SQL Server Instance name (BOE140) given while I installed SAP BO or it will display in dropdown)

Next > SQL Server Authentication > Give ‘boeuser’ and its password. The boeuser is created while installing BO.

Next > Check the box ‘Change default DB to’ give it as BOE140_Audit.

Next>Finish

Then went to CMC>Auditing

Under Configuration>ADS Database

Connection Name: BusinessObjects Audit Server 140

Type: Microsoft SQL Server

User Name: boeuser and its password

Restarted SIA.

auditing status summary.jpg

It will show such a status, if configuration is success.

Added                   <Aliases>

                                  <Alias>MS SQL Server</Alias>

                                 </Aliases>

after the line

     <DataBase Active=”Yes” Name=”MS SQL Server 2008″>

in <Install_Dir>\dataAccess\connectionServer\odbc\sqlsrv.sbo.

In CMC>Applications>Monitoring

Enable Monitoring Application check box if it is not checked.( If you checked it now, restart SIA.)

I took the back up of my Derby DB and exported data from embedded db as CSV files. ( As per SAP, it is not     

mandatory)

Changed Trending DB to Audit universe.

Restarted SIA.

In Server

Opened SQL Server Management Studio.

Gave the boeuser and password to connect to server.

Server type: database engine

Server name: servername\BOE140

There were three databases System databases, BOE140, BOE140_Audit.

BOE140 contains only CMS tables and BOE140_audit contains ADS and MOT tables.

Queried from BOE140_Audit databse.

For example to get number of hits by user

select e.User_Name, count(distinct e.Session_ID)

from dbo.ADS_EVENT e, dbo.ADS_EVENT_TYPE_STR ets

where e.Event_Type_ID=ets.Event_Type_ID

and ets.Event_Type_Name=’Logon’

and ets.Event_Type_ID = 1014

AND Language=’EN’

and e.Start_Time>’2013-07-12′

group by e.User_Name

Also a Universe on Auditing DB was also created.

First I found out the port of SQL Server by following

Task Manager

View | Select Columns | Check PID | OK

Find sqlservr.exe as the Image Name, note the PID

Command prompt:

netstat -ano | findstr *PID*

(http://benchmarkitconsulting.com/colin-stasiuk/2009/02/02/what-tcp-port-is-sql-server-running-under/)

Then opened the port in Server.

Then created a connection to Auditing DB(relational) using the System DSN created through IDT.

Then I created WebI reports needed.

Its working fine… 🙂

All experts are invited to give suggestions and modifications 🙂

Thanks

Sandeep

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Bhupesh Shukla

    Hi Sandeep,

    Very nice article, brief but to the point. Can you please share the steps on sql server management studio for creating the cms database and audit database before starting the bo installation.

    Regards,

    Bhupesh

    (0) 

Leave a Reply