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.
more ▼

asked Mar 29, 2012 at 09:43 AM in Default

srpatel gravatar image

20 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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).
more ▼

answered Mar 29, 2012 at 06:13 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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?
Apr 02, 2012 at 02:00 PM srpatel

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.
Apr 02, 2012 at 05:27 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 29, 2012 at 09:43 AM

Seen: 1365 times

Last Updated: Apr 03, 2012 at 05:29 AM