data warehouse automation tool: benefit or bane? [closed]

Question

Today while investigating ETL and data warehouse solutions like Kettle, I came across a new concept (for me) called data warehouse automation tool.

I also looked at the white paper here, which indicates there are few vendors in this space.

One reaction I have to these tools is: whoa, anyone purchasing one of these tools is making an enormous investment. At the same time, it seems many data warehouse projects fail, even though they take a significant amount of developer hours to implement.

So, if anyone out there does have experience with a data warehouse automation tool in a real-world project, would you be able to answer these questions?

1. Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?
2. If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?
3. If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?
4. When would you see a DWA tool as being overkill?
5. What did you like best about using a DWA tool? What did you like least?

Answer 1

I'll weigh in from the user perspective.
I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed.

1. Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?

The answer is a great big "it depends". I have found it to largely depend on the complexity of the datawarehouse you are building, and the skills you have using the native tools.
We could easily use the tool to allow customers to design their own datawarehouse and olap solutions with little training and knowledge of the underlying technology provided the requirements were simple.
For an experienced datawarehouse and OLAP developer in the native underlying tools the gains were much less impressive and I haven't seen automation tools supporting each and every feature of SSIS and SSAS. Most provide hooks where you can plug in SQL or XMLA scripts, but as complexity of a project increases you end up scripting more than you are using the automation tool.
Furthermore, as datawarehouse automation tools support multiple versions of the underlying technology I have seen new features in SQL appear later or not appear at all in the automation tools.

2. If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?

In big complex projects: performance and the lack of flexibility of the automation tool. If you do everything by hand you can configure parallel processing perfectly the way you like it, you can use tricks in your queries to make them faster, you get to think about which operators are blocking in SSIS, what the lineage of your data flow is etc.

3. If you have done data warehousing in the past without a DWA tool, and you have also used a DWA tool recently, would you use a DWA tool again for your next data warehouse project?

The same "it depends" applies here. It depends on the project. If I expect a lot of data or a lot of complexity, no I wouldn't but I might look at BIML to generate SSIS Packages to avoid repeating tasks.
If it's a simple project, a small source system or I need to have key users at the customer work on the project and take over support after I left, maybe yes.

4. When would you see a DWA tool as being overkill?

Overkill would when the price exceeds the benefit obviously. If it's a fit for the project, and especially if a less experienced user or power user can take over the project instead of paying consultant fees, the cost isn't necessarily prohibitive.

5. What did you like best about using a DWA tool? What did you like least?

What I like best is also the reason to dislike it sometimes. The rigidness and decisions that are made in your place makes sure that projects are "clean" and follow a certain methodology. Especially working with multiple developers it can be a benefit that everybody is forced to work a certain way, you just open a project and know what you will find.
The fact that you are forced to work a certain way can be troublesome if you run into an issue that hasn't been foreseen by the tool.

Some other points I'd like to make

1. Support is better for the underlying technology. If you have a question on how you have to do something in SSIS or SSAS you could just google or ask here. As the DWA tools abstract that layer you need to turn to the vendor support (which may not be free)

2. Every piece of software has bugs. SQL Server's bugs are better documented online. I remember a situation where after a long discussion with our vendor's support I just ended up decompiling the DWA tool to figure out what was going on in their code and why it behaved the way it did.

3. Every DWA tool lacks the flexibility and the community SQL Server has and sooner or later you may run into the limitations the tool is bound to have. Take for example version control. DWA tools may be rolling their own, and that may work more or less, but if you have a simple visual studio project you can add it in your TFS where the rest of your code lives, use BIDShelper to get readable diff's etc. If you need stored procedures in SSAS you'll likely be out of luck too.

Answer 2

Eckerson's list is poorly researched. There is a more comprehensive catalogue of data warehouse automation tools on our web site, at http://ajilius.com/competitors.

I'll answer your questions from a vendor perspective.

1. Our customers report significant savings in project time. Most DWA products are written by data warehouse people who first want to save their OWN time, then commercialise their products. I can't think of any DWA tool that actually takes longer to build a given scenario, compared with any ETL tool.

2. We had two failures in our first year of operation. One was a customer who tried to use our product for a methodology for which it was not designed, we now emphasise the methodology (Kimball) during our sales conversations. The other was a shortcoming in our product, where (at the time) we did not handle bridging tables correctly. Both customers received a full refund, and we now make a 30-day full product trial a feature of our sales before payment is made.

3. I used to sell data warehousing workloads for a global database vendor, including the vendor's ETL tool. I have also used independent ETL tools on a number of warehouse projects. I would never go back to using ETL instead of a DWA tool, even if our own product did not exist and I was forced to use a competitor's tool.

4. Never. Well ... if you're building a data warehouse, I'd expect the tool to be used end-to-end. We've had a couple of use cases where the tool was only used for extract-and-load, but the customer wrote their own transform code. This MAY have been overkill, but our price point (see below) was such that it was cost justified.

5. My favourite features are the ability to show a customer what they asked for, then quickly change the solution as they change their mind; and the generation of full documentation at any point in time.

You mentioned enormous investments. That is only true of the legacy products in the market. Our product (Ajilius) has a site licence of USD5,000 per year, with unlimited users, unlimited servers, and unlimited databases. Other products like Leapfrog, and possibly Dimodelo (pricing changed recently) are also good products at low cost. Products like Quipu have a free version, as does Optimal ODE (in development), and I think Varigence BIML might also have a freemium model.

I hope this doesn't seem too much like an advertisement, but it is an honest answer from the experience of someone who has worked with a number of Data Warehouse Automation products.