There are quite a few Data Warehouse Automation tools on the marketplace right now, but like many things, they are not all created equal. Here are some things you should know if evaluating products from different vendors.
1) Some Tools Don’t Automate Everything.
Some Data Warehouse Automation tools make the same mistake that most developers make – and that is to make the star-schema for reporting the sole objective. That’s fine for a simple data source, but when you need to combine data from multiple data sources, things get complicated. Some tools take you through a convoluted staging process to combine the data before creating the star-schema. That’s not very automated. Your Data Warehouse Automation tool should automate everything without the need for additional data modelling work, mapping or ETL coding.
2) Some Tools Require Lots of Consulting Work to Achieve the Result.
Ask this simple question of your Data Warehouse Automation software vendor: – “How many consultants do you have?” A large team of consultants suggests their tool isn’t automating much, or their product is complex to use. The whole point of Data Warehouse Automation is that it should be sufficiently managed by your own in-house team and be easy to use without having to call upon external consultants every time you need a small job done.
3) Third Normal Form or Data Vault before Star Schema?
If you are planning to build your Data Warehouse properly, then your chosen Data Warehouse Automation tool should automate any or all of the above. Some Data Warehouse Automation tools still require you to manually design the target model, and use their tool to populate it. That isn’t automation – you might as well go back to using an ETL tool. A good Data Warehouse Automation tool will automate the model design AND the code to populate it – allowing you to choose between Third Normal Form and Data Vault, plus of course, Star-Schema.
4) Target Databases for Your Data Warehouse.
Many Data Warehouse Automation tools restrict you to just one target database platform, while others will allow you to create Data Warehouses on many more. You may desire, in the future, to move to a different database platform (say, from DB2 to SQL Server, or from Oracle to Teradata), and so you may need a Data Warehouse Automation tool that offers you future migration options.
5) Scheduling with Dependencies
For any Data Warehouse project, data needs to be loaded at certain times and in a certain sequence. For example, when combining data from multiple sources, you may need to have all the data loaded first before you can start building your Third Normal Form tables, and they must be updated before you can update your star-schemas for reporting. An enterprise-class Data Warehouse Automation tool will understand these dependencies and automate all the required processes and run them in the correct order.
Summary
ETL tools introduce a lot of costly delays and risk into your Business Intelligence project. Worse, they expect your business users to declare their requirements in advance of development, and so the inevitable changes are going to take a long time to fix. The bottom line is, you cannot be Agile with ETL tools and their traditional Waterfall approach they force you to follow. If your traditional Data Warehouse project is estimated to take six months, the same project should be completed in a few weeks with a good Data Warehouse Automation tool.