BusinessObjects Board

Best Practice to manage LOTS of scheduled instances?

scheduling XI Scheduler
#1

I’m not sure if I’m in a unique situtation or not, but figured I’d ask if anyone has any suggestions.

Our BOBJ Environment (Current XIR2 , upgrading to XIR4 as we speak), has approx 700 unique named reports. (I’m slowly going through them and upgrading to XIr4, after which I think we’ll end up with 200-300 after consolidating some via prompts etc…).

About half of the reports are strictly used as scheduled reports which output Excel files via email to users who do not have access to our Business Objects system.

I’m having a tough time trying to manage the schedules in such a way that I can be alerted when one fails for any given reason. Right now I’m dependant on the end user contacting me with “Hey, I didn’t get xyz report this morning”.

I tried to use the Auditing universe to create a list of reports which failed, and it captures SOME failures, but for whatever reason, it seems some failure messages in the scheduler are not being recorded in the database. I noticed another report today for example which has been failing for the last 8 days straight, with a failure error code of "Command text was not set for the command object…Error: WIS 10901) " which is not recorded anywhere. (The fix was to open the report manually one time, run it and resave it… no changes necessary… very odd too).

I’m really debating whether or not I need to purchase a 3rd party scheduler, but I’ve not found any priced for “EDGE” customers, and I’d pay almost as much if not more for the scheduler than we are the BOBJ product. (Not to mention none that I’ve seen are ready for XIR4 yet).

Does anyone else have large amounts of scheduled reports within thier environment, and if so how do you manage them, specifically making sure they all get delivered.

JPetlev (BOB member since 2006-11-01)

How to create a report of failed reports at a certain time
#2

BO has a routine (on support site) to list all failed instances, based on a repository query that’s pretty simple -

You could maybe write a small script to do something similar, at least it would get you your failures. Maybe just use this query to populate an XLS file for your own monitoring - there are examples of ths in the SDK forum, would just need to tweak the source SQL.

The BO code is related to the time zone problems where jobs fire off in large quantity, allows you to clean them up en masse.

Good luck!
B

bdouglas :switzerland: (BOB member since 2002-08-29)

#3

Thanks, at least that query put me in the right direction of how to see what I need from the infostore.

Unfortunately, I’m a 1 man band here, and I simply don’t have the luxury of trying to learn the SDK or how to use it (Last time I wrote vb code, was back in Vb6 days).

I was hopeing someone maybe had some idea I’ve not thought of, or knew of a pre-packaged 3rd party solution even.

I was also thinking about something after looking at some of the VBA code in the SDK folder… this ‘infostore’ object (CI_InfoObjects)… why isn’t there some sort of interface/service etc… which would allow you to attach it to a universe? I mean we have the auditing database which has a universe attached (even in we have to download it in R4 ourselves) but why isn’t the rest of the CMS in a format that we can easily query?

If I can find a way to access the infostore via a Universe then I can use BOBJ itself to write a report, schedule it, export etc…

There has to be an easier way for companies who do not have a VB development team to be able to monitor their systems. Manually going in to ‘check’ via instance manager goes against everything BOBJ itself tries to promote to it’s end user, specifically refering to dashboards and exception based reporting… seems kind of backwards to me.

JPetlev (BOB member since 2006-11-01)

#4

I have not jumped to 4 yet, but if there’s a query tool with it, you can run that SQL to see failures, maybe limit it by date…

You are right, reporting against the CMS can be done, with some under the covers work - there is a repository universe tool for 3x, not sure if it’s been bumped to 4 - CMSConnect (Infolytic? They advertise here…). Its library exposes a lot of the buried objects you’d need.

I used that to query users to see who is inactive, but it may help you with schedules - I’d dig for that, may make life easier for you.

Good luck!
B

bdouglas :switzerland: (BOB member since 2002-08-29)

#5

Thanks for the tip on Infolytik, They actually seem to have exactly what I need in a 3rd party solution (universe based around the cms).

The only downside I realized after talking to them, is that neither version will work for my needs at this point in time.
They have a free ‘community’ version, which runs on XIR3 only
and an Enterprise version which can run against XIR2 or XIR3 but needs an XIR3 host.

We have an XIR2 environment which I’m upgrading to XIR4… neither of which will work with thier current software. They are expecting an XIR4 version by the end of the month and I’ll check back with them then, but it looks like it might be a perfect solution.

JPetlev (BOB member since 2006-11-01)

#6

We managed to overcome this Issue like the below and is very easy for us to maintain.

Create a parent folder for all the Scheduled reports. With in it create sub folders based on their scheduling frequency something like below.

Scheduled reports

Daily 1-5 Monday to friday
Daily 1-6 Monday to Saturday
Daily 1-7 Monday to Sunday
Weekly day 1 monday
Weekly day3 wednesay
Monthly Day1
monthly day5
Monthly Second saturday

Keep your reports based on the categorization above.

Since you are delivering these reports to users email box you donr need to concern about the security as well.

Once you done everything like this, You can even maintain the schedules using Instance manager itself.

Regards,
Mani

Manikandan Elumalai :us: (BOB member since 2009-10-23)

#7

While I appreciate the comment, that doesn’t solve the issue at all. The issue is not one of how to organize reports, but rather how I can quickly and easily be notified of failures pro-actively. (Rather than having to manually log in to the cmc, instance manager, see what failed etc… especially since reports run at different times, so I’d have to check multiple times per day).

Your post does bring up a good point regarding how important it is to organize your reports.

Personally, however I’ve taken a slightly different approach. Due to the sheer number of reports this company has, I’ve decided to place all reports into a General “Public Folder”, with each report in a subfolder named after the report.

All reports since I arrived, and every one I update, gets assigned a report code which is unique to the report. That subfolder is then based on that. For example: Our “Sales Summary Report” would be called: “(S001) - Sales Summary Report. " However , we also have a “Sales Report - Detail” report which takes quite a bit longer to run and has detail information that the Sales Summary doesn’t have. However, since they are both essentially the same report, or based off the same, I’ve coded the 2nd report as (S001.01) - Sales Detail Report”. That way when someone searchs for the “S001” report, both reports come up in a search. A scheduled version which has had it’s prompts modified for dynamic dates would be something like “S001.02 - Sales Summary - Last Week”. This coding scheme works very well when you have multiple versions of similar reports.

That said, all three reports live under the folder “(S001) Sales Summary Report”, since that was the base. The thought is if someone navigates to the Sales Summary folder they can see all flavors of that same report in one spot. It also makes ‘upgrading’ a report easier as they are all next to each other.

Security is handled on a per folder basis using user groups.

To handle organization, I use Categories. Both Corporate and Personal. Corporate categories are used when one area wants to organize thier reports in a special way, for example our warehouse likes to organize “Receiving Reports” seperate from “Shipping Reports” and “Productivity Reports”. So there are corporate categories set up for those. Individual users can use private categories for things like “Reports I run Mondays”, “Reports I run Monthly” etc… I let them handle thier own.

Finally, for IT, there are a set of categories which all scheduled reports go into, which are based on frequency, though I’m going to start breaking it out a bit further such as:
Mon-Fri - Early AM (Meaning 3am to 8am).
Mon-Fri - Morning (Prior to Noon)
Mon-Fri - Afternoon (Noon to 6pm)
Mon-Fri - Late Night (6pm - midnight)
( We have a quiet space reserved from midnight -3am in case we need to do upgrades etc… )
Similar categories would be used for Monday Only Reports, Monthly etc… and will be created as I assign schedules (I’ve not converted any of those yet).

All in all this provides me an easy way to organize reports by similarity/area of focus (via Folders), Business Requirements (Corporate Categories), Scheduling requirements (Corporate categories only IT can see) and User preference (Private categories).

What I really like about the folder security route, is that reports only live in one place and only have one instance. No chance of 2 reports with the same name yet having different results.

JPetlev (BOB member since 2006-11-01)

#8

Failure notification of Scheduled reports is not possible till BO3.x version.

The below query builder query could be handy if you want to find failed report instances for a particular day.

SELECT TOP 1000 SI_ID, SI_NAME, SI_UPDATE_TS, SI_SCHEDULE_STATUS, SI_STATUSINFO
FROM CI_INFOOBJECTS
WHERE SI_UPDATE_TS > ’2012-06-06’ AND SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS = 3
ORDER BY SI_UPDATE_TS DESC

Regards,
Mani

Manikandan Elumalai :us: (BOB member since 2009-10-23)

#9

Have you checked out the Scheduling Instances Viewer?

Joe

joepeters :us: (BOB member since 2002-08-29)

#10

Nope, hadn’t seen that…will try to take a look at it later.

JPetlev (BOB member since 2006-11-01)

#11

JPETLEV,

We’re at XIR2 looking to upgrade to 4 (probably end up at 3.x). In R2, we use the Activity universe which points to the Audit database. Every day at 10 a.m., a report containing schedule success/Failures gets sent to our tier 1 business support. They go in and reschedule the failures or try to figure out why the failure occured.

However, I don’t believe this will work in v4.0. Especially since I had heard the audit database is different/non-existent.

In the short-term, I’ve copied the code for our report which you could run against XI R2. Warning It is neither pretty nor efficient. We had to only keep 6 months of data max otherwise this query would interfere with the process that loads data into the audit database. Since then we’ve created a poor-man’s replications of the audit database and only keep a week of data in it. This query could run for up to 30 minutes easily.

HTH
SELECT
CONVERT(CHAR (100), SERVER_PROCESS.Server_FullName),
CONVERT(CHAR (100), SERVER_PROCESS.Server_FullName),
avg(AUDIT_EVENT.Duration),
max(AUDIT_EVENT.Duration),
CONVERT(CHAR (50), EVENT_TYPE.Event_Type_Description),
rtrim(CONVERT(CHAR (20), AUDIT_EVENT.User_Name)),
DATEADD(HH, -6, AUDIT_EVENT.Start_Timestamp),
CONVERT(CHAR (100), DERIVED_OBJECT_NAME.Detail_Text),
AUDIT_EVENT.Duration,
CONVERT(CHAR (100), DETAIL_TYPE.Detail_Type_Description),
CONVERT(VarCHAR (4000), AUDIT_DETAIL.Detail_Text)

FROM
SERVER_PROCESS,
AUDIT_EVENT,
EVENT_TYPE,
(
select
AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, AUDIT_DETAIL.Detail_Text as Detail_Text
from
AUDIT_EVENT, AUDIT_DETAIL
where
(AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and
(AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and
(AUDIT_DETAIL.Detail_Type_ID = 3)

) DERIVED_OBJECT_NAME,
DETAIL_TYPE,
AUDIT_DETAIL
WHERE
( SERVER_PROCESS.Server_CUID=AUDIT_EVENT.Server_CUID )
AND ( AUDIT_EVENT.Event_Type_ID=EVENT_TYPE.Event_Type_ID )
AND ( DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID )
AND ( AUDIT_DETAIL.Event_ID=AUDIT_EVENT.Event_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.Server_CUID )
AND ( AUDIT_EVENT.Event_ID=DERIVED_OBJECT_NAME.Event_ID and AUDIT_EVENT.Server_CUID=DERIVED_OBJECT_NAME.Server_CUID )
AND ( SERVER_PROCESS.Application_Type_ID IN (12, 13, 15, 17, 18, 19) )
AND
( AUDIT_EVENT.Start_Timestamp BETWEEN DATEADD(HH, 6, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) AND DATEADD(HH, 6, DATEADD(DD, DATEDIFF(DD, 0, DATEADD(DD, 1, GETDATE())), 0)) )
GROUP BY
CONVERT(CHAR (100), SERVER_PROCESS.Server_FullName),
CONVERT(CHAR (100), SERVER_PROCESS.Server_FullName),
CONVERT(CHAR (50), EVENT_TYPE.Event_Type_Description),
rtrim(CONVERT(CHAR (20), AUDIT_EVENT.User_Name)),
DATEADD(HH, -6, AUDIT_EVENT.Start_Timestamp),
CONVERT(CHAR (100), DERIVED_OBJECT_NAME.Detail_Text),
AUDIT_EVENT.Duration,
CONVERT(CHAR (100), DETAIL_TYPE.Detail_Type_Description),
CONVERT(VarCHAR (4000), AUDIT_DETAIL.Detail_Text)

tmcd :us: (BOB member since 2005-10-02)

#12

That’s EXACTLY what I’m trying to acomplish here… except that business support is ME :slight_smile:

I already tested this against our audit database and it only ran in a few seconds… but then again i bet it depends on how many scheduled reports you have in your CMS… today we only had 81 go out, all successful.

As for XIR4 audit universe… it will probably work still however the SQL syntax might change… There is an XIR4 universe using the new IDT tool that you can download… I did so, exported it to the environment, and my current activity reports (built on XIR2 universe) worked just fine, I think I had to tweak one or two fields on the report, but it wasn’t major.

I’m wondering, did you build this using Freehand sql, or is it actually using Activity Universe elements? I couldn’t seem to manage to use the activity universe to generate this but maybe with this code i can reverse engineer it.

UPDATE: Doh spoke too soon… this will not work with ALL failures.
It seems some failures aren’t written to the audit database , but instead are simply attached to the cms database with the new instance object that failed.

For example we had a few reports in a set of days that all failed with the following error:

I never did figure out what the problem was, but going in to the report, running it once manually and resaving fixed it. That error never made it to the audit database.
However it is attached to the instance of the object, which means it’s stored in the bo_cms database somewhere :slight_smile:

So while this query is great for some failures, it only gives half the story due to the way BOBJ stores the data. (Maybe this changes in XIR4? dunno). At least halfway is better than no way for now.

JPetlev (BOB member since 2006-11-01)

#13

I just checked my report and found schedules that had failed with an WIS10901 error do show up on my report. There is a lag between when the schedules run and when they get loaded up to the audit database. We’ve been using this process for years and it has been complete.

However, if running reports against the audit database, it (at least in XI R2) has caused the process that loads text files to the audit database to fail. Only to restart again upon Restart of CMS service(s). If the …/BusinessObjectsxx/auditing folder has many files building up, this could be the case.

tmcd :us: (BOB member since 2005-10-02)

#14

I may have missed the point, but I just get the instance to email me when it fails (in the notification folder of the instance on CMC) (R3).

peacha :uk: (BOB member since 2008-07-31)

#15

Aha! This was not present in XIR2, and when I used R3 a few years ago, I never noticed this (perhaps it was added in a service pack?)… but I just checked in R4 and it seems this might be perfect… we can set it to both place an audit record on Failure/Success and Email individually…

I’ll need to play with the settings in R4 to figure out which I prefer… the Audit event sounds better since I can then get ONE email each day with all reports that failed, rather than seeing a mailbox fill up with dozens if we had some sort of server error.

I’m still amazed after using BOBJ all these years of the ‘little’ things I never noticed or needed.

BTW if anyone is looking for this setting in R4 it’s actually part of the scheduling settings on a given object, under a heading of “Notification”, just under “Schedule For”. (I cannot speak to R3, but there’s no notification folder in R4 that I see).

Thanks Peacha, Never would have noticed this.

JPetlev (BOB member since 2006-11-01)

#16

And I never knew I could get away with just one email! Must give that a go…

Thanks to you too JPetlev

peacha :uk: (BOB member since 2008-07-31)

#17

We use the 3rd-Party APOS Object Manager / Instance Manager tools to manage thousands of Objects and Instances on XI (R2), 3.1 and 4.x environments.

MJRBIM :canada: (BOB member since 2007-03-23)

#18

I feel your pain with failures not making it to the audit database. There is an idea to correct this oversight on idea place:

https://cw.sdn.sap.com/cw/ideas/11374

BoB LoblaW :us: (BOB member since 2007-10-23)

#19

The Instance Manager in BO 3.3 makes tracking down failures very easy. Although I have not looked into BO 4 yet I would expect it continues this.

darwin (BOB member since 2008-06-26)

#20

The Instance Manager in BO 3.3 makes tracking down failures very easy. Although I have not looked into BO 4 yet I would expect it continues this.

darwin (BOB member since 2008-06-26)