Slashdot Mirror
Ask Slashdot: Choosing a Data Warehouse Server System?
New submitter puzzled_decoy writes The company I work has decided to get in on this "big data" thing. We are trying to find a good data warehouse system to host and run analytics on, you guessed it, a bunch of data. Right now we are looking into MSSQL, a company called Domo, and Oracle contacted us. Google BigQuery may be another option. At its core, we need to be able to query huge amounts of data in sometimes rather odd ways. We need a strong ETLlayer, and hopefully we can put some nice visual reporting service on top of wherever the data is stored. So, what is your experience with "big data" servers and services? What would you recommend, and what are the pitfalls you've encountered?
Oregon Resident here. After the recent issues with Oracle..... yup. Not gonna recommend 'em again. Not a big fan of my tax money being wasted.
The first step is to ask Slashdot a really vague question to a highly technical and expensive undertaking.
Define the goals. Don't mistake software for creativity and insight. If your company is going to crunch a lot of data find someone qualified to think analytically and recommend the correct tools for the job.
I hear that R is very upcoming in statistical work. I also hear that any other 'big data' solution is going to cost you as much as a full time employee anyway.
Also, yes, skip Oracle. If you put that much effort in to tuning a system/the way you're asking the question nearly anything could come up with a valid answer that quickly.
Help do my job for me.
Only the State obtains its revenue by coercion. - Murray Rothbard
The way you're going at it you're basically burning money. "We must have this big data thing too!" is every hardware vendor's eyes going "ka-ching" and you'll be overpaying whatever you do. Even if you think you're getting a good price.
The problem with big data as a thing (BDaaT) is that without a clear goal you'll be gathering too much data and storing it for too long. Thereby you "need" too much processing power to shoot through it, and the only way left is downhill. This creates myriads of problems, of which overpaying for too much hardware is but the least.
So, you think you're serious about this big data thing? Just bring sacks of money to your fave distie. That is all.
This. Redshift is far and away the cheapest and most straightforward solution. Hooks up nicely with Tableau to help analysts, efficient ingestion.
Open-source so you don't have to cough up millions of dollars to see if you can get business.
Clusterable, scalable and standards-based so you're not locking down too far into one solution-space.
Trying to become famous by taking photos. Visit my homepage please.
do your job or go apply at mcdonalds.
Pretty easy to try it out immediately... http://aws.amazon.com/redshift
Don't waste your time and money, just go with Hadoop.
Need ETL? Well for one there is PIG, but if you want to do stream processing Apache Storm / Kafka.
Take a look at this, http://hortonworks.com/hdp/
All completely Open Source.
MSSQL?
why would anyone in their right mind go with MICROSOFT for a company database ? specially a big data database ?
I will not claim any "big data" experience.
At least you have an opinion informed by no experience.
Whatever you do, don't go mssql as you will end up processing most of your data in the analytics tool.
I've seen it lock tables even on only reads causing other processes to be terminated.
The closest it has got to materialized views are clustered indexed views which suck and can barely do any processing.
Big data is an entire field of study, this is not "should I use vi or emacs or nano" and even that requires a shitload of context and the source of flame wars until the end of time.
Think about your budget, your audience, and the value that you can add by spending time and money on this.
MapReduce (hadoop) is awesome and open source, you can run it in house or in multiple cloud offerings and has a tremendous community. BUT it sucks at relationships (foreign keys) graph calculations and others.
Graph databases can make connections between things that are impossible in other systems, but are only good for graph relationships.
OLAP data stored in n-dimensional cubes allows reporting and analysis if familiar tools that many analysts (not programmers) think is the cat's pajamas.
Your best be is to slow down and talk to your users, while reading Seven Databases in Seven Weeks
https://pragprog.com/book/rwdata/seven-databases-in-seven-weeks
And then realize that you probably need to hire a consultant so you have somebody to fire when the whole thing goes south.
If the data fits in a database, it is not Big Data.
If I was tasked with coming up with ideas for a Data Warehouse Server System, and given that I know almost nothing about such systems, my first port of call would probably be Apache. What about Cassandra, Hadoop, Hive, Mahout or Pig (or combinations thereof)? All of these are downloadable and playable-with (and being Apache, FLOSS).
As a previous poster pointed out, there is also PostgreSQL, again FLOSS. Again downloadable and playable-with.
You never know what is enough unless you know what is more than enough. - Blake
Sounds like you're very good in the buzzword-department but have no idea what you're doing at all.... What kind of data are we talking about? Lots of writes? Lots of reads? Is the data suitable for splitting up? What kind of queries will you need to run? Do you need uptime? Or consistency?
Also if you're looking at MSSQL or Oracle, you obviously DO NOT HAVE Big Data. Big Data is data that cannot be dealt with using regular RDBMSes. Do you really have or plan to have multiple terabytes of data? If not, you don't have big data.
Based on the information you've given us we cannot give you any advice at all apart from stopping what you're doing and hiring an expert.
0x or or snor perron?!
I would use open source and my own servers, but since you're considering Oracle and Microsoft,
You should look at IBM Bluemix. I've heard good things about it. Watson integration.
"First they came for the slanderers and i said nothing."
You're asking the wrong questions.You should start higher up the chain in business-value land - WHYdo you need a data warehouse system (to run analytics)... great WHY do you need to run analytics (to discover XXXXX from the data we generate/own/handle). OK now you're getting closer... now, armed with the knowledge about what data you will be storing, and what kind of insights you would like to generate, you need to approach a specialist data analysis & insights company who can help you to select the correct products and platforms for your data storage, processing and analysis needs.
The way you have phrased the questions in your post makes it obvious you don't really have a lot of experience in this arena, and this is not a decision you can afford to get wrong. This company may also be able to offer consultancy about generating your queries, reports, and carrying out some of the data analysis, but it sounds like you want to do this yourself - now that's actually quite reasonable to attempt in-house.
If your company buys 'big data', I have a bridge to sell you.
Know your data. Don't build a castle in the sky; that's how SAP happened.
All rites reversed 2010
1. Hire some bonehead that is expendable and ask him to make the decision.
2. Fire him when the project fails.
3. Nobody will ever bring this up again.
Got Code?
The company I consulted for uses SAS (on the mainframe, AIX boxes, and PC's) for almost all of its dataprocessing needs, including ETL work. Now they're looking at "Big Data" and discovered they need parallel processing to make it cost-effective (outperforms the mainframe, no per CPU-second charges, ability to let analysts work on AIX boxes or PC's etc.).
I was able to show significant cost and performance savings in SQL queries over the mainframe (and AIX boxes). Interestingly substantial (50%-100%) speedups were also possible by accessing the Teradata machine in its native SQL (bypassing the SAS "in-database" Teradata support).
The interesting thing about Teradata is that they offer genuine parallel processing (like Hadoop), but offer it as an end-user ready SQL interface to a database engine (you still need sysadmins though). Contrast this to Hadoop where the Hadoop layer is basically the start of the road and you usually have to worry about hardware issues and software architecture issues (such as which database engine to choose) as well. Sometimes you have to take the custom-made route (e.g. Wall-street firms doing automated trading) but sometimes it's an outright liability in a DIY-hostile environment (e.g. in large corporations).
The teradata machine I worked with supports SQL, SAS, and R (which competes with SAS of course, and usually out-competes it when it comes to advanced statistics if you know what you're doing but we had to use SAS exclusively, by order) and could easily handle terabytes of data.
So my suggestion is to take a look at it.
It's not Open Source (although it does support R), and it's less fun for tinkerers, and it's harder to custom-parallise your own algorithms on (I hear, I never tried). On the other hand it does provide a ready-to-run parallelised SQL database and lots of storage. It's not cheap though, but in a corporate environment that's usually not the first consideration.
.. a slashdot topic on which I might actually be qualified to comment. I’ve spent a lot of time analysing suitable databases for data warehouse. As other commenters have mentioned you don’t really give enough detail about the types of data and likely use cases however I can assumer your going to do similar things to most of our customers. We have used 2 products in our business, both are column stores which tend to have the characteristics of very fast read/join and query but should not be used for anything remotely transactional. Initially we used Infobright which has an OSS community edition which for a Kimball-style data warehouse will happily take you up to 2-3 million rows before the query performance on more complex joins starts to creep over 1-2 seconds. As we took on larger clients we switch to Amazon Redshift. This is essentially a fairly distant cousin of Postgres with a bunch of technology thrown in from parexel. we found it the best performer by far in terms of bang for buck (you need to use the SSD disk option) when compared to things like Teradata (mentioned above) supports encryption and is very easy to get up and running with. If you follow Kimball’s http://www.kimballgroup.com/ design patterns you cant go far wrong but keep it simple at all times. We use Talend for ETL but are in the process of developing our own technology and Jasper-server Commercial for out front end Disclaimer: I have no direct interest in the products mentioned however I am CTO of a BI/Data Warehousing start-up (www.matillion.com) and have spent plenty of time in the trenches with
hmmm.
The ELK Stack might be an option. In my field, (many) web servers can stream all their logs off-site in Real-Time using Logstash Forwarder (or instead they might use rysnc, or rsyslog, or...). A central server, in the secure private intranet perhaps reads and indexes this log data, (that's ElasticSearch, which is sort of like a personal Google for your logs, any logs of any kind, or other Big Data). Kibana is a user-friendly Angular.js application and presentation layer. If you're familiar with NewRelic for server monitoring, you can save views just like when using that tool.
http://jakege.blogspot.nl/2014...
Okay, maybe this is sort of like 'when all you have is a hammer, everything looks like a nail', but this suggestion is the extent of my background in this area. Although I have had an itch to scratch, and so far, this is my best open-source result.
There's a ton of citations you should search for yourself, but I'll provide one I found that might start to help. Using this tool, it is fairly easy to parse out the myriad of hacker efforts at attacking the servers for example; even when you're the NY Times.
I know a few. They are all looking at options to get rid of Oracle, and often of Solaris as well. On the other hand, MSSQL is still basically a toy. It really depends on you data model and the queries you run. Key-value stores ("no SQL"), for example, are really easy to distribute over many servers.
Most ACs are not even worth the keystrokes to insult them. Be generically insulted by this and ignored otherwise.
ooh you 'orrible cunt!
Political debates have me rolling my eyes so much I think I got optical whiplash. I should sue. - Foamy The Squirrel
Hi, i would take a look at exadata if you really need great performance. The oracle rdbms is one of the most reliable i know of so paired with hardware specifially designed to perform is a nice thing. Otherwise you could try to work with flash storage (flash cards) for really high performance but you will still need a good database. I dont know how good all these SQLDB (open, ms) work but for sure i know that they dont play a vital role in the enterprise environment i work in and know of. So stick with IBM/DB2 (maybe BLU as performance boost for Big Data) or Oracle (with 12c they have created in memory queries like in SAP HANA so this could work well in your case buuuuttt you need physiscal memory for every bit of data you want to query fast so maybe this gets costly.....
This is a MASSIVE undertaking, requiring deep and profound strategic decisions to be made at the highest levels of the company/organization.
To go all in on what advice you might receive from slashdot is fool hearty at best.
Do yourself and your company a favor, hire a world class consultant to come in and provide some advice.
Opinion:=TMyOpinion.Create(Me);
Not that I am a fanboi of Oracle, but ODI is a fantastic tool.
Opinion:=TMyOpinion.Create(Me);
If you need to ask this question on Slashdot then chances are you don't have the skills to build and run such a system properly.
I'm a little late to the party so this might get buried but here goes.
I would strongly recommend looking into Microsoft's Analytics Platform System(APS), Formerly Parallel Data Warehouse(PDW). It's an MPP appliance that combines PDW and Hadoop. I got to spend a week on one of these appliances recently and I can't wait to get back on it. It supports combined queries usinf polybase across Hadoop and the Data warehouse(as well as the cloud).
Typically data scientists will want to work in Hadoop and use R, this makes it easy to migrate your Data warehouse into Hadoop so the data scientist can do his analysis without affecting the traditional BI clients that are using the warehouse.
I would also recommend SQL Server Analysis Tabular mode to build cubes off your Data warehouse. I have one client that uses the old PDW and creates cubes in SSAS tabular as well as Powerview in Excel and Sharepoint and it is loved by the end users. It's fast and the data visualizations are great. I will admit that Tableau is beautiful and I really like it, but users almost always want their data in Excel. It's best to just start them there.
The good news for you is that MS is offering subsidized POC installations with their gold partners. What this means is you contact MS and tell them you are interested in a Proof of Concept and they will provide you with vouchers to pay for one of their gold certified partners to come in and set up a data warehouse with your data using their appliance. Then the gold partner bills MS instead of you for their time. It's a win win win. If it doesn't work out, tell MS to take the appliance back. You can also have an off premise POC if you like. Those are a little easier to set up because you don't need to get your organizations IT server team involved.
I've been building data warehouses since 1998, been coding since 1985 and I am very impressed with this technology. It seems clear to me that in a massive data warehouse scenario this appliance is a winner. I'm still excited about how easy it is to move massive amounts of data between the PDW and Hadoop. That's incredibly useful for a number of scenarios.
Now before anyone starts skewering me for being an MS fanboi, let me point out that there are a few things that MS does well. Databases is one and Excel is the other. MS pisses me off to no end for many other things, but these two spaces are impressive.
Oh and I forgot to mention one of the great things about it being an appliance is that a lot of the configuration headaches are taken away from you. Need more space? Just plug a few more nodes into the rack, tell the appliance to redistribute the data and off you go. That gives you the freedom to focus more on your data and less on administrative tasks that you shouldn't have to worry about.
You can find out a lot in a few hours just by going to a Big Data meetup. Traditional database vendors are trying to hijack big data and make it their buzzword. Real big data players are using tools like Hadoop, Spark, Solr, Elastic Search and other tools that allow you to use commodity hardware to get a much more performant platform for big data. The appliance vendors have some interesting off the shelf stuff... you should really take some time to see what is going on... it's wild west time.
-- $G
I'm not sure if you're trying to be funny here, or just lacking in knowledge about the history of Sybase.
I'm guessing he has experience with Microsoft, with respect to which his opinion is highly informed.
Microsoft doesn't win the real "Big Data" contracts, but there's many medium data contracts with delusions of grandeur. I work with a TB-size (as in, >1 TB...) database and while it's certainly no longer small data it's not "Big Data". It fits in a traditional RDBMS, when we get past the buzzwords what our users want are fairly traditional cubes/reports with drilldown that OLAP systems provide. If Microsoft is bad, the alternatives like Oracle, SAS, SAP or IBM are worse. Looking at an open source stack replacing the database is actually the easy bit, I'm sure we'd do fine running on PostgreSQL or MariaDB. Reporting tools on par with Reporting Services are also easy to come by. I've seen nothing as user-friendly as Integration Services on the data flow side which we use a lot, but I guess we could use it with foreign sources and destinations too.
Probably the biggest lack on the data warehouse side is an open source OLAP server. The wikipedia page lists two, one is Palo/Jedox which is a very limited marketing version for their commercial product and the other is Mondarian which by closer inspection seems to just translate MDX to SQL and let the RDBMS database do the aggregation which I suppose is okay for small data sets but will choke on any significant volume. Basically it comes down to all the Microsoft tools being "good enough" and working nicely together, while the rest ends up being a mix of different pieces from here and there. Either that or you're looking at a whole different stack, and I got lots of requirements that'd make a NoSQL solution squirm.
Live today, because you never know what tomorrow brings
We use http://en.wikipedia.org/wiki/G... which is a clustered Postgres implementation. It has its problems (Postgres 8.2? seriously?) But it is very fast for ETL and batch queries on large data sets. We house 100+TB and get excellent performance. Its commercial and you pay by the TB.
Then there is also AWS Redshift. We have found it to be quicker at some things and possibly cheaper but immature in its feature set (no UDF, etc). The thinking here is that if you have a separate system for ETL, Redshift would make an excellent data warehouse/ data market SELECT server. Pay by usage/ hour.
Synergies are basically awesome, and they're even better when you leverage them. -PA
Don't confuse a regular data warehouse with Big Data. If Big Data is a "thing" your company wants to get into, it probably does not apply to you.
As for your data warehouse, MS SQL Server and is a good enough base to start with. IBM's DB2 is another underrated platform. Don't feed Oracle please.
Use SSD based storage for the data, so you don't have to wait for spindles. Seems that Pure Storage does it best of late, whereas other vendors have optimized the spindle based storage. PS did it from ground up. Best part is the documentation, Its ALL written on a single 3x5 card. No matter what software you use, skip the spindles.
Time for a new Political party in the US (or two!) One is off the rails Other cant pony up a leader.
This is a wildly nontrivial question. Volumes are written about building data warehouses, and there's a lot to consider. In a large complicated environment, you could spend weeks doing comparisons (some people spend years, but that seems extreme); and some of the decisions are worth weighing.
The first question is what capability are you looking for -- why are you sure one of these vendors is correct, and have you truly explored your options? If you want a place to capture and gather lots of near-real-time sensor data, then Hadoop might be good, if you want a more traditional Kimball or Inmon style warehouse for a small or mid size amount of data, then Microsoft, Oracle, Teradata, IBM, MySQL, and others have decades of experience that is, in fact, useful. But that's just a single-source vendor, and your question is focused on database vendors. Asking what "capability" you need includes ETL, Reporting, Meta Data, Master Data, Data Quality, User Interaction, Training, Methodology... if you're going to in-house all of that, or spread those things to multiple vendors then your answers will be different.
All of those lead to follow-on questions. Where does cost play a role? Watch your up front costs vs long-term TCO. Do you have a development team with any expertise that may make it easier to in-house decisions and developments for one platform over another? Is your corporate buy-in strong so you can weather people second-guessing your decision? There are technical issues, personnel issues, cost issues...
The first ANSWER is really that any vendor will work, and every vendor will have different headaches. Older vendors have very specific ways of doing things, but that can make developers less expensive and more uniformly capable (although you'll always find extremes). Asking several Oracle DBAs to question each other and report back on each other's competencies is rather easy. With newer capabilities like Amazon, Google, and other cloud-big-data vendors, the landscape is newer, people are using different approaches (each of which may be valid), and it's not clear which are going to survive long enough to have the richest eco systems. But again, these systems came into being for a reason -- Hadoop and NoSQL databases can perform better and more cheaply than older databases in raw throughput, or unstructured data, or other areas but they sacrifice different things -- ACID compliance, strong typing or data models, or what have you.
Some of it just depends on taste. Some people avoid a single provider "lock-in" and pick and choose different ETL tools (see Informatica), Reporting Tools (Cognos, Microstrategy, Tableau, Jasper, Pentaho), and other tools (Talend DQ/MDM comes to mind... there are many), while some people prefer single vendors due to massive integration (particularly Microsoft if you're a Windows farm). If you're Gmail based, then Google's apps have good integration; if you have an Oracle ERP then several tools speak nice to it.
I'm generalizing a lot of examples that don't always apply, to keep things shortish, but the bottom line is that every option has strengths and weaknesses. I wish it were easier.
Ahh, yes. Cloud stuff. Where you are processing a lot of data and where your processing and I/O resources are not your own. I always laugh at people who say "Oh, we don't need all that infrastructure stuff" and start moaning "Oh, why does it cost so much and why do we have to spend so much more when we add data?" Not to mention putting your important data on a platform that is financially questionable, has outages that providers simply don't care about and where it's going to be one hell of a PITA to move at any time later owing to the amount of data.
Sounds like a recipe for success.
Data Warehouses are a completely different beast. I've had to do research into several DWH offerings in the past, and Microsoft actually does a very good job. Each system has a lot of pros and cons and different performance characteristics for different kinds of loads, but there are plenty of 100TB+ Microsoft Data Warehouses.
I recommend against MSSQL not because it's not a good DB (it is -- it was originaly Sybase) but because it's cumbersome to work with outside of the Microsoft ecosystem. You mainly interface with it using ODBC and that's a pain outside of Windows. You're stuck with windows boxes on the back end AND on the front end. You can add ODBC systems to the mid-layer/server boxes you'd rather have (Linux, usually) but now you're paying money to add a kludge. Furthermore, because it absolutely needs to run on Windows on the back end, you have to pay employees who are generally of the sort who are going to want more Microsoft tools, so you'll be creeping more and more away from free stuff which is easy to maintain to a bunch of licenses and a complex setup. (Had to get a bunch of Windows boxes set up with precisely this sort of issue just a few weeks ago -- man! was it painful.)
You could start your project with Postgres and find out why you're unhappy with it and plan for a migration to something which is better for you post-hoc: Don't write SQL procs, and don't weave your SQL through a whole lot of code. Though frankly, the suggestions for Red Shift seem right on the money. They use Postgres drivers, JDBC, and ODBC, so you're set on any platform you want to work on without any added cost. They have a two-month free trial. You could try that out first and figure out what you're unhappy with there as a first step. Same rules apply -- keep things simple.
DBs are not for chewing data -- they're for giving you just the data you need so you can chew on it. You use the right tool for the chewing job once you have the data. (Some DB pre-chew is fine in situations where it's efficient and easy -- group by's, mostly.) So it doesn't matter that much how long the feature set of your DB is. What matters is that it's fast and you can get data in and out of it just about anywhere you want to. I've seen shops where they do all their data chewing in SQL server. They write reams of ugly, ugly code. They do this because they know how, and don't realize that a little work learning other things would make them vastly more efficient. The thing to always remember is that you don't buy a hammer and assume everything is a nail. Buy something which works with lots of other tools and pick the right ones for your job.
For a more SQL-oriented approach with open source, take a look at the madlib library that extends PostgreSQL with user-defined types and many stored procedures for in-database analysis. It can also be scaled up with $$$ by running it on Greenplum instead of pure PostgreSQL, but you can go a long way with PostgreSQL on a modern, commodity server with large RAM (256GB...1TB) and/or fast disks (hardware RAID and/or SSD). You may be able to focus more funds on this hardware rather than a bunch of software licenses.
My experience is that many SAS and STATA developers are blind to the extremely inefficient data handling they do. The steps they perform to produce extracts as new intermediate files before running a final aggregation are shockingly primitive. It's like they write a 1970s RDBMs query plan by hand and put all intermediate results to disk as new files: Load a table. Filter it. Sort it. Dump a table. Load another table. Filter it. Sort it. Dump it. Load the first dump. Load-and-merge the second dump. We finally have a trivial join with two tables and a where clause!
Writing a normal SQL query to extract the same intermediate table from the RDBMS is a night and day speedup, and incorporating the actual aggregate calculations into that SQL query can often obviate the need for the SAS or STATA code entirely. Allowing the query planner to optimize the whole data flow is a big win, compared to the naive sequence of tasks the programmer would write by hand.
However, the main obstacle is cultural. Those SAS and STATA developers often have no interest in learning declarative SQL nor allowing their existing programming skills to look unnecessary to management. So, you can get a big backlash just suggesting that the legacy methods in an analysis group might be part of the problem rather than state-of-the-art wizardry. It's a bit like telling Java ORM users that they should actually use the strong capabilities of their RDBMs instead of treating it like a dumb store and doing all the filtering, sorting, and joining in their Java code.
Totally this. I work for a company that has a 5TB database that's currently holding all granular transaction data for a few thousand companies over 10 years. The main transaction detail table grows by 1-200k records per hour on average (around 50 new inserts a second), which amounts to about 1-2 GB a day. With the way things are ramping, we're on track to increase by around 1 TB a year on that database. We allow several levels of reporting to those companies, with details vs. aggregation, and all kinds of data warehouse slicing and dicing for everything they could possibly want. There are issues with some reports being slow sometimes, and data warehouse problems occasionally making it fall as much as a whole day behind (oh, the horror!), but it generally works.
As a rule of thumb, we don't consider this anywhere near big data. A large Oracle database, and some standard (by now we could call them "traditional") tools for cubes and data warehouses is all we need.
I've worked for large financial institutions and Unix is something that would be considered "small" in that environment. I've also seen departments shoehorn Microsoft products into a big problem and fail just to have to turn around and use something else.
A Pirate and a Puritan look the same on a balance sheet.
In SSIS (the ETL tool that comes with SQL Server), the default isolation level is serializable. People often use SSIS to stage data and/or feed a denormalized data warehouse.
Someone claiming that an analytics tool is causing locks in SQL Server does not know what they are talking about. The most recent BI engine from Microsoft (Tabular) does everything in-memory, and with the older one, which is OLAP-based, data is typicalled moved out of SQL Server and into a SSAS cube.
There's the possible scenario of someone deciding to use ROLAP; feeding a cube from a live production database. But if someone took pains to setup that kind of thing and yet used a locking isolation level, then he should not complain about it on Slashdot, he should RTFM.
lucm, indeed.
I see lots of buzz words, but they don't make much sense together. Big Data and a Data Warehouse are not the same thing. If you *only* care about big data, you don't need to care about ETL. All of these things require you to know your data (and to have a goal). Its one of those things were the execution is a lot more important than the product chosen. Your goal cannot be 'get into this "big data" thing'. I'd recommend finding some user groups for the tools you're interested in and asking a few other companies what they are doing.
I recommend against MSSQL not because it's not a good DB
I'm assuming this is based on your extensive MSSQL experience, right?
but because it's cumbersome to work with outside of the Microsoft ecosystem.
Well, at least MS has half-decent tools for it. Other than Oracle, they're the only player with a decent GUI interface.
You mainly interface with it using ODBC and that's a pain outside of Windows.
Or JBDC. It depends. Its not really Microsoft's fault if it doesn't work in your environment, is it?
You're stuck with windows boxes on the back end AND on the front end.
You just summarized 2/3rds of the corporate world.
You could start your project with Postgres and find out why you're unhappy with it and plan for a migration to something which is better for you post-hoc: Don't write SQL procs, and don't weave your SQL through a whole lot of code.
You could, and then figure out how to integrate it with your environment. And *WHICH* ODBC driver to choose. So, the pain you just described previously, its right there. With a half-assed, subpar connection driver.
The only OSS solution that comes somewhere *near* what MSSQL does is PostgreSQL, and its a second-class citizen in Windows. And even PgSQL is easily suprassed when looking at features and replication options.
Note: I'm a huge PostgreSQL fan, to the point of writing C# applications with the native PostgreSQL driver without LINQ support.I'd take PostgreSQL over MSSQL everyday of the week if reporting tools, support, features, replication and integration doesn't matter. But saying that MSSQL is bad, is just a silly mantra.
No offense, but from the sound of it you have no clue about a BI infrastructure, which is what you're talking about. If your company is serious they'll hire a team of 10 people w/ an average salary well north of 100k and have a couple million dollar budget per year for IT systems, including an analytic data base, ETL system, and BI application.
My guess is that you just want to start off by incrementally building a DW and want ad hoc analytic capabilities. My proof of concept solution would be to use Pentaho Data Integration (PDI) as the ETL layer, PostgreSQL as the db, and Tableau for visualizations. As you move into the big data space and build out your data model you should move to an analytical DB, and the cheapest good solution is Redshift from Amazon. Most of the Analytical DBs are derived from an old version of Postgresql anyway, so as long as you don't custom code the ETL solutions and use standard sql, migrations should be very easy. Also, as you grow, you can migrate away from Tableau to a real BI application like Cognos or Microstrategy. Also, as your data grows you may need even more storage for persistent staging areas and can then consider Hadoop. I would not recommend it to start, unless you really know what you're doing. As for advanced statistics everyone is using R now but is problematic w/ big data as it pulls data into memory for processing, so you may have to pre-aggregate the data if super big sets are involved.
I've been in the business intelligence space for over 10 years. My two top lessons learned; you need leadership in this space and to only implement custom code as a last result. For the former, BI has the ability to be implemented somewhat via an agile incremental model, but it's still a large solution and will require long term resources. Therefore, if you can't count on leadership to back you you shouldn't start the project. Secondly, custom code in this space can make a mountain out of a mole hill. For example, while you may be able to write a customized script or stored proc that's 30% faster than the ETL solution, I wouldn't suggest it. ETL, use appropriately, will help you manage your data long term. You be able to visually understand what's going on and switch DBMS rapidly.
I recommend against MSSQL not because it's not a good DB
I'm assuming this is based on your extensive MSSQL experience, right?
Yes, it is.
You're right on the replication. I think that's Postgres's obvious weak point. It's what you'd find that you didn't like. I assume that's why you ignored Red Shift. The rest of your arguments simply prove my point.
FreeTDS works well. Why would you have to use ODBC?
It's what you'd find that you didn't like.
It still is a huge limitation, as you cannot easily sync a local dataset with a remote one.
I assume that's why you ignored Red Shift.
RedShift has a limitation of 16TB per node. Its nice, but not really "big data". Its more like RDS on steroids, and I think RDS is "so-so". Also, you either use sync from/to amazon interfaces, or you're stuck with JBDC, so basically the same limitation you mentioned apply.
DBs are not for chewing data -- they're for giving you just the data you need so you can chew on it. You use the right tool for the chewing job once you have the data. (Some DB pre-chew is fine in situations where it's efficient and easy -- group by's, mostly.)
Seems like there aren't many responses here talking about columnar databases. This a class of relational databases very well suited for data warehousing. I have been working with Vertica, which is a proprietary technology, but the license terms are much more favorable and fair than what you get out of Oracle (they aren't comparable anyway). It's a mindset change when you get into columnar databases, but on the whole they can be simpler than what you get trying to tune a traditional relational database for big data warehousing purposes.
You will still need to think about your ETL and reporting technologies. This can be difficult depending on the nature and stability of your data and reporting customers' needs. On the whole, some things to think about are adherence to standards, not being afraid to operate multiple data marts, separating different reporting functions into different applications (internal vs external, raw data extracts vs analytics, etc.), and look at some map-reduce technologies (some shared-nothing databases give you that under the hood for free, some make it explicit, like Hadoop).
Alexey
Oops, down-modded by accident, sorry about hat. Wish I could undo mods within 10 seconds or something. Posting to undo my mod.
A recursive sig
Can impart wisdom and truth
Call proc signature()
Personally, I think that the RedShift suggestion is perfect for OP. Judging by the vague requirements ("the big boss wants to get on the Big Data bandwagon!"), OP's company has no clue what it wants to do with its Big Data yet. So why throw down a ton of cash on a solution without having a good idea of what problem needs solving?
Playing around with RedShift a bit and seeing what value they can extract from their data would be a great pilot program. Later, once they know what they're doing, they can implement their "real" solution.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
When a piece of data come in, store it everywhere you need it. This might be aggregated tables (if you don't use indexed views) or whatever you may need. If you have background processes like ETL, you'll use a lot of your hardware for processing at the expense of queries.
Avoid ETL. You've got one shot to store your data everywhere.
Yah, you are actually right. I didn't see it. My mistake.