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.

 

clip0012

 

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:

 

clip0014

The Result Objects Pane from Web Intelligence Rich Client

 

And the SQL query might look like:

 

SELECT
 Raw.CMS.SI_ID,
 Raw.CMS.SI_NAME,
 Raw.CMS.SI_KIND,
 Raw.CMS.SI_AUTHOR
FROM
 Raw.CMS

 

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

 

clip0015

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.

 

clip0016

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'.

 

clip0017

SI_USERGROUPS Added to the query

 

The SQL of the query now looks like:

 

SELECT
 Raw.CMS.SI_ID,
 Raw.CMS.SI_NAME,
 Raw.CMS.SI_KIND,
 Raw.CMS.SI_AUTHOR,
Raw.CMS.SI_USERGROUPS_COMPUTEDTITLE
FROM
 Raw.CMS
WHERE
 ( SI_KIND='User'  )

 

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

 

clip0018

 

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:

 

clip0019

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:

 

clip0009

 

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

SELECT
 Raw.CMS.SI_NAME,
 Raw.CMS.SI_DOCUMENTS,
 Raw.CMS.SI_UNIVERSE_COMPUTEDTITLE
FROM
 Raw.CMS
WHERE
 (
  ( SI_KIND='Category'  )
  AND
  ( 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:

 

SI_NAME

SI_DOCUMENTS

SI_UNIVERSE

SI_KIND

Sales Revenue

----

11

FullClient

Production Documents

12

33

92

 

----

Category

 

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:

 

clip0020

 

Generated SQL:

SELECT
 Raw.CMS.SI_NAME,
 Raw.CMS.SI_DOCUMENTS
FROM
 Raw.CMS
WHERE
 ( 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.