Stability problem for Excel commander report using RDBMS and OLAP

Question

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

---

srpatel0

Answer 1

Hi srpate,

I don't like reporting by using excel which is out of control if there is any issue. I'd suggest you use SQL Server Reporting Services, where you can easily build the report and deliver it to end users by subscribution. You can start to use it from http://msdn.microsoft.com/en-us/library/ms159106(v=sql.100).aspx . If you run into any question during building and managing reports, please post them on forum http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/threads which is dedicated to SQL Server Reporting Services.

Regards,
Jerry

Answer 2

Hi Jerry Nee,

Many thanks for your time. I am thinking for this report a new architect approach. I want to build this as fallows.

I have the excel template (which has some sheets and each sheet has different layout and different columns) and i will provide the users this template and  refresh the template once/ twice a day for fresh data. The data will be coming from an OLAP cube.

The question are

How can i automate this process if i am not going to use SSIS package for this?

What should i place in between excel and Cube that will insure the reliable connection and fechting of fresh data in templates?

thanks a lot in advance

    

---

srpatel0