SQL Server vs Oracle vs Open Source 1

[pjam1976]

All things being equal what have people worked with and what do you prefer?

I have a great opportunity on a new project where I get to utilize whatever I really want. I'm leaning toward SQL Server and might try 2008 rather than 2005 just because it's a new data warehouse and the fact that it's a MS shop sort of makes life easier for everybody involved. Plus the whole integration of ssas, ssis, ssrs, and so on. And the fact that you can integrate MS Office and sharepoint is kind of cool. Even though I'm not a big fan of sharepoint. They seem to like it though. SQL Server is comparatively less expensive for smaller companies and projects. But I'm not a big fan of Windows Servers. But would it be wise to jump on the new 2008 bandwagon without really using it or knowing about the bugs? That is the big question. Heck it hasn't really been released yet. But it's intriguing.

There is the Oracle option but the problem is nobody really seems to know Oracle on the team and we'd have to purchase a lot of extra etl and bi tools. But I do like the possibility of using a linux or unix server option and for future growth, I'd still prefer an Oracle DB over SQL Server. But will I need that much robustness? Also I haven't really utilized Oracle DW Builder so teaching others on the team how to use it would be a learning curve. Especially considering they are a MS shop. Not always easy to sell.

There are the MySQL and PostreSQL(more like EnterpriseDB based sort of on PostGreSQL) but I've never actually built a data warehouse based around open source. Might be interesting and would save them some money...but then again who knows. MySQL is great for web based OLTP.... Not so sure it's that great for OLAP or DW. EnterpriseDB is interesting but I've never really used it and I'm sure nobody on the team has worked with it. At least when it comes to data warehouses.

The whole Vertica cloud computing idea is another intriguing idea. But is it wise to base a data warehouse off of a cloud computing server. I'm not so sure about that. I'll have to research Vertica a little more.

DB2, Teradata, Netezza and so on are just not in the equation. Teradata is great but it's too expensive and in all honesty for this project, it's just too big. Even with growth it probably wouldn't be worth it. I've never used Netezza but I'm assuming it's similar in cost to Teradata. They are great and true massive parallel processing systems, but do I really need to spend that much money for a database that isn't holding petabytes of data?
As far as DB2 is concerned.. I'm just not even considering it. The majority of projects I've worked on who used DB2 were mainframe based. I know that's not all it's used with, but that's my experience. A lot of people seem to ignore or forget about DB2. Not exactly something that would get the team or sponsor excited about a new project.

In conclusion :

I like the inexpensive SQL Server aspect with all those integrated tools. I also like the fact that this company is a MS shop. But again is 2008 really going to be able to compete with Oracle and other databases or will it just be another MS product that crashes and burns like Vista? I don't think it will as 2005 actually was a decent enterprise database, but it still wasn't as good as Oracle.

This project has about 3 or 4 sources and only gigabytes of data. It would be interesting being one of the first to actually implement a dw based on SQL Server 2008. But would it be worth it? That I don't know yet.

What are some of your opinions?

 

[chrissolo]

We're also considering SQL Server for a new DWH. I'm not that familiar with either 2005 or 2008, but what strikes me (at least in 2005) is the lack of support for metadata. Otherwise I would think that it would be ok, SSIS is maybe not as sophisticated as other ETL tools out there and the end user query tools are not very user friendly. Apart from that I think it would fit your needs, particularly since your data volume seems quite low. You might even want to consider MySQL, we have at the moment a reporting DB running on MySQL with about 1GB of data, I think it can easily handle much more than that (although maybe not TBs).

I would also be interested in hearing from someone who has actually used SQL server for data warehousing.

 

[MDXer]

MS SQL Server 2005 and 2008 were designed with enterprise DW as one of the intended targets. I have worked on many DWs based on SQL server and the only time issues have arose was due to poor design.

SQL Server is not a light weight RDMS and people have this impression that it is due to their personal bias. COmpanies will spend thousands on bringing in Oracle experts to design and configure a robust system but again have the attitude that all we have to do is install SQL tweak some configs and that is the best we can do.

As sor SSIS being not as sophisticated as other ETL packages maybe it lacks features of some of the others but with the release of 2008 you are working with a V2 product, and probably one of the best V2 products MS has delivered. Can functionality be enhanced? Yes, I don't know of an application that does everything everyone needs. But you are given the tools to extend it's functionality. Again the key to SSIS is understanding the technologies and when to use what. There are tasks in SSIS that 2 or 3 may appear to do the same thing but the manner in which they do it can have a huge impact on preformance. Again it is a matter of knowing what to use, when to use it and how it really works.

If you are in an MS shop, in my opinion you are better off staying in the MS world as you can leverage existing skillsets and you may have an easier time selling it to managment.

Put together a cost analysis.

SQL v Orale v DB2 v Open Source

include hardware, license fees and personel costs. If you bring in oracle and Informatica your team is probably going to require some serious training and time to get up to speed. How much longer would it take developing on a platform where your current group lacks the needed skills.

As for my experience with DWs on SQL server I have been involved with sizes from less than 100GB to over 1TB with refresh times of 30K fact records nightly (10 minute ETL) to 35M records weekly (5HR etl on SQL 2K), and all points in between.

These all included SSAS cubes as the data provider for the user reporting piece. I have not worked with but know people or have seen demos and presentations on most of the top OLAP based applications and in my opinion you can't beat SSAS. Also most of the OLAP based engines have been picked up by other companies I believe Applix and Essbase both have been picked up by IBM and Oracle.

If you want to discuss specific elements about my experience with DW on SQL server I would be more than happy to.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[RiverGuy]

All things being equal what have people worked with and what do you prefer?

The hard thing about this question is that there are a lot of people out there like myself who have worked with mainly one platform over the years. So I won't tell you which is the best, because it's subjective, however I can give you some insight on SQL Server (2005) for data warehouses.

If you're building a small or medium size data warehouse, it's a great price point because it comes with the ETL tool, the multidimensional database and the Reporting Services all in one license. You may have to get a separate OLAP reporting tool if that is needed, unless your company already has a license for whatever product group Microsoft has put ProClarity into. If you're building a larger data warehouse and you need to break out all of the different components onto separate servers, then you're going to have to purchase additional SQL Server licenses. So that's something to keep in mind. Also, be sure to get the x64-bit version. Even SQL Server Standard Edition can utilize 32 GB of RAM on 64-bit.

SSIS works pretty well as an ETL tool. There were a couple of minor bugs that I had to get used in working with it, but overall, it will do the job for you.

SSAS may or may not be needed depending on the requirements of your project. However I will say that SSAS 2005 is a great improvement IMO over 2000, and this may address some of the metadata concerns which chris mentioned. This is where you actually define your dimensional model. SSAS works very nicely with Kimball/Dimensional Modeling methodologies.

SSRS is a decent reporting tool. The look and feel of the reports can be compared to say Business Objects Web Intelligence. But SSRS is mostly just a reporting tool. It doesn't have anything comparable to a BO Universe, and it's not where you define your dimensional model. As mentioned above, if you use SSAS, that's where your dimensional model is defined. Again, if you need an OLAP reporting tool, then you have to get something else above and beyond your SQL Server license.

I haven't worked with SQL 2008 yet, but I will tell you from experience that I started working on a SQL 2005 data warehouse within two months of the SQL 2005 release date without issue. I don't really think you can compare a DBMS to a general purpose operating system in terms of bugs or user dissatisfaction.

Another thing to consider is the collective skillset of the developers, architects and DBAs. If you're a Microsoft shop and you won't be adding new staff, then it does make sense to look at a product with which your team has experience.

As far as free/open source DBMS's, I haven't known anyone who's implemented a data warehouse with one. That's not to say they would be a terrible choice. It's just more likely they will lack some functionality which you might expect from a commerical DBMS.

 

[johnherman]

I have been part of dozens of DW projects, mostly in SQL Server and Oracle (one DB2 and no open source). I have found that SQL Server can be an all-in-one solution, having ETL tool, Reporting, and Analysis all built into the database. Although this tends to lock you into a Microsoft solution, there is absolutely no room for fingerpointing if the products don't work well together.

Oracle's major advantage in the database world is "Optimistic Concurrency", a fancy word indicating a philosophy to assume a transaction will be committed thus updating the database at the time of the SQL execution, writing the previous row values to the redo log, but not yet committing the row. Since this is mostly a OLTP feature, it is usually not important in the DW environment. (An exception would be realtime or near realtime DW).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[pjam1976]

Thanks for the replies. I'm leaning more toward SQL Server but not sure if I'll go with 2005 or 2008 yet. 2008 is intriguing because it's new and we'd be one of the first few at least implementing it. I'll have to research more about 2008 though.

 

[chrissolo]

Can somebody comment on metadata support in SQL Server 2005 or 2009? In particular, is there support (in SSIS, SSRS, SSAS) for retrieveing metadat from a data modeling tool (such as ErWin, or even Visio) and ETL tool and present the metadat to the end user while developing a report or using SSAS for analysis? As far as I can tell, there is virtually no support for metadata (e.g. CWM) in 2005, is this correct? What about 2008?

 

[johnherman]

ERwin and Power Designer have the capability to forward or reverse engineer your database. That is, if you design first, they can build your DDL scripts. Or if the DB already exists, they can reverse engineer it into E-R diagrams. I can't comment on the metadata aspects of SSIS as my experience with that product ended when it was called DTS.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[MDXer]

No you can't use Data Modeling tools to retrive meta data from SSIS, SSAS or SSRS files. Does this mean that meta data can't be accessed from these applications? No it doesn't. SSAS for example has Analysis Management Objects which easily allows someone to use .net to extract Meta data from a DB, Dim, Cube etc. There may be a tool out there already that does it. Codeplex has a number of projects to increase functionality of BI Studio BIDSHelper is just one of the one I use. SSIS, SSAS and SSRS files are XML it probably wouldn't take much for someone to build an app to extract and store/present the information required.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

Maybe this can help you with some of the questions you have.
High Performance Data Warehouse with SQL Server 2005

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MDXer]

one more link for SQL 2005 Meta Data
SQL Server 2005 Metadata

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[pjam1976]

I will be using SQL Server as they already have licenses for many MS products. So it's sort of a no brainer when it comes to fighting a winning battle. Not sure if I'll go 2008 or 2005 yet, but I think they are more comfortable with 2005, so we'll see.

One other question I have is about the front end. In the past I've worked on projects who always used something like Cognos or Business Objects. I was wondering if something like Sharepoint 2007, Office 2007, or Performance Point 2007 would do a similar trick with dashboards, scorecards and reports. I've used those products but never really integrated them as the front end of the BI app in the past. Not sure if they'll do the same as a Cognos or BO would. But again they are pushing toward working with existing licenses so I want to try to use what they have and see if I can make it work.

 

[MDXer]

Performance Point is intended for Dashborading and scorecards. MS also aquired Proclarity and is working it in as part of performance point, and excel 2007 does fairly well when used as a front end for cubes.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck