Sunday, 8 June 2014

Build a data warehouse


Before deciding to build a data warehouse for your organization, you need to ask the following basic and fundamental questions and address the relevant issues:
  • Top-down or bottom-up approach?
  • Enterprise-wide or departmental?
  • Which first—data warehouse or data mart?
  • Build pilot or go with a full-fledged implementation?
  • Dependent or independent data marts?

Top-Down Approach

The advantages of this approach are:
  1. A truly corporate effort, an enterprise view of data
  2. Inherently architected—not a union of disparate data marts
  3.  Single, central storage of data about the content
  4. Centralized rules and control
  5. May see quick results if implemented with iterations
The disadvantages are:
  1. Takes longer to build even with an iterative method
  2. High exposure/risk to failure
  3. Needs high level of cross-functional skills
  4. High outlay without proof of concept
Bottom-Up Approach

The advantages of this approach are:
  1. Faster and easier implementation of manageable pieces
  2. Favorable return on investment and proof of concept
  3. Less risk of failure
  4. Inherently incremental; can schedule important data marts first
  5. Allows project team to learn and grow
The disadvantages are:
  1. Each data mart has its own narrow view of data
  2. Permeates redundant data in every data mart
  3. Perpetuates inconsistent and irreconcilable data
  4. Proliferates unmanageable interfaces

DBT - Models

Models are where your developers spend most of their time within a dbt environment. Models are primarily written as a select statement and ...