Tuesday, July 12, 2011

Start with data warehouse or datamart?

This discussion on LinkedIn presents many ideas about how to start a Business Intelligence (BI) project when there is no existing data warehouse or data mart.  There aren't many companies that haven't got some sort of BI solution already, so I don't think there will be many times this question will need to be answered.

But, in the case where a brand new BI solution is being developed, here are my preferences:
  1. Design the BI solution for the future.
    This means planning ahead so that whatever you start with, you are planning for future enterprise data standards, naming conventions, global uniqueness, data quality, and data governance.  Do not operate in a business silo.
  2. ROI is important.
    While it is difficult to prove a return on investment for a non-tangible product such as BI, most business funders of a BI solution have a niche perspective they want results for quickly.  Since building an Enterprise Data Warehouse (EDW) averages about 18 months, focused business data marts (DM) can be completed relatively quickly in six months or less.
  3. Build both DW (to become EDW) and DM.
    When starting a new BI solution, don't consider an "either / or" solution.  A DM should get its data from the data warehouse (DW), which should be designed for the future EDW.  So, bring the master and transactional data from the source-of-record (SOR) database system into a small DW first.  This small DW could have only a few subject areas yet still be the beginning of the future EDW.  From the small DW, which should be designed as a normalized model in at least 3NF, create the DM that is designed as star (dimensional) model.  The advantage of this approach is that the SOR systems only need to populate a single DW (to become future EDW) and all DM created will be sourced from the same source of truth.  Thus they will all start with the same data and this will reduce the chances of different metrics being reported from different DM.

No comments:

Post a Comment