I had lunch with an old SQL Server friend of mine today. I say "old" because not only are we getting a bit long in the tooth, but also our SQL Server days started back in the Sybase and OS/2 world. We've seen a lot of changes in the SQL Server space. We were chatting about old-school DBAs and how the DBA role has changed, and I stated that extraction, transformation, and loading (ETL) has always been, and always will be, a skill set that's valuable in both the transaction and analytical database worlds. But my more recent observation is that many of the "best of the best" SQL Server Integration Services (SSIS) experts that I know seem to be more aligned with the OLAP side of the database world than the OLTP side.

Certainly a lot of Microsoft's marketing material and product positioning tends to have SSIS aligned with the business intelligence (BI) stack—not that Microsoft would come out and say that SSIS is a BI-centric tool. Perhaps it's just the circle that I run in these days, but the folks that I tend to think of as "relational" generally don't seem to have the same breadth and depth of SSIS expertise as the people that I think of as, and who probably define themselves as, BI professionals.

Am I right or wrong? Does the current crop of SSIS junkies tend to have an affinity for BI? If so, why don't more old-school relational DBAs use SSIS in elegant and interesting ways?

I did a really simple Google search using the keywords SSIS and OLTP and got just 11,500 hits compared with the 96,200 hits I got when I used the keywords SSIS and OLAP. No, that's not a scientific study of any kind, but it's an interesting number. Does that mean there's almost nine times as much content out there related to SSIS for OLAP people than SSIS for OLTP people? If that's true, does it somehow correlate to the depth and breadth of SSIS expertise in the OLAP and OLTP communities?

Old-school DBAs, BI jocks, and SSIS junkies speak your minds. Will the real SSIS expert stand up? What do you think?

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Hi Brian, I've been a SQL DBA for just about 10 years now, I've started my SQL Server career back when migrating from 6.5 to 7.0 was the main stream way to go, I do agree that the SQL Server space has changed, incredibly so and for the better. I think the main reason SSIS is so heavily geared toward OLAP versus OLTP is primarily because of the direction SSIS is going and the expensive tools SSIS will eventually replace. Most DBA's and BI developers I talk to today are anticipating the significant SSIS enhancements and functionality with 2008 and versions beyond.I know in my DBA world it's always been the DBA who created the BI processing with DTS and now I think were starting to see a seperation of those responsabilities because of the power behind SQL Server. SSIS right now is in it's infancy and those that recognize it's potential will benefit by getting in on the ground floor of a great opportunity for future success.

Best Regards, Dave Fraser SQL DBA

dfrasjr- December 13, 2007

Article Rating 3 out of 5

Brian, I've been working with SQL Server now for about 10 years, starting with SQL 6.5. However, in the past 6 years, I've primarily been focused in the BI space as a DW / BI Architect. My response might sound a little different from what you're looking for. I don't think that this is an SSIS issue or question. I believe BI is becoming more and more pervasive as all software vendors look to implement some sort of analysis and reporting capabilities into their applications. Everyone comes to us asking "I need BI", "How can I get BI", etc. So, perhaps the real answer is that many software vendors tend to weight their software heavily with BI functionality, which then makes it very attractive to a BI Developer/Architect.

Thanks for your article Brian. Definitely some interesting thoughts.

kenraetz- December 14, 2007

Article Rating 3 out of 5

Hi Brian, I think that the disparate numbers are due to the nature of the way SSIS packages need to be built. Aside from the number of work-arounds that need to be employed to use SSIS as an ETL tool, I find that it is architected more towards the way an application developer goes about building an application than a straight-forward A-to-B-to-C tool that follows linear and/or dependant processes.

Most OLTP systems exist to provide the users with a place to capture, store, and manipulate their business data. OLAP by nature takes that data massages it to give decision makers the information they need to make their business successful. The BI space is becoming more of a bridge between the users and their data, so it is natural that those people whose thinking is geared to working with the layers that are closer to the user-interface will use SSIS more heavily.

Regards, RBS, SQL DBA

ryanbseattle- December 14, 2007

Article Rating 3 out of 5

Hey Brian, just some thoughts on the subject. It occurred to me that those SSIS users professing to be on the BI/OLAP side are in actuality OLTPers and they don't know it! All kidding aside, the emphasis on such a question (and orientation of the answer) is placed on the "to" (destination) of the data, when in fact, the "from" (source) of many ETL operations is the OLTP system itself, so when there's a desire to limit prod OLTP db impacts of running reports for example (thus the common practice of ETL-ing that data to a central store otimized more for OLAP), I'd say OLTP is heavily involved in SSIS (just from the reverse perspective normally assumed). Unless you're synching up federated dbs or doing some sort of HA/FO replication (using SSIS), an OLTP sytem is rarely the "to" system of an ETL op.

In order for the BI gurus to get the data in the form/place they needed, I've noticed that learning how to aggregate, transform data started out as a "necessary evil" for them to take on, since there wasn't a pronounced need for (hence knowledge about) ETLing stuff around in the OLTP DBA circles to draw from (particularly where the "T" in "ETL" is concerned; straight copies of course were more prevalent/useful in the OLTP world). The traditional OLTP DBAs were more concenred with stuff like security, limiting disk IOs/table/index contention, troubleshooting prodution problems, etc. (you know, keeping the company running! ha) - not to mention helping report off the OLTP-normalized dbs directly (another avenue that didn't require/have a use for ETLing stuff) - especially before marts/warwehouses became vogue, so out of necessity (in my experience), the BIers assumed the role of the ETL experts (again, especially the "T" part, which is more a concern of preping data for reporting/mining, etc.

galaxis13- December 17, 2007

Article Rating 4 out of 5