BusinessObjects Enterprise SDK – Relationship Queries

Edit: So I finally got around to fixing up the format for this blog for the current SCN web site – it seems a few people find the content pretty useful 🙂

I work for SAP Business Objects, in Technical Customer Assurance.  My speciality is the Software Development Kits (SDKs) that we provide with our Business Intelligence products – BusinessObjects Enterprise, Web Intelligence, Desktop Intelligence, Crystal Reports and Crystal Xcelsius.  </p><p>In my blog, I discuss subjects that I personally find interesting – little known or not-well-documented corners of the SDK, new functionality or new SDKs, or interesting issues that I’ve come across in a SAP Incident or SAP Developer Network forums.

You’re more than welcome to suggest any topic (SAP Business Objects SDK related, of course…) that you’d like me to discuss – I have a dozen or so items on my blog to-do list, but I’m always on the hunt for anything interesting with our SDKs.

Are you managing content on a BusinessObjects Enterprise deployment?

Are you a developer creating an application using the BusinessObjects Enterprise SDK?

If you’ve answered yes to either question, then this blog entry may be of interest to you. Specifically, it’s of interest if you’ve ever had the requirement to query the BusinessObjects Enterprise Central Management Server (CMS) for information concerning repository objects. 

Here’s a few of the tasks you’d be able to complete after reading this article:

• Retrieve all Users belonging to two or more specified User Groups.
• Retrieve a document in a specified Folder path using just one query.

You’ve likely noticed something common among all three tasks – they all involve queries across relationships between objects managed by the Enterprise CMS: between a document and a Universe, a User and a UserGroup, or a document and a Folder.

What I’m going to cover here is a little-known corner of the BusinessObjects Enterprise administrative and SDK toolkit – Relationship Queries.  These aren’t part of the public API and won’t be documented, but do come in handy at times.

BusinessObjects Enterprise Query Language

If you’ve developed applications that integrate with BusinessObjects Enterprise, Crystal Reports Server or BusinessObjects Edge, or if you’ve worked with the Query Builder you’re familiar with the Enterprise Query Language.

The Query Language is a SQL-like language used to query for repository objects called InfoObjects.  An InfoObject may be a document, a document Instance, User, UserGroup, Publication,  Server, etc. – any object managed by the Enterprise Central Management Server (CMS).

Here’s a simple example – log onto the Query Builder as administrator, and enter the following query:

Select SI_ID, SI_NAME, SI_USERGROUPS From CI_SYSTEMOBJECTS Where SI_KIND=’User’ And SI_NAME=’Administrator’

that queries the CI_SYSTEMOBJECTS table for the SI_ID, SI_NAME and SI_USERGROUPS properties of the ‘Administrator’ User.  You’ll see that the SI_USERGROUPS property is a list of numbers – it should be ‘1’, and ‘2’.  This property specifies the SI_ID values for all UserGroups to which the User is a member. 

To find which UserGroups these SI_ID numbers represent, you can run the query:

Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’ And SI_ID In (1, 2)

and you’ll see that they’re the ‘Everyone’ and ‘Administrators’ UserGroups.

It’s a bit inconvenient to run two queries, one to to find the SI_IDs of all UserGroups that a User belongs to, and another to translate the SI_IDs to UserGroup names. You can, in fact, accomplish the task using just one query:

Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”, “SI_NAME=’Administrator'”)

This query looks up the User with name ‘Administrator’, and finds the UserGroup associated with the User using the PARENTS relationship function, without the need to have an intermediate query for the SI_USERGROUPS property.

The SI_USERGROUPS property is an example of a Relationship between InfoObject types, here between UserGroup and User types, and the PARENTS query function is an example of a Relationship Query, here based on the ‘UserGroup-User’ relationship.

Query for Universes using ‘efashion-webi’ and/or ‘club-webi’ DataConnections and owned by ‘Administrator’
Select
    SI_ID, SI_NAME, SI_OWNER
From      
    CI_APPOBJECTS
Where
    CHILDREN(“SI_NAME=’DataConnection-Universe’ “, “SI_NAME=’efashion-webi’ OR SI_NAME=’club-webi'”)    
    And SI_OWNER=”Administrator”

I’ll note a quirk with the Enterprise Query Language that you can see in the above example.  All string literals must be delimited by matching double-quote (“) or single-quote (‘) characters, where double-quote characters may only be used top-level, so any nested string literals must be delimited by single-quotes.   You’ll see in later examples that string literals nested in single-quote delimiters must be escaped by an additiona single-quote character.  So you’d use double-quote delimiters at top-level, then single-quote delimiters at the next level, then double single-quote (”) delimiters after that (and not single double-quotes), then quadruple single-quotes (””) at the next level, etc.  It does get a bit difficult to read for more complex queries…

Another quirk – the Query Builder tool doesn’t like return characters, the above example was formatted for readability, but make sure you edit the statement to be on a single line if you want to run it in Query Builder.

Multiple Relationships

You can combine two or more relationships to create quite complex queries, and this is where the power of using relationship queries pays off.  Such complex queries go beyond what’s available in the Central Management Console (CMS).  Furthermore, it simplifies application development when using the Enterprise SDK – instead of running multiple queries and doing set intersection/union computations, you can offload much of the work to the CMS by using a more refined query.

Here’s an example.  Say you’re tasked with finding all Users belonging to the UserGroup ‘UserGroup1’ who also have Query as a Web Service (QaaWS) creation/modification rights (i.e., member of the ‘QaaWS Group Designer’ UserGroup in XI 3).  If you have thousands of users, it’ll take quite an effor to compare the list of Users in the two UserGroups.

Using relationship queries, you’d merely execute the following:

Query for Users in ‘UserGroup1’ and ‘QaaWS Group Designer’
Select   
    SI_ID, SI_NAME
From      
    CI_SYSTEMOBJECTS
Where     
    CHILDREN(“SI_NAME=’UserGroup-User’ “, “SI_NAME=’UserGroup1′ “)   
    And CHILDREN(“SI_NAME=’UserGroup-User’ “, “SI_NAME=’QaaWS Group Designer’ “)’

and there you have it.  The set intersection of Users belonging to ‘UserGroup1’ and belonging to ‘QaaWS Group Designer’ is done within the CMS.

Nested Relationship Queries

Even greater power comes from nesting multiple relationship functions.  This allows you to bridge a relationship between two InfoObject types that do not have a direct relationship defined.

Here’s an example.  You’re tasked with finding all other UserGroups that Users in the ‘Administrators’ UserGroup belongs. You can use a relationship query to query for all Users in the ‘Adminstrators’ UserGroup, then use another relationship query to query for all UserGroups those Users belong. Or, you can combine the two queries using nested relationship functions:

Query for UserGroups of Users in ‘Administrators’ UserGroup
Select   
    SI_ID, SI_NAME
From   
    CI_SYSTEMOBJECTS
WHERE   
    PARENTS(“SI_NAME=’UserGroup-User'”,  “CHILDREN(‘SI_NAME=”UserGroup-User” ‘, ‘SI_NAME=”Administrators” ‘)”)

(note that use of double single-quotes to delimit the name string literals). 

Building up a Nested Relationship Query using Query Builder

The Query Builder tool comes in handy when you have to build up complex queries – you can iteratively refine and add to a query, until you obtain the desired result.

I’ll walk through an example here.  Say I’m tasked with finding all Folders that contain Web Intelligence or Desktop Intelligence documents that report off a Universe  using the ‘efashion-webi’ DataConnection.

I log onto Query Builder, and construct a query that returns me all Universes connected to the ‘efashion-webi’ DataConnection:

Universes using ‘efashion-webi’ Data Connection
Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS
Where   
    CHILDREN(“SI_NAME=’DataConnection-Universe'”,”SI_NAME=’efashion-webi'”)

Query Builder shows me that the query is returning the correct results.  Now I nest this query into one tha returns me all Web Intelligence and Desktop Intelligence documents the reports off the Universes:

Documents using ‘efashion-webi’ Data Connection
Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS, CI_INFOOBJECTS
Where   
    PARENTS(“SI_NAME=’Webi-Universe'”,
            “CHILDREN(‘SI_NAME=”DataConnection-Universe” ‘,’SI_NAME=”efashion-webi” ‘)”)

Note that I alter the string delimiters when nesting, changing double-quotes to single-quotes, single-quotes to double single-quotes, etc.  Note also that, since I’m querying for documents and Universes, I add the CI_INFOOBJECTS to the search tables.

Finally, I write the relationship that gives me the parent folder for the documents (this won’t work in XI Release 2, since the ‘Folder Hierarchy’ relationship type was introduced with XI 3.0):

Folders containing documents using ‘efashion-webi’ Data Connection

Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS, CI_INFOOBJECTS
Where   
    PARENTS(“SI_NAME=’Folder Hierarchy'”,           
            “PARENTS(‘SI_NAME=”Webi-Universe” ‘,                     
                     ‘CHILDREN(”SI_NAME=””DataConnection-Universe”” ”,
                               ”SI_NAME=””efashion-webi”” ”)’)”)

and there you have it!

Summary

I hope you found this article on Relationships Queries interesting, and that their use will simply administrative tasks and lessen programmatic effort.