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. Hi Julian,
    First of all thanks for this extremly helpful tpoic that you have discusssed in very easy way.I have a question.
    I tried to run the Query to obtain the List of all the Universes in the CMS and I got through it and even by using second query in the query builder that you gave, I got the list of Reports in particular Universe. Now I want to make a “WEBI” reports which will show this Details. How Should I create the WEBI reports? Also in this WEBI report I want the name of the owner of the Universe, name of owner of the reports and Report Path.I need to create “two” separate “WEBI” reports: 1. “List of all universes” in the CMS with their name and name of their Owner
    2. “List of reports” contained in each Universe,having universe name,Report Name,Report owner name and Report Path.
    Can you please tell me how can this be done..Please reply

  2. Hi Julian,

    It is very useful article…I have a question. Can you plz tell me how can I obtain the report’s path using query builder.I tried to use the queries in the article to obtain the Universes and the Reports asssociated with the Universe, but now I want the path of each report as well as the Owner’s name of the report as well as of the Universe

  3. Hi Ani, outputting the path of a report is not a simple thing, you can get the parent folder, but you can’t really have any query tell you the entire path. Nevertheless, as of BO XI R2 SP4 and all version afterward, all report refreshes that are audited will store the entire folder path of a report. So if you have auditing data, you can get the path there for any report that has been refreshed within the range of the auditing data.

  4. Hi Amushree, unfortunately if is not possible to put Query Builder output into a WebI report. This is a limitation that BO has imposed since the first release of XI (it came from Crystal). The data is all encrypted and Query Builder is a tool that uses the BO SDK to access the data, the query language for QB is the same as that for BO SDK.

    Nevertheless, if you must obtain the data you specify in a refreshable WebI report then you will need to start playing with the SDK. I have used JSP recently to get similar data, my customer own the work so I can’t just give you the code, but that doesn’t matter because SAP-BO has given you some good things to start with at their “SAP Community Network Wiki – Business Objects – Java BusinessObjects Enterprise SDK Samples” page. Take a look at that and then you will see that you only need to figure out how to get that data into a database (hint, I wrote to a file adn then loaded the file to a database).

  5. Hi,

    It is helpful….I read above replies and i wanted to knw that how can we query auditing data to obtain the report path..please can you give me step wise elaboration to do this

  6. Hi Ritu, my apologies for the delay, here is the Oracle SQL query against XI R2 and XI 3.X, the final filters will need to be adjusted to your requirements:

    SELECT
    ae.event_id,
    et.event_type_description,
    ae.user_name,
    ae.start_timestamp,
    ae.duration,
    ae.error_code,
    sp.server_fullname,
    dt.detail_type_description,
    TO_CHAR(TRIM(ad.detail_text))
    FROM
    Audit_Event ae,
    Audit_Detail ad,
    Event_Type et,
    Detail_Type dt,
    Server_Process sp
    WHERE
    ae.server_cuid = ad.server_cuid AND
    ae.event_id = ad.event_id AND
    ae.event_type_id = et.event_type_id AND
    ad.detail_type_id = dt.detail_type_id AND
    ae.server_cuid = sp.server_cuid AND
    UPPER(et.event_type_description) IN ('DOCUMENT REFRESH','DOCUMENT REFRESHED') AND
    UPPER(dt.detail_type_description) IN ('OBJECT FOLDER PATH') AND
    ae.event_id = '1234567890'
    --( ae.start_timestamp BETWEEN SYSDATE-1 AND SYSDATE )

  7. Maybe you have already answered this, but my question is…

    When I run a query, can I get it to show me the SI_DB along with the SI_OWNER, SI_AUTHOR, ETC. I thought maybe there was a way to go through the SI_LOGIN_INFO to the SI_LOGIN1, then to the SI_DB.

    Thanks.

  8. Hi Stephanie,

    I guess you can use SI_PROCESSINFO.SI_DBNEEDLOGON to fetch the DB Logon information of a user.

    Thanks,
    Sambasiva.

  9. Hi,

    how to identify the fact tables and dimension tables in an universe???
    How many fact tables an universe can have? Is there any restriction??
    Just by visual analysis is itpossible to identify those tables????

    Regards,
    Ramanathan S

  10. Hi Ram, you might be able to get a good idea by looking at the diagram and by looking at the table values (right click > table values), but there are no guarantees. This is really a DB question and it will depend on your understanding and skills of DB modeling. The universe might provide more insight because of the cardinality, but a good Entity Relationship Diagram would do the same.

  11. hey.. thanks for very useful info… How do I get the list of user-defined universes from the BO?? please help me..

  12. Hey Julian and others,
    I hope i am not too late. When a user tries to run a report, I want to be able to check the universe of the document and check that against the current universe in the application. This way i can inform users that their universe is out of date and suggest they upgrade universes. however, given a certain Document Instance, I can only retrieve that doc’s universe. I have my list of universe names, but the changeUnvierse method requires universe Id. I figure QUery Builder can help me get access to all my universe id’s/names and help me solve this problem. However, i am brand new to Query builder and am having trouble figuring it out. Thanks!

  13. Hi Judah, I am not entirely certain what you want to do, but if you want to obtain a lit of all universes and their object IDs this can be done with the query:
    SELECT
    si_id,
    si_name
    FROM
    ci_AppObjects
    WHERE
    si_kind = 'Universe'

  14. Pingback: Confluence: Griffin

  15. Hi Julian,
    The above article is very informative. Many thanks for sharing it.
    I’ve a odd requirement, please advice if this can be possible thru query builder.

    In addition to report name per universe, I like to extract the objects name by per report per universe. Is it possible?

  16. Hi Sumant, yours is a logical requirement. Unfortunately, objects used in a report are not stored in the CMS and therefore they cannot be queried this way. However, it is possible to query them from Auditing data, if “Document Refresh”/”Report Refresh” auditing is enabled. The limitation is that the fields are listed as text strings and of course the report has to have been refreshed in order to view the data.

  17. can we have query that will retrive the user list from specified group ?
    if anyone could provide me the same taht would be great help …
    –
    thanks,
    Vilas

  18. hi julian,

    please let me know that how to find the no of users in a group?

    i mean to say how many no of users are residing in the repository?

    is there any query to find out the no of users in BO??

    Please help me..

    cheers
    Meredianblues

  19. Hi Vilas, Please try this:
    SELECT *
    FROM CI_SystemObjects
    WHERE children("si_name = 'usergroup-user'", "si_name = 'SUBSTITUTE_GROUP_NAME_HERE'")

  20. Hi Meredianblues, sorry I missed your query request. Try this:
    SELECT COUNT(si_id)
    FROM CI_SystemObjects
    WHERE si_kind = 'User'
    This will count all users, even the system ones like “Administrator” and “Guest” so if you want to filter these out then you will need to add a where clause to do so.

  21. can anybody tell me the query which will give the number of reports using a particular object

  22. In Business Objects it is not possible to query objects used in a report from the metadata. The only options are some macro that might open each report and generate an inventory of all objects in the report or to query your auditing data. Each time a report is refreshed all objects in the report are recorded to the auditing database.

  23. Hi Julian, very helpful posts.
    I have a query, whether it is possible to get table name, column name, joins used in a report through query builder??
    Thanks in Advance!!

  24. Hi Zee, I am pleased that you have found the site helpful. Thanks for your question. This is only possible through the SQL that is captured through auditing for the event Document/Report Refresh. Query Builder cannot give you this information.

  25. Thanks so much for your reply, I have been waiting for it.
    Actually I am new to Business Object. Will you please explain in brief or if possible would you share the query?
    Thanks in advance.

    Zee…

  26. Hello Julian,

    We can bring all the report names for a specific universe. In other way, I am thinking how we can get all the universe names associated with a specific Report using query builder. I understand this is an reverse scenario and very much doable using SDK, but need to achieve using Query Builder itself. If you have any answer, could you please share with us.

    Thanks,
    AtanuDutta

  27. Hi AtanuDutta, the article above provides a query (see “Query the Report’s Identifying Information”) that will return the object ID of each universe that a report uses. Does this met your needs?

    BTW, Relationship Queries can be more useful (and more complicated). I don’t have the time right now to build exactly what you may want, but I pulled this from our Query Builder Guide:

    For example, a query of all of the reports using the “Activity” universe would look like this:

    SELECT * FROM CI_InfoObjects, CI_AppObjects WHERE
    si_kind = 'WebI' AND
    Parents("SI_NAME='Webi-Universe'","SI_NAME='Activity'")

  28. Hi Julian,
    First of all thank you very much for this extremly helpful information that you have discusssed in very easy way.

    I am trying get all the user who has used a particular Object in the Universe. Is it possible to do that? if yes, how?

    Appreciate you rhelp!

    Thanks,
    Reeta

  29. Hi Reeta, I am glad to hear that you find the article helpful. The only place you can get information about usage is in the auditing data. Take a look at your “document refresh” events in the “Audit_Event” table and see if you can find what you need.

  30. Thank you very much Julian for your prompt reply! Appreciate it.

    I looked at the XIR2 Auditor Pdf and all I found that “Document refresh” event from the AUdit_event table has this desription “User manually refreshes a
    Web Intelligence document,
    or user opens a Web
    Intelligence document that
    has the “refresh on open”
    document property assigned.” I am looking for the list of users who have used a particular Object in their reports. Is it achievable?

    Thanks,
    Reeta

  31. Hi Reeta, I commend your research, many do not go so far unfortunately. Your question is a common one, but unfortunately, it is not so easy to answer.

    Document Refresh is the easiest method, but it requires a document be refreshed and auditing be enabled at the moment of refresh.

    It may be possible to write a macro that opens reports ad dumps their objects into a file, but I am not aware of any that is available out there.

    SAP has told me that “Metadata Manager” can provide this information, but this is unverfied (by me).

    There may be third part tools, but I don’t have any recommendations at the moment.

  32. Hi,

    I need to know the list of users using a particular object in the Universe
    can you help out in achiving this.

  33. Hi Ramesh, this is not possible with Query Builder. The key to your question is “using”. Therefore, you need to look at the auditing data. Specifically, this information is captured with each “Document Refresh” audit event. Make sure you have this enabled, then look at the data and you will see that all objects used in a report are captured with each refresh.

  34. Hi,
    Can we get a list of ‘File Name’ of all the reports under one folder. The ‘File Name’ is the one we get while viewing the properties of a report and constitute full the path to FRS location.

  35. HI Julian,

    Could you please tell me how to get the list of report using a specific universe using audit database.

    My Requirement is : for my project users are having creator rights and they will be creating their own reports using the universe and i need to develop a webi report which shows the list of reports which were created in the user folders and also the users names.

    Please please let me know how we can do this by using the Audit database ……..

  36. Hi,
    I’m using designer and check a universe done by someone else, I found the following definition:
    DGC_PK_BO.BO_FN_REPL_TD(@Select(test (DEP)\DEP Ex))
    the analysis is ok and I retrieve results in webi.
    But when I test:
    DGC_PK_BO.BO_FN_REMPL_TD
    alone as definition, it tell me the the table does not exists.
    From where could come from this object/table? How could this work?

  37. Hi Fathi, I would go back to the WebI report using this object and view the SQL. That should point you to the database table and field(s).

  38. Hi julian,
    In deski (I think, it is the same as webi),I get this SQL:
    SELECT
    DGC_PK_BO.BO_FN_REPL_TD(( EX_DEP.EXE_COD ))
    FROM
    EX EX_DEP

    Where Table “EX EX_DEP” is an alias of the table EX.
    But I still does not know where “DGC_PK_BO.BO_FN_REPL_TD” come from…

  39. Is it possible to get the list of reports that uses FTP as destination server?

    Please let me know whether I can get this by using the Audit database?

    Thanks
    Kris

  40. Hi Kris, I don’t know if that info is captured in Auditing. I have not audited my job servers in a while.

    You can visually observe the data in Query Builder with this query (but it does not filter down to just FTP destination recurring schedules):
    SELECT TOP 10
    si_id,
    si_cuid,
    si_name,
    si_owner,
    si_recurring,
    si_scheduleinfo.si_destinations
    FROM CI_InfoObjects
    WHERE
    si_instance = 1 AND
    si_recurring = 1 AND
    si_scheduleinfo.si_destinations IS NOT NULL
    ORDER BY si_id

  41. Hi Julian,

    Please accept this small token of our appreciation.the information you have shared is really useful to us.

    Can you please let me know on which database we need to fire the query to get list of reports present in the Universe.
    1) Auditor database
    2) Reporting Database: – this is the database which is being pointed by the universe.

    Thanks,
    Mayur

  42. Hi Mayur, you must run this query in Query Builder. Query Builder is a free, simple Business Objects Enterprise SDK GUI web application provided with every BO XI server install. If you do not have it deployed you will need to refer to your documentation

    Essentially you log in to the tool with a valid BO user account (Administrator if you want to see all objects) and then you paste your query in the text area that you will see.

  43. Hi Julian

    Thanks a lot for information.
    Will check and get back to you.

    Thanks
    Mayur

  44. Hi Julian,

    Many thanks on a much needed article on the often cryptic query builder. I have a unique requirement to fulfill unfortunately 🙂 We are upgrading from BOXI R2/sp2 to BOXI R3.1. So we are trying to find out the reports/instances saved in each user’s favorites folder. In essense, I want to take out a list of user’s folder names and the reports residing inside them. Can you please help me out in going about this? Many thanks in advance!

  45. Hi Mike, the folder-report-instance relationship can get quite deep and honestly, it will become very difficult to try to keep track of everything using just Query Builder. For what you want and SDK extract would be much better, but SDK is a more complex beast.

    Regarding your planned migration, I am not sure of the value of obtaining the data you seek. Import Wizard (honestly the best tool for the migration if you are not just upgrading in place) does not allow you to view personal documents or folders. You will only be able to select users and choose whether or not to migrate their documents and inboxes.

    Of course, if you want to know what the total volume of personal documents is then there may be ways of obtaining this information more easily. In most systems I work with all public reports are owned only by a particular user (usually Administrator) and so determining the total personal documents is just as simple as subtracting the Administrator-owned report count from the Total report count.

  46. Hi Julian,
    Very informative and useful article.Congratulations.
    I have the following requirement : I need to find out the list of webi reports which are using a particular Data base object say a coulumn C in Table T.In the Universe, I could find out ‘View Associated Objects’ and can check the Universe objects created out of that table.But how to get all the reports that are using these DB objects? Is there any way that we could accomplish this.Waiting for your prompt reply.

  47. Hi Dheeraj, this questions keeps be asked in different ways. This is not possible through SDK or Query Builder. The only methods of which I know is some macro that can open each report and collect the data (but I don’t have such a macro). The other method is to look to the auditing data. When a Document Refresh is audited the objects used in the queries of that report are all captured in the Audit_Detail table.

  48. Hi,
    Thanks for a very use full link, i was working on a requirement and needed to give report to table level linking i.e what tables are being accessed from a report. is there any way that information can be extracted without going into the sdk
    Thanks in advance

  49. Hi Ahmed, the SDK won’t be able to get you what tables a report is accessing. The only place this information is available for extraction is in the auditing data. Look for the SQL and you will have all that you need.

  50. Hi,

    I think I am too late but I think someone wanted query to get list of reports using FTP as destination

    Select Top 1000 si_id, si_name, si_owner, si_scheduleinfo.si_destinations FROM CI_InfoObjects WHERE si_scheduleinfo.si_destinations LIKE ‘%CrystalEnterprise.Ftp%’ and si_instance = 0

    Above is reports configured with default settings as FTP

    Similary reports that are scheduled to FTP

    Select Top 1000 si_id, si_name, si_owner, si_scheduleinfo.si_destinations FROM CI_InfoObjects WHERE si_scheduleinfo.si_destinations LIKE ‘%CrystalEnterprise.Ftp%’ and si_instance = 1

    Recurring reports configured to schedule to FTP

    Select Top 1000 si_id, si_name, si_owner, si_scheduleinfo.si_destinations FROM CI_InfoObjects WHERE si_scheduleinfo.si_destinations LIKE ‘%CrystalEnterprise.Ftp%’ and si_recurring = 1

    Similary,

    CrystalEnterprise.Smtp – for email
    CrystalEnterprise.Managed – Enterprise Inbox
    CrystalEnterprise.DiskUnmanaged – File system

Leave a comment

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