BusinessObjects Administration – Audit reporting vs Metadata reporting
I have noticed so many questions flowing around BusinessObjects Audit reporting and Metadata reporting for a quite long time in SCN. The intention of this blog is to explain the basics and the major difference between each of them.
To start with an introduction, Auditing is predominantly used to get the statistics about the BusinessObjects deployment by providing more insights on usage metrics of the system where as metadata reporting is used to get the real-time information about what is available in BusinessObjects repository.
Some of the metrics from Audit database
- Who is the more active user in my BusinessObjects environment?
- What is the major user activity (create/modify/refresh/delete) carried out on reports over a week?
- What are the frequently failing scheduled reports?
Metrics which you can get from CMS database using Query builder (Metadata reporting)
- What is the total number of users in the system and who are all the members of a particular group?
- What is the value set at server parameter called “Maximum Character Stream Size”?
- What are the reports associated with a particular corporate category?
The below table could help you to distinguish the major differences
| Category | Audit reporting | Metadata reporting |
|---|---|---|
| Nature of data | The information stored in Audit tables is historical in nature. | Repository tables contains real-time information about BI contents which is transactional (No history) in nature. |
| Configuration | Audit database configuration is not mandatory during installation. We can continue to use the system even without auditing and It can be configured as on when it is required. | It is mandatory to Configure the CMS database during installation without which installation will not succeed. |
| Direct SQL access | Data available with in Audit tables can be queried directly using traditional SQL. | Direct access to CMS tables is not recommended as they are encrypted. We need to use Query builder to get the required information. |
| Universe and reports | Out of box and As-Is sample of universe & reports are available in BO 3.x and BI 4.x respectively | We need to develop the universe and associated reports from the scratch based on the requirement. |
| Information availability | Data available within Audit tables is not real-time and is based on the parameter – “CMS Polling interval” | Data available in CMS tables are real-time in nature and they get updated as soon as the BI content gets added/modified/deleted. |
| Data retention and purging | We can purge data available within Audit tables based on their age | Not possible. But the inconsistencies between CMS database and FRS can be removed using Repository Diagnostics Tool. |
Hope this blog is interesting.
References
BusinessObjects Query builder – Basics
perfect document
very simple and clearly said.
Thanks for the explanation but I disagree with this point:
"audit database is not mandatory during installation".
I recently installed BI 4.1 SP2 and you can't go through the install wizard if you don't specifiy a valid audit database
We recently installed that same version on our environment, and we made the configuration of the Audit Database at the very final. Never get asked in the install wizard for it.
Best regards!
Hi Mani,
I have been trying to obtain information of connection creator in BI 4.1. I have tried multiple options using SI_NAME , SI_CUID and SI_KIND in query builder. These have not helped in any manner. So I suspect may be the creator information would not be available in CMS Db.
So I tried obtaining the information from Audit Universe provided by SAP. Unfortunately, I have still not been able to fetch this information. I have used custom queries as well to obtain the necessary information. Could you assist with the same.
BR,
Arvind
Very helpful, thank you.
Hi,
how do I check if a user accidentally deleted or modified other users in a BO environment on a given date
any info is greatly appreciated
thank you
Hi Mani,
Is it possible to get details which report connects to which particular database table when producing/refreshing a report? Say, if that audit tables ADS_EVENT contains only objects on universe level, or resource database level?
Thanks,
Jian
In 4.x you can configure auditing to include the SQL that was generated for a report. However, this will vastly increase the size of your Audit database. So, best practice is to only enable this when you're trying to diagnose a SQL issue with a report that can't be diagnosed any other way and then disable it when you have the data you need.
If you're on 4.x you should be able to get this information through the RESTful Web Service for Web Intelligence Reports and the Semantic Layer SDK. You can find more info about the RESTful web services SDKs here: http://scn.sap.com/community/restful-sdk and the documentation for them is available on help.sap.com.
-Dell
Thanks, Dell, for such prompt reply!
I'm a newbie on this topic, please allow me to ask a couple of more questions:
1. Configuring audit: according to the instructions, we need to use SAP Crystal Report Design to complete the configuration. While we only use BO right now, do we need additional license for this Crystal Report Design, or if there is an alternative tool?
2. We are on 4.0 (for production), I will check the web service reports and Symentic Layer SDK and update later.
Thanks again,
Jian
1. There is a new audit universe available here on SCN that can be used for Webi reports. I don't remember the URL for it but you could do a search for it. You could also log in to the audit database and query it directly using Toad, SSMS, etc. depending on your database type.
2. The RESTful Web Services were introduced with 4.0 and have gone through a lot of upgrades since then. You will probably need to be on the latest service pack of 4.0 or on 4.1 to access the functionality that you're looking for.
-Dell
Thanks very much, Dell. It's good to know I can connect to Audit database directly and I do find lots of interesting data! Yet I still have some newbie question:
For table ADS_Event_Detail, the column Event_Detail_value seems encrypted, how can I get the decrypted value? Add any ownership or...?
Thanks again,
Jian
It's not encrypted, specifically. You need to look at the Event_Detail_Type (I think that's what it's called) which is a lookup from another table to get information about what type of detail record it is - it could be a path, a parameter value, the universe the report uses, etc. depending on how you have auditing configured.
-Dell
Thanks, Dell. It's very helpful. We will turn on "Query" log in Audit database temporarily , see if we can get the data needed. Meanwhile will see how much overhead it may add to the server. Thanks again.
An update in Universe and reports category. We do have Out of box universe and reports for Metadata reporting. Please refer the blog below.
https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3
Thanks
Mani