Queries to identify universes and their universe connections

The Query Builder can be used to analyze universe connections. I recently used the Query Builder to assist in how many universes (and which ones) were using a specific connection. Using the information, I was able to update and republish universes to use the “preferred” universe connection. Once all universes were updated, the Query Builder clearly showed that the “non preferred” universe connections were no longer in use and could be safely deleted using the Central Management Console (CMC).

First, determine the SI_ID of the connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
— AND SI_NAME = ‘DW’

ORDER BY SI_NAME

Then, use the list of IDs in the SI_CONNUNIVERSE collection to build a second query to inquire about the universes that use that connection.

SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND=’Universe’
AND SI_ID IN (692,670,633,634,699)

The Query Builder can also identify unused universe connections. Using the first query above, look for connections where the SI_CONNUNIVERSE has a single attribute of SI_TOTAL with a value of 0. For example, no universes are using the test only connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
AND SI_NAME = ‘test only’

ORDER BY SI_NAME

Accordingly, the test only connection can be safely deleted from the Central Management Console (CMC).

Resources

If you’ve found a useful resource regarding the Query Builder, feel free to post a comment to share with others.

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

6 thoughts on “Queries to identify universes and their universe connections

  1. Pingback: Query Builder 4.0
  2. Now, how can I combine the 2 queries, and get the list of all universes and their connection IN ONE SHOT?

Comments are closed.