Data Warehouse latency is often a complaint ,generally heard, from end users when trying to access data via either Reporting Services reports or Excel.
To create a report that could kick off the SQL Agent job that processed Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps :
Step OneCreate a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.
Step TwoCreate 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