Auditing

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

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.

What do the values of AUDIT_EVENT.ERROR_CODE mean?

If you have spent some time with your Business Objects XI auditing data then you have probably asked yourself what significance is of the the error code values for the AUDIT_EVENT table’s ERROR_CODE property. If you haven’t yet pondered this then I suspect you haven’t looked closely at your data, OR you don’t have enough data to have produced a suspicious error code.

What are those weird values for AUDIT_EVENT.ERROR_CODE?

What do all of those ERROR_CODE values mean?

Some of you may have that handy-dandy Business Objects error code cross-reference document (I should see about sharing it next time I find it) and you are thinking these error codes can be found there. Sorry, nice try, wrong, thanks for playing. Failing that look-up I asked around and this is what I found out:
Very Short Answer: nothing
Short Answer: they can only be used in deep debugging analysis
Long Answer: Those error codes are internal Web Intelligence error codes which depend on the workflow followed and on the specific API which failed. The error codes should be able to be referenced in the trace logs produced through active “-trace” logging on the corresponding WebI Report Server (more on this topic in our article “Business Objects Classic Logging and Standard Tracing“).

So can I find any meaning in the ERROR_CODE values?

In my experience the number of errors perceived by the users of the system exceed the number of non-zero/non-null ERROR_CODE values stored in the Business Objects XI auditing data. Truthfully, some of this makes sense. If a BOXI user gets an error immediately when they click on a report in InfoView then the CMS may never get a chance to capture the error to be able to write an auditing record. More truthfully, I don’t really know.

All I can say is that the best use of AUDIT_EVENT.ERROR_CODE values is to count them as Boolean values. Did an error occur, or did no error occur. Moreover, if you find that the ratio of errors to non-error events seems to disagree with other sources of error tracking information (or user perception) then the data still has value. It has relative value. You can track it to measure trends. If you see the ratio of errors to non-error events increase/decrease over time then you know something, and knowing is half the battle.

Mystery Solved?

My sources tell me that this is fixed in BO XI R3 SP1 (XI 3.1). In fact, if you want to dive deeper take a look at ADAPT01092225. The “fix” is to limit all AUDIT_EVENT.ERROR_CODE values to 0 or 1. So, if it were previously possible tie auditing data to Web Intelligence Report Server trace logging through the error code value, that ability is revoked in R3 SP1. Therefore, the only purpose remaining for this property is the relative trend value I detailed above. Good luck.