Using Business Objects Auditor on XI for Metrics on Scheduled Report Run Times (Durations)

If you use the auditor package in Business Objects XI, you will very likely need to create custom versions of the canned reports to provide metrics that are useful to your specific organization.

One of the primary concerns for one of our clients was the monitoring of report run times. Given the timeout limits that are imposed on reports, if they run too long they will fail. We needed to have report stats monitored and sent out each morning after the heavy processing of the scheduled reports was completed.

As our reports run early morning, I wanted to set the report period to be 6AM yesterday to 6AM today.

In our environment our heavy hitting reports are all Web Intelligence reports so we wanted to limit reporting to Web Intel and not all jobs (as the default BO report provide).

So I  set the report to run daily at 7:00AM. Since it reports on 6AM to 6AM it will capture the scheduled report processing which is complete by 6AM.

Our environment is BusinessObjects XI Release 2 on Linux.

I looked through the canned reports that came with XI. It turns out that there weren't any canned reports that shipped with auditor that reported in a form that was useful in our environment.

Here's a list of the canned reports that are packaged with auditor:

1. Average Number of Users Logged In
2. Average Refresh Time
3. Average Session Duration
4. Average Session Duration per Cluster
5. Average Session Duration per User
6. Cluster Nodes
7. Document Information Detail
8. Document Scheduling and Viewing Status
9. Job Summary
10. Jobs per Job Server
11. Jobs per User
12. Job Servers on the System
13. Last Login for User
14. Least Accessed Documents
15. Most Accessed Documents
16. Most Active Users
17. Most Popular Actions
18. Most Popular Actions per Document
19. Number of User Sessions
20. Number of Users in the System
21. Password Modifications
22. Peak Usage
23. Refresh and Edit Activity
24. Rights Modification
25. Total Users Logged In by Day
26. User Activity
27. User Activity per Session
28. Users Who Logged Off Incorrectly

So I made a couple of simple mods to the "Activity" universe (which is what the auditor package runs off of). And I created a custom report for our environment.

 

cms-auditor-enabled.gif

The Activity universe is the universe that comes with auditor and which all the canned reports are based off of. Here's what it looks like. You can't see any of the table details, but you can see that the table structure and the universe is not very large.

 

auditor-activity-universe.gif

I set the report to show any report failures at the top of the report, and I've set an alerter to make the entry red and bold. After failures (if any) it will sort by duration in descending order so we can see the longest running reports on top.

I set up the report to mail an excel format file.

That way, if anyone wants to sort or manipulate the data further, it will be easier.

Unfortunately when BO outputs to Excel it is next to impossible to control where BusinessObjects merges the Excel cells (vertically and horizontally). This can make it difficult to sort in excel without doing some column copy-paste-delete-editing stuff. Excel won't sort cells of uneven widths (merged and not). I tried to get "unmerged" cells in excel… which is usually an exercise in futility… and it was in this case.

Here's the major high level mods I made to set up the custom report. You will likely need to do something at least a little different.

1. Created 2 "relative date" objects in the "Activity" universe: Yesterday6AM and Today6AM . The relative date objects allow for the rolling twenty four hour reporting period.

We run in an Oracle environment so these objects were as follows:

to_date( to_char(sysdate,  'DD-MON-YYYY') || ' 06:00:00', 'DD-MON-YYYY HH24:MI:SS' )

to_date( to_char(sysdate - 1,  'DD-MON-YYYY') || ' 06:00:00', 'DD-MON-YYYY HH24:MI:SS' )

2. Copied the business objects auditor canned report "Jobs Summary" and re-worked. Main steps are below.

3. Deleted the 1st pie chart report. This report seems kinda useless unless you plan on having a large percentage of failed reports. This is not the case for us.

4. Merged the failed and successful reports onto one report. I'd rather look on one report than have to switch between two tabs on a Web Intelligence report or an Excel workbook. I used sorting and alerters to call attention to failures (as in the below steps).

5. Added Result Objects to the query as necessary. It takes a little detective work to figure out what values to filter on. BO uses numeric values that are not meaningful without some digging. I added columns to the result table and did some investigation into key values, etc. to determine how to filter.

6. Changed filtering at the Query level as below

7. [Action Name ID] = 327681;327682 (this represents the statuses of successful and failure)

8. [Action Type] = "Object Folder Path"  (this shows only the locations of the reports. since we have all the auditing options turned on each report job will be represented 3 separate times: "Object Folder Path," "Object Name," "User Groups"). We only want to represent a report one time… thus this clause in the report filter.

9. Filter on the yesterday6am and today6am to only bring in results for the desired 24 hour time period. Since the report is run daily and emailed, this is precisely what we wanted.

10. In the report itself added a filter to only show web intelligence rows, since that's what we run in our environment. [Job Server Kind] = "Web Intelligence Job Server"  (in this current XI implementation we haven't yet had the need to do much with Crystal Reports or Desktop Intelligence).

11. Arrange columns in the table as desired. Columns we wanted were: Duration, Status, Report (path and report name), and Begin Time
 

scheduled-report-duration-report.gif

12. Created a calculated column for the status to resolve the numeric code into English:

=If([Action Name ID] = 327681; "Successful"; If([Action Name ID] = 327682; "Failed"; "Unknown" ) )

13. Added sorts on status then duration (descending)

14. Added alerters on the status to highlight failed reports and long duration reports

Some of the reports are useful as packaged with auditor, but many will need to tweak in order to be optimized for your environment. To do anything outside of the canned reports you'll need at least a rudimentary understanding of the universe.

When you are trying to filter the type of events that are shown in your custom audit report, you will need to reference the Detail_Type table. Here are the values contained.

 

Detail Type Description Detail Type Id
Universe name 2
Object Name 3
Number of lines 6
Number of elements 7
Document name 8
Document size 9
Description 14
Category name 15
Keywords 16
Refresh options 17
Overwrite 18
SQL value 19
Document type 21
Report name 22
Prompt name 23
Prompt value 24
Event ID 40
Event Filename 41
User Groups 42
Object Folder Path 43
Object Category Path 44
Session ID 45
Enterprise Error Text 46
ODBC SQLSTATE 47
ODBC Error Text 48
BOE UserID 49
Object Type 50
Report Data Type 51

In the above example that I walked through (reporting on reports), the "Object Folder Path" and the "Object Name" provided the most useful way to identify reports. This identifies the folder and report name.

Another useful audit meta data table is the Event_Type table. For example, in order to filter and sort based on report status (success or failure), we used the table values from the Event_Type table:

 
Event Type Description Event Type Id
Apply format 28
Concurrent Logon Event 65537
Document refresh 19
Drill out of scope 42
Edit document 22
Event is Registered 262145
Event is Triggered 262148
Event is Unregistered 262146
Event is Updated 262147
Generate SQL 41
Get list of universes 6
Get page 40
List of values 21
Named User Logon Event 65538
Object is Created 65542
Object is Deleted 65543
Object is Modified 65544
Object is Published/Saved Successfully (4 or 6) 458754
Object Publish/Save attempt Fails (4 or 6) 458757
Password Change 65541
Question Failed 655362
Question Succeeded 655361
Read Document 11
Report fails to get Created 458758
Report Fails to Open 458756
Report is Created 458755
Report is Opened 458753
Report Viewed Successfully 131073
Report Viewed Successfully 196609
Report Viewing Attempt Failed 131074
Report Viewing Attempt Failed 196610
Rights on an object have been modified 65546
Save document to repository 9
Scheduling Failed 327682
Scheduling Failed but will be reattempted 327683
Scheduling Succeeded 327681
Selection of universe 13
Select prompt 43
Unresponsive Scheduling 65545
User Logoff 65540
User Logon Fails 65539

The Application_Type table provided reference to filter based on the type of server. In our case we were interested in "Web Intelligence Job Server" records. 

 

Application Type Description Application Type Id
Cache Server 11
CMS 10
DCP Job Server 19
Desktop Intelligence Job Server 21
Destination Job Server 17
Event Server 14
Program Job Server 15
Question Engine Server 20
RAS 13
Report Job Server 12
Unknown application 1
Web Intelligence Job Server 18
Web Intelligence Report Server 8

 

 

Spread the word

del.icio.us Digg Furl Reddit

Permalink • Print • Comment

Your Life Release 2, The Future of the Workplace

The workplace is moving home. In ABC News story: "The Future of the Workplace: No Office, Headquarters in Cyberspace Some Companies Don't Care Where Workers Are as Long as They Get the Job Done"

BETSY STARK, ABC NEWS Business Correspondent covers the migration of workers home (to work remotely):

 Imagine a work world with no commute, no corporate headquarters and perhaps not even an office in the physical world at all.

For Bob Flavin, a computer scientist at IBM; Janet Hoffman, an executive at a management consulting firm; and Joseph Jaffe, a marketing entrepreneur, the future is already here.

"These days we do so much by teleconference it really doesn't matter where you are," Flavin said.

Like 42 percent of IBM's 350,000 employees, Flavin rarely comes in to an IBM office.

"We don't care where and how you get your work done," said Dan Pelino, general manager of IBM's global health care and life sciences business. "We care that you get your work done."

IBM says it saves $100 million a year in real estate costs because it doesn't need the offices.

Clearly the workplace is changing. Smart employers are realizing that what is important is that the work get done… not where it gets done. $100 million a year in savings.

Wow.

 

Spread the word

del.icio.us Digg Furl Reddit

Permalink • Print • Comment