asked
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).
Hi Pavel, Many thanks for your answer. Excel template is already we have have. This template is being filled by a VisaulBasic Programm named (excel commander)with the help of stored procedures from Relational database and Cube. The report is then directly sended to the users via email. your suggestions for Making a SSIS package for the reports execution and then dumping the data to the excel template and sending it to the users. Question: How i will store data for the excel tamplate and where? when should i call the excel macros which will be formating the dumped data?
Hi, I don't know what special formatting and macros you are using, but in most cases the feel and look can be prepared in the template and simply filled by SSIS. Any way if there is some special request for some special format, then eventually those special formats could be applied upon excel file opening. Related to the data for the template... What data you mean? How you are currently storing those data? For normal data, you simply use Data Flow component and inside it you design the data transformation from source component to Excel Destination. Related to sending the report by email, this is also no problem for SSIS. Eventually if you will have excel installed on the SSIS machine (server), then you can open and apply some kind of special handling inside SSIS Script Task where you can write code using `
VB.NET`.
4 People are following this question.
