• jtc900502 (9/14/2012)


    Is it insane to have SSIS to use a Stored Procedures to do incremental loads into DW, if the frequency is like every 5 minutes?

    Here is the bacground. We use SSRS to report off of our production server via stored procs. We are now thinking of creating a replicated server to which we will point the report into. However, we also have a need to give the users access to report data (stored procs), so they can link them with other data sets as they need to using MS Query in excel. Problem is that users won't be able to link data sets if they are based off of stored procs. Have already tried encapsulating SPs inside of Functions but that also does not seem to work.

    What my boss wants:

    - Forget the replicated server.

    - Create a DW which will hold the report data sets in a table per report, to be populated by the existing report stored procedures via SSIS which will be updated every 5 minutes. Some of our SPs are highly complex and have aggregated result sets.

    - Have all the reports point to their corresponding tables in the DW

    - Have the users access these tables so they can link the dataset as they need to.

    JC

    Hi,

    Very recently, I was in the same situation. The approach which I am planning or might take is to build a data warehouse reporting complex reports which are used by the Sr.Exec's. most of them are summarized reports on monthly basis running to year. There are reports which are run on 30 mins basis. For this requirement I'm suggesting to be run on the backup copies of the live system for now but intending to build a cube later say 6 months to address this or an ODS which is run for certain period of time for realtime reporting.

    I didn't understand why there is a need to use SSIS to use SP's for incremental load. I can understand SP's on their can be used to perform incremental data load to storage area which can be used of real-time reporting.

    Now coming to the point how users can access the data. My preference is using SSRS subscription to publish the reports to a location or email and render them in Excel or which ever format.

    Does this help? Do keep us posted on what steps you are taking.