Navigation:  Querying and Reporting with CMS/Connect >

The Basics: How do I query my enterprise metadata with CMS/Connect?

Previous pageReturn to chapter overviewNext page


The CMS/Connect universe is essentially a human-readable translation of the Business Objects enterprise metadata.  It should be kept in mind that it is real-time and sits atop a deployment in a live mode.  Because of this, CMS/Connect also inherits some of the behaviours of how enterprise metadata is stored.




Let's take a closer look at the table above.  This table represents data that's stored within our CMS, which is towards the right.  CMS/Connect essentially combines all three of the CMS virtual tables, into a single, fully denormalized table.  Once denormalized, certain columns that may be relevant to one SI_KIND, or object type, might not be relevant to another.  The reports (in blue), users and groups (in pink) and connection/universe metadata (orange) are all physically present in the same physical database.


The columns shown above are SI_ID, SI_NAME, SI_KIND, SI_AUTHOR.  These are Main Header classes that are common to all objects in the CMS.


If we were to execute a query in Web Intelligence, it would appear so follows:



The Result Objects Pane from Web Intelligence Rich Client


And the SQL query might look like:




When we run this query from Web Intelligence, the following table is produced:



CMS/Connect result set of SI_NAME, SI_ID, SI_KIND, SI_AUTHOR



Supposing we wish to retrieve more specific information about the "User" object type.  Let's browse to the User universe class and see what information we have available to us.



CMS/Connect User Class


The moment we drag/drop any of the objects from the Users class into our query, it will immediately force the SI_KIND='User' clause to be created within our SQL.  Let's add SI_USERGROUPS to our query.  By adding this object, we're basically asking CMS/Connect, "Show me a listing of all users and the usergroups to which they belong".  SI_USERGROUPS is a nested property of the User object.  It is this property that we are retrieving that specifically requires us to query only for SI_KIND = 'User'.



SI_USERGROUPS Added to the query


The SQL of the query now looks like:


 ( SI_KIND='User'  )


When we run this query, the following result (although here, we are not showing SI_USERGROUPS yet) appears:




We already notice that the SI_KIND column only includes User objects, although we did not explicitly ask the query to restrict our results by this type.  Instead, it forced the SI_KIND = 'User' restriction because we asked to also retrieve SI_USERGROUPS.


If we add SI_USERGROUPS* to the report, it looks as follows:



The result set includes SI_USERGROUPS (title), which provides group membership data for each User


* In this example we use the (titles) version of SI_USERGROUPS. Objects marked as [ObjectName (titles) ] denote columns that are translated to human-readable form on-the-fly for  easier reporting.


Use the following rules as a guide when performing analysis with CMS/Connect.  As we explain the rules, we will show invalid queries, and then some valid ones.


Rule #1  Queries may only be created off of the Main Headers Class and one additional class (not including the Security and Rights class)


Suppose we query: SI_NAME, SI_DOCUMENTS, SI_UNIVERSE (titles) from the following classes:




If we take a look at the SQL generated by this query, we see the following:

  ( SI_KIND='Category'  )
  ( SI_KIND='FullClient'  )

The reason why this query will not work is simply because each class has a mandatory class filter that forces the SI_KIND='xxxx' property to be included for each class being queried.


So, if the actual data in the CMS resembles the following dataset:






Sales Revenue




Production Documents








It would be impossible for a query to return BOTH the row containing "FullClient" data AND the row containing "Category" data.


However, if we remove the "SI_KIND" condition from either one or the other, we will successfully retrieve a result.


A valid example of the previous query would be:




Generated SQL:

 ( SI_KIND='Category'  )



Here we have a query against two classes: Main Headers and Category


Whenever we query from Main Headers and an additional class, the data in the Main Headers class will be restricted by the type of data that's included in the additional class used in the query.  So, in this case, we're querying for Main Headers data but specific to objects of type "Category".



Rule #2  At least one object from the Main Headers must be included in any CMS/Connect query


The Main Headers class should be thought of as the driving class behind any query in CMS/Connect. Main Headers describe the basic properties common to all forms of metadata, whether its Web Intelligence, Universe, Crystal Report or Server objects.



Rule #3  Universe classes have a mandatory SI_KIND filter with the exception of [Main Headers] and [Security and Rights]


As already discussed, each domain-specific class, such as Users or Web Intelligence contain columns (or properties) unique to that object.  In order to properly retrieve results of that object, a mandatory filter is applied to the CMS/Connect SQL query.