data warehouse automation tool: benefit or bane? 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?


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?
If the DWA tool resulted in the failure of your data warehouse project, what were the reasons?
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?
When would you see a DWA tool as being overkill? 
What did you like best about using a DWA tool? What did you like least?
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.


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.


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.


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.


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.


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


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)
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.
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.