September 24, 2023 at 4:09 pm
Hey All,
I'm working on a new DW design and I'm learning DW on the fly right now, so I don't have much experience here.
Anyway, we are currently in the planning stages. We are considering not using SSIS, and just writing our own SP to handle all DML logic for the EDW.
I'm just curious if anyone has any experience maintaining SPs instead of using SSIS, or any thoughts on the quality of this idea. Most google results for this sort of question are from about 10-12 years ago, so I'm not sure if the advice from back then still applies now.
Thanks!!
September 25, 2023 at 4:10 am
SSIS handles the extract, transformation. and Load phases efficiently most of the time:
Extract : Initial data input.
Transformation: Data mapping / data conversion / data correction.
Load : Final data delivery : to other tables / files /etc.
--------------------------------------------------------------------------------------------------------------------------------
Keep in mind, you would need to perform above tasks in functions, and stored procedures using views / tables.
Extract : with stored procedures -- Example: SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as ;
Transformation: Data mapping / data conversion / data correction:
+ These steps would require a database / data/process design using a good modular design.
+ This can be done.
Load : Final data delivery : to other tables / files /etc.
+ Final populating of final tables / files.
I would build a set of :
+ Staging tables.
+ Process tables.
+ Final results tables.
DBASupport
September 25, 2023 at 2:15 pm
I use stored procedures to implement the logic (or DML), but still use SSIS (or ADF) to perform the initial extract of the data into a staging area. I do this because SSIS is really good at extracting data from multiple different sources, and I can easily control the flow and order of execution...which would be more of a challenge if you use stored procs only.
I think the choice of whether to perform the transformations in SSIS or stored procs is more a case of personal preference than anything else. I like the idea of being able to edit & maintain stored procedures autonomously and without the need to redeploy SSIS packages, but I know some folks who prefer to write the SQL code directly in SSIS tasks and there isn't really much of a difference from a performance or efficiency standpoint.
September 25, 2023 at 8:08 pm
Thanks all for the suggestions. Do you all have any recommendations for learning resources (books, or sites) on this topic?
Thanks!
September 25, 2023 at 8:40 pm
There are numerous people in the SQL Server community (including myself) that either have blogs or training content, but you'll have to search their content to find everything you may need. Here are a few I can think of:
I know there's a bunch more that may have content on this topic, but this what my failing memory is able to come up with today :-/ (Hoping others will add more)
October 2, 2023 at 1:18 pm
If you have the skillset in house, I highly recommend using SSIS. If the skillset is not in house, scripts are probably preferred. But in ETLs of any complexity, they can be hard to manage. SSIS makes ETLs, particularly ones with lots of steps, easier to manage in my opinion.
October 25, 2023 at 1:11 pm
Thanks all for the suggestions. Do you all have any recommendations for learning resources (books, or sites) on this topic? Thanks!
The Data Warehouse Toolkit by Ralph Kimball
I first acquired the book 20 years ago and still use it now.
There is also a Microsoft Data Warehouse Toolkit which is an extension of the book in terms of relating it to a SQL Server solution.
The Kimball Group website is also a good resource in its own right.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply