I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data sources (from different systems like Billing) that need to be integrated to perform the reporting and analytics. Some of them are data dumps that need some custom conversions to get into a database ready form. These have a large number of columns. These need to be processed, required columns filtered and aggregations done etc. Typically there's about 50 GB of data produced daily and will be inserted to the existing tables each day.

We've identified that an analytical database like Vertica can be worth looking into. We don't have any prior experience with non-OLTP databases. My understanding is that Vertica (and other like it) are read-optimised and good for analytical tasks. My question is how does it fair at the initial stage where the raw data are loaded and processed? Should we use a traditional OLTP database like Oracle for that and then use the Vertica for Star Schema, Dimensional modelling type datastore? Is Vertica suited for ETL scenarios?

How is a typical architecture of this kind of scenario?

| improve this question | | | | |
  • There is a question hidden in here, asking whether OP is likely to need a different DBMS platform to support ETL for the system. @sfactor - you might try re-phrasing the question to make this a bit more clear. – ConcernedOfTunbridgeWells Aug 31 '14 at 19:13

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with Vertica and will be more familiar with the platform, I don't see any reason to assume you need another DBMS platform for your ETL.

The reason Vertica and other systems are ill suited to transactional applications is that they use data structures that are fast to read from and batch load, but have a significant overhead per load operation. A batched ETL process that requires index rebuilds or other such operations will be a non-issue on these systems. The operations are too expensive for a high-volume OLTP platform but will not be too slow for a batched ETL job.

A common architecture used in telcos and other VLDB shops is to use flat files for intermediate storage and then load into the database. Ab Initio and Ascential Datastage are tools that are designed to work in this way and this type of architecture is common on telcos. However, ETL tools tend not to support complex transformations well, so you may well end up with a layer of data manipulation taking place within the database.

All VLDB platforms are expensive, and some are tied to proprietary hardware. Be prepared to spend up big on your server. 3 years data at 50GB/day is around 50TB, which is getting into the region where you would need specialised kit to perform well on these data volumes.

| improve this answer | | | | |
  • Thanks for the answer. There is no legacy data sources here, we will receive data dumps in text form. Thats why I wanted to think up the right architecture firsthand. Any actual case studies would be useful here. From what I know Vertica should cost less than equivalent Oracle or Teradata systems, right? – sfactor Sep 1 '14 at 4:44
  • I doubt you'd find any objective case studies on this sort of kit. It starts at 6 digits and goes up from there, so independents like AnandTech are never going to buy up a batch of machines for comparison. The majority of literature will come from the vendors and should be taken with a grain of salt. – ConcernedOfTunbridgeWells Sep 1 '14 at 8:46

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP.

The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into production. While it can do it, you'll get the best performance if you can use an ETL tool or another process to perform the processing before the data reaches Vertica.

With regards to architecture; there are many factors around what the business needs. Obviously implementing any kind of database platform will be light years ahead of using Excel. However, you may want to consider having an OLTP if there are actual transactions that need to be quickly captured and later move the data to an OLAP. Some organizations load certain data that is immediately required by users straight to Vertica, while others batch the data throughout the day.

Since Vertica is licensed by TB, you'll want to carefully consider how much data will actually be needed for analysis. Using your 50 GB figure, you'll need about 1.5 TB to store a month of data. If you want to play around with the platform, the community edition allows for up to 1 TB of raw data on up to 3 nodes.

| improve this answer | | | | |
  • Thanks Kermit for the answer. I'm trying to find actual case studies of an architecture where something similar was done. This analytical framework is separate from the live transactional system. We need to figure out the ETL front-end for the ETL. Any thoughts on @ConcernedOfTunbridgeWells below? – sfactor Sep 1 '14 at 4:41
  • You can try browsing around vertica.com or third party vendors for white papers or use cases. If you choose Vertica, architecture could be part of the work. In regards to your earlier comment, I'm pretty sure Vertica has a much lower cost of ownership than other EDW. Feel free to reach out privately (send me a message at vertica.tips) and we can discuss further. – Kermit Sep 1 '14 at 14:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.