Transcript Free SDK Utilities to Help Manage Your Business Objects

[
0501 - Free SDK Utilities to Help
Manage Your Business Objects
By Steve Rademacher
[ Learning Points
 In this session you will learn how to create your own or find
free existing SDK code programs
 You will learn how to schedule program objects that contain
SDK code
 You will learn how to effectively pull information out of your
Business Objects repository and run SDK code written in
.NET, Java, and Excel VBA to better run your Business Objects
environment
Real Experience. Real Advantage.
2
[ What is the SAP Business Object SDK?
SDK = Software Developers Kit
 The Business Objects SDK allows you to develop custom
applications that require SAP Business Objects BI platform
functionality
 It allows you to extend or customize the functionality of the
SAP Business Objects BI platform
 Server SDK code is installed by the server install
 Client SDK code is installed by the client install
Real Experience. Real Advantage.
3
[ Where to find SDK help?
SDK Guides on http://help.sap.com
A) Universe Design Tool SDK
B) Web Services SDK
C) Semantic Layer SDK (Java)
D) Data Access Connection SDK (Java)
E) Data Access Driver SDK (Java)
F) Business Intelligence Platform SDK (Java & .NET) *
G) Crystal Reports Viewers SDK (Java)
H) Report Application Server SDK (Java)
* We are only covering the Business Intelligence Platform SDK in this session.
Real Experience. Real Advantage.
4
[ Why Use the SDK?
 We do not like manual monitoring
 We want to automate as much as possible
 We need some special functionality that other
companies do not require
 SAP “forgot” some functionality in their software that
we would like to have
 Audit data shows what occurred to your SAP Business
Objects system but does not show what is existing in
your system. i.e. We cannot see which reports have
not been accessed in the past 30 days because audit
data does not show what currently exists.
 We have had Business Objects a very long time and
have found many new uses for it
Real Experience. Real Advantage.
5
[ What Is Query Builder and Why Use It?
 http://[server]:[port]/AdminTools/
• Do not worry about the Building a query statement step by step as it is
usually not useful.
• Query builder is limited to 1000 records by default. Use select top 10000
to limit to 10000 on that query.
• Query builder allows you to run SDK server queries interactively but
contains a very limited set of SQL.
Real Experience. Real Advantage.
6
[ What Is Query Builder and Why Use It?
 Query Builder Output
Select * from CI_INFOOBJECTS – returns data but not in a useful format.
Real Experience. Real Advantage.
7
[ What are the SDK tables?
 CI_INFOOBJECTS – Contains reports, instances,
folders, scheduling information, etc.
 CI_SYSTEMOBJECTS – Contains servers, server
groups, user sessions, users, groups, authentication
objects, calendars, events, etc.
 CI_APPOBJECTS – Contains InfoView App., Desktop
Intelligence App., Web Intelligence App., Universes,
Universe Connections, etc.
 The SI_KIND field is very important and tells you
which kinds of objects you are retrieving. SI_PROGID
can also be used to tell the kind of object you are
retrieving. Use a select top 1 * query in Query Builder
to see all of the available fields.
Real Experience. Real Advantage.
8
[ Enough of this already, give me some free SDK .NET code!
' List all Folders - Author: Steve Rademacher
Dim APS
Dim UserID
Dim Password
Dim Aut
Dim oSessionManager
Dim oEnterpriseSession
Dim oSess
Dim iStore
Dim queryString
Dim Records
Dim objFSO
Dim logStream
Dim x
Dim Folder
'For the logon, Authenticate as Following
APS = Wscript.Arguments(0)
UserID=Wscript.Arguments(1)
Password = Wscript.Arguments(2)
Aut = Wscript.Arguments(3)
'Create an Enterprise oSessionManager
Set oSessionManager =
WScript.CreateObject("CrystalEnterprise.SessionMgr")
Set oSess = oSessionManager.Logon(UserID, Password, APS, Aut)
'Create a new Variable for the login token
Set iStore = oSess.Service("", "InfoStore")
Real Experience. Real Advantage.
‘ Continued…..
'Query the iStore and retreive the SI_ID
queryString = "select top 10000 * from CI_INFOOBJECTS where SI_KIND
= 'Folder' order by SI_ID"
Set Records = iStore.Query(queryString)
'Create a file on the C Drive if it does not already exist. Append if it it
exists.
Set objFSO = CreateObject("scripting.filesystemobject")
If objFSO.FileExists("C:\BOfolders.csv") Then
Set logStream =
objFSO.OpenTextFile("C:\BOfolders.csv", 8, 1)
Else
Set logStream =
objFSO.OpenTextFile("C:\BOfolders.csv", 8, 1)
logStream.writeline "CMS, SI_ID, SI_NAME,
SI_PARENTID"
End If
for x = 1 to Records.Count
Set Folder = Records.Item(x)
'Write the output
logStream.writeline """" & APS & """," & _
Folder.Properties.Item("SI_ID").Value & ",""" & _
Folder.Properties.Item("SI_NAME").Value & """," & _
Folder.Properties.Item("SI_PARENTID").Value
Next
oSess.logoff
logStream.Close
9
[ Now that I have code, what do I do with it?
1.
2.
3.
4.
5.
Save the code in a text file. In this case the code should be saved as
filename.vbs as this code uses the .NET SDK.
To run the code, use CSCRIPT.EXE filename.vbs parm1 parm2 parm3
parm4. On a 64-bit system, you may have to use
C:\WINDOWS\SYSWOW64\CSCRIPT.EXE as the 64-bit version of
CSRIPT is used by default and may not work with the 32-bit SDK code
installed. If you are accessing anything on a machine with Windows
Security turned on, you may have to run the code as administrator.
The code requires 4 parameters. They are: CMS Name, User Id,
Password, Security method. The security methods are secEnterprise,
secWinAD, secLDAP, secWindowsNT, secSAPR3. You may need extra
field values to login as secSAPR3.
Figure out how to use the output.
Study the code and determine how you can use it yourself to pull data out
of the repository.
Real Experience. Real Advantage.
10
[ How about some free Java SDK code!!!!!!
import java.util.Date;
import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;
import com.crystaldecisions.sdk.plugin.authentication.secwinad.IsecWinAD;
public class SAP_User_Update {
public static void main(String[] args) {
IEnterpriseSession lEntSession = null;
String lCMSName = "servername";
String lUserName = "administrator";
String lPassword = "";
String lAuthType = "secEnterprise";
System.out.println("java SAP_User_Update -u cmsusername -p cmspassword -s
cmshostname -a secEnterprise(optional)");
try {
int index = 0;
while (index < args.length)
{
String arg = args[index++];
if (arg.equals("-u"))
lUserName = args[index++];
else if (arg.equals("-p"))
lPassword = args[index++];
else if (arg.equals("-s"))
lCMSName = args[index++];
else if (arg.equals("-a"))
lAuthType = args[index++];
}
Real Experience. Real Advantage.
System.out.println("Logging to Enterprise");
lEntSession = CrystalEnterprise.getSessionMgr().logon(lUserName,
lPassword, lCMSName, lAuthType);
System.out.println("Querying for the plugin");
IInfoStore lInfoStore
= (IInfoStore) lEntSession.getService("InfoStore");
//Query for any object in the CMS.
String lQuery = "SELECT TOP 1 * "
+ "FROM CI_SYSTEMOBJECTS "
+ "WHERE SI_NAME='secSAPR3'";
IInfoObjects lInfoObjects = (IInfoObjects) lInfoStore.query(lQuery);
if (lInfoObjects.size() > 0) {
System.out.println("A plugin was found");
IsecWinAD lPlugin = (IsecWinAD) lInfoObjects.get(0);
lPlugin.properties().getProperty("SI_UPDATE_TS").setValue(new Date());
System.out.println("Commiting the value");
lInfoStore.commit(lInfoObjects);
} else {
System.out.println("no plugin was found using SQL [" + lQuery + "]");
}
} catch (SDKException lException) {
lException.printStackTrace();
} finally {
if (lEntSession != null) {
lEntSession.logoff();
}
System.out.println("Application finished, logged off the enterprise system");
}
}
}
11
[ Compile Java SDK code
Save your java code in a .java txt file
Save this code in a .bat txt file
Run the .bat file and pass the name of the .java file to it.
.
@echo off
if NOT "%JAVA_HOME%" == "" GOTO NEXT1
if exist "C:\Program Files\Java\jdk1.5.0_14" goto ONEFIVE
SET JAVA_HOME=C:\Program Files\Business Objects\j2sdk1.4.2_08
goto SETPATH
:onefive
SET JAVA_HOME=C:\Program Files\Java\jdk1.5.0_14
GOTO setpath
:setpath
set Path=%JAVA_HOME%\bin;%Path%
:NEXT1
set CLASSPATH=.\cecore.jar;.\celib.jar;.\ceplugins.jar;.\cesession.jar;.\ceutils.jar;.\cexsd.jar;.\corbaidl.jar;.\ebus405.jar;.\InstallEntSdkWrapper.jar;.\Serialization.jar;
javac.exe %1
:END
Real Experience. Real Advantage.
12
[ But I want Excel SDK code……..
• The Excel code that I have is too long to include in a
PowerPoint. We should go to the source.
• I use BOB to find interesting SDK code that I can
customize to make my own.
• http://www.forumtopics.com/busobj/index.php
• Make an account if you do not have one and go to
BOB’s Downloads. You can contribute in BOB’s
Uploads. When your contributions are approved by a
moderator, they will be moved to BOB’s downloads.
• For Excel, start with the popular BOXI UserList &
Group Extraction
Real Experience. Real Advantage.
13
[ The BusinessObjects Board
Real Experience. Real Advantage.
14
[ The BusinessObjects Board Downloads
Real Experience. Real Advantage.
15
[ BOXI UserList & Group Extraction
• This utility was created by Julien Bras
• It uses the Excel VBA Macro facility to call the .NET
SDK.
• You may have to adjust the references for your
version of Business Objects.
Real Experience. Real Advantage.
16
[ BOXI UserList & Group Extraction
• To change the references, display the Developer
toolbar – File – Options – Customize Ribbon –
Check the Developer toolbar option
• Go to Developer – Visual Basic – Tools – References
• Ensure the references are set to 11.5 for XI R2, 12.0
for XI 3
Real Experience. Real Advantage.
17
[ BOXI UserList & Group Extraction
• Output
Real Experience. Real Advantage.
18
[ Delete old Inbox documents
Real Experience. Real Advantage.
19
[ Schedule the run of an SDK extract – System Setup
• To Schedule SDK code as a program object, perform
the following one time setup:
• In the Central Management Console go to
Applications and right click on the CMC application
• Select Program Object Rights
• Ensure the boxes are
checked as in this
box and enter the
service account and
password
Real Experience. Real Advantage.
20
[ Schedule the run of an SDK extract - Program Object
• We write our SDK extract data to a file share as a
CSV file and load the results into MS Access MDB files
so that we can put a universe over them
• Go to CMC – Folders – Select the folder where you
would like to create a program object
• Select Manage – Add – Program File
• Browse to a .CMD or .BAT file. I recommend that
you have a single line in your .CMD or .BAT file that
runs another .CMD or .BAT file. You will not be able
to change the contents of your Program File after you
create it. You must delete the program object and
recreate it to change it.
• "\\fileservername\filesharename\folder\filetorun.cmd"
Real Experience. Real Advantage.
21
[ Schedule the run of an SDK extract - Program Object
• Ensure that the line: cscript
“\\fileservername\filesharename\folder\SQLCommands
.vbs” is in the filetorun.cmd file
• On a 64-bit system, you may have to use
C:\WINDOWS\SYSWOW64\CSCRIPT.EXE as the 64bit version of CSRIPT is used by default and may not
work with the 32-bit SDK code installed. If you are
accessing anything on a machine with Windows
Security turned on, you may have to run the code as
administrator.
Real Experience. Real Advantage.
22
[ Schedule the run of an SDK extract – into MS Access
• “\\fileservername\filesharename\folder\SQLCommands
.vbs” contents
• In your MS Access database, import your CSV file as
both a real Access table and a linked table
Dim objCN, strConnection, strSQLQuery, objRS
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\fileservername\filesharename\foldername\Remedy Service Reporting Landscape Logic Map.mdb;"
strSQLQuery = "delete * from accesstablename;"
wscript.Echo "delete accesstablename table"
Set objCN = CreateObject("ADODB.Connection")
objCN.Open strConnection
objCN.Execute strSQLQuery,,129
strSQLQuery = "INSERT INTO accesstablename SELECT accesslinkedtablename.* FROM
accesslinkedtablename;"
wscript.Echo "Load accesstablename table"
Set objCN = CreateObject("ADODB.Connection")
objCN.Open strConnection
objCN.Execute strSQLQuery,,129
Real Experience. Real Advantage.
23
[ Schedule the run of an SDK extract – Java
• To run a compiled java program
@echo off
If NOT "%1%" == "" goto Next1
echo Prompts are Username, Password, Servername, authentication (optional)
pause
goto END
:Next1
If NOT "%2%" == "" goto Next2
echo Prompts are Username, Password, Servername, authentication (optional)
pause
goto END
:Next2
If NOT "%3%" == "" goto Next3
echo Prompts are Username, Password, Servername, authentication (optional)
pause
goto END
:Next3
if NOT "%JAVA_HOME%" == "" GOTO NEXT1
if exist "C:\Program Files\Java\jdk1.5.0_14" goto ONEFIVE
SET JAVA_HOME=C:\Program Files\Business Objects\j2sdk1.4.2_08
goto SETPATH
:onefive
SET JAVA_HOME=C:\Program Files\Java\jdk1.5.0_14
GOTO setpath
:setpath
set Path=%JAVA_HOME%\bin;%Path%
:NEXT1
set CLASSPATH=.\cecore.jar;.\celib.jar;.\ceplugins.jar;.\cesession.jar;.\ceutils.jar;.\cexsd.jar;.\corbaidl.jar;.\ebus405.jar;.\InstallEntSdkWrapper.jar;.\Serialization.jar;
If NOT "%4%" == "" goto Next4
java groupDiag -u %1 -p %2 -s %3
goto END
:Next4
If "%4" == "secWinAD" goto Next5
If "%4" == "secWinNT" goto Next5
If "%4" == "secEnterprise" Goto Next5
echo Valid authentication options are secWinAd, secWinNT, secEnterprise
goto END
:Next5
java groupDiag -u %1 -p %2 -s %3 -a %4
:END
Real Experience. Real Advantage.
24
[ Set all scheduled reports to a default server group
' ************************************************************************
' Changes scheduled reports set to run on any server group to run only on
' the specified Server Group.
' Steve Rademacher - Original Author
' ************************************************************************
Dim APS
Dim UserID
Dim Password
Dim Aut
Dim NewServerGroup
Dim NewServerGroups
Dim ServerGroup
Dim ScheduleInfo
Dim ScheduledReport
Dim ScheduledReports
Dim oSessionManager
Dim oEnterpriseSession
Dim oSess
Dim iStore
Dim queryString
On Error Resume Next
'For the logon, Authenticate as Following
APS = Wscript.Arguments(0)
UserID=Wscript.Arguments(1)
Password = Wscript.Arguments(2)
Aut = Wscript.Arguments(3)
NewServerGroup = Wscript.Arguments(4)
On Error GoTo 0
'Query the iStore and get the Scheduled Reports that did not run on a particular server group
queryString = "select TOP 1000 SI_ID, SI_NAME, SI_SCHEDULEINFO from CI_INFOOBJECTS " & _
"Where SI_INSTANCE = 1 AND SI_SCHEDULEINFO.SI_MACHINECHOICE = Null AND
SI_RECURRING = 1"
Set ScheduledReports = iStore.Query(queryString)
for x = 1 to ScheduledReports.Count
Set ScheduledReport = ScheduledReports.Item(x)
Set ScheduleInfo = ScheduledReport.SchedulingInfo
' 1 = Give Preference to a server Group
' 2 = Run only on the specified Server Group
' Null = Run on any server (0 instead of null if it was set to 1 or 2 first and then changed)
ScheduleInfo.ServerGroup = ServerGroup
ScheduleInfo.ServerGroupChoice = 2
wscript.echo "SI_NAME: " & ScheduledReport.Properties.Item("SI_NAME").Value
wscript.echo "Server Group: " & ScheduleInfo.ServerGroup
wscript.echo "Server Group Choice: " & ScheduleInfo.ServerGroupChoice
Next
Else
wscript.echo "ServerGroup not found. Parameters are: CMS, userid, password, authentication,
ServerGroup"
End If
IStore.Commit(ScheduledReports)
oSess.logoff
'Create an Enterprise oSessionManager
Set oSessionManager = WScript.CreateObject("CrystalEnterprise.SessionMgr")
Set oSess = oSessionManager.Logon(UserID, Password, APS, Aut)
'Create a new Variable for the login token
Set iStore = oSess.Service("", "InfoStore")
'Query the iStore and get the NewServerGroup
queryString = "select TOP 1 * from CI_SYSTEMOBJECTS Where SI_KIND =
'ServerGroup' AND SI_NAME = '"
queryString = queryString & NewServerGroup & "'"
Set NewServerGroups = iStore.Query(queryString)
If NewServerGroups.Count = 1 Then
Set NewServerGroup = NewServerGroups.Item(1)
ServerGroup = NewServerGroup.Properties.Item("SI_ID").Value
wscript.echo "ServerGroupID: " & Servergroup
Real Experience. Real Advantage.
25
[ Return on Investment
 Using SDK code, you can run your Business Objects
environment more efficiently and be able to explain to others
how your environment is being used.
Real Experience. Real Advantage.
26
[ Best Practices
 Know what is happening in your Business Objects system and
you will be able to manage it with more efficiency.
 Pull information from your Business Objects and analyze that
information regularly to determine what your users are doing
and how they are using the tool.
 True capacity management can only occur if you know how
your users are using the tool.
Real Experience. Real Advantage.
27
[ Key Learnings




In this session, you have learned how to create SDK code
You have learned how to find free SDK code parts
You have learned how to schedule and run SDK code
You have learned how to take the output of your SDK code
and analyze it in Business Objects
Real Experience. Real Advantage.
28