Executing Stored Procedure / SSIS Package / Agent Job before running Report

[Nicholas]

Hello,

This might be more of an Admin question or Event Studio question but I'm not sure so here goes:

I've been googling the topic and I've seen some related articles but nothing I've seen does exactly what I wanted. Ideally I would like to set up a report so that when a user goes to run it (or types in a prompt and then runs it), the report would send off a SQL command to execute a job (refreshing the database) and only after the job is finished, run the report.

Does anyone know if this is possible? Aside from not knowing that, my other concern is that it would send the command and immediately execute the report without waiting for the package to report success or failure or return any results.

Much of the data I report on is located in an online database I don't have full access to, so I have SSIS packages moving and massaging the data to a SQL Server database Cognos is reading. Currently I have SQL Server Agent Jobs scheduled to update the data daily and weekly, however having "Live" reporting would be great.

I'd appreciate any feedback or alternatives.

Thanks,
Nicholas


EDIT:  Another possibility I was wondering about is running the report as normal, but having a checkbox or similar which states "Use Latest Possible Data" and if they rerun the report with that checked, it then executes the Job before showing the report again.

[bdbits]

And what are you going to get when two users try to run your "report" at the same time, kicking off the data refresh at the same time? Depending on how you are going about refreshing the data, this could be a disaster.

I think you need to sit down and review your user requirements and current technical architecture. If you need near-realtime reporting you can use change data capture or replication technologies (could get expensive) if you absolutely cannot connect to the source database.

[Nicholas]

In whatever SQL logic I whip up I can create a report table that checks when the last time a package / SP was run. If it was within an hour or the like, it simply wouldn't run and report this. Not a huge deal there.

Near real-time reporting is the ideal situation. Unfortunately data capture or replication like you suggest on our online system would involve heavy monitoring / processes running in the background (like having every change to a record listened for and then written to a DB I have access to). That would end up being a large strain and thus not really an option for us due to performance degradation.

I would love to have our technical architecture change, but I'm not a decision maker in that regard.  :-\

[bdbits]

The strain for replication is unlikely to be any worse than ETLing the same records on demand. And with some databases, going between multiple instances of the same vendor's databases, the replication is free.

In my opinion, Cognos is a very capable BI/reporting tool. But it is not meant to kick off backend processes or manage the timeliness of data. Just because you can do something not mean you should. Screwdriver, meet hammer. But whatever floats your boat.