A not infrequent request I receive is for a way to report off the BusinessObjects Enterprise Central Management Server (CMS) database. There's very compelling business reasons for doing so - to record details of scheduled reports, user activity and server usage and load. The Enterprise Auditing features have come a long way and been greatly expanded since version 10, but there's still some data that's not being recorded in the audit database.
Question: How to Report off the CMS Database?
You cannot connect to and report off the CMS database directly. Much of the info is blobbed in a composite column in the repository tables, and this data cannot be decrypted without having to go through the CMS service.
To access the repository data, you would use the Enterprise SDK or Enterprise Web Services to send an Enterprise query (a SQL-type query) or a path query (an XPath-type query) to the CMS, that then will return a collection of InfoObject objects. An InfoObject is a programmatic, object-oriented representation of data held in the CMS database.
The Query Builder Admin Tool (found on the Enterprise XI 3.x Java web app deployment at http://
What's required is a way to "hook up" either the Enteprise SDK or Enterprise Web Services to a data source that can be consumed by a report.
One possibility is to use Desktop Intelligence documents with VBA data providers, where the VBA invokes the Enterprise COM SDK to retrieve the InfoObject objects. The VBA then will translate the InfoObject properties to a data cube. The COM version of the SDK, however, has been deprecated with XI Release 2 and is no longer available with XI 3.x
Another possibility is to use the POJO reporting functionality of Crystal Reports. You can write Java code that uses the Enterprise SDK or Web Services to retrieve InfoObject objects and copies the desired InfoObject properties to a POJO collection. This solution, however, is restricted to Crystal Reports reports only.
What of Web Intelligence? Or Query as a Web Service (QaaWS)? Fortunately, there is a way.
POJO Reporting using the DDK
In a previous blog entry, POJO and RSS Feed Reporting with Web Intelligence Using the Data Access Driver Development Kit, I describe how you can use the new Universe Data Access Driver Development Kit (DDK) SDK and the Crystal Reports Java SDK POJO ResultSet Factory to create a Universe Connection that consumes POJO collection data sources.
This blog entry is a continuation of that blog, so that blog is worth a read if you're interested in this current subject.
What I'm going to describe here is how to connect the BusinessObjects Enterprise BIPlatform Web Services to the POJO data driver. The connection will pass a specified path query to Enterprise via Web Services, and fill a POJO collection with returned InfoObject collection properties.
The data flow in this workflow is:
CMS -> Web Services -> CRJ SDK -> DDK -> Universe
To understand this blog, you should be familiar with the following:
1. Java programming
2. BusinessObjects Enterprise Web Services programming
3. BusinessObjects Enterprise Path Queries
4. How to download Crystal Reports for Eclipse 2.0 (CRJ SDK)
5. Configuring the DDK JavaBean Driver
6. Creating Universe connections and Universes
I think the results well worth the effort. Here's a screenshot of what I was able to accomplish:
image
This is a Web Intelligence document that lists all scheduled instances that were created since last September, grouped by name. You can see that many InfoObject properties for Web Intelligence, Crystal Reports and even Program Objects are represented in the available objects.
I'll provide code samples, brief deployment instructions, and possible avenues for extending the code.
Hooking up Enterprise Web Services to the DDK JavaBeans
I'll list the sample code here. The SCN width restrictions make the code not easy to read here, so I recommend copy-and-pasting them to local file and reading them using your favorite text editor.
The POJO JavaBeans code comes in three Java source code files. First is the POJO definition, that is used to hold the InfoObject properties retrieved from the CMS. Second is the JavaBean, that defines the method the Universe calls to retrieve the data as a java.sql.ResultSet object. Third is the JavaBeanInfo, that informs the JavaBean consumer which method in the JavaBean code should be called. There is also the DDK JavaBean configuration file, javabean.sbo, used to configure the connection - location of the Java jar library files and connection parameters.
Here is the backing POJO code:
InfoObjectPOJO.java - the POJO representation of InfoObject data
package com.sap.ddk.javabean.sample; import java.sql.Timestamp; /* * POJO for InfoObject properties, designed as adapter between Universe DDK, * Crystal Reports Java POJO ResultSet Factory and BusinessObjects Enterprise * Web Services. * * Each property maps to a possible property for an InfoObject or its subclass. * * - Universes represent a boolean as Number. * - CRJ POJO does not use java.util.Calendar but java.sql.Timestamp for timestamps. * - CRJ POJO does not handle nested properties, so nested InfoObject properties * are mapped to a property with name reflecting the nesting. For example, * the property InfoObject.getSchedulingInfo().getBeginDate() will map to * InfoObjectPOJO.getSchedulingInfo_BeginDate(). */ public class InfoObjectPOJO { private Timestamp creationTime; private String cuid; private String description; private Timestamp endTime; private String errorMessage; private String fileProperties_Files_FileArray_Name; private Integer id; private Integer instance; private Integer isRecurring; private String keywords; private String kind; private Timestamp lastRunTime; private String lastSuccessfulInstanceCUID; private String name; private Timestamp nextRunTime; private String owner; private String parentCUID; private Timestamp schedulingInfo_BeginDate; private Timestamp schedulingInfo_EndDate; private String schedulingInfo_Flags; private Integer schedulingInfo_RightNow; private String schedulingInfo_ScheduleType; private String schedulingInfo_Status; private String schedulingInfo_TimeZone; private Timestamp startTime; private Timestamp updateTime; public Integer getInstance() { return instance; } public void setInstance(Integer instance) { this.instance = instance; } public Integer getIsRecurring() { return isRecurring; } public void setIsRecurring(Integer isRecurring) { this.isRecurring = isRecurring; } public String getKeywords() { return keywords; } public void setKeywords(String keywords) { this.keywords = keywords; } public String getKind() { return kind; } public void setKind(String kind) { this.kind = kind; } public Timestamp getLastRunTime() { return lastRunTime; } public void setLastRunTime(Timestamp lastRunTime) { this.lastRunTime = lastRunTime; } public String getLastSuccessfulInstanceCUID() { return lastSuccessfulInstanceCUID; } public void setLastSuccessfulInstanceCUID(String lastSuccessfulInstanceCUID) { this.lastSuccessfulInstanceCUID = lastSuccessfulInstanceCUID; } public Timestamp getNextRunTime() { return nextRunTime; } public void setNextRunTime(Timestamp nextRunTime) { this.nextRunTime = nextRunTime; } public String getOwner() { return owner; } public void setOwner(String owner) { this.owner = owner; } public String getParentCUID() { return parentCUID; } public void setParentCUID(String parentCUID) { this.parentCUID = parentCUID; } public Timestamp getSchedulingInfo_EndDate() { return schedulingInfo_EndDate; } public void setSchedulingInfo_EndDate(Timestamp schedulingInfo_EndDate) { this.schedulingInfo_EndDate = schedulingInfo_EndDate; } public String getSchedulingInfo_Flags() { return schedulingInfo_Flags; } public void setSchedulingInfo_Flags(String schedulingInfo_Flags) { this.schedulingInfo_Flags = schedulingInfo_Flags; } public Integer getSchedulingInfo_RightNow() { return schedulingInfo_RightNow; } public void setSchedulingInfo_RightNow(Integer schedulingInfo_RightNow) { this.schedulingInfo_RightNow = schedulingInfo_RightNow; } public String getSchedulingInfo_ScheduleType() { return schedulingInfo_ScheduleType; } public void setSchedulingInfo_ScheduleType(String schedulingInfo_ScheduleType) { this.schedulingInfo_ScheduleType = schedulingInfo_ScheduleType; } public String getSchedulingInfo_TimeZone() { return schedulingInfo_TimeZone; } public void setSchedulingInfo_TimeZone(String schedulingInfo_TimeZone) { this.schedulingInfo_TimeZone = schedulingInfo_TimeZone; } public Timestamp getStartTime() { return startTime; } public void setStartTime(Timestamp startTime) { this.startTime = startTime; } public Timestamp getUpdateTime() { return updateTime; } public void setUpdateTime(Timestamp updateTime) { this.updateTime = updateTime; } public String getSchedulingInfo_Status() { return schedulingInfo_Status; } public void setSchedulingInfo_Status(String schedulingInfo_Status) { this.schedulingInfo_Status = schedulingInfo_Status; } public Timestamp getSchedulingInfo_BeginDate() { return schedulingInfo_BeginDate; } public void setSchedulingInfo_BeginDate(Timestamp schedulingInfo_BeginDate) { this.schedulingInfo_BeginDate = schedulingInfo_BeginDate; } public Timestamp getCreationTime() { return creationTime; } public void setCreationTime(Timestamp creationTime) { this.creationTime = creationTime; } public String getCUID() { return cuid; } public void setCUID(String cuid) { this.cuid = cuid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getID() { return id; } public void setID(Integer id) { this.id = id; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public Timestamp getEndTime() { return endTime; } public void setEndTime(Timestamp endTime) { this.endTime = endTime; } public String getErrorMessage() { return errorMessage; } public void setErrorMessage(String errorMessage) { this.errorMessage = errorMessage; } public String getFileProperties_Files_FileArray_Name() { return fileProperties_Files_FileArray_Name; } public void setFileProperties_Files_FileArray_Name( String fileProperties_Files_FileArray_Name) { this.fileProperties_Files_FileArray_Name = fileProperties_Files_FileArray_Name; } }
here is the JavaBean:
InfoObjectJavaBean.java - the DDK JavaBean
package com.sap.ddk.javabean.sample; import java.lang.reflect.Method; import java.net.URL; import java.sql.ResultSet; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Calendar; import java.util.List; import java.util.Properties; import com.businessobjects.connectionserver.CSException; import com.businessobjects.connectionserver.datasources.ddk.Context; import com.businessobjects.connectionserver.datasources.ddk.javabean.Bean; import com.businessobjects.dsws.Connection; import com.businessobjects.dsws.biplatform.BIPlatform; import com.businessobjects.dsws.biplatform.ResponseHolder; import com.businessobjects.dsws.session.EnterpriseCredential; import com.businessobjects.dsws.session.Session; import com.businessobjects.enterprise.infoobject.InfoObject; import com.businessobjects.enterprise.infoobject.InfoObjects; import com.crystaldecisions12.sdk.occa.report.application.internal.POJOResultSetHelper; /* * BusinessObjects Enterprise Data Access Driver Development Kit JavaBean * sample for reporting off of the CMS database using the BusinessObjects Enterprise * Web Services. * * Enterprise logon credentials are read from the Universe Connection User and * Password. The Enterprise path query is read from the Connection url. * * CMS name is read from the "CMS Name" Parameter defined for the connection in the * javabean.sbo. * * CMS authentication type is read from the "CMS Authentication Type" Parameter in the * javabean.sbo. * * This sample requires the XI 3 Business Objects Web Services Java Consumer and Crystal * Report Java SDK POJO ResultSet Factory. */ public class InfoObjectJavaBean implements Bean { private String url; private String boUsername; private String boPassword; private String boCMSName; private String boAuthType; private String wsSessionURL; /* * Read and store Enterprise query and logon credentials from the context * and CMS name and authentication type from javabean.sbo parameters. */ public void initialize(Context context, String url, Properties properties) throws CSException { this.url = url; this.boUsername = (String) context.getCredentials().get("USER"); this.boPassword = (String) context.getCredentials().get("PASSWORD"); try { this.boCMSName = context.getConfiguration().getString("CMS Name"); } catch (CSException cse) { this.boCMSName = "localhost"; } try { this.boAuthType = context.getConfiguration().getString("CMS Authentication Type"); } catch (CSException cse) { this.boAuthType = "secEnterprise"; } try { this.wsSessionURL = context.getConfiguration().getString("Session Web Services URL"); } catch(CSException cse) { this.wsSessionURL = "http://localhost:8080/dswsbobje/services/Session"; } } public void close() {} /* * Retrieve InfoObject collection from Enterprise Web Services, copy properties * over to InfoObjectPOJO, and generate and return ResultSet using POJO * ResultSet Factory. */ public ResultSet getInfoObjectResultSet() throws Throwable { Method[] pojoSetters; List
and finally the JavaBeanInfo:
InfoObjectJavaBeanInfo.java - the DDK JavaBean Info
package com.sap.ddk.javabean.sample; import java.beans.BeanDescriptor; import java.beans.MethodDescriptor; import java.beans.ParameterDescriptor; import java.beans.SimpleBeanInfo; import java.util.Properties; public class InfoObjectJavaBeanInfo extends SimpleBeanInfo { public InfoObjectJavaBeanInfo() { super(); } @Override public BeanDescriptor getBeanDescriptor() { BeanDescriptor beanDescriptor; beanDescriptor = new BeanDescriptor(InfoObjectJavaBean.class); beanDescriptor.setName("InfoObjectJavaBean"); beanDescriptor.setShortDescription("JavaBean from a InfoObject"); return beanDescriptor; } @Override public MethodDescriptor[] getMethodDescriptors() { MethodDescriptor[] methodDescriptors = null; try { methodDescriptors = new MethodDescriptor[] { new MethodDescriptor (InfoObjectJavaBean.class.getDeclaredMethod ("initialize", new Class[] {com.businessobjects.connectionserver.datasources.ddk.Context.class, String.class, Properties.class}), new ParameterDescriptor[] {new ParameterDescriptor (), new ParameterDescriptor (), new ParameterDescriptor ()}), new MethodDescriptor(InfoObjectJavaBean.class.getDeclaredMethod("close", (Class[])null)), new MethodDescriptor(InfoObjectJavaBean.class.getDeclaredMethod("getInfoObjectResultSet", (Class[])null)) }; methodDescriptors[0].setShortDescription ("initialize JavaBean"); methodDescriptors[0].getParameterDescriptors ()[0].setName ("context"); methodDescriptors[0].getParameterDescriptors ()[0].setShortDescription ("context of JavaBean"); methodDescriptors[0].getParameterDescriptors ()[1].setName ("url"); methodDescriptors[0].getParameterDescriptors ()[1].setShortDescription ("url for InfoObject feed"); methodDescriptors[0].getParameterDescriptors ()[2].setName ("properties"); methodDescriptors[0].getParameterDescriptors ()[2].setShortDescription ("properties for JavaBean"); methodDescriptors[1].setShortDescription ("finalize JavaBean"); methodDescriptors[2].setShortDescription ("Retrieve InfoObject ResultSet"); } catch(Exception e_ignore_at_your_peril){} return methodDescriptors; } }
Here's my copy of the DDK JavaBean Connection configuration file. Note that it references the CRJ SDK jars and the BusinessObjects Enterprise Web Services Consumer jars in the classpath definition, and specifies some runtime parameters. This file is found in the
javabean.sbo - the Universe JavaBean Connectivity configuration file
The ClassPath Path entities include the CRJ SDK library jar file CrystalReportsRuntime.jar (at the location installed with the Crystal Reports for Eclipse 2.0 All-In-One installation) and the BusinessObjects Enterprise Web Services library jars found in the
The javabean.sbo references the bean_infoobject.jar file, that contains the compiled Java program, copied to the
BusinessObjects Enterprise Web Services Connection
Connecting and logging onto the Enterprise Web Services Provider requires five items of info: the Enterprise user name, password, CMS name, authentication type, and the Enterprise Session Web Services URL. These items will be provided either in the javabean.sbo file or when configuring the connection in the Universe Designer.
The CMS Name, authentication type and Session Web Services URL are specified in the javabean.sbo file, using Parameter entities with Name attributes "CMS Name", "CMS Authentication Type" and "Session Web Services URL".
The Universe Designer presents three configuration fields when creating a JavaBean connection: the username, password and uri. In the sample, the username and password fields are used to specify the Enterprise credentials, and the uri is used to specify the path query.
Deployment
To deploy the sample, compile the Java code where you ensure you have the CRJ SDK library jars, the DDK library jar
Back up the javabean.sbo configuration file in your deployment, and replace it with the file above. Edit it to ensure it reflects where you have the required jar files. Also edit the file to configure your CMS name, authentication type and Session Web Services URL.
Launch the Universe Designer, then navigate to Tools -> Connections. Click "Add" then ensure the "Filter stored procedure network layer" option is selected. Give a name for your connection, then navigate the tree to find Java Beans -> POJO JavaBean datasource -> JavaBean. Click Next.
You will see the authentication panel. Enter your Enterprise credential user name and password, then enter a path query. A simple one to start off would be:
path://InfoObjects/Root Folder/@STATIC,SI_OWNER
to return all the root Public Folders.
Test the connection, then click Finish. Create an Universe off of this connection - JavaBeans are considered "stored procedure" connections, so ensure you enable that when selecting your connection, then insert the "Stored Procedure" into the Universe. Save the Universe.
You should now be able to fire up your copy of the Web Intelligence Rich Client, and create a report off of the Universe you just created.
Code Description
I've placed comments in the Java code, but I'll add a few more notes here.
* Items in a InfoObject object collection returned from Enterprise do not have uniformly the same properties - subclasses such as CrytalReport, Web Intelligence, User, UserGroup, Server, etc, all have a different set of properties. The returned collection may contain a mix of different subclasses.
Since a relational table row-column structure requires consistency, I specify the properties that are returned by the definition of the InfoObjectPOJO class.
The JavaBean code uses Java Reflection to introspect the InfoObjectPOJO class for all defined properties, then uses Reflection on each item in the InfoObject object collection to retrieve the property with the same name. If an InfoObject does not define a property, the property is set to null.
To extend the properties exposed by this driver, add new properties to the InfoObjectPOJO.
* The relational model of Universe tables prohibit automatic handling of nested properties, such as InfoObject.SchedulingInfo.BeginDate. To allow for flattening this to a 1-deep representation, nested properties are declared in the InfoObjectPOJO by separting the class/property names by underscores. For example, the InfoObjectPOJO defines the getter getSchedulingInfo_BeginDate() method to retrieve the InfoObject.SchedulingInfo.BeginDate property.
Also, since the relation model cannot automatically handle indexed properties, array values are translated into comma-delimited strings.
* The BusinessObjects Enterprise Web Services Consumer library uses a Thread-local-context ClassLoader. The DDK uses JNI with custom ClassLoader (used to load the classpath path entities specified in the javabean.sbo) and does not provide a Thread-local ClassLoader.
In the JavaBean code, I retrieve the ClassLoader for the JavaBean instance, and set it as the Thread-local ClassLoader programmatically, similar to what I did for the RSS feed library as described in the previous blog.
* The CRJ SDK POJO ResultSet Factory data type limitation causes all date, date time and timestamp types to be java.sql.Timestamp. Universes represent boolean values as Number and not java.lang.Boolean, but the ResultSet Factory prevents Booleans and Numbers from being nullable. So the default values are set to 0 (but the Web Services Consumer uses primitives for numbers and booleans anyways, so it's not the driver causing this lossage).
Reporting using Query as a Web Service
You're not restricted to just Web Intelligence documents when reporting off the CMS Universe connection. By building a QaaWS, you can expose the data to many other reporting types, including LiveOffice/Excel/Xcelsius and Crystal Reports.
As a simple test, I created the QaaWS from a CMS DDK JavaBean connection with the following query:
query://{Select STATIC, SI_OWNER From CI_INFOOBJECTS Where SI_INSTANCE=1 AND SI_RECURRING=0 AND SI_CREATION_TIME >= '2008.09.01'}
that returns info for all scheduled reports since September of last year (approximately when I installed BusinessObjects Enterprise XI 3.1).
I designed a Crystal Report off of this QaaWS, and focussed on the total processing time - the difference between the schedule run end time and begin time. Grouping by report name, I listed out the Top 6 by total of all scheduled run times, then plotted the result, along with average run time per instance.
Here's a screenshot:
image
A bit of a surprise there - almost half the total scheduled processing time was taken up by a single report! Maybe not suprising - the "World Sales Report" is the one I use most often for testing and creating sample applications.
The "Create Webi From Scratch", on the other hand, was a not-simple Web Intelligence document I created programmatically for load testing.
I find it reassuring that the Top 6 covers a spectrum of reporting types - Crystal Reports, Web Intelligence, Publications, Crystal Reports off of Universes, and even a RAS SDK app running from within a Java Program Object. Good to know I'm covering the field...
So even with a simple report that I whipped up just for this blog, I've gotten considerable insight into how I use my Enterprise installation. I'm thinking I should schedule this report monthly, so I can keep track of my usage.
If this was a production deployment, such info would be invaluable in tuning the system for better performance. Just goes to show how illuminat Business Intelligence (BI), Business Objects, Java Programminging having ready access to the CMS data can be.
As I conclude this blog entry, I'm looking at the report chart again and it brings back memories of very interesting Support Incidents I've worked on. Maybe I'll blog about one or two or three in the near future, before new Incidents pushes them out of my recollection...
Ted Ueda is a Senior Engineer with Technical Customer Assurance, SAP Business Objects.
