Scalability Engineers

Redesign data flow to save 2 hours in report generation

Business Requirement

The existing method to fill some reporting tables calls SP Usp_ on primary server and fills all required reporting tables on primary server itself. These tables are then copied to reporting server using SSIS package. This copying process takes around 50 min currently. Since the reporting tables belong to reporting team only, they can be filled directly on reporting server using the new method. This will save us the time spent on copying the data from primary server to reporting server. Also Processing job has to wait for all reporting tables to get filled for it to be marked as completed. That time will also be saved.

Existing Flow



Proposed Work Flow



Gains with the new approach

1. Time Saving
There will be time saving in 2 ways. First, In existing method, after filling of EOD tables, SSIS package takes around 50 min to copy the data of 2 institutions (6969 and 6981) to reporting server. This 50 min time will be saved because SSIS package will no longer be needed and EOD tables will be directly filled on reporting server. Secondly, ATID60 job waits for filling of EOD tables at the primary server before it is marked as complete. This takes upto 1hr 45 min. Once the process to fill EOD tables shifts to reporting server, ATID60 processing don’t have to wait for EOD tables to get filled and this will make ATID60 jobs faster by 1hr 45 min. And ATID60 will finish in around 10 seconds.

Time Saving(Primary side) Time Saving(Reporting Side)
1 hr 45 min 50 min

2. Space Saving
Since all 5 EOD tables will be removed from Primary server eventually, below will be the space saving on Primary Server:


3. Processing saving
The process to fill 5 EOD tables on Primary server takes around 1 hr 45 min. With this process moving to Reporting Server, all the processing done by procedure on Primary server is saved. Also the SSIS package reads data from Primary server for 50 min. That processing is also saved.