Stability problem for Excel commander report using RDBMS and OLAP
Hi All. I have not much experience in Business intelligence area. I am facing a problem in regarding stability of a report. We are providing the Business users an Excel report in Retail Sales Reporting with Dynamic filtering options, with multiple sheets/tabs in for different geographical, product and time hierarchies. The report is using two Stored Procedures which are going to the Relational database and OLAP database to collect the data. The report is being started by Sql Agent scheduled job, which starts an Excel commander and then Excel commander starts the stored procedures to collect data and then populate the data in a predefined Excel file layout with multiple sheets/tabs. Of course the Excel file is using the macros (vba). The problem of this solution is the stability or the reliability of the report execution. Many times the report crashes without giving any error message or sometimes it takes too much time to execute so we have to restart it. Any idea about how we can get a better degree of reliability / stability. We are even thinking to replace this report generating method for the sack of stability but Excel is must part for its ability of offering of dynamic filtering and multiple tabs/sheets. I would be very thankful for your Ideas for the improvement of the situation, especially my humble request to Experienced and Seniors.
In case of stability problems with Excel commander and depending on the layout of the excel file you could make the solution in below way: 1. Create an empty excel sheet which will be used as template and will be stored somewhere 2. Create SSIS package which will prepare all the necessary data, copy the the template from original location to the report destination and write data to the excel file Also you should monitor what is the root cause of the slowness etc. (there can be locks caused by other users etc).