In Business Objects how can I get a list of reports using a specific universe?

Posted on by 169 Comments

I have been asked the following related questions a few times in the past months and I thought it would be a good idea to post a public answer to them. First the questions:

  • Is there any way to get a list of reports that are using a specific universe?
  • If I change this universe which reports will be affected?

The Answer – Yes, Definitely, Using Query Builder

The short answer to these questions is “Yes, you can”. The slightly longer answer is, “Yes, this can be done through Query Builder, the BO Auditing data*, or through the Business Objects SDK.” For the purposes of this article, I will confine my further comments to the Query Builder and BO Auditing data solutions; the BO SDK solution requires a set of skills and even licenses that many BO Developers and Administrators do not possess.

I haven’t been keeping track, but I expect that those who have emailed me this question have not studied The Best Query Builder Guide Ever Written. I just wanted to point out that our Query Builder Guide answers these questions, and provides so much more.

The Resolution using Query Builder

For those of you familiar with SQL or just the concept of tables you might think that all you have to do is join up the record of the specific universe with all of the records of the reports where it is used. Well, this is partially true. Firstly, if you know something about query builder you know that reports and universes are stored in separate logical tables and you know that you can’t really join two tables together. The solution here is two part: (1) query the record for the specific universe retrieving all associated report IDs and then (2) query for for each of those report IDs to get the reports’ identifying information.

Query the Universe’s Reports

This is a straightforward query, but be careful if you do not have the universe’s object ID, because it is possible to have two universes with the same name and you wouldn’t want to get wrong data from the start. Here is the SQL statement you need to put in Query Builder:

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
si_name = 'UNIVERSE_NAME' AND
si_kind = 'Universe'

From this you will get some additional universe info and a list of all reports that are bound to this universe. Copy that list of report object IDs to a text editor and proceed.

Query the Report’s Identifying Information

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string “111111,222222,33333 below in the SQL:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_id IN (111111,222222,33333) AND
si_kind = 'WebI' AND
si_instance = 0

The output should be your desired list of reports. Now that you see the “si_universe" property you might ask yourself why I don’t query the report table for my universe’s object ID. Good idea, but Business Objects’ Query Builder query language doesn’t yet support filtering on “property bags”, and si_universe, just like “si_webi” are property bags. They hold multiple values in a single property.

The Resolution using BusinessObjects Auditing Data

If you have enabled auditing on your environment’s report servers then you can look to your Auditing data for an answer to this question. However, auditing data is not meta data; it’s records are created as the objects are used. Therefore if a report is never used then it will not be present in this data set. If this is not a concern, or if you would like to know which reports have been used belong to a certain universe, then querying the auditing data is an option for you.

Querying the Auditing Records

There are many ways to mine this data source for the data you need. For most folks the best, easiest method is to use the “Activity” universe. Here you will find the objects you need. A tutorial on this universe and the auditing data is out of scope here, but I’d like to put together a guide on the topic if I hear of any interest. Another way to mine your data is directly against the database tables (these tables are not encrypted like the Business Objects XI CMS Infostore. This certainly allows for better query tuning, but it will increase the complexity for you. You could even use the SQL of a report created against the “Activity” universe as a starting point, but be careful as that universe is full of derived tables.

Final Thoughts

Both of these steps might seem difficult at first, but with a little practice you will find that you can whip out answers to these kinds of questions rather quickly. In fact, now you have the tools to answer the reverse as well: “How can I get a list of universe using a specific set of reports?”. Now you know, and knowing is half the battle.

Enjoyed this post? Share it!

 

169 thoughts on “In Business Objects how can I get a list of reports using a specific universe?”

  1. Dear Julian,

    How can we getting from the auditing DB using activity universe, list of report refreshed/Schedule for a specific universe.

    Or is there any Macro showing report name, type,status active or paused ,owner,Univername

  2. Hi Neha, no macro is needed. “refreshed” reports will be found using the Activity universe. That is straightforward. Scheduled reports will be found using Query Builder only. Just follow the directions in the article and look for the field called “si_scheduled”.

  3. Thru Query builder, Is there a way I can get List of users/usergroups who have access to a specific universe?
    Environment: Business Objects XI 3.1

  4. Hi Leo, I have never been able to get a BO object security query to work in Query Builder. Perhaps someone reading here has and could kindly share any ideas or queries.

  5. Along a similar line of thinking… Is it possible to get a list of Crystal Reports(published to BOE) that hit a specific table on a specific database?

    The above question assumes a command object is not used and the Crystal Report is linked directly to a table in a database.

    Also, if a command object is used….is there some way to query the body of the command object via the CMC or using Query Builder, or some other method?

    Thank you

  7. Hi Julian,
    First of all thanks for sharing such usefull information and maintaing it overtime.
    I was wondering if there is a way to generate a list of objects used in a report using query builder or sdk ?
    I am able to generate a list of reports and list of objects seprately but havent been able to link them togeather.
    Thanks & Regards
    Ahmed

  8. charantej, your questions could be taken as how do I get a list of reports which used a specific server. If you are talking about historical usage then you should be looking at auditing data. The “server_cuid” on auditing data tells you all you which report server was used for the Business Objects report refresh.

  9. Julian,

    I’m trying to activate the Auditing feature within BusinessObjects 6.5 so that I can gather more data about what reports are being run (and hopefully from where). However currently the Audit menu (Off, System, or User and System) will not allow being set to anything but Off. There is an additional message below that states that a connection needs to be created in Supervisor to enable Auditing.

    I’m not very familiar with BO, so having inherited the system from a prior administrator. As such, I’m not sure how to proceed to get Auditing up and running.

    Would it be possible for you to help me with this? Perhaps we could discuss directly via email?

    Thanks

    Ryan

  10. Dear Julian,
    I am using BO 4.1(.Unv) , I want to add optional filter into the universe.
    Is there any way by which we can create a universe level filter condition ?
    Thanks a lot.
    Thanks
    Mayur

  12. Hi Naveen, I don’t think it’s possible to distinguish macros embedded in DeskI reports through Query Builder. Sorry.

  13. Hi I have an requirement to find unused universe and also i need find all universe based reports, one bye one we find, but we have 150 unv, we need an query to find one shot, if any one knows please post the query.

  14. Hi Julian,

    Is it possible to generate webi reports based on the sql created in Query Builder? How would I do this? Can I access the queries through the Universe?

    Singaram

  15. Sorry Singaram, this is not at all possible. I wish it were, but do far, SAP Business Objects has not offered any drivers to allow such a connection. I’d love to be able to do this, but even Query Builder is an outdated afterthought.

  16. I am tasked with identifying reports from a given universe that use a specific table or dimension in the report either as a result object or query filter. Is there anyway through Query builder or Auditor to do this in BO 4.1?

  17. Hi,

    Is there a macro/mechanism to find out what all objects are unused from an Universe (not used in any of our BOXI reports including public and user Favorites folder) so that we can delete them from our Universe before moving onto new version.

    Thanks

  18. Hi Everyone,

    Recently i am facing one issue and need all your help and suggestion on this.I have around 1000 reports and I need to get all the tables which are used by this 1000 reports. I don’t want to go each and every report to get the table details but want to prepare on query and execute it in query builder to get the details.Is there any way to make this query and execute the same in Query builder to retrive all the tables at one go.

Leave a comment

Your email address will not be published. Required fields are marked *