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?
In a simple answer, no. Unusual, yes, but not insane. I don't usually keep my warehouses up to date this much though, I'll usually replicate at that point and deal with it that way.
However, a warehouse is not what I'm understanding your intent to be here.
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.
So far, so good. Real time reporting off your OLTP system can hose you up a bit if you have a lot of volume. Transactionally replicating the database to another server can offload a lot of lock blocking and other concerns.
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.
Here's where you lost me. Giving them access to the procs just requires them having execute permissions on the proc. What are they doing now with the stored procs in the original database that they can't do in the replicated database? You mention they can't link data sets if they are from stored procs... how are they linking data sets *now*?
Also, don't stick Sprocs inside CREATE FUNCTIONs in SQL Server... it doesn't end well. You're already finding some of the problems. Just... ow, yeah, Functions in Procs, not Procs in functions.
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
Continuously running those procs on your oltp system may end up with more headaches than it's worth, however, I don't know your system to have a solid idea of the resultant impact. But, that's definately not a warehouse, it's a report result storage. And every 5 minutes you're going to truncate and rebuild those solutions, and if they're taking a decent amount of time to run your business is only going to get what, 2-3 minutes every 5 to actually use the data? That seems less effective.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA