Recently I came across an automated data warehouse builder software called Dimodelo, based in Brisbane. Dimodelo is an SSIS plugin on SSDT. It is a new company with unknown financial strength, but their product is excellent. It is the best warehouse builder in the market today, better than the three software already in the market (WhereScape, Insource and Kalido) and as good as Effektor.
Dimodelo creates the data warehouse tables, including staging tables, and create an SSIS package which does the ETL to populate the DW tables. Yes we need to point it where the source tables are, what SCD we’d like, what is the business key, etc. But it is amazingly good and very user friendly. It is more modern and user friendly than Red and Kalido, and it creates SSIS packages, which is a big plus for a SQL Server shops. It is as user friendly and as modern as Effektor who is based in Copenhagen, which is also built for SQL Server, with integrated interface to SSRS and SSAS, not just SSIS (so yes, Effektor is more complete than Dimodelo in terms of BI stack).
Back to the article I just wrote, “Choosing an ETL Tool” (link), we should not consider vendors who can’t demonstrate financial strength, no matter how good their software is. But as a good start up I think and I hope Dimodello will be able to demonstrate this.
Dimodelo said in their website that their average development time for a fact or a dimension table is 6.8 hours. In my experience, doing it manually in SSIS takes 1-3-5 for a dimension (1 day for a simple dimension, 3 days for medium and 5 days for a complex dimension) and 3-5-7 for a fact table (3 days for a simple fact table, 5 days for medium and 7 days for a complex dimension). This is development + testing but excluding design. So 6.8 hour is very impressive. But perhaps it was tested on simple dimensions, not complex fact tables?
This is the 5th time I heard about a software which automates the development of a data warehouse. A DW Automation software can build simple DWs in a standard way. But for complex DWs, I don’t think they will be able to cope. But still, there is a lot of values to be gained by using them. We can jump start the development in a big way, saving months of initial DW development effort (saving 3 months x 4 people x $500/day is equal to $132k). When we use these products, the onus shifted to the architecture and design, which now takes the longest time, instead of the development itself.
Examples of “complex” things in DW development are: loading data from message queue, a normalised data warehouse (link, link), accumulating snapshots (link, link, link), distinct attribute dimension (link, link), connecting fact tables (link), deduplication (link), initial data load (link), initialising a new attribute in SCD type 2 (link), a data warehouse without surrogate keys (link), flip flopping in dimension tables (link), partitioning fact tables (link), processing past data (link). Most of the above are issues in dimensional warehouses, but apart from dimensional DWs these days we also have data lakes, Hadoop-based warehouses, Inmon’s CIFs (ODSes, EDW, Normalised DW, etc), real time DWs, MPP-based DWs, Cloud-based DWs, EIIs, and the latest incarnation of temporal-based DW in SQL Server 2016, which I don’t think DW Automation software can handle well either.
So in the end, as our DW developed into a more complex one, we will have to alter the ETL code manually (we will have to do some manual development, along with the automated one), but the $100-200k saving development jump-start is very valuable in any DW project. So DW automation software still give us a lot of value. The bigger the DW project, the more cost saving can be made by using DW automation software.
The value is even greater if your business is consultancy, i.e. you build DWs for companies. Because you will make this cost saving on every single project, every single client. And you can choose to pass that cost saving to your client, or to add to your bottom line (meaning the Profit Before Tax line in your Cash Flow).
Note: I do not receive any incentive or reward, in monetary term or otherwise from Dimodelo or its related partners, in writing this article.