BO 4.1 Audit schema

[Former Member]

Hello everyone,

   I am working on building a list of reports in SAP BO 4.1 environment (WEBI and Crystal).I am looking for report SQL and report objects (dimensions, measures etc.) along with other report metadata. In BO 3.1,audit database did store the report SQL and the object information but the audit schema has changed drastically in 4.1 so I am unable to find that info in 4.1 audit tables. Am I supposed to be looking at one of the repository tables instead to get this info in 4.1 or use an SDK ? Please suggest.

Thanks !!! 

[Matthew_Shaw]

Hello,

We capture both the SQL used and the objects used in the audit database, including BI4. Though with BI4.0 and BI4.1 we didn't capture the objects used for .WID documents, we did for Crystal!

Take a look at my Auditing Universe and Web Intelligence documents and download the solution. This will provide you with all you want to know and much more. Listen to the recording of my presentation to get a full understanding of the solution.  See my blog summary of issues for more details and read through some of the many comments. Most questions should be answered for you there.

Regards Matthew (Twitter: @MattShaw_on_BI)

[DellSC]

And to get the SQL, you have to turn on that option in the Auditing section of the CMC.  However, leaving that turned on is not recommended because it can significantly slow down report and audit processing if you have many large queries.  I always turn on the option to get the folder path to the report in the CMC, though - that way if you have multiple reports with the same name, it's much easier to know which one you're looking at.

-Dell

[Former Member]

Thanks for your response Christy.That option is turned on but we are still not able to figure out where's the report SQL and the report dimensions/measures are stored in BO 4.1.I am aware that using SDK is an option (or the only option) but those details could be captured from 3.1 audit schema but I don't see that in 4.1.

[Matthew_Shaw]

Hello.

As I mentioned if you use my audit universe then you'll be able to do exactly this. No SDK required.  Regards Matthew

[Former Member]

Thanks for the response Matthew. Can you please clarify what you mean by "Though with BI4.0 and BI4.1 we didn't capture the objects used for .WID documents, we did for Crystal!" ?

[Matthew_Shaw]

Hello abz,

Cut and paste from my blog 'issues', Regards, Matthew

List of Universe Objects not recorded when refreshing a Web Intelligence document based off a Universe. Impact is you the "USA4" document can't return anything. Can't say "given an object, which documents has it been used in before and by whom"., BI 4.2 SP2 now stores the universe objects names, however only the name of the object is captured and not the full path to it. This means if you have 2 objects of the same name (not a good idea anyway), then you can't tell which one of the objects was used. Additionally the objects (or filter objects too) used as filters to any prompts are also not captured, though the prompt and prompt values are stored. Crystal Reports for Enterprise stores the full path of all objects used.

[former_member185603]

Auditing will never store the SQL of the report. You need to use SDK to extract the SQL of the report.

[Former Member]

Thanks for your response Jawahar,Is it stored in one of the BO Repository tables ?