The system CMS is an encrypted transactional repository where we can find the “current snapshot” of what is going on in BusinessObjects. Everything related to content, security and platform services can be found here, at a real time. Bringing this information into our reporting environment has always been key for administrators and power users and a line of investigation for years. Automating the capture of this data is the starting point for projects involving data quality reports, monitoring, alerts and corporate portals.
Despite many attempts made on this, you might like to try a simple and cost-free approach. In this case please read through this article which goes through the existing options and ends up with an original method to obtain the raw data you need for your projects.
Query builder
This is the “official” way to obtain the information from the CMS, using a “pseudo-SQL” language. This module existed in XI R2, XI 3.x and BI4 with minimal differences between versions.
Its advantage is that it is a straight and mature method with much literature available in the net. Just by typing “BusinessObjects Query Builder queries” in your search engine you will get enough instructions to query basic data at a mouse click.
Some disadvantages for this are:
Deep knowledge of the database structure is needed
The data is presented as it is stored -> Sometimes it is difficult to visualize
The query language is very limited -> No transformations are allowed
There is not “export to file” option! Instead a manual copy & paste of the screen’s result can be used, which is far from a flat file format
3rd party tools
Having discovered this limitation in the BusinessObjects tool and in order to fill in the gap, some companies decided to work on the encrypted algorithm emulating Query Builder. A few of them launched a product in the market including a Universe on top of CMS. This can be found in the net by typing e.g the following key words: “BusinessObjects CMS universe”.
The disadvantages of such tools are:
They might not offer the whole set of information
There is a risk that the vendor, typically a small company with low resources, does not support new versions or specific customer requirements or simply disappears, leaving the product out of maintenance and their customers unattended
Performance against the CMS repository in busy hours can be very poor
You need to pay for it
BI4 monitoring
In SAP BI 4 this module was introduced in the administrator console, in an attempt to regularly capture and store snapshots of key metrics from the CMS during time for the most recent history. The idea of having a repository being loaded and fully integrated in the platform with a Universe on top of it is really brilliant, but unfortunately it still constitutes a limited and partial approach because it:
Focuses on pre-selected content related to servers performance and not the user content
Is too high level - KPI-focused
Lacks the ability to control time periods
Reduces performance of the overall platform
A new approach
Having seen the above and taking some ideas out of it, we have developed a method to query the CMS with the following features:
All available information can be obtained
Data is easily extracted in flat files
Extraction of the info only happens at a certain time, and only from the info we need
A customized Universe can be used to allow any transformation
Reporting may happen on top of a reporting database with very fast response
Now we will shortly explain these and complete the explanation with a practical exercise:
In here the genuine & native Query Builder SQL language can be used. SAP has many articles and manuals explaining it, like the following article: http://scn.sap.com/community/bi-platform/blog/2012/10/11/businessobjects-query-builder-queries, so we ensure any piece of information can be extracted
This is the core of our method. We are not using the Graphical User Interface but a direct script which queries exactly what we need. See below a detailed explanation about this
Our extraction process can be triggered at specific times with low activity – e.g. lunch time, weekends – using a SAP BusinessObjects scheduled program or a scheduled task on the server side
The flat files can be uploaded to a professional database and from there being queried using a Universe, or alternatively, a Universe directly on top of the flat file can be used
The queries on top of small tables with a very fast performance can be designed using e.g. Web Intelligence
Applicability: List of Documents with their assigned Categories
We will show the 5 steps of our method applied to a real-life example, where a customer requires a list of documents and their assigned Categories.
1. Generate the needed query lists:
List of categories:
SELECT SI_CUID,SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND='Category'
List of documents and their categories:
SELECT SI_CUID,SI_ID, SI_NAME, SI_CORPORATE_CATEGORIES, SI_PERSONAL_CATEGORIES FROM CI_INFOOBJECTS WHERE (SI_CORPORATE_CATEGORIES.SI_TOTAL>0 OR SI_PERSONAL_CATEGORIES.SI_TOTAL>0
2.Locate the querybuilder.jar file (from the BusinessObjects installation), copy it into C drive and type the following from the command line, one after the other.
List of categories:
cmd /c c:\querybuilder.jar -cms:BOSERVER:6400 -username:Administrator -password:Xxxxxx01 -query:"SELECT SI_CUID,SI_ID, SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND='Category'”-csv:”c:\Categories.csv"
List of documents and their categories (I)
cmd /c c:\querybuilder.jar -cms:BOSERVER:6400 -username:Administrator -password:Xxxxxx01 -query:" SELECT SI_CUID,SI_ID, SI_NAME, SI_CORPORATE_CATEGORIES, SI_PERSONAL_CATEGORIES FROM CI_INFOOBJECTS WHERE (SI_CORPORATE_CATEGORIES.SI_TOTAL>0 OR SI_PERSONAL_CATEGORIES.SI_TOTAL>0)"-csv:” c:\CategoriesDocs.csv"
List of documents and their categories (II)
cmd /c c:\querybuilder.jar -cms:BOSERVER:6400 -username:Administrator -password:Xxxxxx01 -query:" SELECT SI_CUID,SI_ID, SI_NAME, SI_CORPORATE_CATEGORIES, SI_PERSONAL_CATEGORIES FROM CI_INFOOBJECTS WHERE (SI_CORPORATE_CATEGORIES.SI_TOTAL>0 OR SI_PERSONAL_CATEGORIES.SI_TOTAL>0)"-csv2:”c:\CategoriesDocs2.csv"
Notice that the 2nd requirement (List of documents and their categories) is complex and 2 query generations are needed, that can be discriminated using –csv and –csv2 command fields
This can be adapted to be used in a .bat or .vbs file so it is scheduled to be run frequently.
3.Extract the following 3 tables with the following fields:
List of categories (Categories.csv):
- SI_NAME -> Category Name
- SI_ID -> Category SI_ID
- SI_CUID
List of documents and their categories (I) (CategoriesDocs.csv):
- SI_ID -> Doc SI_ID
- SI_CUID
- PROPERTY
- VALUE -> Category SI_ID
List of documents and their categories (II) (CategoriesDocs2.csv):
- SI_NAME -
- SI_ID -> Doc SI_ID
- SI_CUID -> Doc CUID
The fields actually needed for reporting are only three and are shown in bold
4.Define a Universe on top of these tables:
1
5.Create a WebI document on top of this Universe:
2
This is just a short example of an automated query generation on top of CMS. Next steps could be enriching the universe by adding instances, document owners with the groups they belong to, etc. In principle any piece of information coming from the CMS is possible.
This method has been tested used for all SAP BusinessObjects releases, ranging from XI R2 to BI 4.1.x in Window environments.
Summary and Benefits
By using this approach users can build their own reporting environment on top of the CMS repository to be fully customized to their needs. All you need is some knowledge of Query Builder which is easily accessible from the net.
Specific benefits for customers are:
Those who have nothing in place can start receiving SAP BusinessObjects system snapshot insights very quickly and at a zero cost
Those who already have a 3rd party tool can complement their information – not provided by the vendor due to lack of maintenance / end of life support – by adding Universe capability and information queried in this alternative way
The benefit for SAP corporation is to give a workaround for existing ideas that have never been implemented - e.g. “Query Builder - Output to csv and schedulable” at https://ideas.sap.com/. With the method shown in this article it would not be too difficult for SAP to create an “Export to File” button in the SAP BI BusinessObjects Query Builder module or embed Query Builder petitions in BOE scheduler so insights are sent automatically to users.
Please let us know your comments or questions below