Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel. Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner. Screenshots for this blog are available at my regular blog site
With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did:
Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.
Create a Dataset that runs the system stored procedure sp_start_job with the name of the job.
Add some text! Let the user know what’s going on after they click on the report otherwise it will just show a blank report. Drag a textbox over and add the appropriate text.
Deploy the report and test!
There are some circumstances where you would not want to use this method:
· Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.
· The job takes longer than just a few minutes to process. Remember you are trying to improve latency. You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)
· You haven’t trained your end users in what the report does. You don’t want end users clicking this report over and over again because it is running a major process.