Audit Reports - Insight on LOV Performance and Database Arch

building semantic layer
1

Hello BOB,

I am searching for answers…regarding auditing items. Please see below, and provide any insight you think can assist me.

Overview:
The audit database has grown to a very large number of records (ie ~22 million), which is hindering the performance on the webi audit reports. For example, we have an LOV that takes FOREVER to run in a webi report because it is hitting the database row-by-row. Currently, I have two resolutions: 1.) Associate LOVs to universe and export them with the universe, and 2.) Apply indexes to the audit database.

My inquiries:

1.) For resolution 1, I have an issue. The associated LOVs are still refreshing in webi when ‘auto refresh’ is unchecked, and ‘export with universe’ is checked. I researched this issue and consulted with a BO Engineer. From what I’ve gathered, this is a bug and will not be resolved until 3.0 fix is applied. Since we will not be getting the 3.0 fix till later this year, I want to find a temporary work-around for these LOVs. Do you have any suggestions?

2.) I spoke to Brian about adding derived tables on the database (rather than in the universe) to assist LOV performance. I assume the LOV performance would substantially improve if we created the derived tables on the database, Would you recommend this approach?

3.) If we are going to apply indexes, I need to communicate with the audit dba on what dimensions will remain static, meaning no new IDs will be added to the database. For example, I want to assume the following fields listed below are static since they are representing actions by the user and we will not be creating new actions. In contrast, Document Name is considered a dynamic object because users will be creating new documents. Can you please confirm my understanding? Also, could you recommend a document that can assist with these types of questions or just understanding the audit database architecture?

    Object Name                	       Field  			
    Action Type ID	     	       AUDIT_DETAIL.Detail_Type_ID 
    Action Name ID	                       AUDIT_EVENT.Event_Type_ID

Thank you everyone in advance!

Jdog (BOB member since 2006-05-31)

2

Jdog,

In response to para 3 of your posting, here is the link to the documentation PDF:
http://support.businessobjects.com/documentation/product_guides/download.asp?manual=boexir2/en/xir2_bip_auditor_en.pdf

Also, look at page 36, it is the Entity-Relationship Diagram (ERD) for the Auditor database which will help you to better understand the database schema.

The two objects you mentioned:

are both part of the “transaction” portion of the schema, i.e., these tables (and data points) grow with each influx of auditor data, but not to discourage you from building an index on this stuff, but just be aware of the “growth” factor…

If you’re open to a more philosophical discussion, how about thinking to trim down your auditor database? If you have ~22 million rows and its choking a horse, why live with that? If you think that the auditor data is valuable, I would suggest creating another database area, replicate your ~22 million rows over to it, then delete all of the 2008 data from the new area, and delete all of the non-2008 data back in the original area. Now you’ll have two databases (historical – pre-2008) and current (2008 only). Then you could possibly build (replicate) the Auditor universe pointing at the old data for “historical auditor info”, and get back to the good life. Just a thought in wrapping your arms around this bear.

Thanks,
John

P.S. the “formula” for properly mass deleting data from auditor (in XI R2) is back over in the Auditor forum (see here)https://bobj-board.org/t/105734

jsanzone :us: (BOB member since 2006-09-12)