A BI solution without an underlying DW is like a house on stilts. Whether it will survive the high waves of analytical queries is anybody’s guess. Some vendors have convinced their clients to layer their BI technology directly on operational source systems. This puts consultants like me in a difficult position – telling clients that have already bought into that vision that they are heading for disaster. The BI tool can do it all – so why do we need to over-engineer this again?
Not putting a well designed DW in the DWBI architecture is a huge and costly mistake that will result in failure in meeting the performance criterion that is expected of BI. Operational source systems are not designed to handle the load of analytical queries. They have a spider web of tiny little tables that are good at handling single operations related to processes, and storing an attribute, such as Customer Name or Address, in one place only, so that any updates are to that one point of truth.
To support analysis, you need special data structures, called Facts, and Dimensions around it. Take the Opportunity Lifecycle of a Sales process, for example. The process starts with a Lead, which goes through many phases, eventually ending in an Order if successful. An easy way to track that is to have a Fact with each Phase as a Date attribute. You can calculate Days to Close by simply subtracting one Date from another. You can also track the probability of achieving every phase, alongside the Date. You can now easily create averages across Opportunity Type, Product Line, etc. in a BI tool. Imagine doing this against a database that has each Phase stored in a separate table. You will have to stitch across each table, opportunity by opportunity, and then average across. You will have to virtually construct the “Fact”, using standard SQL. The performance will obviously be sub-par.
Just because a BI tool can interact with any database does not mean you can put it on an operational data source and expect it to perform well with highly summarized queries that have to stitch together bits and pieces of information from across the database. For a well-performing BI solution, you need a strong foundation. That is the role the DW plays. Don’t get swayed by the idea of doing away with DW. Get real. Technology isn’t there yet.