Queries to identify universes and their universe connections

Query Builder connections for troubleshooting 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.

Author: Dallas Marks

I am a business intelligence architect, author, and trainer. I help organizations harness the power of analytics, primarily with SAP BusinessObjects products. An active blogger, SAP Mentor 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 about business intelligence.

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

  1. Pingback: Query Builder 4.0

Leave a Reply